🔰 PostgreSQLをPythonで利用しよう(windows11)

Pythonの環境構築の説明は省きます。

想定環境

OS: windows11
Python: Python3.7

PostgreSQLのインストール

2022/5月時点
↓ PostgresSQLのダウンロードサイトです。
https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
今回は version14.3でwindows x86-64を選びます。

Nextを押していきましょう。
・デフォルトの保存先→ C:\Program Files\PostgreSQL\14

・コンポーネントはすべて入れておいて問題ないでしょう

・パスワードは忘れないようにしておきましょう。
・ポート番号のデフォルトは5432になっていますが、このまま進めます。
・Advanced OptionsのLocaleは「Japanese Japan」にしました。

これでFinishです。スタックビルダーのチェックボックスは外しても問題ありません。

pgAdmin4の設定

pgAdmin4からPostgreSQLに接続することが可能です。

PostgreSQLに接続する

windowsのアプリからpgAdmin4を開きましょう。
pgAdmin4のパスワードを設定しましょう。

次に左のブラウザの部分をクリックするとパスワードが求められますのでPostgreSQLで設定したパスワードを入力しましょう。

DBを作成しよう

Databasesを右クリックして新しいDBを作成してみましょう。

DBの設定画面でDatabaseの名前を自由につけましょう。作成した後は「save」をクリックします。

(UIから)テーブルを作成しよう。

作成したDBのSchemas→public→Tablesを右クリックして(createを選択)テーブルを作成してみましょう。

今回はテーブルの命名規則はlower snake caseに従うとします。
すべて小文字で作っておくと安全です。

Tablespace は pg_defaultを選択しておきます。

テーブル名に「user」はやめておきましょう。

行の設定もしてみましょう。

自動採番
自動採番とは、データをインサートすると自動的に値が割り振られる機能のことです。
DataTypeにserialを設定しています。

SQLで実行する場合

sql

CREATE TABLE public."user"
(
    id serial NOT NULL,
    name text NOT NULL,
    PRIMARY KEY (id)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public."user"
    OWNER to postgres;

COMMENT ON TABLE public."user"
    IS 'ユーザー情報のテーブル';

Pythonから接続する(psycopg2編)

psycopg2を使用して接続してみましょう。

pipコマンド

pip install psycopg2

pipコマンド
pipコマンドを使用してローカルにライブラリを入れてみましょう。

※パスワードの部分は自身で入力すること

sample.py

import psycopg2

# 接続情報
dsn = "dbname=pythondb host=localhost user=postgres password=パスワード"

conn = psycopg2.connect(dsn)  # コネクション
cur = conn.cursor()  # カーサー

cur.execute("select version()") # クエリの実行
print(cur.fetchone()) 

# コネクション等は閉じる。
cur.close()
conn.close()

sample.pyを実行すると出力結果が表示されれば問題ありません。

出力結果

('PostgreSQL 14.3, compiled by Visual C++ build 1914, 64-bit',)

insert文を実行する。

import psycopg2

# 接続情報
dsn = "dbname=pythondb host=localhost user=postgres password=パスワード"

conn = psycopg2.connect(dsn)  # コネクション
cur = conn.cursor()  # カーサー

cur.execute("INSERT INTO app_user (name) VALUES ('山田 太郎')")
conn.commit()

# コネクション等は閉じる。
cur.close()
conn.close()

自動採番されるので値は指定していない。
練習でいろいろ値を入力してみよう。

select文を実行する。

select文を実行してみましょう

sample.py

import psycopg2

# 接続情報
dsn = "dbname=pythondb host=localhost user=postgres password=パスワード"

conn = psycopg2.connect(dsn)  # コネクション
cur = conn.cursor()  # カーサー

cur.execute("select * from app_user ")  # クエリの実行
print(cur.fetchall())

# コネクション等は閉じる。
cur.close()
conn.close()

堅牢なコードにするためにwithを使おう

自動的にcloseしてくれるのでwith句を使用しましょう。

sample.py

import psycopg2

# 接続情報
dsn = "dbname=pythondb host=localhost user=postgres password=パスワード"

with psycopg2.connect(dsn) as conn:
    with conn.cursor() as cur:
        cur.execute("select * from app_user ")  # クエリの実行
        print(cur.fetchall())

Pythonから接続する(sqlalchemy編)

pip install sqlalchemy

test.py

from sqlalchemy import create_engine

# dsnには"postgresql://ユーザー名:パスワード@localhost:5432/データベース名"
dsn = "ここに入力"
engine = create_engine(dsn)
result = engine.execute("select * from app_user ")

for row in result:
    print(row)

pandasでsqlalchemyを使って接続

pip install pandas

import pandas as pd

#例 "postgresql://ユーザー名:パスワード@localhost:5432/データベース名"
dsn = "postgresql://postgres:password@localhost/pythondb"
result = pd.read_sql(sql="select * from app_user ", con=dsn)
print(result)

上記の書き方でpandasの形式で情報を入手することができます。