SqlAlchemy 2.0 中文文档（三）（2）

SqlAlchemy 2.0 中文文档（三）（1）https://developer.aliyun.com/article/1562411

delete() SQL 表达式构造

delete()函数生成一个新的Delete实例，表示 SQL 中的 DELETE 语句，它将从表中删除行。

delete()语句从 API 的角度来看与update()构造非常相似，传统上不返回任何行，但在一些数据库后端上允许使用 RETURNING 变体。

>>> from sqlalchemy import delete
>>> stmt = delete(user_table).where(user_table.c.name == "patrick")
>>> print(stmt)
DELETE  FROM  user_account  WHERE  user_account.name  =  :name_1 

多表删除

像Update一样，Delete支持在 WHERE 子句中使用相关子查询，以及后端特定的多表语法，例如 MySQL 上的DELETE FROM..USING：

>>> delete_stmt = (
...     delete(user_table)
...     .where(user_table.c.id == address_table.c.user_id)
...     .where(address_table.c.email_address == "patrick@aol.com")
... )
>>> from sqlalchemy.dialects import mysql
>>> print(delete_stmt.compile(dialect=mysql.dialect()))
DELETE  FROM  user_account  USING  user_account,  address
WHERE  user_account.id  =  address.user_id  AND  address.email_address  =  %s 
```### 多表删除
与`Update`类似，`Delete`也支持在 WHERE 子句中使用相关子查询，以及后端特定的多表语法，例如在 MySQL 上的 `DELETE FROM..USING`：
```py
>>> delete_stmt = (
...     delete(user_table)
...     .where(user_table.c.id == address_table.c.user_id)
...     .where(address_table.c.email_address == "patrick@aol.com")
... )
>>> from sqlalchemy.dialects import mysql
>>> print(delete_stmt.compile(dialect=mysql.dialect()))
DELETE  FROM  user_account  USING  user_account,  address
WHERE  user_account.id  =  address.user_id  AND  address.email_address  =  %s 

从 UPDATE、DELETE 获取受影响的行数

Update 和 Delete 都支持在语句执行后返回匹配的行数的功能，对于使用 Core Connection 调用的语句，即 Connection.execute()。根据下面提到的注意事项，此值可从 CursorResult.rowcount 属性中获取：

>>> with engine.begin() as conn:
...     result = conn.execute(
...         update(user_table)
...         .values(fullname="Patrick McStar")
...         .where(user_table.c.name == "patrick")
...     )
...     print(result.rowcount)
BEGIN  (implicit)
UPDATE  user_account  SET  fullname=?  WHERE  user_account.name  =  ?
[...]  ('Patrick McStar',  'patrick')
1
COMMIT 

提示

CursorResult 类是 Result 的子类，它包含特定于 DBAPI cursor 对象的其他属性。当通过 Connection.execute() 方法调用语句时，将返回此子类的实例。在使用 ORM 时，Session.execute() 方法为所有 INSERT、UPDATE 和 DELETE 语句返回此类型的对象。

有关 CursorResult.rowcount 的事实：

• 返回的值是由语句的 WHERE 子句匹配的行数。无论实际上是否修改了行都无关紧要。
  
• CursorResult.rowcount 对于使用 RETURNING 的 UPDATE 或 DELETE 语句，或者使用 executemany 执行的语句未必可用。可用性取决于所使用的 DBAPI 模块。
  
• 在 DBAPI 未确定某种类型语句的行数的任何情况下，返回值都将是 -1。
  
• SQLAlchemy 在游标关闭之前预先缓存 DBAPIs cursor.rowcount 的值，因为某些 DBAPIs 不支持在事后访问此属性。为了为非 UPDATE 或 DELETE 的语句（例如 INSERT 或 SELECT）预先缓存 cursor.rowcount，可以使用 Connection.execution_options.preserve_rowcount 执行选项。
  
• 一些驱动程序，特别是非关系数据库的第三方方言，可能根本不支持 CursorResult.rowcount。CursorResult.supports_sane_rowcount 游标属性将指示这一点。
  
• “rowcount” 被 ORM 工作单元 过程用于验证 UPDATE 或 DELETE 语句是否匹配预期的行数，并且还是 ORM 版本控制功能的关键，该功能在 配置版本计数器 中有文档记录。
  

使用 RETURNING 与 UPDATE、DELETE

