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

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

创建和持久化新的只写集合

只写集合允许仅对瞬态或待处理对象进行集合的直接赋值。通过我们上述的映射，这表示我们可以创建一个新的 Account 对象，并将一系列 AccountTransaction 对象添加到 Session 中。任何 Python 可迭代对象都可以作为对象的来源，下面我们使用了 Python 的 list：

>>> new_account = Account(
...     identifier="account_01",
...     account_transactions=[
...         AccountTransaction(description="initial deposit", amount=Decimal("500.00")),
...         AccountTransaction(description="transfer", amount=Decimal("1000.00")),
...         AccountTransaction(description="withdrawal", amount=Decimal("-29.50")),
...     ],
... )
>>> with Session(engine) as session:
...     session.add(new_account)
...     session.commit()
BEGIN  (implicit)
INSERT  INTO  account  (identifier)  VALUES  (?)
[...]  ('account_01',)
INSERT  INTO  account_transaction  (account_id,  description,  amount,  timestamp)
VALUES  (?,  ?,  ?,  CURRENT_TIMESTAMP)  RETURNING  id,  timestamp
[...  (insertmanyvalues)  1/3  (ordered;  batch  not  supported)]  (1,  'initial deposit',  500.0)
INSERT  INTO  account_transaction  (account_id,  description,  amount,  timestamp)
VALUES  (?,  ?,  ?,  CURRENT_TIMESTAMP)  RETURNING  id,  timestamp
[insertmanyvalues  2/3  (ordered;  batch  not  supported)]  (1,  'transfer',  1000.0)
INSERT  INTO  account_transaction  (account_id,  description,  amount,  timestamp)
VALUES  (?,  ?,  ?,  CURRENT_TIMESTAMP)  RETURNING  id,  timestamp
[insertmanyvalues  3/3  (ordered;  batch  not  supported)]  (1,  'withdrawal',  -29.5)
COMMIT

一旦对象已经持久化到数据库（即处于持久化或分离状态），集合就具有了扩展新项目以及删除单个项目的能力。但是，集合可能不能再重新分配为完整的替换集合，因为这样的操作需要将先前的集合完全加载到内存中，以便将旧条目与新条目进行协调：

>>> new_account.account_transactions = [
...     AccountTransaction(description="some transaction", amount=Decimal("10.00"))
... ]
Traceback (most recent call last):
...
sqlalchemy.exc.InvalidRequestError: Collection "Account.account_transactions" does not
support implicit iteration; collection replacement operations can't be used

向现有集合添加新项目

对于持久化对象的只写集合，使用工作单元过程修改集合只能通过使用WriteOnlyCollection.add()、WriteOnlyCollection.add_all() 和 WriteOnlyCollection.remove() 方法进行：

>>> from sqlalchemy import select
>>> session = Session(engine, expire_on_commit=False)
>>> existing_account = session.scalar(select(Account).filter_by(identifier="account_01"))
BEGIN  (implicit)
SELECT  account.id,  account.identifier
FROM  account
WHERE  account.identifier  =  ?
[...]  ('account_01',)
>>> existing_account.account_transactions.add_all(
...     [
...         AccountTransaction(description="paycheck", amount=Decimal("2000.00")),
...         AccountTransaction(description="rent", amount=Decimal("-800.00")),
...     ]
... )
>>> session.commit()
INSERT  INTO  account_transaction  (account_id,  description,  amount,  timestamp)
VALUES  (?,  ?,  ?,  CURRENT_TIMESTAMP)  RETURNING  id,  timestamp
[...  (insertmanyvalues)  1/2  (ordered;  batch  not  supported)]  (1,  'paycheck',  2000.0)
INSERT  INTO  account_transaction  (account_id,  description,  amount,  timestamp)
VALUES  (?,  ?,  ?,  CURRENT_TIMESTAMP)  RETURNING  id,  timestamp
[insertmanyvalues  2/2  (ordered;  batch  not  supported)]  (1,  'rent',  -800.0)
COMMIT

上面添加的项目在 Session 内部保持在挂起队列中，直到下一次刷新，在此时它们被插入到数据库中，假设添加的对象之前是瞬态的。

