[解決済み] SQLAlchemy ORMによるデータベース更新の効率化

質問

私は新しいアプリケーションを始めていて、ORM -- 特に SQLAlchemy -- を使うことを検討しています。

データベースに 'foo' というカラムがあり、それをインクリメントしたいとします。 ストレートなsqliteでは、これは簡単です。

db = sqlite3.connect('mydata.sqlitedb')
cur = db.cursor()
cur.execute('update table stuff set foo = foo + 1')

SQLAlchemyのSQL-builderに相当するものが分かりました。

engine = sqlalchemy.create_engine('sqlite:///mydata.sqlitedb')
md = sqlalchemy.MetaData(engine)
table = sqlalchemy.Table('stuff', md, autoload=True)
upd = table.update(values={table.c.foo:table.c.foo+1})
engine.execute(upd)

これは若干遅いですが、あまり中身はありません。

SQLAlchemyのORMのアプローチについて、私の最良の推測を紹介します。

# snip definition of Stuff class made using declarative_base
# snip creation of session object
for c in session.query(Stuff):
    c.foo = c.foo + 1
session.flush()
session.commit()

これは正しいことなのですが、他の2つのアプローチに比べて50倍弱の時間がかかっています。 これは、データを扱う前に、すべてのデータをメモリに取り込む必要があるからだと推測されます。

SQLAlchemyのORMを使用して効率的なSQLを生成する方法はありますか？ あるいは他の Python ORM を使って？ それとも、手書きで SQL を書くことに戻るべきでしょうか？

どのように解決するのですか？

SQLAlchemyのORMは、SQLレイヤーを隠すのではなく、一緒に使うことを意図しています。しかし、ORM と普通の SQL を同じトランザクションで使うとき、1つか2つのことを心に留めておく必要があります。基本的に、一方から見ると、ORM のデータ修正は、セッションから変更をフラッシュするときにのみデータベースにヒットします。もう一方から見ると、SQLのデータ操作文は、セッションにあるオブジェクトに影響を与えません。

ということは、もしあなたが

for c in session.query(Stuff).all():
    c.foo = c.foo+1
session.commit()

はその言葉通り、データベースから全てのオブジェクトを取得し、全てのオブジェクトを修正し、そしてデータベースへの変更をフラッシュする時に、1つずつ行を更新していきます。

その代わりに、このようにする必要があります。

session.execute(update(stuff_table, values={stuff_table.c.foo: stuff_table.c.foo + 1}))
session.commit()

これは期待通りの1つのクエリとして実行され、少なくともデフォルトのセッション構成ではコミット時にセッションのすべてのデータを失効させるため、データが古くなる問題は発生しません。

もうすぐリリースされる0.5シリーズでは、この方法を更新に使用することもできます。

session.query(Stuff).update({Stuff.foo: Stuff.foo + 1})
session.commit()

これは基本的に前のスニペットと同じ SQL 文を実行しますが、変更された行を選択し、セッション内の古いデータを失効させることもできます。もし更新後にセッションデータを使用しないことが分かっているのであれば、このスニペットに synchronize_session=False を追加し、select を削除することもできます。