与 Insert 构造相似，Update 和 Delete 也支持通过使用 Update.returning() 和 Delete.returning() 方法添加的 RETURNING 子句。当这些方法在支持 RETURNING 的后端上使用时，匹配 WHERE 条件的所有行的选定列将作为可迭代的行返回到 Result 对象中：

>>> update_stmt = (
...     update(user_table)
...     .where(user_table.c.name == "patrick")
...     .values(fullname="Patrick the Star")
...     .returning(user_table.c.id, user_table.c.name)
... )
>>> print(update_stmt)
UPDATE  user_account  SET  fullname=:fullname
WHERE  user_account.name  =  :name_1
RETURNING  user_account.id,  user_account.name
>>> delete_stmt = (
...     delete(user_table)
...     .where(user_table.c.name == "patrick")
...     .returning(user_table.c.id, user_table.c.name)
... )
>>> print(delete_stmt)
DELETE  FROM  user_account
WHERE  user_account.name  =  :name_1
RETURNING  user_account.id,  user_account.name 

关于 UPDATE、DELETE 的进一步阅读

请参阅

UPDATE / DELETE 的 API 文档：

• Update
  
• Delete
  

启用 ORM 的 UPDATE 和 DELETE：

ORM 支持的 INSERT、UPDATE 和 DELETE 语句 - 在 ORM 查询指南 中

使用 ORM 进行数据操作

原文：docs.sqlalchemy.org/en/20/tutorial/orm_data_manipulation.html

上一节处理数据保持了从核心角度来看 SQL 表达语言的关注，以便提供各种主要 SQL 语句结构的连续性。接下来的部分将扩展Session的生命周期，以及它如何与这些结构交互。

先决条件部分 - 教程中 ORM 重点部分建立在本文档中的两个先前 ORM 中心部分的基础上：

• 使用 ORM 会话执行 - 介绍如何创建 ORM Session对象
  
• 使用 ORM 声明性表单定义表元数据 - 我们在这里设置了User和Address实体的 ORM 映射
  
• 选择 ORM 实体和列 - 一些关于如何为诸如User之类的实体运行 SELECT 语句的示例
  

使用 ORM 工作单元模式插入行

当使用 ORM 时，Session对象负责构造Insert构造并将它们作为 INSERT 语句发出到正在进行的事务中。我们指示Session这样做的方式是通过添加对象条目到它; Session然后确保这些新条目在需要时被发出到数据库，使用称为flush的过程。Session用于持久化对象的整体过程被称为工作单元模式。

类的实例代表行

而在前一个示例中，我们使用 Python 字典发出了一个 INSERT，以指示我们要添加的数据，使用 ORM 时，我们直接使用我们定义的自定义 Python 类，在使用 ORM 声明性表单定义表元数据中。在类级别，User和Address类用作定义相应数据库表应该如何查看的位置。这些类还用作可扩展的数据对象，我们用它们来创建和操作事务中的行。下面我们将创建两个User对象，每个对象代表一个要插入的潜在数据库行：

>>> squidward = User(name="squidward", fullname="Squidward Tentacles")
>>> krabs = User(name="ehkrabs", fullname="Eugene H. Krabs")

我们可以使用映射列的名称作为构造函数中的关键字参数来构造这些对象。这是可能的，因为 User 类包含一个由 ORM 映射提供的自动生成的 __init__() 构造函数，以便我们可以使用构造函数中的列名作为键来创建每个对象。

类似于我们在 Insert 的核心示例中的做法，我们没有包含主键（即 id 列的条目），因为我们希望利用数据库的自动递增主键功能，这里是 SQLite，ORM 也与之集成。上述对象的 id 属性的值，如果我们查看它，会显示为 None：

>>> squidward
User(id=None, name='squidward', fullname='Squidward Tentacles')

None 值由 SQLAlchemy 提供，表示属性目前没有值。在处理尚未分配值的新对象时，SQLAlchemy 映射的属性始终在 Python 中返回一个值，并且如果缺少值，则不会引发 AttributeError。

目前，上述两个对象被称为处于 transient 状态 - 它们与任何数据库状态都没有关联，尚未与可以为它们生成 INSERT 语句的 Session 对象关联。

将对象添加到会话

为了逐步说明添加过程，我们将创建一个不使用上下文管理器的 Session（因此我们必须确保稍后关闭它！）：

>>> session = Session(engine)

然后使用 Session.add() 方法将对象添加到 Session 中。当调用此方法时，对象处于一种称为 pending 的状态，尚未插入：

>>> session.add(squidward)
>>> session.add(krabs)