查询项目

WriteOnlyCollection 在任何时候都不会存储对集合当前内容的引用，也不会具有直接发出 SELECT 到数据库以加载它们的行为；覆盖的假设是集合可能包含许多千万个行，并且绝不应作为任何其他操作的副作用完全加载到内存中。

相反，WriteOnlyCollection 包括 SQL 生成助手，如 WriteOnlyCollection.select()，它将生成一个预先配置了当前父行的正确 WHERE / FROM 条件的 Select 构造，然后可以进一步修改以选择所需的任何行范围，以及使用诸如服务器端游标等功能调用，以便以内存有效的方式迭代通过整个集合。

生成的语句如下所示。请注意，示例映射中包括 ORDER BY 标准，由 relationship() 的 relationship.order_by 参数指示；如果未配置该参数，则会省略此标准：

>>> print(existing_account.account_transactions.select())
SELECT  account_transaction.id,  account_transaction.account_id,  account_transaction.description,
account_transaction.amount,  account_transaction.timestamp
FROM  account_transaction
WHERE  :param_1  =  account_transaction.account_id  ORDER  BY  account_transaction.timestamp

我们可以使用这个 Select 结构以及 Session 来查询 AccountTransaction 对象，最简单的方法是使用 Session.scalars() 方法，该方法将直接返回一个 Result，其中包含 ORM 对象。通常情况下，虽然不是必需的，但Select 可能会进一步修改以限制返回的记录；在下面的示例中，添加了额外的 WHERE 条件，仅加载“借方”账户交易，并且使用“LIMIT 10”只检索前十行：

>>> account_transactions = session.scalars(
...     existing_account.account_transactions.select()
...     .where(AccountTransaction.amount < 0)
...     .limit(10)
... ).all()
BEGIN  (implicit)
SELECT  account_transaction.id,  account_transaction.account_id,  account_transaction.description,
account_transaction.amount,  account_transaction.timestamp
FROM  account_transaction
WHERE  ?  =  account_transaction.account_id  AND  account_transaction.amount  <  ?
ORDER  BY  account_transaction.timestamp  LIMIT  ?  OFFSET  ?
[...]  (1,  0,  10,  0)
>>> print(account_transactions)
[AccountTransaction(amount=-29.50, timestamp='...'), AccountTransaction(amount=-800.00, timestamp='...')]

移除项目

在当前 Session 中加载到持久状态的个别项目可以使用WriteOnlyCollection.remove() 方法标记为从集合中移除。在操作继续时，刷新过程将隐式考虑对象已经是集合的一部分。下面的示例说明了删除单个 AccountTransaction 项目，根据 cascade 设置，会导致删除该行：

>>> existing_transaction = account_transactions[0]
>>> existing_account.account_transactions.remove(existing_transaction)
>>> session.commit()
DELETE  FROM  account_transaction  WHERE  account_transaction.id  =  ?
[...]  (3,)
COMMIT

与任何 ORM 映射的集合一样，对象移除可以根据relationship() 的 delete-orphan 配置，要么取消与集合的关联，同时保留对象在数据库中的存在，要么根据配置发出对其行的 DELETE 请求。

不删除的集合移除涉及将外键列设置为 NULL（对于一对多关系）或删除相应的关联行（对于多对多关系）。

新项目的批量插入

WriteOnlyCollection可以生成诸如Insert对象之类的 DML 构造，这些构造可以在 ORM 上下文中用于生成批量插入行为。有关 ORM 批量插入的概述，请参阅 ORM 批量插入语句部分。

一对多集合

对于普通的一对多集合，WriteOnlyCollection.insert()方法将产生一个预先建立了与父对象对应的 VALUES 条件的Insert构造。由于这个 VALUES 条件完全针对相关表，所以该语句可以用于插入新行，这些新行将同时成为相关集合中的新记录：

