ORM Querying Guide

This page is part of the ORM Querying Guide.

このページは ORM Querying Guide の一部です。

Previous: Writing SELECT statements for Inheritance Mappings | Next: Column Loading Options

ORM-Enabled INSERT, UPDATE, and DELETE statements

About this Document

このセクションでは、 SQLAlchemy Unified Tutorial で最初に説明されているORMマッピング、セクション Declaring Mapped Classes で示されているORMマッピング、およびセクション:ref:inheritance_toplevel で示されている継承マッピングを使用します。

View the ORM setup for this page.

Session.execute() メソッドは、ORM対応の Select オブジェクトの処理に加えて、ORM対応の Insert,:class:_sql.Update および Delete オブジェクトにも対応できます。これらのオブジェクトは、それぞれ一度に多くのデータベース行をINSERT、UPDATE、またはDELETEするために使用されるさまざまな方法で対応できます。また、ORM対応の「upcerts」(すでに存在する行に対して自動的にUPDATEを使用するINSERT文)に対する方言固有のサポートもあります。

次の表に、このマニュアルで説明する呼び出し形式の概要を示します。:

ORM Use Case	DML Construct Used	Data is passed using …	Supports RETURNING?	Supports Multi-Table Mappings?
ORM Bulk INSERT Statements	insert()	List of dictionaries to Session.execute.params	yes	yes
ORM Bulk Insert with SQL Expressions	insert()	Session.execute.params with Insert.values()	yes	yes
ORM Bulk Insert with Per Row SQL Expressions	insert()	List of dictionaries to Insert.values()	yes	no
ORM “upsert” Statements	insert()	List of dictionaries to Insert.values()	yes	no
ORM Bulk UPDATE by Primary Key	update()	List of dictionaries to Session.execute.params	no	yes
ORM UPDATE and DELETE with Custom WHERE Criteria	update(), delete()	keywords to Update.values()	yes	partial, with manual steps

ORM Bulk INSERT Statements

insert() 構文はORMクラスの観点から構築でき、 Session.execute() メソッドに渡すことができます。 Insert オブジェクト自体とは別に、 Session.execute.params パラメータに送られるパラメータ辞書のリストは、文に対して**バルクインサートモード**を呼び出します。これは本質的に、操作が多くの行に対して可能な限り最適化されることを意味します:

>>> from sqlalchemy import insert
>>> session.execute(
...     insert(User),
...     [
...         {"name": "spongebob", "fullname": "Spongebob Squarepants"},
...         {"name": "sandy", "fullname": "Sandy Cheeks"},
...         {"name": "patrick", "fullname": "Patrick Star"},
...         {"name": "squidward", "fullname": "Squidward Tentacles"},
...         {"name": "ehkrabs", "fullname": "Eugene H. Krabs"},
...     ],
... )
INSERT INTO user_account (name, fullname) VALUES (?, ?)
[...] [('spongebob', 'Spongebob Squarepants'), ('sandy', 'Sandy Cheeks'), ('patrick', 'Patrick Star'),
('squidward', 'Squidward Tentacles'), ('ehkrabs', 'Eugene H. Krabs')]
<...>

パラメータ辞書には、キーと値のペアが含まれています。これは、マップされた Column または mapped_column() 宣言や composite 宣言と並んでいるORMマップ属性に対応している可能性があります。これら2つの名前が異なる場合、キーは ORMマップ属性名 と一致し、実際のデータベース列名とは 一致しない 必要があります。

Changed in version 2.0: Insert 構文を Session.execute() メソッドに渡すと、従来の Session.bulk_insert_mappings() メソッドと同じ機能を使用する”バルクインサート”が呼び出されるようになりました。これは、 Insert が値のキーにカラム名を使用してコア中心の方法で解釈される1.xシリーズと比較した場合の動作の変更です。ORMアトリビュートキーが受け入れられるようになりました。コアスタイルの機能は Session.execute() の Session.execution_options パラメータに実行オプション {"dml_strategy":"raw"} を渡すことで利用できます。

Getting new objects with RETURNING

バルクORM挿入機能は、選択されたバックエンドに対してINSERT.RETURNINGをサポートします。これは、個々の列を返す可能性のある Result オブジェクトと、新しく生成されたレコードに対応する完全に構築されたORMオブジェクトを返すことができます。INSERT.RETURNINGには、SQL RETURNING構文をサポートするバックエンドの使用と、RETURNINGでの executemany のサポートが必要です。この機能は、MySQL(MariaDBが含まれています)を除くすべての SQLAlchemy-included バックエンドで利用できます。

例として、以前と同じ文を実行し、 UpdateBase.returning() メソッドの使用を追加して、返すものとして完全な User エンティティを渡すことができます。 Session.scalars() は、 User オブジェクトの反復を許可するために使用されます:

>>> users = session.scalars(
...     insert(User).returning(User),
...     [
...         {"name": "spongebob", "fullname": "Spongebob Squarepants"},
...         {"name": "sandy", "fullname": "Sandy Cheeks"},
...         {"name": "patrick", "fullname": "Patrick Star"},
...         {"name": "squidward", "fullname": "Squidward Tentacles"},
...         {"name": "ehkrabs", "fullname": "Eugene H. Krabs"},
...     ],
... )
INSERT INTO user_account (name, fullname)
VALUES (?, ?), (?, ?), (?, ?), (?, ?), (?, ?)
RETURNING id, name, fullname, species
[...] ('spongebob', 'Spongebob Squarepants', 'sandy', 'Sandy Cheeks',
'patrick', 'Patrick Star', 'squidward', 'Squidward Tentacles',
'ehkrabs', 'Eugene H. Krabs')
>>> print(users.all())
[User(name='spongebob', fullname='Spongebob Squarepants'),
 User(name='sandy', fullname='Sandy Cheeks'),
 User(name='patrick', fullname='Patrick Star'),
 User(name='squidward', fullname='Squidward Tentacles'),
 User(name='ehkrabs', fullname='Eugene H. Krabs')]

