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

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

使用 RETURNING 获取新对象

批量 ORM 插入功能支持为选定的后端进行 INSERT…RETURNING，该功能可以返回一个 Result 对象，该对象可以返回单个列以及对应于新生成记录的完全构造的 ORM 对象。INSERT…RETURNING 需要使用支持 SQL RETURNING 语法以及支持带有 RETURNING 的 executemany 的后端；除了 MySQL（包括 MariaDB）之外，所有 SQLAlchemy 包含的后端都支持此功能。

例如，我们可以运行与之前相同的语句，添加使用 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 功能使用的形式，其中个别参数字典被内联到单个 INSERT 语句中，以便使用 RETURNING。

在 2.0 版本中更改：ORM Session 现在在 ORM 上下文中解释来自 Insert、Update 甚至 Delete 构造的 RETURNING 子句，这意味着可以将一系列列表达式和 ORM 映射实体传递给 Insert.returning() 方法，然后以从构造物如 Select 传递 ORM 结果的方式传递，包括映射实体将作为 ORM 映射对象在结果中传递。还存在对于 ORM 加载器选项的有限支持，如 load_only() 和 selectinload()。

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

使用带有 RETURNING 的批量 INSERT 时，重要的是要注意，大多数数据库后端不保证从 RETURNING 返回的记录的顺序，包括不能保证它们的顺序与输入记录的顺序对应。对于需要确保 RETURNING 记录与输入数据相关联的应用程序，可以指定额外的参数 Insert.returning.sort_by_parameter_order，根据后端的不同，可能使用特殊的 INSERT 表单来维护一个标记，该标记用于适当地重新排序返回的行，或者在某些情况下，例如在下面使用 SQLite 后端的示例中，操作将一次插入一行：

>>> 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}]

2.0.10 新内容：添加了 Insert.returning.sort_by_parameter_order，该内容在 insertmanyvalues 架构中实现。

另请参阅

将 RETURNING 行与参数集相关联 - 关于保证输入数据和结果行之间对应关系的方法的背景信息，而又不显著降低性能 ### 使用异构参数字典

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')

在上面的示例中，传递的五个参数字典被转换为三个 INSERT 语句，按照每个字典中特定键的组合进行分组，同时保持行顺序，即 ("name", "fullname", "species")、 ("name", "species")、 ("name","fullname", "species")。### 在 ORM 批量 INSERT 语句中发送 NULL 值

批量 ORM 插入功能借鉴了遗留的“批量”插入行为，以及 ORM 工作单元总体上的行为，即包含 NULL 值的行将使用不引用这些列的语句进行插入；这样做的理由是，包含服务器端插入默认值的后端和模式可能对 NULL 值的存在与不存在敏感，将产生预期的服务器端值。这种默认行为会将批量插入的批次分解成更多行数较少的批次：

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

在上面的示例中，四行的批量插入被分成三个单独的语句，第二个语句重新格式化以不引用包含 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')]
...

在上面的示例中，所有参数字典都在单个 INSERT 批次中发送，包括第三个参数字典中的 None 值。

从版本 2.0.23 开始：添加了 render_nulls 执行选项，其反映了遗留的 Session.bulk_insert_mappings.render_nulls 参数的行为。### 关于连接表继承的批量 INSERT

ORM 批量插入建立在传统的 unit of work 系统使用的内部系统之上，以发出 INSERT 语句。这意味着对于一个被映射到多个表的 ORM 实体，通常是使用 joined table inheritance 映射的实体，批量插入操作将为每个由映射表示的表发出一个 INSERT 语句，正确地将服务器生成的主键值传递给依赖于它们的表行。RETURNING 特性在这里也受支持，ORM 将为每个执行的 INSERT 语句接收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')

提示

插入连接继承映射的批量操作要求 ORM 内部使用 Insert.returning.sort_by_parameter_order 参数，以便它可以将来自 RETURNING 行的主键值从基表相关联到用于插入到“子”表中的参数集，这就是为什么上面示例中的 SQLite 后端会透明地降级到使用非批处理语句的原因。关于此功能的背景请参阅将 RETURNING 行与参数集相关联。### 带 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” 语句。

下面是一个人为的示例，展示了嵌入每行 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 语句。
不支持异构参数集 - 值集中的每个元素必须具有相同的列。
不可用于核心级别的规模优化，例如 insertmanyvalues 提供的批处理；语句需要确保参数的总数不超过由支持数据库施加的限制。

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

另请参见

ORM “upsert” 语句 ### 传统会话批量插入方法

Session 包括执行“批量”插入和更新语句的传统方法。这些方法与 SQLAlchemy 2.0 版本的这些功能共享实现，描述在 ORM 批量插入语句和 ORM 按主键批量更新，但缺少许多功能，即不支持 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"}])

另请参阅

传统会话批量更新方法 ### 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()与在 ORM Bulk Insert with Per Row SQL Expressions 中演示的方式一样与 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')]

以上示例使用 RETURNING 来返回由语句插入或更新的每一行的 ORM 对象。该示例还添加了对已有对象填充执行选项的使用。此选项表示对于已存在的 Session 中已经存在的行的 User 对象应该使用新行的数据进行刷新。对于纯 Insert 语句来说，此选项并不重要，因为每个生成的行都是全新的主键标识。但是当 Insert 还包括“upsert”选项时，它也可能会产生来自已存在行的结果，因此这些行可能已经在 Session 对象的标识映射中表示了主键标识。

