使用 ORM 进行数据操作¶

Data Manipulation with the ORM

中文

上一节处理数据从核心视角继续关注 SQL 表达式语言，以提供跨主要 SQL 语句构造的连续性。本节将构建 Session 的生命周期及其如何与这些构造交互。

前提章节 - 本教程的 ORM 重点部分建立在本文档的两个先前的 ORM 中心章节之上：

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

英文

The previous section 处理数据 remained focused on the SQL Expression Language from a Core perspective, in order to provide continuity across the major SQL statement constructs. This section will then build out the lifecycle of the Session and how it interacts with these constructs.

Prerequisite Sections - the ORM focused part of the tutorial builds upon two previous ORM-centric sections in this document:

使用 ORM 会话执行 - introduces how to make an ORM Session object
使用 ORM 声明形式定义表元数据 - where we set up our ORM mappings of the User and Address entities
选择 ORM 实体和列 - a few examples on how to run SELECT statements for entities like User

使用工作单元模式更新 ORM 对象¶

Updating ORM Objects using the Unit of Work pattern

中文

在前一节使用 UPDATE 和 DELETE 语句中，我们介绍了表示 SQL UPDATE 语句的 Update 构造。在使用 ORM 时，有两种方式使用该构造。主要方式是它作为 Session 使用的 unit of work 过程的一部分自动发出，其中 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 充当数据库中行的 代理(proxy)，更具体地说，是 当前事务中的(in terms of the current transaction) 数据库行，该行具有 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 来更新数据库中的此值。如前所述，flush 会在我们发出任何 SELECT 之前自动发生，使用一种称为 autoflush 的行为。我们可以直接查询此行的 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

但是请注意，我们 仍然在事务中(still in a transaction)，我们的更改尚未推送到数据库的永久存储中。由于 Sandy 的姓氏实际上是“Cheeks”而不是“Squirrel”，我们将在稍后回滚事务时修复此错误。但首先我们将进行一些数据更改。

参见

刷新 - 详细介绍了 flush 过程以及 Session.autoflush 设置的相关信息。

英文

In the preceding section 使用 UPDATE 和 DELETE 语句, we introduced the Update construct that represents a SQL UPDATE statement. When using the ORM, there are two ways in which this construct is used. The primary way is that it is emitted automatically as part of the unit of work process used by the Session, where an UPDATE statement is emitted on a per-primary key basis corresponding to individual objects that have changes on them.

Supposing we loaded the User object for the username sandy into a transaction (also showing off the Select.filter_by() method as well as the Result.scalar_one() method):

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

The Python object sandy as mentioned before acts as a proxy for the row in the database, more specifically the database row in terms of the current transaction, that has the primary key identity of 2:

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

If we alter the attributes of this object, the Session tracks this change:

>>> sandy.fullname = "Sandy Squirrel"

The object appears in a collection called Session.dirty, indicating the object is “dirty”:

>>> sandy in session.dirty
True

When the Session next emits a flush, an UPDATE will be emitted that updates this value in the database. As mentioned previously, a flush occurs automatically before we emit any SELECT, using a behavior known as autoflush. We can query directly for the User.fullname column from this row and we will get our updated value back:

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

We can see above that we requested that the Session execute a single select() statement. However the SQL emitted shows that an UPDATE were emitted as well, which was the flush process pushing out pending changes. The sandy Python object is now no longer considered dirty:

>>> sandy in session.dirty
False

However note we are still in a transaction and our changes have not been pushed to the database’s permanent storage. Since Sandy’s last name is in fact “Cheeks” not “Squirrel”, we will repair this mistake later when we roll back the transaction. But first we’ll make some more data changes.

参见

刷新- details the flush process as well as information about the Session.autoflush setting.

使用工作单元模式删除 ORM 对象¶

Deleting ORM Objects using the Unit of Work pattern

中文

为了完成基本的持久化操作，可以使用 Session.delete() 方法在 unit of work 过程中标记单个 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 查找该表中可能与目标行相关的行引起的；这种行为是 cascade 行为的一部分，可以通过允许数据库自动处理 address 中的相关行来提高效率；部分删除提供了有关此的所有详细信息。

参见

删除 - 介绍如何调整 Session.delete() 的行为，以处理其他表中的相关行。

此外，现在被删除的 patrick 对象实例不再被视为 Session 中的持久对象，如包含检查所示：

>>> patrick in session
False

但是，就像我们对 sandy 对象所做的 UPDATE 一样，我们在此处所做的每一个更改都是本地的持续事务的一部分，如果我们不提交它，它们将不会变为永久的。由于回滚事务实际上更有趣，我们将在下一节中进行回滚。