上の例では、レンダリングされたSQLは、SQLiteバックエンドが要求する insertmanyvalues 機能で使用される形式になります。ここでは、個々のパラメータ辞書が1つのINSERT文にインライン化されているので、RETURNINGを使用できます。

Changed in version 2.0: ORM Session は、ORMコンテキスト内の Insert 、 Update 、さらには Delete 構文からのRETURNING句を解釈するようになりました。つまり、列式とORMにマップされたエンティティの組み合わせを Insert.returning() メソッドに渡すことができます。このメソッドは、マップされたエンティティがORMにマップされたオブジェクトとして結果に配信されることも含めて、 Select などの構文からORMの結果が配信される方法で配信されます。 load_only() や selectinload() などのORMローダーオプションの限定的なサポートもあります。

Correlating RETURNING records with input data order

RETURNINGとともにバルクインサートを使用する場合、ほとんどのデータベースバックエンドでは、RETURNINGからのレコードが返される順序が正式に保証されていないことに注意してください。これには、レコードの順序が入力されたレコードの順序と一致する保証がないことも含まれます。RETURNINGレコードを入力データと確実に関連付ける必要があるアプリケーションでは、追加のパラメータ Insert.returning.sort_by_parameter_order を指定できます。これは、バックエンドによっては、返される行を適切に並べ替えるために使用されるトークンを保持する特殊なINSERT形式を使用する場合があります。また、以下のSQLiteバックエンドを使用した例のように、操作は一度に1行ずつINSERTします。:

>>> data = [
...     {"name": "pearl", "fullname": "Pearl Krabs"},
...     {"name": "plankton", "fullname": "Plankton"},
...     {"name": "gary", "fullname": "Gary"},
... ]
>>> user_ids = session.scalars(
...     insert(User).returning(User.id, sort_by_parameter_order=True), data
... )
INSERT INTO user_account (name, fullname) VALUES (?, ?) RETURNING id
[... (insertmanyvalues) 1/3 (ordered; batch not supported)] ('pearl', 'Pearl Krabs')
INSERT INTO user_account (name, fullname) VALUES (?, ?) RETURNING id
[insertmanyvalues 2/3 (ordered; batch not supported)] ('plankton', 'Plankton')
INSERT INTO user_account (name, fullname) VALUES (?, ?) RETURNING id
[insertmanyvalues 3/3 (ordered; batch not supported)] ('gary', 'Gary')
>>> for user_id, input_record in zip(user_ids, data):
...     input_record["id"] = user_id
>>> print(data)
[{'name': 'pearl', 'fullname': 'Pearl Krabs', 'id': 6},
{'name': 'plankton', 'fullname': 'Plankton', 'id': 7},
{'name': 'gary', 'fullname': 'Gary', 'id': 8}]

New in version 2.0.10: insertmanyvalues アーキテクチャ内で実装される Insert.returning.sort_by_parameter_order が追加されました。

See also

Correlating RETURNING rows to parameter sets-パフォーマンスを大幅に低下させることなく、入力データと結果行の対応を保証するために採用されたアプローチの背景

Using Heterogeneous Parameter Dictionaries

ORMバルクインサート機能は、「異種」のパラメータ辞書のリストをサポートしています。これは基本的に、「個々の辞書が異なるキーを持つことができる」ことを意味します。この条件が検出されると、ORMはパラメータ辞書を各キーセットに対応するグループに分割し、それに応じて別々のINSERT文にバッチ化します:

>>> users = session.scalars(
...     insert(User).returning(User),
...     [
...         {
...             "name": "spongebob",
...             "fullname": "Spongebob Squarepants",
...             "species": "Sea Sponge",
...         },
...         {"name": "sandy", "fullname": "Sandy Cheeks", "species": "Squirrel"},
...         {"name": "patrick", "species": "Starfish"},
...         {
...             "name": "squidward",
...             "fullname": "Squidward Tentacles",
...             "species": "Squid",
...         },
...         {"name": "ehkrabs", "fullname": "Eugene H. Krabs", "species": "Crab"},
...     ],
... )
INSERT INTO user_account (name, fullname, species)
VALUES (?, ?, ?), (?, ?, ?) RETURNING id, name, fullname, species
[... (insertmanyvalues) 1/1 (unordered)] ('spongebob', 'Spongebob Squarepants', 'Sea Sponge',
'sandy', 'Sandy Cheeks', 'Squirrel')
INSERT INTO user_account (name, species)
VALUES (?, ?) RETURNING id, name, fullname, species
[...] ('patrick', 'Starfish')
INSERT INTO user_account (name, fullname, species)
VALUES (?, ?, ?), (?, ?, ?) RETURNING id, name, fullname, species
[... (insertmanyvalues) 1/1 (unordered)] ('squidward', 'Squidward Tentacles',
'Squid', 'ehkrabs', 'Eugene H. Krabs', 'Crab')

上の例では、渡された5つのパラメータ辞書は3つのINSERT文に変換され、各行の順序を維持しながら、各辞書の特定のキーセットに沿ってグループ化されます。つまり、 ("name","fullname","species") 、 ("name","species") 、 ("name","fullname","species") です。

Sending NULL values in ORM bulk INSERT statements

ORMバルクインサート文でのNULL値の送信

バルクORM挿入機能は、従来の”バルク”挿入動作やORM作業単位全体に存在する動作を利用します。つまり、NULL値を含む行は、それらの列を参照しない文を使用してINSERTされます。ここでの理論的根拠は、NULL値の存在と値が存在しないことに敏感なサーバ側のINSERTデフォルトを含むバックエンドやスキーマが、期待通りにサーバ側の値を生成するようにするためです。このデフォルト動作は、バルク挿入されたバッチを、より少ない行のより多くのバッチに分割する効果があります:

>>> session.execute(
...     insert(User),
...     [
...         {
...             "name": "name_a",
...             "fullname": "Employee A",
...             "species": "Squid",
...         },
...         {
...             "name": "name_b",
...             "fullname": "Employee B",
...             "species": "Squirrel",
...         },
...         {
...             "name": "name_c",
...             "fullname": "Employee C",
...             "species": None,
...         },
...         {
...             "name": "name_d",
...             "fullname": "Employee D",
...             "species": "Bluefish",
...         },
...     ],
... )
INSERT INTO user_account (name, fullname, species) VALUES (?, ?, ?)
[...] [('name_a', 'Employee A', 'Squid'), ('name_b', 'Employee B', 'Squirrel')]
INSERT INTO user_account (name, fullname) VALUES (?, ?)
[...] ('name_c', 'Employee C')
INSERT INTO user_account (name, fullname, species) VALUES (?, ?, ?)
[...] ('name_d', 'Employee D', 'Bluefish')
...

上の例では、4行のバルクインサートが3つの別々の文に分割されています。2番目の文は、 None 値を含む単一パラメータディクショナリのNULLカラムを参照しないように再フォーマットされています。データセット内の多くの行にランダムなNULL値が含まれている場合、このデフォルトの動作は望ましくない可能性があります。これは、「executemany」操作が多数の小さな操作に分割されるためです。特に、 insertmanyvalues に依存して文の総数を減らす場合、これはパフォーマンスに大きな影響を与える可能性があります。

パラメータ内の None 値を別々のバッチで処理しないようにするには、実行オプション render_nulls=True を渡します。これにより、各辞書内のキーのセットが同じであると仮定して、すべてのパラメータ辞書が同等に扱われます。:

>>> session.execute(
...     insert(User).execution_options(render_nulls=True),
...     [
...         {
...             "name": "name_a",
...             "fullname": "Employee A",
...             "species": "Squid",
...         },
...         {
...             "name": "name_b",
...             "fullname": "Employee B",
...             "species": "Squirrel",
...         },
...         {
...             "name": "name_c",
...             "fullname": "Employee C",
...             "species": None,
...         },
...         {
...             "name": "name_d",
...             "fullname": "Employee D",
...             "species": "Bluefish",
...         },
...     ],
... )
INSERT INTO user_account (name, fullname, species) VALUES (?, ?, ?)
[...] [('name_a', 'Employee A', 'Squid'), ('name_b', 'Employee B', 'Squirrel'), ('name_c', 'Employee C', None), ('name_d', 'Employee D', 'Bluefish')]
...

上記では、3番目のパラメータ辞書に存在する None 値を含め、すべてのパラメータ辞書が1回のINSERTバッチで送信されます。

New in version 2.0.23: 従来の Session.bulk_insert_mappings.render_nulls パラメータの動作を反映した render_nulls 実行オプションが追加されました。

Bulk INSERT for Joined Table Inheritance

ORMバルクインサートは、INSERT文を発行するために従来の:term:作業単位`システムで使用される内部システムに基づいて構築されます。つまり、複数のテーブルにマップされるORMエンティティ(通常は:ref:`結合されたテーブルの継承<joined_inheritance>`を使用してマップされるもの)の場合、バルクインサート操作は、マッピングによって表される各テーブルに対してINSERT文を発行し、サーバが生成したプライマリキーの値を、それらに依存するテーブルの行に正しく転送します。RETURNING機能もここでサポートされており、ORMは実行された各INSERT文に対して :class:.Result` オブジェクトを受け取り、それらを「水平につなぎ合わせ」て、返される行に挿入されたすべての列の値が含まれるようにします:

>>> managers = session.scalars(
...     insert(Manager).returning(Manager),
...     [
...         {"name": "sandy", "manager_name": "Sandy Cheeks"},
...         {"name": "ehkrabs", "manager_name": "Eugene H. Krabs"},
...     ],
... )
INSERT INTO employee (name, type) VALUES (?, ?) RETURNING id, name, type
[... (insertmanyvalues) 1/2 (ordered; batch not supported)] ('sandy', 'manager')
INSERT INTO employee (name, type) VALUES (?, ?) RETURNING id, name, type
[insertmanyvalues 2/2 (ordered; batch not supported)] ('ehkrabs', 'manager')
INSERT INTO manager (id, manager_name) VALUES (?, ?), (?, ?) RETURNING id, manager_name, id AS id__1
[... (insertmanyvalues) 1/1 (ordered)] (1, 'Sandy Cheeks', 2, 'Eugene H. Krabs')

Tip

結合された継承マッピングのバルクインサートでは、ORMが内部で Insert.returning.sort_by_parameter_order パラメータを使用する必要があります。これにより、ベーステーブルのRETURNING行のプライマリキー値を、”サブ”テーブルへのINSERTに使用されるパラメータセットに相関させることができます。これが、上で説明したSQLiteバックエンドが、バッチ化されていない文を使用するように透過的に劣化する理由です。この機能の背景は Correlating RETURNING rows to parameter sets にあります。

ORM Bulk Insert with SQL Expressions

ORMバルクインサート機能は、すべての対象行に適用されるSQL式を含む可能性のあるパラメータの固定セットの追加をサポートします。これを実現するには、すべての行に適用されるパラメータの辞書を渡す Insert.values() メソッドの使用と、 Session.execute() を呼び出すときに個々の行の値を含むパラメータ辞書のリストを含める通常の一括呼び出し形式を組み合わせます。

例として、”timestamp”列を含むORMマッピングがあるとします。:

import datetime

class LogRecord(Base):
    __tablename__ = "log_record"
    id: Mapped[int] = mapped_column(primary_key=True)
    message: Mapped[str]
    code: Mapped[str]
    timestamp: Mapped[datetime.datetime]

それぞれが一意の message フィールドを持つ一連の LogRecord 要素を挿入したいのであれば、SQL関数の now() をすべての行に適用したいのですが、 Insert.values() 内で timestamp を渡し、 bulk モードを使って追加のレコードを渡すことができます。:

>>> from sqlalchemy import func
>>> log_record_result = session.scalars(
...     insert(LogRecord).values(code="SQLA", timestamp=func.now()).returning(LogRecord),
...     [
...         {"message": "log message #1"},
...         {"message": "log message #2"},
...         {"message": "log message #3"},
...         {"message": "log message #4"},
...     ],
... )
INSERT INTO log_record (message, code, timestamp)
VALUES (?, ?, CURRENT_TIMESTAMP), (?, ?, CURRENT_TIMESTAMP),
(?, ?, CURRENT_TIMESTAMP), (?, ?, CURRENT_TIMESTAMP)
RETURNING id, message, code, timestamp
[... (insertmanyvalues) 1/1 (unordered)] ('log message #1', 'SQLA', 'log message #2',
'SQLA', 'log message #3', 'SQLA', 'log message #4', 'SQLA')
>>> print(log_record_result.all())
[LogRecord('log message #1', 'SQLA', datetime.datetime(...)),
 LogRecord('log message #2', 'SQLA', datetime.datetime(...)),
 LogRecord('log message #3', 'SQLA', datetime.datetime(...)),
 LogRecord('log message #4', 'SQLA', datetime.datetime(...))]

ORM Bulk Insert with Per Row SQL Expressions

Insert.values() メソッド自体は、パラメータ辞書のリストに直接対応しています。 Session.execute.params パラメータにパラメータ辞書のリストを渡さずに、このように Insert 構文を使用する場合、バルクORM挿入モードは使用されず、代わりにINSERT文は指定されたとおりにレンダリングされ、1回だけ呼び出されます。この操作モードは、行単位でSQL式を渡す場合にも便利ですし、この章の後半の ORM “upsert” Statements で説明されているように、ORMで”upsert”文を使用する場合にも使用されます。

行ごとのSQL式を埋め込み、 Insert.returning() をこの形式で示すINSERTの不自然な例を以下に示します。:

>>> from sqlalchemy import select
>>> address_result = session.scalars(
...     insert(Address)
...     .values(
...         [
...             {
...                 "user_id": select(User.id).where(User.name == "sandy"),
...                 "email_address": "sandy@company.com",
...             },
...             {
...                 "user_id": select(User.id).where(User.name == "spongebob"),
...                 "email_address": "spongebob@company.com",
...             },
...             {
...                 "user_id": select(User.id).where(User.name == "patrick"),
...                 "email_address": "patrick@company.com",
...             },
...         ]
...     )
...     .returning(Address),
... )
INSERT INTO address (user_id, email_address) VALUES
((SELECT user_account.id
FROM user_account
WHERE user_account.name = ?), ?), ((SELECT user_account.id
FROM user_account
WHERE user_account.name = ?), ?), ((SELECT user_account.id
FROM user_account
WHERE user_account.name = ?), ?) RETURNING id, user_id, email_address
[...] ('sandy', 'sandy@company.com', 'spongebob', 'spongebob@company.com',
'patrick', 'patrick@company.com')
>>> print(address_result.all())
[Address(email_address='sandy@company.com'),
 Address(email_address='spongebob@company.com'),
 Address(email_address='patrick@company.com')]

上記ではバルクORM挿入モードが使用されていないため、次の機能はありません。:

• Joined table inheritance やその他の複数テーブルのマッピングは、複数のINSERT文を必要とするため、サポートされていません。

• Heterogeneous parameter sets はサポートされていません-VALUESセットの各要素は同じ列を持たなければなりません。

• insertmanyvalues によって提供されるバッチ処理のようなコアレベルのスケール最適化は利用できません。文は、パラメータの総数がバッキングデータベースによって課せられた制限を超えないようにする必要があります。

上記の理由から、明確な根拠がない限り、ORM INSERT文で Insert.values() を使用して複数のパラメータセットを使用することは推奨されません。明確な根拠とは、「upsert」が使用されているか、各パラメータセットに行ごとのSQL式を埋め込む必要があるかのいずれかです。

See also

ORM “upsert” Statements

Legacy Session Bulk INSERT Methods

Session には、”バルク”INSERT文とUPDATE文を実行するためのレガシーなメソッドが含まれています。これらのメソッドは、 ORM Bulk INSERT Statements と ORM Bulk UPDATE by Primary Key で説明されているこれらの機能のSQLAlchemy 2.0バージョンと実装を共有していますが、RETURNINGサポートやセッション同期のサポートなど、多くの機能が欠けています。

たとえば、 Session.bulk_insert_mappings() を利用するコードは、次のマッピング例から始めて、次のようにコードを移植できます:

session.bulk_insert_mappings(User, [{"name": "u1"}, {"name": "u2"}, {"name": "u3"}])

上記は、新しいAPIを使用して次のように表されます。:

from sqlalchemy import insert

session.execute(insert(User), [{"name": "u1"}, {"name": "u2"}, {"name": "u3"}])

See also

Legacy Session Bulk UPDATE Methods

ORM “upsert” Statements

SQLAlchemyで選択されたバックエンドには、方言固有の Insert 構文が含まれることがあります。この構文には、「upcerts」を実行する機能や、パラメータセット内の既存の行をUPDATE文の近似に変換するINSERTを実行する機能が追加されています。「既存の行」とは、同じ主キー値を共有する行を意味する場合もあれば、一意であると見なされる行内の他のインデックス付き列を参照する場合もあります。これは使用中のバックエンドの機能に依存します。

方言固有の”upsert”API機能を含むSQLAlchemyに含まれる方言は、次のとおりです。:

• SQLite - using Insert documented at INSERT…ON CONFLICT (Upsert)

• PostgreSQL - using Insert documented at INSERT…ON CONFLICT (Upsert)

• MySQL/MariaDB - using Insert documented at INSERT…ON DUPLICATE KEY UPDATE (Upsert)

ユーザは、これらのオブジェクトの適切な構築に関する背景について、上記のセクションを確認する必要があります。特に、「アップサート」メソッドは通常、元のステートメントを参照する必要があるため、ステートメントは通常2つの別々のステップで構築されます。