>>> session.execute(
...     existing_account.account_transactions.insert(),
...     [
...         {"description": "transaction 1", "amount": Decimal("47.50")},
...         {"description": "transaction 2", "amount": Decimal("-501.25")},
...         {"description": "transaction 3", "amount": Decimal("1800.00")},
...         {"description": "transaction 4", "amount": Decimal("-300.00")},
...     ],
... )
BEGIN  (implicit)
INSERT  INTO  account_transaction  (account_id,  description,  amount,  timestamp)  VALUES  (?,  ?,  ?,  CURRENT_TIMESTAMP)
[...]  [(1,  'transaction 1',  47.5),  (1,  'transaction 2',  -501.25),  (1,  'transaction 3',  1800.0),  (1,  'transaction 4',  -300.0)]
<...>
>>> session.commit()
COMMIT

另请参阅

ORM 批量插入语句 - 在 ORM 查询指南中

一对多 - 在基本关系模式中

多对多集合

对于多对多集合，两个类之间的关系涉及第三个表，该表使用relationship.secondary参数配置relationship。要使用WriteOnlyCollection批量插入此类型的集合中的行，可以先单独批量插入新记录，然后使用 RETURNING 检索，然后将这些记录传递给WriteOnlyCollection.add_all()方法，工作单元过程将继续将其作为集合的一部分持久化。

假设一个类BankAudit通过多对多表引用了许多AccountTransaction记录：

>>> from sqlalchemy import Table, Column
>>> audit_to_transaction = Table(
...     "audit_transaction",
...     Base.metadata,
...     Column("audit_id", ForeignKey("audit.id", ondelete="CASCADE"), primary_key=True),
...     Column(
...         "transaction_id",
...         ForeignKey("account_transaction.id", ondelete="CASCADE"),
...         primary_key=True,
...     ),
... )
>>> class BankAudit(Base):
...     __tablename__ = "audit"
...     id: Mapped[int] = mapped_column(primary_key=True)
...     account_transactions: WriteOnlyMapped["AccountTransaction"] = relationship(
...         secondary=audit_to_transaction, passive_deletes=True
...     )

为了说明这两个操作，我们使用批量插入添加了更多的AccountTransaction对象，我们通过在批量 INSERT 语句中添加returning(AccountTransaction)来检索它们 (注意我们也可以使用现有的AccountTransaction对象)：

>>> new_transactions = session.scalars(
...     existing_account.account_transactions.insert().returning(AccountTransaction),
...     [
...         {"description": "odd trans 1", "amount": Decimal("50000.00")},
...         {"description": "odd trans 2", "amount": Decimal("25000.00")},
...         {"description": "odd trans 3", "amount": Decimal("45.00")},
...     ],
... ).all()
BEGIN  (implicit)
INSERT  INTO  account_transaction  (account_id,  description,  amount,  timestamp)  VALUES
(?,  ?,  ?,  CURRENT_TIMESTAMP),  (?,  ?,  ?,  CURRENT_TIMESTAMP),  (?,  ?,  ?,  CURRENT_TIMESTAMP)
RETURNING  id,  account_id,  description,  amount,  timestamp
[...]  (1,  'odd trans 1',  50000.0,  1,  'odd trans 2',  25000.0,  1,  'odd trans 3',  45.0)

有了一系列准备好的AccountTransaction对象，可以使用WriteOnlyCollection.add_all()方法一次性将许多行与新的BankAudit对象关联起来：

>>> bank_audit = BankAudit()
>>> session.add(bank_audit)
>>> bank_audit.account_transactions.add_all(new_transactions)
>>> session.commit()
INSERT  INTO  audit  DEFAULT  VALUES
[...]  ()
INSERT  INTO  audit_transaction  (audit_id,  transaction_id)  VALUES  (?,  ?)
[...]  [(1,  10),  (1,  11),  (1,  12)]
COMMIT

另请参阅

ORM 批量插入语句 - 在 ORM 查询指南中

多对多 - 位于基本关系模式

Items 的批量 UPDATE 和 DELETE

与WriteOnlyCollection类似，它可以生成带有预先建立的 WHERE 条件的Select结构，也可以生成带有相同 WHERE 条件的Update和Delete结构，以允许针对大型集合中的元素进行基于条件的 UPDATE 和 DELETE 语句。