当我们有待处理的对象时，我们可以通过查看 Session 上的一个集合来查看这种状态，该集合称为 Session.new：

>>> session.new
IdentitySet([User(id=None, name='squidward', fullname='Squidward Tentacles'), User(id=None, name='ehkrabs', fullname='Eugene H. Krabs')])

上述视图使用一个名为 IdentitySet 的集合，它本质上是一个 Python 集合，以所有情况下的对象标识哈希（即使用 Python 内置的 id() 函数，而不是 Python 的 hash() 函数）。

刷新

Session 使用一种称为工作单元（unit of  work）的模式。这通常意味着它逐个累积更改，但实际上直到需要时才将它们传递到数据库。这使它能够根据给定的一组待处理更改，更好地决定如何在事务中发出  SQL DML。当它确实向数据库发出 SQL 以推送当前更改集时，该过程被称为刷新。

我们可以通过调用Session.flush()方法来手动说明刷新过程：

>>> session.flush()
BEGIN  (implicit)
INSERT  INTO  user_account  (name,  fullname)  VALUES  (?,  ?)  RETURNING  id
[...  (insertmanyvalues)  1/2  (ordered;  batch  not  supported)]  ('squidward',  'Squidward Tentacles')
INSERT  INTO  user_account  (name,  fullname)  VALUES  (?,  ?)  RETURNING  id
[insertmanyvalues  2/2  (ordered;  batch  not  supported)]  ('ehkrabs',  'Eugene H. Krabs') 

上面我们观察到首先调用Session以发出 SQL，因此它创建了一个新的事务并为两个对象发出了适当的 INSERT 语句。事务现在保持打开，直到我们调用任何Session.commit()、Session.rollback()或Session.close()方法。

虽然Session.flush()可用于手动推送待处理更改到当前事务，但通常是不必要的，因为Session具有一种称为自动刷新的行为，我们稍后将说明。每当调用Session.commit()时，它也会刷新更改。

自动产生的主键属性

一旦行被插入，我们创建的两个 Python 对象处于持久（persistent）状态，它们与它们被添加或加载到的Session对象相关联，并具有稍后将介绍的许多其他行为。

发生的 INSERT 的另一个效果是 ORM 检索了每个新对象的新主键标识符；在内部，它通常使用我们之前介绍的相同的CursorResult.inserted_primary_key访问器。squidward 和 krabs 对象现在具有这些新的主键标识符，并且我们可以通过访问 id 属性查看它们：

>>> squidward.id
4
>>> krabs.id
5

提示

当 ORM 在刷新对象时为什么会发出两个单独的 INSERT 语句，而不是使用 executemany？正如我们将在下一节中看到的，Session在刷新对象时始终需要知道新插入对象的主键。如果使用了诸如 SQLite 的自动增量（其他示例包括 PostgreSQL IDENTITY 或 SERIAL，使用序列等）之类的功能，则CursorResult.inserted_primary_key功能通常要求每次 INSERT 都逐行发出。如果我们提前为主键提供了值，ORM 将能够更好地优化操作。一些数据库后端，如 psycopg2，还可以一次插入多行，同时仍然能够检索主键值。

通过主键从身份映射获取对象

对象的主键标识对于Session非常重要，因为这些对象现在使用称为身份映射的功能与此标识在内存中连接在一起。身份映射是一个内存存储器，它将当前加载在内存中的所有对象与它们的主键标识链接起来。我们可以通过使用Session.get()方法之一来检索上述对象之一来观察到这一点，如果本地存在，则返回身份映射中的条目，否则发出一个 SELECT：

>>> some_squidward = session.get(User, 4)
>>> some_squidward
User(id=4, name='squidward', fullname='Squidward Tentacles')

身份映射的重要一点是，在特定Session对象的范围内，它维护着特定 Python 对象的唯一实例与特定数据库标识的关系。我们可以观察到，some_squidward指的是之前squidward所指的同一个对象：

>>> some_squidward is squidward
True

身份映射是一个关键特性，允许在事务中操作复杂的对象集合而不会出现同步问题。

提交

关于Session的工作方式还有很多要说的，这将在后续进一步讨论。现在我们将提交事务，以便在深入研究 ORM 行为和特性之前积累关于如何在 SELECT 行之前的知识：

>>> session.commit()
COMMIT

上述操作将提交正在进行的事务。 我们处理过的对象仍然附加到 Session，这是一个状态，直到 Session关闭（在关闭会话中介绍）。

提示

