AI 助理
备案控制台
开发者社区 数据库 文章 正文

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

2024-07-08 17
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： SqlAlchemy 2.0 中文文档（十三）

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

文章标签：
Python
数据库
SQL
数据库管理
存储
布客飞龙
目录
相关文章
布客飞龙
|
2月前
|
SQL 存储 API
SqlAlchemy 2.0 中文文档（十三）（4）
SqlAlchemy 2.0 中文文档（十三）
布客飞龙
23 1
布客飞龙
|
2月前
|
SQL 存储 数据库
SqlAlchemy 2.0 中文文档（十九）（2）
SqlAlchemy 2.0 中文文档（十九）
布客飞龙
21 2
布客飞龙
|
2月前
|
SQL 测试技术 Go
SqlAlchemy 2.0 中文文档（十八）（3）
SqlAlchemy 2.0 中文文档（十八）
布客飞龙
18 1
布客飞龙
|
2月前
|
SQL 前端开发 Go
SqlAlchemy 2.0 中文文档（十八）（4）
SqlAlchemy 2.0 中文文档（十八）
布客飞龙
18 1
布客飞龙
|
2月前
|
SQL 测试技术 Go
SqlAlchemy 2.0 中文文档（十八）（2）
SqlAlchemy 2.0 中文文档（十八）
布客飞龙
15 1
布客飞龙
|
2月前
|
SQL 存储 大数据
SqlAlchemy 2.0 中文文档（十八）（1）
SqlAlchemy 2.0 中文文档（十八）
布客飞龙
23 1
布客飞龙
|
2月前
|
SQL 测试技术 Go
SqlAlchemy 2.0 中文文档（十八）（5）
SqlAlchemy 2.0 中文文档（十八）
布客飞龙
20 1
布客飞龙
|
2月前
|
SQL Java Go
SqlAlchemy 2.0 中文文档（十九）（1）
SqlAlchemy 2.0 中文文档（十九）
布客飞龙
25 1
布客飞龙
|
2月前
|
SQL 测试技术 知识图谱
SqlAlchemy 2.0 中文文档（十五）（4）
SqlAlchemy 2.0 中文文档（十五）
布客飞龙
31 1
布客飞龙
|
2月前
|
SQL 存储 API
SqlAlchemy 2.0 中文文档（十三）（2）
SqlAlchemy 2.0 中文文档（十三）
布客飞龙
25 0