一对多集合

与 INSERT 一样，这个特性对于一对多集合来说最为直接。

在下面的示例中，WriteOnlyCollection.update()方法用于生成一条 UPDATE 语句，针对集合中的元素发出，定位“amount”等于-800的行，并将200的数量添加到它们：

>>> session.execute(
...     existing_account.account_transactions.update()
...     .values(amount=AccountTransaction.amount + 200)
...     .where(AccountTransaction.amount == -800),
... )
BEGIN  (implicit)
UPDATE  account_transaction  SET  amount=(account_transaction.amount  +  ?)
WHERE  ?  =  account_transaction.account_id  AND  account_transaction.amount  =  ?
[...]  (200,  1,  -800)
<...>

类似地，WriteOnlyCollection.delete()将生成一个 DELETE 语句，以相同的方式调用：

>>> session.execute(
...     existing_account.account_transactions.delete().where(
...         AccountTransaction.amount.between(0, 30)
...     ),
... )
DELETE  FROM  account_transaction  WHERE  ?  =  account_transaction.account_id
AND  account_transaction.amount  BETWEEN  ?  AND  ?  RETURNING  id
[...]  (1,  0,  30)
<...>

多对多集合

提示

这里涉及到稍微高级的多表 UPDATE 表达式技巧。

对于多对多集合的批量 UPDATE 和 DELETE，为了使 UPDATE 或 DELETE 语句与父对象的主键相关联，必须显式地将关联表包含在 UPDATE/DELETE 语句中，这要求后端要么包括对非标准 SQL 语法的支持，要么在构建 UPDATE 或 DELETE 语句时需要额外的显式步骤。

对于支持多表版本 UPDATE 的后端，WriteOnlyCollection.update()方法应该可以直接用于多对多集合，就像下面的示例中针对多对多BankAudit.account_transactions集合中的AccountTransaction对象发出 UPDATE 一样：

>>> session.execute(
...     bank_audit.account_transactions.update().values(
...         description=AccountTransaction.description + " (audited)"
...     )
... )
UPDATE  account_transaction  SET  description=(account_transaction.description  ||  ?)
FROM  audit_transaction  WHERE  ?  =  audit_transaction.audit_id
AND  account_transaction.id  =  audit_transaction.transaction_id  RETURNING  id
[...]  (' (audited)',  1)
<...>

上述语句自动使用了“UPDATE…FROM”语法，该语法由 SQLite 和其他数据库支持，以在 WHERE 子句中命名额外的audit_transaction表。

要更新或删除多对多集合，其中多表语法不可用，多对多条件可以移动到 SELECT 语句中，例如可以与 IN 组合以匹配行。在这里，WriteOnlyCollection仍然对我们有帮助，因为我们使用WriteOnlyCollection.select()方法为我们生成此 SELECT，利用Select.with_only_columns()方法生成标量子查询：

>>> from sqlalchemy import update
>>> subq = bank_audit.account_transactions.select().with_only_columns(AccountTransaction.id)
>>> session.execute(
...     update(AccountTransaction)
...     .values(description=AccountTransaction.description + " (audited)")
...     .where(AccountTransaction.id.in_(subq))
... )
UPDATE  account_transaction  SET  description=(account_transaction.description  ||  ?)
WHERE  account_transaction.id  IN  (SELECT  account_transaction.id
FROM  audit_transaction
WHERE  ?  =  audit_transaction.audit_id  AND  account_transaction.id  =  audit_transaction.transaction_id)
RETURNING  id
[...]  (' (audited)',  1)
<...>

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

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

创建和持久化新的只写集合

向现有集合添加新项目

查询项目

移除项目

新项目的批量插入

一对多集合

多对多集合

Items 的批量 UPDATE 和 DELETE

一对多集合

多对多集合

热门文章

最新文章

相关电子书

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

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

创建和持久化新的只写集合

向现有集合添加新项目

查询项目

移除项目

新项目的批量插入

一对多集合

多对多集合

Items 的批量 UPDATE 和 DELETE

一对多集合

多对多集合

热门文章

最新文章

相关电子书