External Dialects で言及されているようなサードパーティのバックエンドも、同様の構造を持つことがあります。

SQLAlchemyにはまだバックエンドに依存しないupsert構文がありませんが、上記の Insert のバリアントは、 ORM Bulk Insert with Per Row SQL Expressions に記載されているように、 Insert 構文自体と同じように、つまり、 Insert.values() メソッド内に目的の行をINSERTに埋め込むことによって使用できるという点で、ORMと互換性があります。次の例では、SQLite _SQLite.insert() 関数を使用して、”ON CONFLICT DO UPDATE”サポートを含む _SQLite.Insert 構文を生成します。その後、この文は Session.execute() に渡され、そこで通常通り処理されます。さらに、 Insert.values() に渡されたパラメータ辞書は、列名ではなくORMにマップされた属性キーとして解釈されます。

:<数値>:

>>> from sqlalchemy.dialects.sqlite import insert as sqlite_upsert
>>> stmt = sqlite_upsert(User).values(
...     [
...         {"name": "spongebob", "fullname": "Spongebob Squarepants"},
...         {"name": "sandy", "fullname": "Sandy Cheeks"},
...         {"name": "patrick", "fullname": "Patrick Star"},
...         {"name": "squidward", "fullname": "Squidward Tentacles"},
...         {"name": "ehkrabs", "fullname": "Eugene H. Krabs"},
...     ]
... )
>>> stmt = stmt.on_conflict_do_update(
...     index_elements=[User.name], set_=dict(fullname=stmt.excluded.fullname)
... )
>>> session.execute(stmt)
{execsql}INSERT INTO user_account (name, fullname)
VALUES (?, ?), (?, ?), (?, ?), (?, ?), (?, ?)
ON CONFLICT (name) DO UPDATE SET fullname = excluded.fullname
[...] ('spongebob', 'Spongebob Squarepants', 'sandy', 'Sandy Cheeks',
'patrick', 'Patrick Star', 'squidward', 'Squidward Tentacles',
'ehkrabs', 'Eugene H. Krabs')
{stop}<...>

Using RETURNING with upsert statements

SQLAlchemy ORMの観点から見ると、アップサート文は通常の Insert 構文のように見えます。これには、 Insert.returning() が ORM Bulk Insert with Per Row SQL Expressions で示されたのと同じ方法でアップサート文と共に動作することが含まれているので、任意の列式や関連するORMエンティティークラスを渡すことができます。前のセクションの例から続けます:

>>> result = session.scalars(
...     stmt.returning(User), execution_options={"populate_existing": True}
... )
INSERT INTO user_account (name, fullname)
VALUES (?, ?), (?, ?), (?, ?), (?, ?), (?, ?)
ON CONFLICT (name) DO UPDATE SET fullname = excluded.fullname
RETURNING id, name, fullname, species
[...] ('spongebob', 'Spongebob Squarepants', 'sandy', 'Sandy Cheeks',
'patrick', 'Patrick Star', 'squidward', 'Squidward Tentacles',
'ehkrabs', 'Eugene H. Krabs')
>>> print(result.all())
[User(name='spongebob', fullname='Spongebob Squarepants'),
  User(name='sandy', fullname='Sandy Cheeks'),
  User(name='patrick', fullname='Patrick Star'),
  User(name='squidward', fullname='Squidward Tentacles'),
  User(name='ehkrabs', fullname='Eugene H. Krabs')]

上記の例では、RETURNINGを使用して、ステートメントによって挿入または更新された各行のORMオブジェクトを返します。この例では、 Populate Existing 実行オプションの使用も追加されています。このオプションは、すでに存在する行の Session にすでに存在する User オブジェクトを、新しい行のデータで 更新 する必要があることを示します。純粋な Insert ステートメントでは、このオプションは重要ではありません。なぜなら、生成されるすべての行はまったく新しい主キーIDだからです。しかし、 Insert に”upsert”オプションも含まれている場合は、すでに存在する行から結果を生成している可能性もあるため、 Session オブジェクトの identity map で表された主キーIDをすでに持っている可能性があります。

See also

Populate Existing

ORM Bulk UPDATE by Primary Key

Update 構文は、 ORM Bulk INSERT Statements で説明されているように、 Insert 文と同様の方法で Session.execute() と一緒に使用することができ、多くのパラメータ辞書のリストを渡します。各辞書は、単一の主キー値に対応する個々の行を表します。この使用法を、 ORM UPDATE and DELETE with Custom WHERE Criteria で文書化されている、明示的なWHERE句を使用してORMで Update 文を使用するより一般的な方法と混同しないでください。

UPDATEの”バルク”バージョンでは、 update() 構文はORMクラスとして作成され、 Session.execute() メソッドに渡されます。結果の Update オブジェクトは**値を持たず、通常はWHERE条件を持たない**必要があります。つまり、 Update.values() メソッドは使用されず、 Update.where() は**通常は**使用されませんが、追加のフィルタリング条件が追加されるような特殊なケースでは使用される可能性があります。

Update 構文と、それぞれが完全なプライマリキー値を持つパラメータ辞書のリストを渡すと、文に対して**プライマリキーモードによる一括UPDATE**が呼び出され、プライマリキーによって各行に一致する適切なWHERE条件が生成され、 executemany を使用してUPDATE文に対して各パラメータセットが実行されます:

>>> from sqlalchemy import update
>>> session.execute(
...     update(User),
...     [
...         {"id": 1, "fullname": "Spongebob Squarepants"},
...         {"id": 3, "fullname": "Patrick Star"},
...         {"id": 5, "fullname": "Eugene H. Krabs"},
...     ],
... )
UPDATE user_account SET fullname=? WHERE user_account.id = ?
[...] [('Spongebob Squarepants', 1), ('Patrick Star', 3), ('Eugene H. Krabs', 5)]
<...>

各パラメータ辞書**には、各レコード**の完全な主キーが含まれている必要があります。そうでない場合は、エラーが発生します。

