Python コードから PostgreSQL に可変な SQL 文を投げてみた

こんにちは、みかみです。

はじめに

やりたいこと

• Python コードと SQL 文を分離したい
• テーブル名や指定条件などを動的にしてクエリを発行したい
• Python から可変な SQL を投げるにはどんな方法（ライブラリ）があるのか知りたい

確認環境

• OS：Windows10（Mac VMware Fusion）
• Python：3.6.0
• PostgreSQL：9.6.1（環境変数に PGPASSWORD 設定済み）

確認用データ準備

id と name カラムを持つ test テーブルを作成し、データを insert しました。

postgres=# create table test(id int, name varchar(20));
CREATE TABLE
postgres=# \d test
        テーブル "public.test"
  列  |          型           | 修飾語
------+-----------------------+--------
 id   | integer               |
 name | character varying(20) |


postgres=# insert into test values(1, 'yuki'), (2, 'ruka'), (3, 'uri');
INSERT 0 3

test テーブルに、id=1～3 の 3レコードが入りました。

postgres=# select * from test;
 id | name
----+------
  1 | yuki
  2 | ruka
  3 | uri
(3 行)

やってみた

案1. psql コマンドオプションでバインド変数を指定する

バインド変数入りの SQL を書いた外部ファイルを実行します。

取得元テーブル名と where 句で指定する id の値を、バインド変数(:変数名)で指定しています。

select.sql

select * from :table where id = :id;

Pythonコードから、subprocess モジュールで psql コマンドを実行。

→ psql コマンドのオプション(-v)でバインド変数を指定して、ファイルに書いてある SQL を実行します。

import subprocess

file_name = 'select.sql'

id = 1
table = 'test'

cmd = 'psql -d postgres -h localhost -p 5432 -U postgres -f {} -v table={} -v id={}'.format(file_name, table, id)

result = subprocess.Popen(cmd, stdout=subprocess.PIPE,shell=True).communicate()[0]
print(result.decode('sjis'))

実行してみると、ちゃんと id=1 のレコードだけが取れました。

 id | name 
----+------
  1 | yuki
(1 行)

案2. Psycopg2 でバインドパラメータを指定する

Python の PostgreSQL アクセスライブラリ psycopg2 の execute メソッドで、SQL実行時にバインドパラメータを指定します。

先ほどと同じ SQL ファイル（↓）を読み込みます。

select.sql

select * from :table where id = :id;

psycopg2 の execute ではバインドパラメータを %(変数名)s で指定する必要があるので、ファイルから読み込んだクエリの :変数名 を replace してから実行します。

import psycopg2

file_name = 'select.sql'

with open(file_name, 'r') as f:
    query = f.read()
query = query.replace(':table', '%(table)s').replace(':id', '%(id)s')

url = 'postgresql://postgres:password@localhost:5432/postgres'

table = 'test'
id = 1

try:
    conn = psycopg2.connect(url)
    with conn.cursor() as cursor:
        cursor.execute(query % {'table':table, 'id':id})
        print(cursor.fetchall())
except Exception as e:
    print(e)
finally:
    conn.close()

実行すると、ちゃんとパラメータが適用されて、指定した条件のレコードだけが取れました。

[(1, 'yuki')]

案3. SQLAlchemy で ORM を使う

Python の ORM ライブラリ sqlalchemy で、対象テーブルをマッピングして、指定条件のレコードを取得します。

まずは、テーブルクラスを定義してマッピングします。

from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
class Test(Base):
    __tablename__ = 'test'
    id = Column(Integer, primary_key=True)
    name = Column(String(20))

セッションを作成して、先ほどマッピングしたテーブルに対するクエリを実行します。

取得するレコードの条件は filter メソッドで指定します。

from sqlalchemy.orm.session import sessionmaker

url = 'postgresql://postgres:password@localhost:5432/postgres'
engine = create_engine(url)
session = sessionmaker(bind=engine)()

id = 1
result = session.query(Test).filter(Test.id == id).all()
for row in result:
    print(row.id, row.name)

session.close()

実行してみると、ちゃんと指定条件の結果が返ってきました。

1 yuki

なお、sqlalchemy の session.execute() でも、2. の psycopg2 と同様に SQL 文を指定して実行でき、バインド変数は通常の SQLと同じ :変数名 で指定します。

%(変数名)s 形式への replace が不要なので、外部 SQL ファイル読み込んでそのまま実行できるかと思ったのですが、コンパイルされたクエリのテーブル名にシングルクォートがついてしまい、エラーになってしまいました。。

"'test'"またはその近辺で構文エラー
LINE 1: select * from 'test' where id = 1
                      ^

おわりに（所感、わかったこと）

• テーブル名やカラム名を可変にしないならば、SQLAlchemy でバインド変数指定がよさげ。
• ちょっと作業用のスクリプト的な位置づけならば、psql コマンドで外部ファイル実行が一番シンプル？
• ORM 使うのはちょっとやりすぎ感。。（アプリを作りたいわけではないので。
• Flask（Python の アプリフレームワーク）の ORM は SQLAlchemy らしい。

参考

• Amazon Redshiftに対してpsqlコマンドでSQLファイル＆バインド変数を使ってアクセスする ｜ Developers.IO
• Psycopg 2.7.2.dev0 documentation » The cursor class
• SQLAlchemy 0.6.5 ドキュメント (和訳) » オブジェクトリレーショナルマッパ　チュートリアル
• SQLAlchemy 0.6.5 ドキュメント (和訳) » セッション関連 » execute()
• Python sqlalchemy 〜 変数 が埋め込まれた sql文を sqlalchemy の session.execute() に渡して、動的SQLを実行してみる ｜ Quiita