SQLAlchemyでPostgresにスキーマを作成しテーブル作成・操作を生SQLでやってみる

はじめに

最近はデータ分析ばかりでデータベースはご無沙汰していたが（クソ面白くないので避けていたが), そうもいってられなくなったので真面目にやろうと思う。早速Pythonでよく使われるDBライブラリを調べたところSQLAlchemyがよさそうだ。OR Mapperだけでなく、生SQLの実行や、SQLの組み立て等も充実しており、この手のツールにありがちな「ガチガチで融通が利かない」ものではなさそうなところが気に入った。Django等でも使えるそうで、知っておいて損はないということで今後コミットしていく。

まずは生SQLでどれくらいのことができるか知りたかったため、今回はPostgresにスキーマを作成し、そのスキーマ上にテーブルを作成し、データの登録、更新、検索、削除等の操作を一通り確認した。その結果psycopg2で直接ゴリゴリ書くのと全く変わらずに生SQLを実行することができることが分かった。

環境

• Windows10
• Postgres 12
• Python 3.X
• SQLalchemy 1.3.6

ソース

ソースは以下の通りである。見てもらえれば分かる思うので、説明は省略する。

Test.py

from sqlalchemy.schema import CreateSchema
from sqlalchemy import *
from sqlalchemy.sql import text

def main():

    # データベース接続
    engine = create_engine("postgresql://kimisyo:kimisyo@localhost:5433/chemsyo")

    # コネクションを獲得
    conn = engine.connect()

    # スキーマを作成する
    if not conn.dialect.has_schema(conn, "common"):
        engine.execute(CreateSchema("common"))

    # 前回作成したテーブルを一旦削除しておく
    try:
        conn.execute("DROP TABLE common.all_data")
    except:
        pass

    # 作成したスキーマを参照するよう変更
    conn.execute("SET search_path TO common")

    # テーブルを作成する
    conn.execute("CREATE TABLE all_data(id INTEGER, name TEXT, data TEXT)")

    # トランザクションを開始する
    trans = conn.begin()

    # テーブルにデータを登録する
    sql = text("INSERT INTO all_data(id, name, data) VALUES (:id, :name, :data)")
    for i in range(1000):
        conn.execute(sql, id=i, name="name"+str(i), data="text"+str(i))

    # データを更新する
    sql = text("UPDATE all_data SET name=:name WHERE id = :id")
    for i in range(1000):
        conn.execute(sql, id=i, name="name_upd"+str(i), data="text"+str(i))

    # トランザクションを一旦commitする
    trans.commit()

    # データを検索する(1件)
    sql = text("SELECT name FROM all_data WHERE id = :id")
    result = conn.execute(sql, id=3)
    for row in result:
        print("name:{0}".format(row['name']))

    # データを検索する(like検索)
    sql = text("SELECT id, name FROM all_data WHERE name LIKE :keyword")
    result = conn.execute(sql, keyword="%33%") # "33"を部分一致として含むものを検索
    for row in result:
        print("id:{0}, name:{1}".format(row['id'], row['name']))

    # トランザクションを開始する
    trans = conn.begin()

    # データを削除する(全件)
    sql = text("DELETE FROM all_data")
    result = conn.execute(sql)

    # commitする
    trans.commit()

    # コネクションをクローズする
    conn.close()


if __name__ == "__main__":
    main()

今後の展望

今回は泥臭い使い方ができることが分かったため、今後は以下のようにもう少しエレガントな使い方を試してみたい。
- 複雑なクエリを、SQL組み立て機能を使ってエレガントに作成、実行する。
- SQLの実行ログを、エレガントに一元的に出力する。
- ソース上に記載したテーブル定義や、DBに作成済のテーブルから、OR-Mapper機能を用いてエレガントにテーブルの操作を行う。

柔軟かつエレガントであることが今後の変化に耐え抜くために重要なのだ。