另见

已有对象填充 ### 使用 RETURNING 获取新对象

批量 ORM 插入功能支持选定后端的 INSERT…RETURNING，它可以返回一个 Result 对象，该对象可以返回单独的列以及与新生成记录相对应的完全构造的 ORM 对象。INSERT…RETURNING 需要使用支持 SQL RETURNING 语法以及支持带 RETURNING 的 executemany 的后端；除了 MySQL（MariaDB 已包含在内）之外，此功能在所有 SQLAlchemy 包含的后端中都可用。

例如，我们可以运行与之前相同的语句，添加对 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 功能使用的形式，其中个别参数字典被内联到单个 INSERT 语句中，以便使用 RETURNING。

2.0 版本中的更改：ORM Session 现在会在 ORM 上下文中解释来自 Insert、Update 甚至 Delete 构造的 RETURNING 子句，这意味着可以传递混合的列表达式和 ORM 映射实体给 Insert.returning() 方法，然后将以与 Select 等构造中的 ORM 结果相同的方式传递，包括将映射实体作为 ORM 映射对象在结果中传递。还存在对 ORM 加载器选项（例如 load_only() 和 selectinload()）的有限支持。

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

在使用带有 RETURNING 的批量 INSERT 时，重要的是要注意，大多数数据库后端没有明确保证返回的 RETURNING 记录的顺序，包括没有保证其顺序与输入记录的顺序相对应。对于需要确保 RETURNING 记录与输入数据相关联的应用程序，可以指定额外的参数 Insert.returning.sort_by_parameter_order，这取决于后端可能使用特殊的 INSERT 形式，以保持适当地重新排序返回的行，或者在某些情况下，例如在使用 SQLite 后端的下面示例中，该操作将逐行插入：

>>> 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}]

新版本 2.0.10 中新增了 Insert.returning.sort_by_parameter_order，该功能是在 insertmanyvalues 架构内实现的。

请参见

将 RETURNING 行与参数集相关联 - 关于采取的方法背景，以确保输入数据与结果行之间的对应关系，而不会显著降低性能 #### 将 RETURNING 记录与输入数据顺序相关联

当使用带有 RETURNING 的批量 INSERT 时，重要的是要注意，大多数数据库后端不保证返回 RETURNING 记录的顺序，包括它们的顺序与输入记录的顺序相对应这一点。对于需要确保 RETURNING 记录与输入数据相关联的应用程序，可以指定额外的参数 Insert.returning.sort_by_parameter_order，具体取决于后端，它可能使用特殊的 INSERT 形式来维护一个令牌，该令牌用于适当地重新排序返回的行，或者在某些情况下，例如使用 SQLite 后端的以下示例中，该操作将一次插入一行：

>>> 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}]

从 2.0.10 版开始：新增了 Insert.returning.sort_by_parameter_order，它是在 insertmanyvalues 架构中实现的。

另请参阅

将 RETURNING 行与参数集对应起来 - 关于采取的方法，以确保输入数据与结果行之间的对应关系而不会显著降低性能

使用异构参数字典

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')

在上面的示例中，传递的五个参数字典被转换为三个 INSERT 语句，根据每个字典中的特定键组合成组，同时保持行顺序，即 ("name", "fullname", "species")，("name", "species")，("name","fullname", "species")。

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

批量 ORM 插入功能利用了在传统“批量”插入行为以及整体 ORM 工作单元中也存在的行为，即包含 NULL 值的行将使用不引用这些列的语句进行 INSERT；其理由是后端和包含服务器端 INSERT 默认值的模式可能对存在 NULL 值与不存在值的情况敏感，将产生预期的服务器端值。这种默认行为会导致批量插入的批次被分成更多的少行批次：

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

上面，四行的批量插入被分解为三个单独的语句，第二个语句重新格式化以不引用包含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')]
...

上面，所有参数字典都在单个插入批次中发送，包括第三个参数字典中的None值。

从版本 2.0.23 开始：添加了render_nulls执行选项，它镜像了传统Session.bulk_insert_mappings.render_nulls参数的行为。

批量插入联合表继承

ORM 批量插入建立在传统工作单元系统中使用的内部系统之上，以发出 INSERT 语句。这意味着对于映射到多个表的 ORM 实体，通常是使用联合表继承映射的实体，批量插入操作将为映射的每个表发出一个 INSERT 语句，将服务器生成的主键值正确传递给依赖于它们的表行。此外，这里还支持 RETURNING 功能，ORM 将接收每个执行的 INSERT 语句的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')

提示

批量插入联合继承映射要求 ORM 在内部使用Insert.returning.sort_by_parameter_order参数，以便它可以将 RETURNING 表中的主键值与用于插入“子”表的参数集相关联，这就是为什么上面的 SQLite 后端在透明地降级为使用非批处理语句的原因。关于此功能的背景信息，请参阅将 RETURNING 行与参数集相关联。

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

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

使用 RETURNING 获取新对象

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

带每行 SQL 表达式的 ORM 批量插入

使用 RETURNING 语句与 upsert 语句

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

使用异构参数字典

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

批量插入联合表继承

热门文章

最新文章

相关课程

相关电子书