注意的一件重要事情是，我们刚刚处理的对象上的属性已经过期，意味着，当我们下一次访问它们的任何属性时，Session 将启动一个新的事务并重新加载它们的状态。 这个选项有时对性能原因或者如果希望在关闭Session后继续使用对象（这被称为分离状态）可能会有问题，因为它们将不会有任何状态，并且将没有 Session 与其一起加载该状态，导致“分离实例”错误。 可以使用一个名为Session.expire_on_commit的参数来控制行为。 更多信息请参见关闭会话。 ## 使用工作单元模式更新 ORM 对象

在前面的一节使用 UPDATE 和 DELETE 语句中，我们介绍了代表 SQL UPDATE 语句的 Update 构造。 当使用 ORM 时，有两种方式使用此构造。 主要方式是，它作为Session使用的工作单元过程的一部分自动发出，其中对具有更改的单个对象对应的每个主键发出一个 UPDATE 语句。

假设我们将用户名为sandy的User对象加载到一个事务中（同时还展示了Select.filter_by()方法以及Result.scalar_one()方法）：

>>> sandy = session.execute(select(User).filter_by(name="sandy")).scalar_one()
BEGIN  (implicit)
SELECT  user_account.id,  user_account.name,  user_account.fullname
FROM  user_account
WHERE  user_account.name  =  ?
[...]  ('sandy',) 

如前所述，Python 对象sandy充当数据库中的行的代理，更具体地说，是相对于当前事务的具有主键标识2的数据库行：

>>> sandy
User(id=2, name='sandy', fullname='Sandy Cheeks')

如果我们更改此对象的属性，Session将跟踪此更改：

>>> sandy.fullname = "Sandy Squirrel"

对象出现在一个名为Session.dirty的集合中，表示对象“脏”：

>>> sandy in session.dirty
True

当Session下次执行 flush 时，将会发出一个 UPDATE，以在数据库中更新此值。如前所述，在发出任何 SELECT 之前，会自动执行 flush，这种行为称为自动 flush。我们可以直接查询这一行的User.fullname列，我们将得到我们的更新值：

>>> sandy_fullname = session.execute(select(User.fullname).where(User.id == 2)).scalar_one()
UPDATE  user_account  SET  fullname=?  WHERE  user_account.id  =  ?
[...]  ('Sandy Squirrel',  2)
SELECT  user_account.fullname
FROM  user_account
WHERE  user_account.id  =  ?
[...]  (2,)
>>> print(sandy_fullname)
Sandy Squirrel

我们可以看到上面我们请求Session执行了一个单独的select()语句。然而，发出的 SQL 显示了还发出了一个 UPDATE，这是 flush 过程推出挂起的更改。sandy Python 对象现在不再被认为是脏的：

>>> sandy in session.dirty
False

然而请注意，我们仍然处于一个事务中，我们的更改尚未推送到数据库的永久存储中。由于桑迪的姓实际上是“Cheeks”而不是“Squirrel”，我们将在回滚事务时修复这个错误。但首先我们会做一些更多的数据更改。

亦可参见

Flush-详细说明了 flush 过程以及关于Session.autoflush设置的信息。##使用工作单元模式删除 ORM 对象

为了完成基本的持久性操作，可以使用Session.delete()方法在工作单元过程中标记一个个别的 ORM 对象以进行删除操作。让我们从数据库加载patrick：

>>> patrick = session.get(User, 3)
SELECT  user_account.id  AS  user_account_id,  user_account.name  AS  user_account_name,
user_account.fullname  AS  user_account_fullname
FROM  user_account
WHERE  user_account.id  =  ?
[...]  (3,) 

如果我们标记patrick以进行删除，与其他操作一样，直到进行 flush 才会实际发生任何事情：

>>> session.delete(patrick)

当前的 ORM 行为是patrick会一直留在Session中，直到 flush 进行，如前所述，如果我们发出查询，就会发生 flush：

>>> session.execute(select(User).where(User.name == "patrick")).first()
SELECT  address.id  AS  address_id,  address.email_address  AS  address_email_address,
address.user_id  AS  address_user_id
FROM  address
WHERE  ?  =  address.user_id
[...]  (3,)
DELETE  FROM  user_account  WHERE  user_account.id  =  ?
[...]  (3,)
SELECT  user_account.id,  user_account.name,  user_account.fullname
FROM  user_account
WHERE  user_account.name  =  ?
[...]  ('patrick',) 

