启用 ORM 的 INSERT、UPDATE 和 DELETE 语句¶

ORM-Enabled INSERT, UPDATE, and DELETE statements

中文

关于本文档

本节使用了在 SQLAlchemy 统一教程中首次介绍的 ORM 映射，展示在声明映射类部分，以及在映射类继承层次结构部分展示的继承映射。

Session.execute() 方法除了处理启用 ORM 的 Select 对象外，还可以以各种方式处理启用 ORM 的 Insert、Update 和 Delete 对象，这些方式都用于一次性 INSERT、UPDATE 或 DELETE 多个数据库行。还支持方言特定的启用 ORM 的 “upserts”，即自动使用 UPDATE 的 INSERT 语句，用于已存在的行。

下表总结了本文档中讨论的调用形式：

ORM 用例	使用的 DML	数据通过 … 传递	支持 RETURNING?	支持多表映射?
ORM 批量 INSERT 语句	`insert()`	List of dictionaries to `Session.execute.params`	yes	yes
使用 SQL 表达式的 ORM 批量插入	`insert()`	`Session.execute.params` with `Insert.values()`	yes	yes
使用每行 SQL 表达式的 ORM 批量插入	`insert()`	List of dictionaries to `Insert.values()`	yes	no
ORM“upsert”语句	`insert()`	List of dictionaries to `Insert.values()`	yes	no
ORM 按主键批量 UPDATE	`update()`	List of dictionaries to `Session.execute.params`	no	yes
使用自定义 WHERE 条件的 ORM UPDATE 和 DELETE	`update()`, `delete()`	keywords to `Update.values()`	yes	部分，采用手动步骤

英文

About this Document

This section makes use of ORM mappings first illustrated in the SQLAlchemy 统一教程, shown in the section 声明映射类, as well as inheritance mappings shown in the section 映射类继承层次结构.

View the ORM setup for this page.

The Session.execute() method, in addition to handling ORM-enabled Select objects, can also accommodate ORM-enabled Insert, Update and Delete objects, in various ways which are each used to INSERT, UPDATE, or DELETE many database rows at once. There is also dialect-specific support for ORM-enabled “upserts”, which are INSERT statements that automatically make use of UPDATE for rows that already exist.

The following table summarizes the calling forms that are discussed in this document:

ORM Use Case	DML Construct Used	Data is passed using …	Supports RETURNING?	Supports Multi-Table Mappings?
ORM 批量 INSERT 语句	`insert()`	List of dictionaries to `Session.execute.params`	yes	yes
使用 SQL 表达式的 ORM 批量插入	`insert()`	`Session.execute.params` with `Insert.values()`	yes	yes
使用每行 SQL 表达式的 ORM 批量插入	`insert()`	List of dictionaries to `Insert.values()`	yes	no
ORM“upsert”语句	`insert()`	List of dictionaries to `Insert.values()`	yes	no
ORM 按主键批量 UPDATE	`update()`	List of dictionaries to `Session.execute.params`	no	yes
使用自定义 WHERE 条件的 ORM UPDATE 和 DELETE	`update()`, `delete()`	keywords to `Update.values()`	yes	partial, with manual steps

ORM 按主键批量 UPDATE¶

ORM Bulk UPDATE by Primary Key

中文

Update 构造可以像 Insert 语句那样与 Session.execute() 一起使用，传递多个参数字典的列表，每个字典表示一个对应单个主键值的行。需要注意的是，这种用法不应与使用显式 WHERE 子句的更常见的 Update 语句用法混淆，这种用法在使用自定义 WHERE 条件的 ORM UPDATE 和 DELETE 中有详细说明。

对于“批量”版本的 UPDATE，首先创建一个基于 ORM 类的 update() 构造，并将其传递给 Session.execute() 方法；生成的 Update 对象通常 不包含值，并且通常不包含 WHERE 条件 ，也就是说，通常不使用 Update.values() 方法，且 Update.where() 通常不使用，但在某些特殊情况下可以使用，以添加额外的过滤条件。

传递 Update 构造及其参数字典列表（每个字典包含完整的主键值）将启动 按主键批量更新模式，为每一行生成相应的 WHERE 条件，并使用 executemany 将每个参数集应用于 UPDATE 语句:

>>> from sqlalchemy importupdate>>> 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)]
<...>

请注意，每个参数字典 必须包含每条记录的完整主键，否则会引发错误。

与批量 INSERT 功能类似，这里也支持异构参数列表，其中参数将被分成 UPDATE 运行的子批次。

在 2.0.11 版本发生变更: 通过使用 Update.where() 方法添加额外的条件，可以将额外的 WHERE 条件与 ORM 按主键批量 UPDATE 结合使用。然而，这些条件始终是与已经存在的 WHERE 条件一起使用的，后者包括主键值。

当使用“按主键批量更新”功能时，RETURNING 功能不可用；多个参数字典的列表必然使用 DBAPI executemany，而通常这种形式不支持返回结果行。

在 2.0 版本发生变更: 将 Update 构造传递给 Session.execute() 方法，并与参数字典列表一起使用时，现在会调用“批量更新”，其功能与旧版的 Session.bulk_update_mappings() 方法相同。这与 1.x 系列的行为有所不同，1.x 系列中 Update 仅支持显式的 WHERE 条件和内联 VALUES。

英文

The Update construct may be used with Session.execute() in a similar way as the Insert statement is used as described at ORM 批量 INSERT 语句, passing a list of many parameter dictionaries, each dictionary representing an individual row that corresponds to a single primary key value. This use should not be confused with a more common way to use Update statements with the ORM, using an explicit WHERE clause, which is documented at 使用自定义 WHERE 条件的 ORM UPDATE 和 DELETE.