英文

To round out the basic persistence operations, an individual ORM object may be marked for deletion within the unit of work process by using the Session.delete() method. Let’s load up patrick from the database:

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

If we mark patrick for deletion, as is the case with other operations, nothing actually happens yet until a flush proceeds:

>>> session.delete(patrick)

Current ORM behavior is that patrick stays in the Session until the flush proceeds, which as mentioned before occurs if we emit a query:

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

Above, the SELECT we asked to emit was preceded by a DELETE, which indicated the pending deletion for patrick proceeded. There was also a SELECT against the address table, which was prompted by the ORM looking for rows in this table which may be related to the target row; this behavior is part of a behavior known as cascade, and can be tailored to work more efficiently by allowing the database to handle related rows in address automatically; the section 删除 has all the detail on this.

参见

删除 - describes how to tune the behavior of Session.delete() in terms of how related rows in other tables should be handled.

Beyond that, the patrick object instance now being deleted is no longer considered to be persistent within the Session, as is shown by the containment check:

>>> patrick in session
False

However just like the UPDATEs we made to the sandy object, every change we’ve made here is local to an ongoing transaction, which won’t become permanent if we don’t commit it. As rolling the transaction back is actually more interesting at the moment, we will do that in the next section.

批量/多行 INSERT、upsert、UPDATE 和 DELETE¶

Bulk / Multi Row INSERT, upsert, UPDATE and DELETE

中文

unit of work 技术旨在结合 dml （即 INSERT/UPDATE/DELETE 语句）与 Python 对象机制，通常涉及复杂的相互关联的对象图。一旦使用 Session.add() 将对象添加到 Session 中，unit of work 过程将透明地为我们发出 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 语句）。当在 ORM Session 中使用这些构造而不是简单的 Connection 时，它们的构造、执行和结果处理完全与 ORM 集成。

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

参见

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

英文

The unit of work techniques discussed in this section are intended to integrate dml, or INSERT/UPDATE/DELETE statements, with Python object mechanics, often involving complex graphs of inter-related objects. Once objects are added to a Session using Session.add(), the unit of work process transparently emits INSERT/UPDATE/DELETE on our behalf as attributes on our objects are created and modified.

However, the ORM Session also has the ability to process commands that allow it to emit INSERT, UPDATE and DELETE statements directly without being passed any ORM-persisted objects, instead being passed lists of values to be INSERTed, UPDATEd, or upserted, or WHERE criteria so that an UPDATE or DELETE statement that matches many rows at once can be invoked. This mode of use is of particular importance when large numbers of rows must be affected without the need to construct and manipulate mapped objects, which may be cumbersome and unnecessary for simplistic, performance-intensive tasks such as large bulk inserts.

The Bulk / Multi row features of the ORM Session make use of the insert(), update() and delete() constructs directly, and their usage resembles how they are used with SQLAlchemy Core (first introduced in this tutorial at 使用 INSERT 语句 and 使用 UPDATE 和 DELETE 语句). When using these constructs with the ORM Session instead of a plain Connection, their construction, execution and result handling is fully integrated with the ORM.

For background and examples on using these features, see the section 启用 ORM 的 INSERT、UPDATE 和 DELETE 语句 in the ORM 查询指南.

参见

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

回滚¶

Rolling Back

中文

Session 具有一个 Session.rollback() 方法，该方法如预期的那样在正在进行的 SQL 连接上发出 ROLLBACK。然而，它对当前与 Session 关联的对象也有影响，在我们之前的示例中是 Python 对象 sandy。虽然我们将 sandy 对象的 .fullname 更改为 "Sandy Squirrel"，但我们想要回滚此更改。调用 Session.rollback() 不仅会回滚事务，还会 expire 当前与此 Session 关联的所有对象，这将使它们在下次访问时使用称为 lazy loading 的过程刷新自己：

>>> session.rollback()
ROLLBACK

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

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

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

