SQLAlchemyのORMを活用したデータの一括操作

大量データを1件ずつ登録、更新、削除をした場合、データベースとのやりとりが必要になる。 そのため、大量データをまとめて投入したい。 その方法として、bulk insertなどがある。 SQLAlchemyのORMを使ってbulk操作を実現するのか確認する。

wa3.i-3-i.info

Bulk Insert

insert関数にORMクラスを指定して実行する。 ここでは、insert後に確定するidがあるので、returning関数を指定して、insert後のデータを取得する。 returning関数で結果を取得するには、scalars関数を使う必要がある。 returning不要であれば、execute関数でもOK。 ただし、returningから返却されるデータの順序は保証されていない。 順序を保証するために、sort_by_parameter_orderパラメータを指定する必要がある。

students = session.scalars(
    insert(Student).returning(Student),
    [
        {"name": "sato", "address": "Tokyo", "email": "sato@ddd.com"},
        {"name": "tanaka", "address": "Kanagawa", "email": "tanaka@ddd.com"},
        {"name": "wata", "address": "Chiba", "email": "wata@ddd.com"}
    ]
)
session.commit()

Bulk Update

主キーをWHERE条件に設定して、UPDATEを実行する。 下記の場合は、idが主キー。

session.execute(
    update(Student),
    [
        {"id": 1, "name": "sato2"},
        {"id": 2, "name": "tanaka2"},
        {"id": 3, "name": "wata2"}
    ]
)

SQLAlchemyで生成するSQL文。

UPDATE student SET name=%(name)s WHERE student.id = %(student_id)s

主キーを使用せずに、別の条件でUPDATEをしたい場合はwhere関数を使用する。 ここでは、Student.nameと辞書のキーであるu_nameをバインドするために、bindparam関数を使ってバインドする。 また、実行時にはsession.executeではなくsession.connection().executeを使用する必要がある。

session.connection().execute(
    update(Student).where(Student.name == bindparam("u_name")),
    [
        {"u_name": "sato", "name": "sato3"},
        {"u_name": "wata", "name": "wata3"}
    ]
)

docs.sqlalchemy.org

Bulk Delete

Bulk ORM DELETEはサポートされていないため、session.connection().executeでcoreモジュールを使って、実行する。

session.connection().execute(
    delete(Student).where(Student.name == bindparam("d_name")), 
    [
        {"d_name": "wata3"},
        {"d_name": "tanaka"}
    ]
)
session.commit()

session.executeでもできる。

session.execute(
    delete(Student).where(Student.name.in_(["wata3", "tanaka"]))
)
session.commit()

最後に

ORMを使ったBulk操作のやり方を理解できた。 より高度な使い方をする場合は、もう少しマニュアルを見る必要があると思う。 開発でも活かしていきたい。

参考

docs.sqlalchemy.org