データベース – notemite.com

テーブルの作成
csv ファイルをテーブルに copy (insert)
（ついでに）inspectdb でモデル情報を確認

1. テーブルの作成

ターミナルにコマンド「psql データベース名」を入力し対象のデータベースを開いて、別で書いておいた create 文のコピペで処理可能。最後に「;」をつけるのを忘れない様に気をつけてください。

下記の例では「yt_trend_channel」というテーブルを作りました。

sample_db=# create table yt_trend_channel (
channel_id varchar(30),
view_count varchar(20),
join_date varchar(20),
country varchar(20),
channel_name varchar(100),
thumbnail_url varchar(50),
subscriber_count varchar(20),
data_date varchar(8)
);

2. csv ファイルをテーブルに copy (insert)

csv ファイルをテーブルに copy します。

psql を起動する前に対象のファイルのディレクトリへ cd しておくと、単純にファイル名を渡すだけで済むのでわかりやすいです。

下記の例では先ほど create した yt_trend_channel テーブルに、別で用意した yt_trend_channel.csv という csv ファイルのデータを copy (insert) しています。

sample_db=# \copy yt_trend_channel from 'yt_trend_channel.csv' with csv
COPY 4259
sample_db=#

全部で 4259 件のレコードが copy されました。

select クエリで確認します。

sample_db=# select * from yt_trend_channel;
        channel_id        | view_count  | join_date  |    country     |                            channel_name                             |                   thumbnail_url                    | subscriber_count | data_date 
--------------------------+-------------+------------+----------------+---------------------------------------------------------------------+----------------------------------------------------+------------------+-----------
 UC9OvLVXb-okaVtoK9V8Biwg | 103499474   | 2018/05/23 | 日本           | ABEMA バラエティ【公式】                                            | https://yt3.ggpht.com/ytc/AAUvwnjBKC3y2KycvPfvHdn1 |  19.9万人        | 20210104
 UCZZ0UGjWsRdM8_5bsqtxYaQ | 190130764   | 2015/10/02 | 日本           | RIZIN FIGHTING FEDERATION                                           | https://yt3.ggpht.com/ytc/AAUvwnjDj0e-hS0um-ZKG1Tc |  35万人          | 20210104
 UCaminwG9MTO4sLYeC3s6udA | 3201321432  | 2016/03/18 | 日本           | ヒカル（Hikaru）                                                    | https://yt3.ggpht.com/ytc/AAUvwnhBYwfz6DycI4xBURsZ |  428万人         | 20210104
 UCPn_e-7LD7SbzIBxdU_XBnQ | 21113326    | 2020/07/30 | 日本           | あーたろチャンネル                                                  | https://yt3.ggpht.com/ytc/AAUvwngZDvNAYRF_qgPfOgy9 |  18.7万人        | 20210104
 UC4-TMrb7Mm4KnYx1VsUgcJA | 135489838   | 2012/08/04 | 日本           | あいり                                                              | https://yt3.ggpht.com/ytc/AAUvwngy1VncfSpEQkWmZXYK |  26.8万人        | 20210104

ちなみに MySQL の場合は下記の様な感じ

load data local infile  'yt_trend_channel.csv'
into table yt_trend_channel
fields terminated by ',' 
enclosed by '"'
;

3. （ついでに Django）inspectdb でモデル情報を確認

実はそもそも Django で使うテーブルなので「python manage.py inspectdb テーブル名」でモデル情報を確認してみます。

% python manage.py inspectdb yt_trend_channel
# This is an auto-generated Django model module.
# You'll have to do the following manually to clean this up:
#   * Rearrange models' order
#   * Make sure each model has one field with primary_key=True
#   * Make sure each ForeignKey has `on_delete` set to the desired behavior.
#   * Remove `managed = False` lines if you wish to allow Django to create, modify, and delete the table
# Feel free to rename the models, but don't rename db_table values or field names.
from django.db import models


