読者です 読者をやめる 読者になる 読者になる

Prepared Statementも簡単

Python SQLAlchemy

 引き続き、SQLAlchemyの練習。
 前回のエントリでは、AssignMapperを使ってオブジェクト・リレーション間のマッピングを行ってみましたが、状況によっては、オブジェクトと通さず、単純にSQLを実行できた方が簡単なことも多々あります。


 SQLAlchemyのテーブルオブジェクトを使えば、自分でSQLを書かずとも、Pythonの式によって、SQLを組み立てることができます。前回エントリで使用したスキーマとデータをそのまま使います。

from sqlalchemy import *

# メタデータ・オブジェクト(データベースのテーブルの物理設計を管理するオブジェクト)
# ここでは、データベースへの接続を保持したBoundMetaDataを使います。
metadata = BoundMetaData('mysql://username:password@localhost/dbname', echo=True)

# テーブルオブジェクトを自動的につくる
cats = Table('cat', metadata, autoload=True)

# catテーブルの全行を取得
cats.select().execute().fetchall()


 例えば、Webアプリケーションで、ドロップダウンリスト(select)を表示するために、((id, name), (id, name), ..)というタプルのタプル(要素数2のシーケンスのシーケンス)が必要だとします。catテーブルからid, name列だけを取得したい場合は、次のようにします。

# クエリオブジェクトを作る
query = select([cats.c.id, cats.c.name])

# 結果セット(ResultProxy)を取得
result = query.execute()

# 結果セットからタプルを取得
result.fetchall()


 SQLAlchemyのクエリオブジェクトにはcompile()というメソッドがあり、これを使えば、Prepared Statementをcompile()が呼ばれた時点で動的に生成し、SQLの組み立てのコストを削減することができます。(これは、僕がSQLAlchemyで一番気に入っている機能です。)

# Prepared statementを生成
# "SELECT id, name FROM cat WHERE has_owner=?"に相当
query = select(
    [cats.c.id, cats.c.name],
    whereclause=cats.c.has_owner==bindparam("has_owner")
).compile()

# 実行し、結果行をすべて取得
query.execute(has_owner=True).fetchall()


 挿入や更新も、わざわざオブジェクトにマッピングする必要がない場合は、次のようにした方が、簡潔だと思います。

# 挿入用Prepared statement
create_cat = cats.insert(values={
    cats.c.name:bindparam('name'),
    cats.c.has_owner:bindparam('has_owner')
}).compile()

# 実行
create_cat.execute(name="Insight", has_owner=True)
# 更新用のPrepared statement
update_cat_name = cats.update(values={
    cats.c.name:bindparam('name')
}, whereclause=(cats.c.id==bindparam('id'))).compile()

# 実行
update_cat_name.execute(name=u"インサイト".encode("utf-8"), id=5)


 SQLAlchemyのクエリオブジェクトは、かなり複雑なSQLでもPythonの構文で表現できるので、複雑なクエリになればなるほど、django.dbと比べたときの利便性が明らかになります。
 django.dbでは、副問い合わせを使用した更新、削除や、条件に一致する行の一括更新、削除を、手でSQLを書かないと実現できなかったので、その点がずっと不満でした。SQLObjectはよく知らないから比べられないなー。