PostgreSQLをPythonから利用する

アプリケーションからPostgreSQLを使用するときには、DB API経由で使う。
DB APIは仕様で実装の一つとしてpsycopg2がある。psycopg2はPostgreSQL公式Cクライアントライブラリのlibpqを内部で使用している。

ソースコード

jsonbを利用する

id	info
1	{"a": 100, "b": "Hello"}
2	{"a": 200, "b": "World"}

CREATE TABLE json_test(
  id integer,
  info jsonb
)

低レベルAPI(psycopg2)からjsonbの利用

SELECT

cur.execute(
"SELECT info FROM json_test WHERE id=1"
)
row = cur.fetchone()
print(row[0])
# {'b': 'Hello', 'a': 100}

jsonの列を取得すると、dict(Pythonの辞書型)で取得できる。

INSERT (1)

from psycopg2.extras import Json

#json objectで更新
cur.execute(
  "INSERT INTO json_test(info) VALUES(%s)",
  [ 
    Json({"a":30, "b": "update"})
  ]
)

psycopg2が提供するJsonオブジェクトでパラメータをセットする。
psycopg2.extras.Jsonを使用する。dict→Json。

INSERT (2)

import json

# dictを文字列化してSQLに渡す
cur.execute(
  "INSERT INTO json_test(info) VALUES(jsonb(%s))",
  [
    json.dumps({"a":30, "b": "update"}) //dict→str
  ]
)

JSON文字列としてパラメータをセットし、SQL文でjsonbにキャストするパターン。
この場合DB APIのインターフェースで使用できる。

高レベルAPI(SQLAlchemy)からjsonbの利用

SELECT / INSERT

from sqlalchemy.dialects.postgresql import JSONB

class JsonTest(Base):
    __tablename__ = 'json_test'
    id = Column(Integer, primary_key=True)
    info = Column(JSONB)  # JSONB型の列と宣言
    ...

ORマッパーの機能を提供するSQLAlchemyの場合、列定義にpostgresql方言(dialect)が使用できる。

PythonからPostgreSQLのデータ型(jsonb)を使う