For the “bulk” version of UPDATE, a update() construct is made in terms of an ORM class and passed to the Session.execute() method; the resulting Update object should have no values and typically no WHERE criteria, that is, the Update.values() method is not used, and the Update.where() is usually not used, but may be used in the unusual case that additional filtering criteria would be added.

Passing the Update construct along with a list of parameter dictionaries which each include a full primary key value will invoke bulk UPDATE by primary key mode for the statement, generating the appropriate WHERE criteria to match each row by primary key, and using executemany to run each parameter set against the UPDATE statement:

>>> from sqlalchemy importupdate>>> 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)]
<...>

Note that each parameter dictionary must include a full primary key for each record, else an error is raised.

Like the bulk INSERT feature, heterogeneous parameter lists are supported here as well, where the parameters will be grouped into sub-batches of UPDATE runs.

在 2.0.11 版本发生变更: Additional WHERE criteria can be combined with ORM 按主键批量 UPDATE 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.

The RETURNING feature is not available when using the “bulk UPDATE by primary key” feature; the list of multiple parameter dictionaries necessarily makes use of DBAPI executemany, which in its usual form does not typically support result rows.

在 2.0 版本发生变更: Passing an Update construct to the Session.execute() method along with a list of parameter dictionaries now invokes a “bulk update”, which makes use of the same functionality as the legacy Session.bulk_update_mappings() method. This is a behavior change compared to the 1.x series where the Update would only be supported with explicit WHERE criteria and inline VALUES.

禁用具有多个参数集的 UPDATE 语句的按主键批量 ORM 更新¶

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

中文

ORM 按主键批量更新功能会针对每条记录运行一个 UPDATE 语句，并为每个主键值添加 WHERE 条件，当满足以下条件时会自动启用此功能：

给定的 UPDATE 语句是针对 ORM 实体的
使用 Session 执行语句，而不是 Core 的 Connection
传递的参数是一个 字典列表。

若要在不使用“ORM 按主键批量更新”的情况下调用 UPDATE 语句，可以直接通过 Session.connection() 方法获取当前的 Connection，然后在事务中执行该语句:

>>> from sqlalchemy importbindparam>>> 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')]
<...>

参见

每行 ORM 按主键批量更新要求记录包含主键值

英文

The ORM Bulk Update by Primary Key feature, which runs an UPDATE statement per record which includes WHERE criteria for each primary key value, is automatically used when:

the UPDATE statement given is against an ORM entity
the Session is used to execute the statement, and not a Core Connection
The parameters passed are a list of dictionaries.

In order to invoke an UPDATE statement without using “ORM Bulk Update by Primary Key”, invoke the statement against the Connection directly using the Session.connection() method to acquire the current Connection for the transaction:

>>> from sqlalchemy importbindparam>>> 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')]
<...>

参见

每行 ORM 按主键批量更新要求记录包含主键值

用于连接表继承的按主键批量 UPDATE¶

Bulk UPDATE by Primary Key for Joined Table Inheritance

中文

ORM 批量更新在使用带有联合表继承的映射时，行为与 ORM 批量插入类似；如用于连接表继承的批量 INSERT 中所述，批量 UPDATE 操作会为映射中每个表发出一个 UPDATE 语句，其中给定的参数包括需要更新的值（未受影响的表会被跳过）。

示例:

>>> 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)]
<...>

英文

ORM bulk update has similar behavior to ORM bulk insert when using mappings with joined table inheritance; as described at 用于连接表继承的批量 INSERT, the bulk UPDATE operation will emit an UPDATE statement for each table represented in the mapping, for which the given parameters include values to be updated (non-affected tables are skipped).

Example:

>>> 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)]
<...>

旧会话批量 UPDATE 方法¶

Legacy Session Bulk UPDATE Methods

中文

如旧会话批量 INSERT 方法中所述，:meth:`_orm.Session.bulk_update_mappings 方法是 Session 的遗留批量更新形式，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”},

],

)

参见

旧会话批量 INSERT 方法

英文

As discussed at 旧会话批量 INSERT 方法, the Session.bulk_update_mappings() method of Session is the legacy form of bulk update, which the ORM makes use of internally when interpreting a update() statement with primary key parameters given; however, when using the legacy version, features such as support for session-synchronization are not included.

The example below:

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"},
    ],
)

Is expressed using the new API as:

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"},
    ],
)

参见

旧会话批量 INSERT 方法

启用 ORM 的 INSERT、UPDATE 和 DELETE 语句¶

ORM 批量 INSERT 语句¶

使用 RETURNING 获取新对象¶

将 RETURNING 记录与输入数据顺序关联¶

使用异构参数字典¶

在 ORM 批量 INSERT 语句中发送 NULL 值¶

用于连接表继承的批量 INSERT¶

使用 SQL 表达式的 ORM 批量插入¶

使用每行 SQL 表达式的 ORM 批量插入¶

旧会话批量 INSERT 方法¶

ORM“upsert”语句¶

将 RETURNING 与 upsert 语句结合使用¶

ORM 按主键批量 UPDATE¶

禁用具有多个参数集的 UPDATE 语句的按主键批量 ORM 更新¶

用于连接表继承的按主键批量 UPDATE¶

旧会话批量 UPDATE 方法¶

使用自定义 WHERE 条件的 ORM UPDATE 和 DELETE¶

启用 ORM 的更新和删除的重要说明和注意事项¶

选择同步策略¶

使用 RETURNING 和 UPDATE/DELETE 以及自定义 WHERE 条件¶

使用 UPDATE/DELETE 和自定义 WHERE 条件进行连接表继承¶

旧式查询方法¶