SqlAlchemy 2.0 中文文档（十七）（4）-阿里云开发者社区

SqlAlchemy 2.0 中文文档（十七）（3）https://developer.aliyun.com/article/1562954

使用 SQL 表达式进行 ORM 批量插入

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，然后使用“批量”模式传递附加记录：

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

通过每行 SQL 表达式进行 ORM 批量插入

Insert.values() 方法本身直接支持参数字典列表。当以这种方式使用 Insert 构造时，如果没有将参数字典列表传递给 Session.execute.params 参数，将不会使用批量 ORM 插入模式，而是将 INSERT 语句按原样呈现并精确调用一次。这种操作模式可能在按行基础上传递 SQL 表达式的情况下非常有用，并且在使用 ORM 进行“upsert”语句时也会使用，后文会在本章节中的 ORM “upsert” Statements 进行详细记录。

以下是嵌入每行 SQL 表达式的 INSERT 的人为示例，同时也演示了这种形式中的 Insert.returning()：

>>> 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 插入模式，因此以下特性不可用：

不支持联接表继承或其他多表映射，因为那将需要多个 INSERT 语句。
不支持异构参数集 - VALUES 集合中的每个元素必须具有相同的列。
不提供诸如 insertmanyvalues 所提供的批处理等核心级别的规模优化；语句将需要确保参数总数不超过由后端数据库施加的限制。

出于上述原因，通常不建议在 ORM INSERT 语句中使用多个参数集合Insert.values()，除非有明确的理由，即要么使用了“upsert”，要么需要在每个参数集合中嵌入每行 SQL 表达式。

另请参阅

ORM“upsert”语句 #### 使用每行 SQL 表达式进行 ORM 批量插入

Insert.values()方法本身直接支持参数字典列表。当以这种方式使用Insert构造时，在不将任何参数字典列表传递给Session.execute.params参数的情况下，将不使用批量 ORM 插入模式，而是完全按照给定的方式呈现 INSERT 语句，并且仅调用一次。这种操作模式对于在每行基础上传递 SQL 表达式的情况可能很有用，并且在使用 ORM 时使用“upsert”语句时也会使用，后文将在本章的 ORM“upsert”语句中进行说明。

下面是一个虚构的示例，演示了嵌入每行 SQL 表达式的 INSERT，并以这种形式演示了Insert.returning()：

>>> 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 插入模式，因此不存在以下功能：

连接表继承或其他多表映射不受支持，因为这将需要多个 INSERT 语句。
不支持异构参数集合 - VALUES 集合中的每个元素必须具有相同的列。
不可用核心级别的缩放优化，例如 insertmanyvalues 提供的批处理；语句需要确保参数的总数不超过由支持数据库施加的限制。

另请参阅

ORM “upsert”语句

旧版会话批量插入方法

Session包括用于执行“批量”INSERT 和 UPDATE 语句的传统方法。这些方法与 SQLAlchemy 2.0 版本的这些特性共享实现，描述在 ORM 批量 INSERT 语句和 ORM 主键批量 UPDATE 中，但缺少许多功能，特别是缺少对 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"}])

另请参阅

旧版会话批量 UPDATE 方法

ORM “upsert” 语句

SQLAlchemy 的部分后端可能包含特定于方言的Insert构造，此外还具有执行“upserts”或将参数集中的现有行转换为近似 UPDATE 语句的能力。通过“现有行”，这可能意味着具有相同主键值的行，或者可能是指其他被认为是唯一的行中的索引列；这取决于正在使用的后端的功能。

SQLAlchemy 包含的方言特定“upsert”API 功能的方言包括：

SQLite - 使用Insert，在 INSERT…ON CONFLICT (Upsert)有详细说明。
PostgreSQL - 使用Insert，在 INSERT…ON CONFLICT (Upsert)有详细说明。
MySQL/MariaDB - 使用Insert，在 INSERT…ON DUPLICATE KEY UPDATE (Upsert)有详细说明。

用户应该查看上述部分了解这些对象的正确构造背景；特别是，“upsert”方法通常需要引用原始语句，因此语句通常是分两步构建的。

第三方后端，如在外部方言中提到的那些，也可能具有类似的构造。

虽然 SQLAlchemy 还没有与后端无关的 upsert 构造，但以上的Insert变体仍然与 ORM 兼容，因为它们可以像文档中记录的Insert构造本身一样使用，即通过在Insert.values()方法中嵌入要插入的行。在下面的示例中，使用 SQLite insert()函数生成一个包含“ON CONFLICT DO UPDATE”支持的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)
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')
<...>

使用 RETURNING 与 upsert 语句