バルクインサート機能と同様に、ここでも異種パラメータリストがサポートされており、パラメータはUPDATE実行のサブバッチにグループ化されます。

versionchanged:: 2.0.11 Additional WHERE criteria can be combined with ORM Bulk UPDATE by Primary Key by using the Update.where() method to add additional criteria. However this criteria is always in addition to the WHERE criteria that’s already made present which includes primary key values.

Changed in version :meth:`_dml.Update.where`: メソッドを使用して追加のWHERE条件を ORM Bulk UPDATE by Primary Key と組み合わせて追加の条件を追加することができます。ただし、この条件は常に、主キー値を含む既存のWHERE条件に追加されます。

RETURNING機能は”プライマリキーによる一括UPDATE”機能を使用している場合には利用できません。複数パラメータ辞書のリストは必然的にDBAPI executemany を利用しますが、通常の形式では結果行をサポートしません。

Changed in version 2.0: Update 構文をパラメータ辞書のリストとともに Session.execute() メソッドに渡すと、”一括更新”が呼び出されるようになりました。これは、従来の Session.bulk_update_mappings() メソッドと同じ機能を利用します。これは、 Update が明示的なWHERE条件とインラインVALUESでしかサポートされない1.xシリーズと比較した場合の動作の変更です。

Disabling Bulk ORM Update by Primary Key for an UPDATE statement with multiple parameter sets

ORM Bulk Update by Primary Keyの機能は、各プライマリ・キー値のWHERE条件を含むUPDATE文をレコードごとに実行し、次の場合に自動的に使用されます。

1. 与えられたUPDATE文がORMエンティティに対するものである場合2. Session が文の実行に使用され、Core Connection ではありません。3. 渡されるパラメータは 辞書のリスト です。

“ORM Bulk Update by Primary Key”を使わずにUPDATE文を呼び出すには、 Session.connection() メソッドを使って Connection に対して直接UPDATE文を呼び出し、そのトランザクションの現在の Connection を取得します:

>>> from sqlalchemy import bindparam
>>> session.connection().execute(
...     update(User).where(User.name == bindparam("u_name")),
...     [
...         {"u_name": "spongebob", "fullname": "Spongebob Squarepants"},
...         {"u_name": "patrick", "fullname": "Patrick Star"},
...     ],
... )
UPDATE user_account SET fullname=? WHERE user_account.name = ?
[...] [('Spongebob Squarepants', 'spongebob'), ('Patrick Star', 'patrick')]
<...>

See also

per-row ORM Bulk Update by Primary Key requires that records contain primary key values

Bulk UPDATE by Primary Key for Joined Table Inheritance

Bulk INSERT for Joined Table Inheritance で説明されているように、一括UPDATE操作は、指定されたパラメータに更新される値が含まれる(影響を受けないテーブルはスキップされる)、マッピングで表現される各テーブルに対してUPDATE文を発行します。これは、結合テーブルバルクインサートを持つマッピングを使用する場合のORM継承と同様の動作です。

例:

>>> session.execute(
...     update(Manager),
...     [
...         {
...             "id": 1,
...             "name": "scheeks",
...             "manager_name": "Sandy Cheeks, President",
...         },
...         {
...             "id": 2,
...             "name": "eugene",
...             "manager_name": "Eugene H. Krabs, VP Marketing",
...         },
...     ],
... )
UPDATE employee SET name=? WHERE employee.id = ?
[...] [('scheeks', 1), ('eugene', 2)]
UPDATE manager SET manager_name=? WHERE manager.id = ?
[...] [('Sandy Cheeks, President', 1), ('Eugene H. Krabs, VP Marketing', 2)]
<...>

Legacy Session Bulk UPDATE Methods

Legacy Session Bulk INSERT Methods で説明されているように、 Session の Session.bulk_update_mappings() メソッドは一括更新のレガシー形式であり、ORMはプライマリキーパラメータが指定された update() 文を解釈する際に内部でこれを利用します。しかし、レガシーバージョンを使用する場合、セッション同期のサポートなどの機能は含まれません。

次に例を示します。:

session.bulk_update_mappings(
    User,
    [
        {"id": 1, "name": "scheeks", "manager_name": "Sandy Cheeks, President"},
        {"id": 2, "name": "eugene", "manager_name": "Eugene H. Krabs, VP Marketing"},
    ],
)

新しいAPIを使用して次のように表現されます。:

from sqlalchemy import update

session.execute(
    update(User),
    [
        {"id": 1, "name": "scheeks", "manager_name": "Sandy Cheeks, President"},
        {"id": 2, "name": "eugene", "manager_name": "Eugene H. Krabs, VP Marketing"},
    ],
)

See also

Legacy Session Bulk INSERT Methods

ORM UPDATE and DELETE with Custom WHERE Criteria

Update および Delete 構文は、カスタムWHERE条件で(つまり、 Update.where() および Delete.where() メソッドを使って)作成された場合、 Session.execute.params パラメータを使わずに Session.execute() に渡すことで、ORMコンテキストで呼び出すことができます。 Update の場合、更新される値は Update.values() を使って渡す必要があります。

この使用モードは、以前に ORM Bulk UPDATE by Primary Key で説明した機能とは異なり、ORMはWHERE句を主キーに固定するのではなく、与えられたWHERE句をそのまま使用します。つまり、1つのUPDATE文またはDELETE文が一度に多くの行に影響を与える可能性があります。

以下の例では、複数行の”fullname”フィールドに影響するUPDATEが発行されています。:

>>> from sqlalchemy import update
>>> stmt = (
...     update(User)
...     .where(User.name.in_(["squidward", "sandy"]))
...     .values(fullname="Name starts with S")
... )
>>> session.execute(stmt)
UPDATE user_account SET fullname=? WHERE user_account.name IN (?, ?)
[...] ('Name starts with S', 'squidward', 'sandy')
<...>

DELETEの場合、基準に基づいてローを削除する例:

