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 * 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 * 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
