今回はdockerを使って、postgresサーバの環境を準備する方法について書いてみます。
dockerの環境が構築済みであることが前提です。 未構築の方はこちらもどうぞ。
Postgresサーバ
dockerを使ってサーバをたてます。
オフィシャルイメージはこちらから確認できます。
起動方法はシンプルで、以下のコマンドでpostgresサーバの起動を行います。
docker run -p 5432:5432 -e POSTGRES_PASSWORD=mysecretpassword -d postgres
ちょっとだけ補足を入れておくと
-p 5432:5432を指定することで、localhost:5432に対するパケットを受けると、POSTGRESコンテナの5432へポートフォワードされます。よくわからない場合は、dockerでTCPとかUDPをしたい時のおまじないであると考えてください。-e POSTGRES_PASSWORD=mysecretpasswordを指定することで、環境変数POSTGRES_PASSWORDに値mysecretpasswordをセットします。POSTGRESコンテナは、これをユーザpostgresのパスワードであると解釈します。-dを指定することで、バックグラウンドで起動させておきます。
Postgresクライアント
次に、このサーバに接続して、動作確認をするために、クライアントソフトpsqlをローカル環境にインストールします。
いつものごとく、homebrewを使ってインストールを行いますが、入れるパッケージはlibpqです。
libpqの詳細はこちらを参照していただければと思いますが、大事な箇所だけ抜き出すと
libpq is the C application programmer's interface to PostgreSQL. libpq is a set of library functions that allow client programs to pass queries to the PostgreSQL backend server and to receive the results of these queries.
あとは
libpq is also the underlying engine for several other PostgreSQL application interfaces, including those written for C++, Perl, Python, Tcl and ECPG.
あたりでしょうか。要するに、Cで書かれたクライアントソフトであり、他の言語からも使用されることがある、くらいの理解を私はしています。
ということで、インストールを以下のコマンドで行います。
brew install libpq
バイナリの置かれるパスを、環境変数PATHに追加しろ、と指示がありますので、これも実行します。 環境によってはzshrcではなくzprofileが良いかもしれませんので、適宜置き換えてください。
echo 'export PATH="/opt/homebrew/opt/libpq/bin:$PATH"' >> ~/.zshrc
注意：homebrewでインストールしたバイナリがどこに置かれるかは環境によって異なりそうです。画面に表示されたコマンドを実行してください。
その後シェルの再起動、もしくは設定ファイルの再読み込みを行って、ようやくpsqlが実行できるようになるはずです。
なお、設定ファイルの再読み込みは以下です。
source ~/.zshrc
以下のコマンドでインストールされていることを確認します。
psql --version
以下のようなメッセージが出れば成功です。
psql (PostgreSQL) 15.2
接続確認
では、満を持してサーバへの接続確認を行いましょう
まずは、以下のコマンドで接続をします。
psql -h localhost -p 5432 -U postgres
続いて、パスワードを尋ねられるので、環境変数にセットしたmysecretpasswordを入力します。
以下の画面が表示されたら成功です。あとはお好きなSQLを書いて楽しみましょう。
postgres=#
GUIをインストールする
おまけで、GUIのインストール方法も書いておきます。 （実のところ、私はあまり使ったことがありません）
homebrewで以下のツールをインストールしましょう
brew install --cask pgadmin4
参考までに、homebrewのformulaeはこちら、本家のサイトはこちら。
起動するとこんな感じになります。
なお、マスタパスワードの作成を求められると思いますので、適当に作成しておきましょう。
Serversを右クリック -> Register -> Serverを選んで
サーバを識別するための任意の名前と
ホスト名もしくはアドレス、そしてパスワード（上の例ではmysecretpassword）をいれます。
接続成功したのち、右クリックメニューなどでテーブルを作成できます。
その後、Create ScriptでSQLスクリプトを作成し、お好きなSQLを書いて楽しみましょう。
Pythonスクリプトを実行してみる
おまけその２として、PythonからPostgresに接続してみます。
Pythonはインストールされている前提です。
psycopg2のインストール
以下を実行して、Pythonから接続するためのツールをインストールします。
pip3 install psycopg2-binary
なお、以下のコマンドでもソースのダウンロード -> clangでビルド -> インストールできるようです。
pip3 install psycopg2
が、私の環境ではssl関連のライブラリが参照できない？などの理由でビルドに失敗しているようでした。 調べれば解決できそうでしたが、面倒だったのでビルド済みのバイナリだけを取ってくることにしました。
データを書き込む
「ポケモン csv」などで検索し、ヒットしたデータを使用させていただき、データベースに格納してみたいと思います。
import psycopg2 import csv connection = psycopg2.connect(host='localhost', user='postgres', password='mysecretpassword', database='pokemon') with connection: with connection.cursor() as cursor: # ポケモンテーブルを初期化 sql = "DROP TABLE IF EXISTS monsters;" cursor.execute(sql) sql = "CREATE TABLE monsters (\ id serial PRIMARY KEY, \ pokedex_number smallint, \ name varchar(255), \ base_total smallint, \ attack smallint, \ defense smallint, \ sp_attack smallint, \ sp_defense smallint, \ speed smallint, \ hp smallint \ );" cursor.execute(sql) # タイプテーブルを初期化 sql = "DROP TABLE IF EXISTS types;" cursor.execute(sql) sql = "CREATE TABLE types (\ id serial PRIMARY KEY, \ pokedex_number smallint, \ type varchar(255) \ );" cursor.execute(sql) # ポケモンテーブルにデータを挿入 with open('pokemon.csv') as f: for row in csv.reader(f): monsterinfo = row[:9] typeinfo = row[-2:] if monsterinfo[0] == 'pokedex_number': continue sql = "INSERT INTO monsters (pokedex_number, name, base_total, attack, defense, sp_attack, sp_defense, speed, hp) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)" cursor.execute(sql, monsterinfo) for t in typeinfo: if t == '': continue sql = "INSERT INTO types (pokedex_number, type) VALUES (%s, %s)" cursor.execute(sql, (monsterinfo[0], t)) # 相性テーブルを初期化 sql = "DROP TABLE IF EXISTS affinities;" cursor.execute(sql) sql = "CREATE TABLE affinities (\ id serial PRIMARY KEY, \ attacker varchar(255), \ defender varchar(255), \ magnifier real \ );" cursor.execute(sql) # 相性テーブルにデータ挿入 with open('affinity.csv') as f: header = [] for i, row in enumerate(csv.reader(f)): if i == 0: header = [r.strip() for r in row] continue t0 = row[0].strip() for t1 in header[1:]: t1 = t1.strip() mag = float(row[header.index(t1)]) sql = "INSERT INTO affinities (attacker, defender, magnifier) VALUES (%s, %s, %s)" cursor.execute(sql, (t0, t1, mag)) connection.commit()
pokemon.csvはこんな感じ。
pokedex_number,name,base_total,attack,defense,sp_attack,sp_defense,speed,hp,type1,type2 1,フシギダネ,318,49,49,65,65,45,45,grass,poison 2,フシギソウ,405,62,63,80,80,60,60,grass,poison 3,フシギバナ,625,100,123,122,120,80,80,grass,poison 4,ヒトカゲ,309,52,43,60,50,65,39,fire, 5,リザード,405,64,58,80,65,80,58,fire, 6,リザードン,634,104,78,159,115,100,78,fire,flying
以下を参考にさせていたきました。ありがとうございます。
https://gist.github.com/leoyuholo/b12f882a92a25d43cf90e29812639eb3
affinity.csvはこんな感じ。
" ",normal, fire, water, electric, grass,ice, fighting, poison, ground, flying,psychic, bug, rock,ghost, dragon,dark, steel, fairy normal,1,1,1,1,1,1,1,1,1,1,1,1,0.5,0,1,1,0.5,1 fire,1,0.5,0.5,1,2,2,1,1,1,1,1,2,0.5,1,0.5,1,2,1 water,1,2,0.5,1,0.5,1,1,1,2,1,1,1,2,1,0.5,1,1,1 electric,1,1,2,0.5,0.5,1,1,1,0,2,1,1,0.5,1,0.5,1,1,1 grass,1,0.5,2,1,0.5,1,1,0.5,2,0.5,1,0.5,2,1,0.5,1,0.5,1 ice,1,0.5,0.5,1,2,0.5,1,1,2,2,1,1,1,1,2,1,0.5,1 fighting,2,1,1,1,1,2,1,0.5,1,0.5,0.5,1,2,1,1,2,2,0.5 poison,1,1,1,1,2,1,1,0.5,0.5,1,1,1,0.5,0.5,1,1,0,2 ground,1,2,1,2,0.5,1,1,2,1,1,1,0.5,2,1,1,1,2,1 flying,1,1,1,0.5,2,1,2,1,1,1,1,2,0.5,1,1,1,0.5,1 psychic,1,1,1,1,1,1,2,2,1,1,0.5,1,1,1,1,1,0.5,1 bug,1,0.5,1,1,2,1,0.5,0.5,1,0.5,2,1,1,0.5,1,2,0.5,0.5 rock,1,2,1,1,1,2,0.5,1,0.5,2,1,2,1,1,1,1,0.5,1 ghost,0,1,1,1,1,1,1,1,1,1,2,1,1,2,1,0.5,1,1 dragon,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2,1,0.5,0 dark,1,1,1,1,1,1,0.5,1,1,1,2,1,1,2,1,0.5,1,0.5 steel,1,0.5,0.5,0.5,1,2,1,1,1,1,1,1,2,1,1,1,0.5,2 fairy,1,0.5,1,1,1,1,2,0.5,1,1,1,1,1,1,2,2,0.5,1
こちらを使用させていただきました、ありがとうございます。
https://sironekolab.com/archives/6012
ちょっと遊んでみる
ポケモンのリストをとりあえず表示してみます。
種族値の合計が600を超えるポケモンで、ほのおタイプを持つものを表示してみたいと思います。SQL的に正しいかはよくわかりません。
ポケモンのタイプを横持ちに変換