>>> from sqlalchemy import delete
>>> stmt = delete(User).where(User.name.in_(["squidward", "sandy"]))
>>> session.execute(stmt)
DELETE FROM user_account WHERE user_account.name IN (?, ?)
[...] ('squidward', 'sandy')
<...>

Warning

Session.delete() メソッドを使用して個々のオブジェクトを削除するなど、ORM対応のUPDATEとDELETEの機能がORMの unit of work の機能とどのように異なるかに関する重要な注意については、次のセクション Important Notes and Caveats for ORM-Enabled Update and Delete をお読みください。

Important Notes and Caveats for ORM-Enabled Update and Delete

ORM対応のUPDATEとDELETE機能は、ORM unit of work の自動化をバイパスして、複雑にならずに一度に複数の行に一致する単一のUPDATEまたはDELETE文を発行できるようにします。

• これらの操作は、Python内での関係のカスケードを提供しません。ON UPDATE CASCADEおよび/またはON DELETE CASCADEが、それを必要とする任意の外部キー参照に対して設定されていることを前提としています。そうでなければ、外部キー参照が強制されている場合、データベースは整合性違反を発行する可能性があります。いくつかの例については、 Using foreign key ON DELETE cascade with ORM relationships の注を参照してください。

• UPDATEまたはDELETEの後、関連するテーブルのON UPDATE CASCADEまたはON DELETE CASCADEによって影響を受けた Session 内の依存オブジェクト、特に削除された行を参照するオブジェクトは、それらのオブジェクトを参照する可能性があります。この問題は、 Session が期限切れになると解決されます。これは通常、 Session.commit() によって発生しますが、 Session.expire_all() を使用して強制することもできます。

• ORMが有効なUPDATEとDELETEは、結合されたテーブルの継承を自動的に処理しません。結合された継承のマッピングの扱い方については UPDATE/DELETE with Custom WHERE Criteria for Joined Table Inheritance の節を参照してください。

• 単一テーブル継承マッピングの特定のサブクラスに多様IDを制限するために必要なWHERE基準は、 自動的に含まれます 。これは、独自のテーブルを持たないサブクラスマッパーにのみ適用されます。

• with_loader_criteria() オプションは、ORMの更新および削除操作で サポート されています。ここでの条件は、発行されるUPDATE文またはDELETE文の条件に追加され、”同期化”処理中に考慮されます。

• ORM対応のUPDATE操作とDELETE操作をイベントハンドラでインターセプトするには、 SessionEvents.do_orm_execute() イベントを使用してください。

Selecting a Synchronization Strategy

update() または delete() を、 Session.execute() を使用したORM対応の実行と組み合わせて使用する場合、追加のORM固有の機能が存在します。これは、文によって変更される状態を、 Session の identity map 内に現在存在するオブジェクトの状態と 同期化 します。”同期化”とは、更新された属性が新しい値で更新されるか、少なくとも expired で更新されて、次のアクセス時に新しい値で再読み込みされ、削除されたオブジェクトが DELETEd 状態に移動されることを意味します。

この同期は”同期戦略”として制御可能で、文字列のORM実行オプションとして渡されます。通常は Session.execute.execution_options 辞書を使います:

>>> from sqlalchemy import update
>>> stmt = (
...     update(User).where(User.name == "squidward").values(fullname="Squidward Tentacles")
... )
>>> session.execute(stmt, execution_options={"synchronize_session": False})
UPDATE user_account SET fullname=? WHERE user_account.name = ?
[...] ('Squidward Tentacles', 'squidward')
<...>

実行オプションは、 Executable.execution_options() メソッドを使用して、文自体にバンドルすることもできます。:

>>> from sqlalchemy import update
>>> stmt = (
...     update(User)
...     .where(User.name == "squidward")
...     .values(fullname="Squidward Tentacles")
...     .execution_options(synchronize_session=False)
... )
>>> session.execute(stmt)
UPDATE user_account SET fullname=? WHERE user_account.name = ?
[...] ('Squidward Tentacles', 'squidward')
<...>

synchronize_session では以下の値がサポートされています。:

• auto - これがデフォルトです。RETURNINGをサポートするバックエンドでは、MySQLを除くすべてのSQLAlchemyネイティブドライバを含む fetch 戦略が使用されます。RETURNINGがサポートされていない場合は、代わりに evaluate 戦略が使用されます。

• 'fetch' - UPDATEやDELETEの前にSELECTを実行するか、データベースがサポートしていればRETURNINGを使用して、操作の影響を受けるメモリ内のオブジェクトを新しい値で更新(更新)したり、 Session から削除(削除)したりして、影響を受ける行のプライマリキーのIDを取得します。この同期方法は、与えられた update() や delete() 構文が UpdateBase.returning() を使ってエンティティや列を明示的に指定している場合でも使用できます。

  Changed in version 2.0: ORM対応のUPDATEとDELETEをWHERE条件付きで使用する場合、明示的な UpdateBase.returning() を 'fetch' 同期戦略と組み合わせることができます。実際の文には、 'fetch' 戦略が必要とする列と要求された列の間の列の結合が含まれます。

• 'evaluate' - これは、PythonのUPDATE文やDELETE文で与えられたWHERE条件を評価し、 Session 内で一致するオブジェクトを見つけることを示します。この方法は、操作にSQLのラウンドトリップを追加せず、RETURNINGサポートがない場合には、より効率的かもしれません。複雑な条件を持つUPDATE文やDELETE文では、Pythonの式を評価できない可能性があり、エラーが発生します。このような場合は、代わりに操作に対して 'evaluate' 戦略を使用してください。このような場合は、代わりに操作に 'fetch' 方法を使ってください。

  Tip

  SQL式が Operators.op() または custom_op 機能を使用したカスタム演算子を使用する場合、 Operators.op.python_impl パラメータを使用して、 "evaluate" 同期戦略で使用されるPython関数を示すことができます。

  New in version 2.0.

  Warning

  多くのオブジェクトが期限切れになっている Session に対してUPDATE操作を実行する場合は、それぞれにSELECTを発行する指定されたWHERE条件に対してオブジェクトをテストするためにオブジェクトを更新する必要があるため、"evaluate" 戦略は避けるべきです。この場合、特にバックエンドがRETURNINGをサポートしている場合は、 "fetch" 戦略を優先すべきです。