从 SQLAlchemy ORM 的角度来看，upsert 语句看起来像是常规的Insert构造，其中包括Insert.returning()与 upsert 语句的工作方式相同，就像在 ORM 批量插入与每行 SQL 表达式中演示的那样，因此可以传递任何列表达式或相关的 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 对象。该示例还添加了对现有数据的填充执行选项的使用。该选项指示对于已经存在于 Session 中的行，应该使用新行的数据刷新User对象。对于纯粹的 Insert 语句，此选项不重要，因为每个生成的行都是全新的主键标识。但是，当 Insert 还包括“upsert”选项时，它可能也会产生已经存在的行的结果，因此可能已经在 Session 对象的身份映射中表示了主键标识。

另请参阅

使用 RETURNING 的 upsert 语句 #### 使用 RETURNING 语句的合并插入

从 SQLAlchemy ORM 的角度来看，upsert 语句看起来像是常规的 Insert 构造，这包括 Insert.returning() 在与示例每行 SQL 表达式的 ORM 批量插入中展示的方式上与 upsert 语句一样工作，因此可以传递任何列表达式或相关的 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')]

另请参阅

填充现有

根据主键进行 ORM 批量更新

Update 构造可以与 Session.execute() 一起使用，类似于描述的 Insert 语句在 ORM 批量插入语句中的使用方式，传递许多参数字典的列表，每个字典代表一个对应于单个主键值的单独行。这种用法不应与更常见的使用 Update 语句与 ORM 一起使用的方式混淆，使用显式的 WHERE 子句，该方式在 ORM 更新和删除自定义 WHERE 条件中有记录。

对于 UPDATE 的“批量”版本，将根据 ORM 类制作一个 update() 构造，并传递给 Session.execute() 方法；生成的 Update 对象应该没有值，通常也没有 WHERE 条件，也就是说，不使用 Update.values() 方法，通常也不使用 Update.where()，但在需要添加额外过滤条件的不寻常情况下可能会使用。

传递包含完整主键值的参数字典列表以及 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)]
<...>

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

像批量插入功能一样，这里也支持异构参数列表，其中参数将被分组为更新运行的子批次。

在 2.0.11 版本中更改：可以使用Update.where()方法添加额外的 WHERE 条件与 ORM 按主键批量更新相结合。但是，此条件始终是额外添加的，这包括主键值。

在使用“按主键批量更新”功能时，不支持 RETURNING 功能；多个参数字典列表必须使用 DBAPI 的 executemany，通常情况下不支持结果行。

在 2.0 版本中更改：将Update构造传递给Session.execute()方法，以及参数字典列表，现在会调用“批量更新”，这与传统的Session.bulk_update_mappings()方法使用相同的功能。这是与 1.x 系列不同的行为更改，1.x 系列中的Update只支持显式的 WHERE 条件和内联 VALUES。

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

当满足以下条件时，自动使用 ORM 按主键批量更新功能：

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

为了在不使用“ORM 按主键批量更新”功能的情况下调用 UPDATE 语句，请直接针对Connection使用Session.connection()方法来获取当前事务的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')]
<...>

另请参阅

按行 ORM 按主键批量更新需要记录包含主键值 ### 按主键批量更新联合表继承

当使用具有联合表继承的映射时，ORM 批量更新与 ORM 批量插入具有类似的行为；如在 Bulk INSERT for Joined Table Inheritance 中所述，批量 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)]
<...>
```### 旧版 Session 批量更新方法
如在旧版 Session 批量插入方法中讨论的那样，`Session.bulk_update_mappings()`方法是批量更新的旧版形式，ORM 在解释给定带有主键参数的`update()`语句时内部使用；但是，当使用旧版时，不包括诸如会话同步支持等功能。
下面的示例：
```py
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"},
 ],
)

另请参阅

旧版 Session 批量插入方法 ### 禁用 UPDATE 语句的多参数集的基于主键的批量 ORM 更新

当满足以下条件时，会自动使用基于主键的 ORM 批量更新功能，该功能对每个包含主键值的记录运行 UPDATE 语句，并包括每个主键值的 WHERE 条件：

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

为了调用 UPDATE 语句而不使用“基于主键的 ORM 批量更新”，直接使用Session.connection()方法针对当前事务获取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')]
<...>

另请参阅

基于主键的逐行 ORM 批量更新要求记录包含主键值

基于主键的联合表继承批量更新

ORM 批量更新在使用具有联合表继承的映射时与 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)]
<...>

旧版会话批量更新方法

如旧版会话批量插入方法中所讨论的，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"},
 ],
)

另请参阅

旧版会话批量插入方法

SqlAlchemy 2.0 中文文档（十七）（5）https://developer.aliyun.com/article/1562956

SqlAlchemy 2.0 中文文档（十七）（4）

使用 SQL 表达式进行 ORM 批量插入

通过每行 SQL 表达式进行 ORM 批量插入

旧版会话批量插入方法

ORM “upsert” 语句

使用 RETURNING 与 upsert 语句

根据主键进行 ORM 批量更新

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

基于主键的联合表继承批量更新

旧版会话批量更新方法

热门文章

最新文章

相关课程

相关电子书