class YtTrendChannel(models.Model):
    channel_id = models.CharField(max_length=30, blank=True, null=True)
    view_count = models.CharField(max_length=20, blank=True, null=True)
    join_date = models.CharField(max_length=20, blank=True, null=True)
    country = models.CharField(max_length=20, blank=True, null=True)
    channel_name = models.CharField(max_length=100, blank=True, null=True)
    thumbnail_url = models.CharField(max_length=50, blank=True, null=True)
    subscriber_count = models.CharField(max_length=20, blank=True, null=True)
    data_date = models.CharField(max_length=8, blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'yt_trend_channel'
%

class 文を models.py にコピーすると Django でモデルとして使える様になります。その際に 1 つ「primary_key=True」のフィールドを持つ必要がありますので確認してください。

Homebrew を使って PostgreSQL の最新バーションをインストールします。

実行環境：maxOS Big Sur バージョン 11.2.2

PostgreSQL をインストールする
PostgreSQL を起動・終了する

PostgreSQL をインストールする

下記のコマンドを実行します。

% brew install postgresql

ログが表示されます。（下記のログには Homebrew 自体の自動アップデートなど余分なものも含まれてます。）

% brew install postgresql            
Updating Homebrew...
==> Auto-updated Homebrew!
Updated 1 tap (homebrew/core).
==> New Formulae
aerc                       hblock                     php@7.4
aida-header                heppdt2                    pickle
aliddns                    hexo                       pkger
ansible@2.9                htmltest                   projectm
arturo                     iconsur                    ptpython
as-tree                    indicators                 pyqt-3d
atkmm@2.28                 inframap                   pyqt-builder
attr                       isl@0.18                   pyqt-networkauth
aws-console                isort                      pyqt@5
aws-rotate-key             jpeg-xl                    pyside@2
bandit                     jql                        python-tabulate
bas55                      json5                      qt-libiodbc
bit-git                    k3sup                      qt-mariadb
blaze                      kcgi                       qt-mysql
bpython                    keptn                      qt-percona-server
bpytop                     khiva                      qt-postgresql
cairomm@1.14               klee                       qt-unixodbc
cargo-audit                ko                         rain
cargo-edit                 kotlin-language-server     rdkit
cargo-watch                kube-linter                regipy
cfn-flip                   kubecm                     richmd
cfn-format                 kubergrunt                 rm-improved
chars                      kubevela                   rtl_433
checkov                    lab                        rttr
cherrytree                 latino                     ruby@2.7
clash                      libbsd                     saml2aws
cloudflare-wrangler        libcap                     showkey
coin3d                     libcap-ng                  snowpack
condure                    libfuse                    spack
counterfeiter              libmd                      sqlancer
cpplint                    libnsl                     sqlc
crane                      libpinyin                  strace
crcany                     libpipeline                t-rec
crispy-doom                libprelude                 taskwarrior-tui
curlie                     librttopo                  tctl
cyrus-sasl                 libslirp                   tendermint
dasel                      libtirpc                   terracognita
datalad                    libva                      terraform@0.13
delve                      libvdpau                   tgenv
device-mapper              libvnc                     thrax
dnsx                       libxml++@4                 tm
docui                      libxml++@5                 tomcat@9
dog                        libxp                      trec_eval
dotenv-linter              linux-headers              tree-sitter
dprint                     logswan                    truffle
driftctl                   lp_solve                   utf8cpp
dstask                     lua@5.3                    v2ray
duf                        luajit-openresty           vc
easy-rsa                   luv                        vint
efm-langserver             magic_enum                 vitess
enzyme                     markdownlint-cli           vsh
evernote2md                massdns                    vulture
f3d                        md4c                       waffle
fizz                       mermaid-cli                wangle
flow-cli                   mesa-glu                   wayland
flux                       mockery                    wayland-protocols
fnm                        mpdecimal                  wllvm
forge                      msc-generator              xbitmaps
gallery-dl                 muffet                     xcb-util
gcalcli                    name-that-hash             xcb-util-cursor
gdu                        ncspot                     xcb-util-image
geph4                      nicotine-plus              xcb-util-keysyms
ghc@9                      node@14                    xcb-util-renderutil
gitlint                    nuclei                     xcb-util-wm
giza                       oakc                       xcbeautify
glab                       ocaml-zarith               xcinfo
glibc                      open-adventure             xcprojectlint
go@1.15                    openalpr                   xinput
gofumpt                    openjdk@8                  xkeyboardconfig
gojq                       openmama                   xorgrgb
googletest                 openmodelica               xray
gopass-jsonapi             or-tools                   xterm
gopls                      oras                       yh
goredo                     osmcoastline               ykdl
gosec                      overdrive                  youtube-dlc
gost                       ox                         youtubedr
gping                      parliament                 yq@3
grokj2k                    pcalc                      yubikey-agent
grokmirror                 pdftilecut                 zsh-async
gtkmm4                     pdm
h2spec                     phive
==> Updated Formulae
Updated 5020 formulae.
==> Renamed Formulae
glibmm@2.64 -> glibmm@2.66               pangomm@2.42 -> pangomm@2.46
gtk+4 -> gtk4                            prest -> prestd
ht-rust -> xh                            qt5 -> qt@5
now-cli -> vercel-cli
==> Deleted Formulae
avian                      gobby                      scw@1
boost@1.55                 godep                      stlviewer
boost@1.59                 llvm@6                     unp64
confluent-platform         meson-internal             unrar
curl-openssl               mysql-connector-c++@1.1    woboq_codebrowser
dtrx                       ori                        xspin
fmsx                       pgplot
geant4                     rmtrash

==> Downloading https://homebrew.bintray.com/bottles/icu4c-68.2.big_sur.bottle.t
==> Downloading from https://d29vzk4ow07wi7.cloudfront.net/ba2fd8c7d37025c93db6b
######################################################################## 100.0%
==> Downloading https://homebrew.bintray.com/bottles/openssl%401.1-1.1.1j.big_su
==> Downloading from https://d29vzk4ow07wi7.cloudfront.net/5725361adcd088a5b4fb2
######################################################################## 100.0%
==> Downloading https://homebrew.bintray.com/bottles/krb5-1.19.1.big_sur.bottle.
==> Downloading from https://d29vzk4ow07wi7.cloudfront.net/d544c1111503eb27b253e
######################################################################## 100.0%
==> Downloading https://homebrew.bintray.com/bottles/readline-8.1.big_sur.bottle
==> Downloading from https://d29vzk4ow07wi7.cloudfront.net/2cc3a9582e3c7e21eb3c2
######################################################################## 100.0%
==> Downloading https://homebrew.bintray.com/bottles/postgresql-13.2_1.big_sur.b
==> Downloading from https://d29vzk4ow07wi7.cloudfront.net/67a547842ae49911d301d
######################################################################## 100.0%
==> Installing dependencies for postgresql: icu4c, openssl@1.1, krb5 and readline
==> Installing postgresql dependency: icu4c
==> Pouring icu4c-68.2.big_sur.bottle.tar.gz
==> Caveats
icu4c is keg-only, which means it was not symlinked into /usr/local,
because macOS provides libicucore.dylib (but nothing else).

If you need to have icu4c first in your PATH, run:
  echo 'export PATH="/usr/local/opt/icu4c/bin:$PATH"' >> ~/.zshrc
  echo 'export PATH="/usr/local/opt/icu4c/sbin:$PATH"' >> ~/.zshrc

For compilers to find icu4c you may need to set:
  export LDFLAGS="-L/usr/local/opt/icu4c/lib"
  export CPPFLAGS="-I/usr/local/opt/icu4c/include"

==> Summary
🍺  /usr/local/Cellar/icu4c/68.2: 259 files, 72.5MB
==> Installing postgresql dependency: openssl@1.1
==> Pouring openssl@1.1-1.1.1j.big_sur.bottle.tar.gz
==> Caveats
A CA file has been bootstrapped using certificates from the system
keychain. To add additional certificates, place .pem files in
  /usr/local/etc/openssl@1.1/certs

and run
  /usr/local/opt/openssl@1.1/bin/c_rehash

openssl@1.1 is keg-only, which means it was not symlinked into /usr/local,
because macOS provides LibreSSL.

If you need to have openssl@1.1 first in your PATH, run:
  echo 'export PATH="/usr/local/opt/openssl@1.1/bin:$PATH"' >> ~/.zshrc

For compilers to find openssl@1.1 you may need to set:
  export LDFLAGS="-L/usr/local/opt/openssl@1.1/lib"
  export CPPFLAGS="-I/usr/local/opt/openssl@1.1/include"

==> Summary
🍺  /usr/local/Cellar/openssl@1.1/1.1.1j: 8,071 files, 18.5MB
==> Installing postgresql dependency: krb5
==> Pouring krb5-1.19.1.big_sur.bottle.tar.gz
==> Caveats
krb5 is keg-only, which means it was not symlinked into /usr/local,
because macOS already provides this software and installing another version in
parallel can cause all kinds of trouble.

If you need to have krb5 first in your PATH, run:
  echo 'export PATH="/usr/local/opt/krb5/bin:$PATH"' >> ~/.zshrc
  echo 'export PATH="/usr/local/opt/krb5/sbin:$PATH"' >> ~/.zshrc

For compilers to find krb5 you may need to set:
  export LDFLAGS="-L/usr/local/opt/krb5/lib"
  export CPPFLAGS="-I/usr/local/opt/krb5/include"

==> Summary
🍺  /usr/local/Cellar/krb5/1.19.1: 162 files, 5.2MB
==> Installing postgresql dependency: readline
==> Pouring readline-8.1.big_sur.bottle.tar.gz
==> Caveats
readline is keg-only, which means it was not symlinked into /usr/local,
because macOS provides BSD libedit.

For compilers to find readline you may need to set:
  export LDFLAGS="-L/usr/local/opt/readline/lib"
  export CPPFLAGS="-I/usr/local/opt/readline/include"

==> Summary
🍺  /usr/local/Cellar/readline/8.1: 48 files, 1.6MB
==> Installing postgresql
==> Pouring postgresql-13.2_1.big_sur.bottle.tar.gz
==> /usr/local/Cellar/postgresql/13.2_1/bin/initdb --locale=C -E UTF-8 /usr/loca
==> Caveats
To migrate existing data from a previous major version of PostgreSQL run:
  brew postgresql-upgrade-database

This formula has created a default database cluster with:
  initdb --locale=C -E UTF-8 /usr/local/var/postgres
For more details, read:
  https://www.postgresql.org/docs/13/app-initdb.html

To have launchd start postgresql now and restart at login:
  brew services start postgresql
Or, if you don't want/need a background service you can just run:
  pg_ctl -D /usr/local/var/postgres start
==> Summary
🍺  /usr/local/Cellar/postgresql/13.2_1: 3,218 files, 42.6MB
==> `brew cleanup` has not been run in 30 days, running now...
Removing: /Users/ユーザー名/Library/Caches/Homebrew/mysql--8.0.21_1.catalina.bottle.tar.gz... (80.4MB)
Removing: /usr/local/Cellar/openssl@1.1/1.1.1h... (8,067 files, 18.5MB)
Removing: /Users/ユーザー名/Library/Caches/Homebrew/openssl@1.1--1.1.1h.catalina.bottle.tar.gz... (5.4MB)
Removing: /Users/ユーザー名/Library/Caches/Homebrew/protobuf--3.13.0.catalina.bottle.tar.gz... (4.8MB)
Removing: /Users/ユーザー名/Library/Logs/Homebrew/mysql... (1.1KB)
Removing: /Users/ユーザー名/Library/Logs/Homebrew/protobuf... (64B)
Pruned 1 symbolic links and 2 directories from /usr/local
==> Upgrading 2 dependents:
mysql 8.0.21_1 -> 8.0.23_1, wget 1.20.3_2 -> 1.21.1
==> Upgrading mysql 8.0.21_1 -> 8.0.23_1 
==> Downloading https://homebrew.bintray.com/bottles/protobuf-3.15.5.big_sur.bot
==> Downloading from https://d29vzk4ow07wi7.cloudfront.net/872baaeb92aaaaa8d2634
######################################################################## 100.0%
==> Downloading https://homebrew.bintray.com/bottles/mysql-8.0.23_1.big_sur.bott
==> Downloading from https://d29vzk4ow07wi7.cloudfront.net/9e9e4b5bfcad47adfbf7f
######################################################################## 100.0%
==> Installing dependencies for mysql: protobuf
==> Installing mysql dependency: protobuf
==> Pouring protobuf-3.15.5.big_sur.bottle.tar.gz
==> Caveats
Emacs Lisp files have been installed to:
  /usr/local/share/emacs/site-lisp/protobuf
==> Summary
🍺  /usr/local/Cellar/protobuf/3.15.5: 211 files, 17.7MB
==> Installing mysql
==> Pouring mysql-8.0.23_1.big_sur.bottle.tar.gz
==> Caveats
We've installed your MySQL database without a root password. To secure it run:
    mysql_secure_installation

MySQL is configured to only allow connections from localhost by default

To connect run:
    mysql -uroot

To have launchd start mysql now and restart at login:
  brew services start mysql
Or, if you don't want/need a background service you can just run:
  mysql.server start
==> Summary
🍺  /usr/local/Cellar/mysql/8.0.23_1: 298 files, 297.7MB
Removing: /usr/local/Cellar/mysql/8.0.21_1... (290 files, 291.2MB)
==> Upgrading wget 1.20.3_2 -> 1.21.1 
==> Downloading https://homebrew.bintray.com/bottles/gettext-0.21.big_sur.bottle
==> Downloading from https://d29vzk4ow07wi7.cloudfront.net/a025e143fe3f5f7e24a93
######################################################################## 100.0%
==> Downloading https://homebrew.bintray.com/bottles/wget-1.21.1.big_sur.bottle.
==> Downloading from https://d29vzk4ow07wi7.cloudfront.net/e6ea2a50b8196206f7072
######################################################################## 100.0%
==> Installing dependencies for wget: gettext
==> Installing wget dependency: gettext
==> Pouring gettext-0.21.big_sur.bottle.tar.gz
🍺  /usr/local/Cellar/gettext/0.21: 1,953 files, 19.8MB
==> Installing wget
==> Pouring wget-1.21.1.big_sur.bottle.tar.gz
🍺  /usr/local/Cellar/wget/1.21.1: 88 files, 4MB
Removing: /usr/local/Cellar/wget/1.20.3_2... (50 files, 4.0MB)
==> Checking for dependents of upgraded formulae...
==> No broken dependents found!
==> Caveats
==> icu4c
icu4c is keg-only, which means it was not symlinked into /usr/local,
because macOS provides libicucore.dylib (but nothing else).

If you need to have icu4c first in your PATH, run:
  echo 'export PATH="/usr/local/opt/icu4c/bin:$PATH"' >> ~/.zshrc
  echo 'export PATH="/usr/local/opt/icu4c/sbin:$PATH"' >> ~/.zshrc

For compilers to find icu4c you may need to set:
  export LDFLAGS="-L/usr/local/opt/icu4c/lib"
  export CPPFLAGS="-I/usr/local/opt/icu4c/include"

==> openssl@1.1
A CA file has been bootstrapped using certificates from the system
keychain. To add additional certificates, place .pem files in
  /usr/local/etc/openssl@1.1/certs

and run
  /usr/local/opt/openssl@1.1/bin/c_rehash

openssl@1.1 is keg-only, which means it was not symlinked into /usr/local,
because macOS provides LibreSSL.

If you need to have openssl@1.1 first in your PATH, run:
  echo 'export PATH="/usr/local/opt/openssl@1.1/bin:$PATH"' >> ~/.zshrc

For compilers to find openssl@1.1 you may need to set:
  export LDFLAGS="-L/usr/local/opt/openssl@1.1/lib"
  export CPPFLAGS="-I/usr/local/opt/openssl@1.1/include"

==> krb5
krb5 is keg-only, which means it was not symlinked into /usr/local,
because macOS already provides this software and installing another version in
parallel can cause all kinds of trouble.

If you need to have krb5 first in your PATH, run:
  echo 'export PATH="/usr/local/opt/krb5/bin:$PATH"' >> ~/.zshrc
  echo 'export PATH="/usr/local/opt/krb5/sbin:$PATH"' >> ~/.zshrc

For compilers to find krb5 you may need to set:
  export LDFLAGS="-L/usr/local/opt/krb5/lib"
  export CPPFLAGS="-I/usr/local/opt/krb5/include"

==> readline
readline is keg-only, which means it was not symlinked into /usr/local,
because macOS provides BSD libedit.

For compilers to find readline you may need to set:
  export LDFLAGS="-L/usr/local/opt/readline/lib"
  export CPPFLAGS="-I/usr/local/opt/readline/include"

==> postgresql
To migrate existing data from a previous major version of PostgreSQL run:
  brew postgresql-upgrade-database

This formula has created a default database cluster with:
  initdb --locale=C -E UTF-8 /usr/local/var/postgres
For more details, read:
  https://www.postgresql.org/docs/13/app-initdb.html

To have launchd start postgresql now and restart at login:
  brew services start postgresql
Or, if you don't want/need a background service you can just run:
  pg_ctl -D /usr/local/var/postgres start
==> protobuf
Emacs Lisp files have been installed to:
  /usr/local/share/emacs/site-lisp/protobuf
==> mysql
We've installed your MySQL database without a root password. To secure it run:
    mysql_secure_installation

MySQL is configured to only allow connections from localhost by default

To connect run:
    mysql -uroot

To have launchd start mysql now and restart at login:
  brew services start mysql
Or, if you don't want/need a background service you can just run:
  mysql.server start
%

コマンド「psql --version」を実行し、PostgreSQL のバージョンが正常に表示されれば問題ありません。

% psql --version
psql (PostgreSQL) 13.2

PostgreSQL を起動・終了する

「brew services start postgresql」で起動します。初回起動時のみ下記の様なログが表示されますが、２回目以降は表示されません。

% brew services start postgresql
==> Tapping homebrew/services
Cloning into '/usr/local/Homebrew/Library/Taps/homebrew/homebrew-services'...
remote: Enumerating objects: 20, done.
remote: Counting objects: 100% (20/20), done.
remote: Compressing objects: 100% (20/20), done.
remote: Total 1141 (delta 8), reused 0 (delta 0), pack-reused 1121
Receiving objects: 100% (1141/1141), 331.40 KiB | 5.18 MiB/s, done.
Resolving deltas: 100% (485/485), done.
Tapped 1 command (41 files, 420.9KB).
==> Successfully started `postgresql` (label: homebrew.mxcl.postgresql)
%

コマンド「psql -l」を実行するとデータベースの一覧が参照できます。

% psql -l
                                         List of databases
   Name    |      Owner       | Encoding | Collate | Ctype |           Access privileges           
-----------+------------------+----------+---------+-------+---------------------------------------
 postgres  | ユーザー名         | UTF8     | C       | C     | 
 template0 | ユーザー名         | UTF8     | C       | C     | =c/ユーザー名                  +
           |                  |          |         |       | ユーザー名=CTc/ユーザー名
 template1 | ユーザー名         | UTF8     | C       | C     | =c/ユーザー名                  +
           |                  |          |         |       | ユーザー名=CTc/ユーザー名
(3 rows)
%

「brew services stop postgresql」で終了

% brew services stop postgresql
Stopping `postgresql`... (might take a while)
==> Successfully stopped `postgresql` (label: homebrew.mxcl.postgresql)
%

カテゴリー: データベース

Mac ローカルにインストールした MySQL を使う方法

1. MySQL を起動する

2. MySQL に接続する

3. MySQL を遮断する

4. MySQL を終了する

【mac】PostgreSQL にテーブルを作って csv のデータを挿入した時のメモ