• False-セッションを同期させません。このオプションは、バックエンドがRETURNINGをサポートしておらず、かつ "evaluate" 戦略を使用できない場合に便利です。この場合、 Session 内のオブジェクトの状態は変更されず、通常はマッチした行に対応するオブジェクトが存在する場合、生成されたUPDATE文やDELETE文に自動的に対応することはありません。

Using RETURNING with UPDATE/DELETE and Custom WHERE Criteria

UpdateBase.returning() メソッドは、ORM対応のUPDATEおよびWHERE条件付きDELETEと完全に互換性があります。完全なORMオブジェクトおよび/または列がRETURNINGに示されることがあります:

>>> from sqlalchemy import update
>>> stmt = (
...     update(User)
...     .where(User.name == "squidward")
...     .values(fullname="Squidward Tentacles")
...     .returning(User)
... )
>>> result = session.scalars(stmt)
UPDATE user_account SET fullname=? WHERE user_account.name = ?
RETURNING id, name, fullname, species
[...] ('Squidward Tentacles', 'squidward')
>>> print(result.all())
[User(name='squidward', fullname='Squidward Tentacles')]

RETURNINGのサポートは、同じくRETURNINGを使用する fetch 同期戦略とも互換性があります。ORMはRETURNINGの列を適切に編成して、同期が進行するとともに、返された Result に要求されたエンティティとSQL列が要求された順序で含まれるようにします。

New in version 2.0: UpdateBase.returning() は、ORM対応のUPDATEとDELETEに使用できますが、同時に fetch 同期戦略との完全な互換性も保持しています。

UPDATE/DELETE with Custom WHERE Criteria for Joined Table Inheritance

WHERE条件付きUPDATE/DELETE機能は、 ORM Bulk UPDATE by Primary Key とは異なり、 Session.execute() への呼び出しごとに1つのUPDATE文またはDELETE文のみを発行します。つまり、結合テーブル継承マッピングのサブクラスなど、複数テーブルのマッピングに対して update() または delete() 文を実行する場合、その文はバックエンドの現在の機能に準拠している必要があります。これには、バックエンドが複数テーブルを参照するUPDATE文またはDELETE文をサポートしていないことや、サポートが制限されていることが含まれます。つまり、結合継承サブクラスなどのマッピングでは、WHERE条件付きUPDATE/DELETE機能のORMバージョンは、仕様に応じて、限られた範囲でしか使用できないか、まったく使用できないことを意味します。

ジョインされたテーブルのサブクラスに対して複数行のUPDATE文を発行する最も簡単な方法は、サブテーブルのみを参照することです。つまり、 Update() 構文は、以下の例のように、サブクラステーブルに対してローカルな属性のみを参照する必要があります。:

>>> stmt = (
...     update(Manager)
...     .where(Manager.id == 1)
...     .values(manager_name="Sandy Cheeks, President")
... )
>>> session.execute(stmt)
UPDATE manager SET manager_name=? WHERE manager.id = ?
[...] ('Sandy Cheeks, President', 1)
<...>

上記の形式では、任意のSQLバックエンドで動作する行を見つけるためにベーステーブルを参照する基本的な方法は、副問い合わせを使用することです。:

>>> stmt = (
...     update(Manager)
...     .where(
...         Manager.id
...         == select(Employee.id).where(Employee.name == "sandy").scalar_subquery()
...     )
...     .values(manager_name="Sandy Cheeks, President")
... )
>>> session.execute(stmt)
UPDATE manager SET manager_name=? WHERE manager.id = (SELECT employee.id
FROM employee
WHERE employee.name = ?) RETURNING id
[...] ('Sandy Cheeks, President', 'sandy')
<...>

UPDATE.FROMをサポートするバックエンドでは、副問い合わせを追加のWHERE条件として記述することもできますが、2つのテーブル間の条件は何らかの方法で明示的に記述する必要があります。:

>>> stmt = (
...     update(Manager)
...     .where(Manager.id == Employee.id, Employee.name == "sandy")
...     .values(manager_name="Sandy Cheeks, President")
... )
>>> session.execute(stmt)
UPDATE manager SET manager_name=? FROM employee
WHERE manager.id = employee.id AND employee.name = ?
[...] ('Sandy Cheeks, President', 'sandy')
<...>

DELETEの場合、ベース・テーブルとサブテーブルの両方のローが同時にDELETEされることが想定されています。カスケード外部キーを 使用せずに 、ジョインされた継承オブジェクトの多くの行をDELETEするには、各テーブルに対して個別にDELETEを発行します。:

>>> from sqlalchemy import delete
>>> session.execute(delete(Manager).where(Manager.id == 1))
DELETE FROM manager WHERE manager.id = ?
[...] (1,)
<...>
>>> session.execute(delete(Employee).where(Employee.id == 1))
DELETE FROM employee WHERE employee.id = ?
[...] (1,)
<...>

全体として、通常の unit of work プロセスは、カスタムWHERE基準を使用するパフォーマンス上の理由がない限り、結合された継承やその他のマルチテーブルマッピングの行の更新と削除に**推奨**されるべきです。

Legacy Query Methods

WHEREを使用したORM対応のUPDATE/DELETE機能は、元々は Query.update() および Query.delete() メソッド内の、現在はレガシーになった Query オブジェクトの一部でした。これらのメソッドは引き続き使用可能で、 ORM UPDATE and DELETE with Custom WHERE Criteria で説明されているものと同じ機能のサブセットを提供します。主な違いは、レガシーメソッドが明示的なRETURNINGサポートを提供しないことです。

See also

Query.update()

Query.delete()

ORM Querying Guide

Next Query Guide Section: Column Loading Options