>>> sandy.fullnameBEGIN (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()ispatrickSELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ?
[...] ('patrick',)
True

英文

The Session has a Session.rollback() method that as expected emits a ROLLBACK on the SQL connection in progress. However, it also has an effect on the objects that are currently associated with the Session, in our previous example the Python object sandy. While we changed the .fullname of the sandy object to read "Sandy Squirrel", we want to roll back this change. Calling Session.rollback() will not only roll back the transaction but also expire all objects currently associated with this Session, which will have the effect that they will refresh themselves when next accessed using a process known as lazy loading:

>>> session.rollback()
ROLLBACK

To view the “expiration” process more closely, we may observe that the Python object sandy has no state left within its Python __dict__, with the exception of a special SQLAlchemy internal state object:

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

This is the “expired” state; accessing the attribute again will autobegin a new transaction and refresh sandy with the current database row:

>>> sandy.fullnameBEGIN (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'

We may now observe that the full database row was also populated into the __dict__ of the sandy object:

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

For deleted objects, when we earlier noted that patrick was no longer in the session, that object’s identity is also restored:

>>> patrick in session
True

and of course the database data is present again as well:

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

关闭会话¶

Closing a Session

中文

在上述部分中，我们在 Python 上下文管理器之外使用了 Session 对象，即我们没有使用 with 语句。这没问题，但是如果我们这样做，最好在完成后显式关闭 Session：

>>> session.close()ROLLBACK

关闭 Session （在上下文管理器中也会发生）完成以下事情：

它将所有连接资源 releases 到连接池，取消（例如回滚）正在进行的任何事务。

这意味着当我们使用会话执行一些只读任务然后关闭它时，我们不需要显式调用 Session.rollback() 来确保事务已回滚；连接池会处理这些。
它 expunges 所有对象从 Session 中。

这意味着我们为此 Session 加载的所有 Python 对象，如 sandy、patrick 和 squidward，现在处于称为 detached 的状态。特别是，我们将注意到仍处于 expired 状态的对象，例如由于调用了 Session.commit()，现在无法使用，因为它们不包含当前行的状态，并且不再与任何数据库事务关联以进行刷新:
```
# 注意'squidward.name'之前刚刚过期，所以它的值未加载
>>> squidward.name
Traceback (most recent call last):
...
sqlalchemy.orm.exc.DetachedInstanceError: Instance <User at 0x...> is not bound to a Session; attribute refresh operation cannot proceed
```

可以使用 Session.add() 方法将分离的对象重新关联到相同或新的 Session，这将重新建立它们与特定数据库行的关系：

>>> session.add(squidward)>>> squidward.nameBEGIN (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 = ?
[...] (4,)
'squidward'

小技巧

尽量避免在分离状态下使用对象，如果可能的话。当 Session 关闭时，清理对所有以前附加对象的引用。对于需要分离对象的情况，通常是为了刚提交对象的即时显示，例如在视图呈现之前关闭 Session 的 Web 应用程序，为此，将 Session.expire_on_commit 标志设置为 False。

英文

Within the above sections we used a Session object outside of a Python context manager, that is, we didn’t use the with statement. That’s fine, however if we are doing things this way, it’s best that we explicitly close out the Session when we are done with it:

>>> session.close()ROLLBACK

Closing the Session, which is what happens when we use it in a context manager as well, accomplishes the following things:

It releases all connection resources to the connection pool, cancelling out (e.g. rolling back) any transactions that were in progress.

This means that when we make use of a session to perform some read-only tasks and then close it, we don’t need to explicitly call upon Session.rollback() to make sure the transaction is rolled back; the connection pool handles this.
It expunges all objects from the Session.

This means that all the Python objects we had loaded for this Session, like sandy, patrick and squidward, are now in a state known as detached. In particular, we will note that objects that were still in an expired state, for example due to the call to Session.commit(), are now non-functional, as they don’t contain the state of a current row and are no longer associated with any database transaction in which to be refreshed:
```
# note that 'squidward.name' was just expired previously, so its value is unloaded
>>> squidward.name
Traceback (most recent call last):
...
sqlalchemy.orm.exc.DetachedInstanceError: Instance <User at 0x...> is not bound to a Session; attribute refresh operation cannot proceed
```

The detached objects can be re-associated with the same, or a new Session using the Session.add() method, which will re-establish their relationship with their particular database row:

>>> session.add(squidward)>>> squidward.nameBEGIN (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 = ?
[...] (4,)
'squidward'

小技巧

Try to avoid using objects in their detached state, if possible. When the Session is closed, clean up references to all the previously attached objects as well. For cases where detached objects are necessary, typically the immediate display of just-committed objects for a web application where the Session is closed before the view is rendered, set the Session.expire_on_commit flag to False.

使用 ORM 进行数据操作¶

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

类的实例代表行¶

将对象添加到会话¶

刷新¶

自动生成的主键属性¶

从身份映射中按主键获取对象¶

提交¶

使用工作单元模式更新 ORM 对象¶

使用工作单元模式删除 ORM 对象¶

批量/多行 INSERT、upsert、UPDATE 和 DELETE¶

回滚¶

关闭会话¶