在上面，我们要求发出的 SELECT 语句之前是一个 DELETE，这表明 patrick 的待删除操作已经进行了。还有一个针对 address 表的 SELECT，这是由于 ORM 在寻找与目标行可能相关的这个表中的行而引起的；这种行为是所谓的 级联 行为的一部分，并且可以通过允许数据库自动处理 address 中的相关行来更有效地工作；关于此的详细信息请参见 delete。

另请参见

delete - 描述了如何调整 Session.delete() 的行为，以便处理其他表中的相关行应该如何处理。

除此之外，现在正在被删除的 patrick 对象实例不再被视为在 Session 中持久存在，这可以通过包含性检查来显示：

>>> patrick in session
False

然而，就像我们对 sandy 对象进行的更新一样，我们在这里做出的每一个改变都只在正在进行的事务中有效，如果我们不提交事务，这些改变就不会永久保存。由于此刻回滚事务更加有趣，我们将在下一节中进行。## 批量/多行 INSERT、upsert、UPDATE 和 DELETE

本节讨论的工作单元技术旨在将 dml（即 INSERT/UPDATE/DELETE 语句）与 Python 对象机制集成，通常涉及到相互关联对象的复杂图。一旦对象使用 Session.add() 添加到 Session 中，工作单元过程会自动代表我们发出 INSERT/UPDATE/DELETE，因为我们的对象属性被创建和修改。

但是，ORM Session 也有处理命令的能力，使其能够直接发出 INSERT、UPDATE 和 DELETE  语句，而不需要传递任何 ORM 持久化的对象，而是传递要 INSERT、UPDATE 或 upsert 的值列表，或者 WHERE  条件，以便可以调用一次匹配多行的 UPDATE 或 DELETE  语句。当需要影响大量行而无需构建和操作映射对象时，这种用法尤为重要，因为对于简单、性能密集型的任务，如大批量插入，这可能是繁琐和不必要的。

ORM 的批量/多行功能Session直接使用insert()、update()和delete()构造，并且它们的使用方式类似于与 SQLAlchemy Core 一起使用它们的方式（首次在本教程中介绍于使用 INSERT 语句和使用 UPDATE 和 DELETE 语句）。当使用这些构造与 ORMSession而不是普通的Connection时，它们的构建、执行和结果处理与 ORM 完全集成。

关于使用这些功能的背景和示例，请参见 ORM-启用的 INSERT、UPDATE 和 DELETE 语句部分，位于 ORM 查询指南中。

另请参阅

ORM-启用的 INSERT、UPDATE 和 DELETE 语句 - 在 ORM 查询指南中

回滚

Session有一个Session.rollback()方法，如预期般在进行中的 SQL 连接上发出 ROLLBACK。但是，它还会影响当前与Session关联的对象，例如我们先前示例中的 Python 对象sandy。虽然我们将sandy对象的.fullname更改为读取"Sandy Squirrel"，但我们想要回滚此更改。调用Session.rollback()不仅会回滚事务，还会过期与此Session当前关联的所有对象，这将使它们在下次使用时自动刷新，使用一种称为延迟加载的过程：

>>> session.rollback()
ROLLBACK

要更仔细地查看“过期”过程，我们可以观察到 Python 对象sandy在其 Python__dict__中没有留下状态，除了一个特殊的 SQLAlchemy 内部状态对象：

>>> sandy.__dict__
{'_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x...>}

这是“过期”状态；再次访问属性将自动开始一个新的事务，并使用当前数据库行刷新sandy：

>>> sandy.fullname
BEGIN  (implicit)
SELECT  user_account.id  AS  user_account_id,  user_account.name  AS  user_account_name,
user_account.fullname  AS  user_account_fullname
FROM  user_account
WHERE  user_account.id  =  ?
[...]  (2,)
'Sandy Cheeks'

我们现在可以观察到完整的数据库行也被填充到sandy对象的__dict__中：

>>> sandy.__dict__  
{'_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x...>,
 'id': 2, 'name': 'sandy', 'fullname': 'Sandy Cheeks'}

对于删除的对象，当我们之前注意到patrick不再在会话中时，该对象的身份也被恢复：

>>> patrick in session
True

当然，数据库数据也再次出现了：

>>> session.execute(select(User).where(User.name == "patrick")).scalar_one() is patrick
SELECT  user_account.id,  user_account.name,  user_account.fullname
FROM  user_account
WHERE  user_account.name  =  ?
[...]  ('patrick',)
True

SqlAlchemy 2.0 中文文档（三）（3）https://developer.aliyun.com/article/1562431