SQLAlchemy 1.4 / 2.0 Tutorial

This page is part of the SQLAlchemy 统一教程.

Previous: 使用 SELECT 语句 | Next: 使用 ORM 进行数据操作

使用 UPDATE 和 DELETE 语句

Using UPDATE and DELETE Statements

中文

到目前为止，我们已经介绍了 Insert，以便我们可以将一些数据插入数据库，然后花了很多时间在 Select 上，该类处理从数据库检索数据的广泛使用模式。在本节中，我们将介绍 Update 和 Delete 构造，这些构造用于修改现有行以及删除现有行。本节将从核心视角介绍这些构造。

ORM 读者 - 正如在 使用 INSERT 语句 中提到的情况一样，当与 ORM 一起使用时，Update 和 Delete 操作通常在 Session 对象内部作为 unit of work 过程的一部分调用。

然而，与 Insert 不同，Update 和 Delete 构造也可以直接与 ORM 一起使用，使用一种称为“ORM 启用的更新和删除”的模式；因此，熟悉这些构造对 ORM 的使用是有用的。这两种使用风格在 使用工作单元模式更新 ORM 对象 和 使用工作单元模式删除 ORM 对象 部分中讨论。

英文

So far we’ve covered Insert, so that we can get some data into our database, and then spent a lot of time on Select which handles the broad range of usage patterns used for retrieving data from the database. In this section we will cover the Update and Delete constructs, which are used to modify existing rows as well as delete existing rows. This section will cover these constructs from a Core-centric perspective.

ORM Readers - As was the case mentioned at 使用 INSERT 语句, the Update and Delete operations when used with the ORM are usually invoked internally from the Session object as part of the unit of work process.

However, unlike Insert, the Update and Delete constructs can also be used directly with the ORM, using a pattern known as “ORM-enabled update and delete”; for this reason, familiarity with these constructs is useful for ORM use. Both styles of use are discussed in the sections 使用工作单元模式更新 ORM 对象 and 使用工作单元模式删除 ORM 对象.

update() SQL 表达式构造

The update() SQL Expression Construct

中文

update() 函数生成一个新的 Update 实例，表示 SQL 中的 UPDATE 语句，它将更新表中的现有数据。

与 insert() 构造类似，update() 也有一种“传统”形式，它一次对单个表发出 UPDATE 并且不返回任何行。然而，一些后端支持一个 UPDATE 语句可以一次修改多个表，并且 UPDATE 语句还支持 RETURNING，这样包含在匹配行中的列可以在结果集中返回。

一个基本的 UPDATE 看起来像这样:

>>> from sqlalchemy import update
>>> stmt = (
...     update(user_table)
...     .where(user_table.c.name == "patrick")
...     .values(fullname="Patrick the Star")
... )
>>> print(stmt)
UPDATE user_account SET fullname=:fullname WHERE user_account.name = :name_1

Update.values() 方法控制 UPDATE 语句的 SET 元素的内容。这是 Insert 构造共享的方法。参数通常可以使用列名作为关键字参数传递。

UPDATE 支持所有主要的 SQL 形式的 UPDATE，包括针对表达式的更新，我们可以使用 Column 表达式:

>>> stmt = update(user_table).values(fullname="Username: " + user_table.c.name)
>>> print(stmt)
UPDATE user_account SET fullname=(:name_1 || user_account.name)

为了支持在“executemany”上下文中进行 UPDATE，其中许多参数集将针对同一语句调用，可以使用 bindparam() 构造来设置绑定参数；这些参数将替换通常放置文字值的位置：

>>> from sqlalchemy import bindparam
>>> stmt = (
...     update(user_table)
...     .where(user_table.c.name == bindparam("oldname"))
...     .values(name=bindparam("newname"))
... )
>>> with engine.begin() as conn:
...     conn.execute(
...         stmt,
...         [
...             {"oldname": "jack", "newname": "ed"},
...             {"oldname": "wendy", "newname": "mary"},
...             {"oldname": "jim", "newname": "jake"},
...         ],
...     )
BEGIN (implicit)
UPDATE user_account SET name=? WHERE user_account.name = ?
[...] [('ed', 'jack'), ('mary', 'wendy'), ('jake', 'jim')]
<sqlalchemy.engine.cursor.CursorResult object at 0x...>
COMMIT

其他可以应用于 UPDATE 的技术包括：

英文

The update() function generates a new instance of Update which represents an UPDATE statement in SQL, that will update existing data in a table.

Like the insert() construct, there is a “traditional” form of update(), which emits UPDATE against a single table at a time and does not return any rows. However some backends support an UPDATE statement that may modify multiple tables at once, and the UPDATE statement also supports RETURNING such that columns contained in matched rows may be returned in the result set.

A basic UPDATE looks like:

>>> from sqlalchemy import update
>>> stmt = (
...     update(user_table)
...     .where(user_table.c.name == "patrick")
...     .values(fullname="Patrick the Star")
... )
>>> print(stmt)
UPDATE user_account SET fullname=:fullname WHERE user_account.name = :name_1

The Update.values() method controls the contents of the SET elements of the UPDATE statement. This is the same method shared by the Insert construct. Parameters can normally be passed using the column names as keyword arguments.

UPDATE supports all the major SQL forms of UPDATE, including updates against expressions, where we can make use of Column expressions:

>>> stmt = update(user_table).values(fullname="Username: " + user_table.c.name)
>>> print(stmt)
UPDATE user_account SET fullname=(:name_1 || user_account.name)

To support UPDATE in an “executemany” context, where many parameter sets will be invoked against the same statement, the bindparam() construct may be used to set up bound parameters; these replace the places that literal values would normally go:

>>> from sqlalchemy import bindparam
>>> stmt = (
...     update(user_table)
...     .where(user_table.c.name == bindparam("oldname"))
...     .values(name=bindparam("newname"))
... )
>>> with engine.begin() as conn:
...     conn.execute(
...         stmt,
...         [
...             {"oldname": "jack", "newname": "ed"},
...             {"oldname": "wendy", "newname": "mary"},
...             {"oldname": "jim", "newname": "jake"},
...         ],
...     )
BEGIN (implicit)
UPDATE user_account SET name=? WHERE user_account.name = ?
[...] [('ed', 'jack'), ('mary', 'wendy'), ('jake', 'jim')]
<sqlalchemy.engine.cursor.CursorResult object at 0x...>
COMMIT

Other techniques which may be applied to UPDATE include:

相关更新

Correlated Updates

中文

UPDATE 语句可以通过使用 相关子查询 来利用其他表中的行。子查询可以在任何可以放置列表达式的地方使用:

>>> scalar_subq = (
...     select(address_table.c.email_address)
...     .where(address_table.c.user_id == user_table.c.id)
...     .order_by(address_table.c.id)
...     .limit(1)
...     .scalar_subquery()
... )
>>> update_stmt = update(user_table).values(fullname=scalar_subq)
>>> print(update_stmt)
UPDATE user_account SET fullname=(SELECT address.email_address
FROM address
WHERE address.user_id = user_account.id ORDER BY address.id
LIMIT :param_1)

英文

An UPDATE statement can make use of rows in other tables by using a correlated subquery. A subquery may be used anywhere a column expression might be placed:

>>> scalar_subq = (
...     select(address_table.c.email_address)
...     .where(address_table.c.user_id == user_table.c.id)
...     .order_by(address_table.c.id)
...     .limit(1)
...     .scalar_subquery()
... )
>>> update_stmt = update(user_table).values(fullname=scalar_subq)
>>> print(update_stmt)
UPDATE user_account SET fullname=(SELECT address.email_address
FROM address
WHERE address.user_id = user_account.id ORDER BY address.id
LIMIT :param_1)

UPDATE..FROM

UPDATE..FROM

中文

某些数据库（如 PostgreSQL 和 MySQL）支持 “UPDATE FROM” 语法，其中可以在一个特殊的 FROM 子句中直接声明其他表。当在语句的 WHERE 子句中找到其他表时，将隐式生成此语法:

>>> update_stmt = (
...     update(user_table)
...     .where(user_table.c.id == address_table.c.user_id)
...     .where(address_table.c.email_address == "patrick@aol.com")
...     .values(fullname="Pat")
... )
>>> print(update_stmt)
UPDATE user_account SET fullname=:fullname FROM address
WHERE user_account.id = address.user_id AND address.email_address = :email_address_1

MySQL 还有一种特定语法，可以更新多个表。这要求我们在 VALUES 子句中引用 Table 对象，以便引用其他表:

>>> update_stmt = (
...     update(user_table)
...     .where(user_table.c.id == address_table.c.user_id)
...     .where(address_table.c.email_address == "patrick@aol.com")
...     .values(
...         {
...             user_table.c.fullname: "Pat",
...             address_table.c.email_address: "pat@aol.com",
...         }
...     )
... )
>>> from sqlalchemy.dialects import mysql
>>> print(update_stmt.compile(dialect=mysql.dialect()))
UPDATE user_account, address
SET address.email_address=%s, user_account.fullname=%s
WHERE user_account.id = address.user_id AND address.email_address = %s

英文

Some databases such as PostgreSQL and MySQL support a syntax “UPDATE FROM” where additional tables may be stated directly in a special FROM clause. This syntax will be generated implicitly when additional tables are located in the WHERE clause of the statement:

>>> update_stmt = (
...     update(user_table)
...     .where(user_table.c.id == address_table.c.user_id)
...     .where(address_table.c.email_address == "patrick@aol.com")
...     .values(fullname="Pat")
... )
>>> print(update_stmt)
UPDATE user_account SET fullname=:fullname FROM address
WHERE user_account.id = address.user_id AND address.email_address = :email_address_1

There is also a MySQL specific syntax that can UPDATE multiple tables. This requires we refer to Table objects in the VALUES clause in order to refer to additional tables:

>>> update_stmt = (
...     update(user_table)
...     .where(user_table.c.id == address_table.c.user_id)
...     .where(address_table.c.email_address == "patrick@aol.com")
...     .values(
...         {
...             user_table.c.fullname: "Pat",
...             address_table.c.email_address: "pat@aol.com",
...         }
...     )
... )
>>> from sqlalchemy.dialects import mysql
>>> print(update_stmt.compile(dialect=mysql.dialect()))
UPDATE user_account, address
SET address.email_address=%s, user_account.fullname=%s
WHERE user_account.id = address.user_id AND address.email_address = %s

参数有序更新

Parameter Ordered Updates

中文

另一种 MySQL 特有的行为是，UPDATE 的 SET 子句中参数的顺序实际上会影响每个表达式的评估。对于这种用例，Update.ordered_values() 方法接受一个元组序列，以便可以控制此顺序 [1]:

>>> update_stmt = update(some_table).ordered_values(
...     (some_table.c.y, 20), (some_table.c.x, some_table.c.y + 10)
... )
>>> print(update_stmt)
UPDATE some_table SET y=:y, x=(some_table.y + :y_1)

[1]

虽然 Python 字典在 Python 3.7 及以后版本中 保证按插入顺序排列， 但 Update.ordered_values() 方法仍然提供了额外的意图明确性， 当 MySQL UPDATE 语句的 SET 子句需要按特定方式进行时尤为重要。

英文

Another MySQL-only behavior is that the order of parameters in the SET clause of an UPDATE actually impacts the evaluation of each expression. For this use case, the Update.ordered_values() method accepts a sequence of tuples so that this order may be controlled [2]:

>>> update_stmt = update(some_table).ordered_values(
...     (some_table.c.y, 20), (some_table.c.x, some_table.c.y + 10)
... )
>>> print(update_stmt)
UPDATE some_table SET y=:y, x=(some_table.y + :y_1)

[2]

While Python dictionaries are guaranteed to be insert ordered as of Python 3.7, the Update.ordered_values() method still provides an additional measure of clarity of intent when it is essential that the SET clause of a MySQL UPDATE statement proceed in a specific way.

delete() SQL 表达式构造

The delete() SQL Expression Construct

中文

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

从 API 角度来看，delete() 语句与 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

英文

The delete() function generates a new instance of Delete which represents a DELETE statement in SQL, that will delete rows from a table.

The delete() statement from an API perspective is very similar to that of the update() construct, traditionally returning no rows but allowing for a RETURNING variant on some database backends.

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

多表删除

Multiple Table Deletes

中文

与 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

英文

Like Update, Delete supports the use of correlated subqueries in the WHERE clause as well as backend-specific multiple table syntaxes, such as DELETE FROM..USING on MySQL:

>>> 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 获取受影响的行数

Getting Affected Row Count from 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 子句 匹配(matched) 的行数。无论行是否实际修改都无关紧要。

• 对于使用 RETURNING 的 UPDATE 或 DELETE 语句，或使用 executemany 执行的语句，CursorResult.rowcount 不一定可用。这取决于所使用的 DBAPI 模块。

• 在任何 DBAPI 不确定某种语句的 rowcount 的情况下，返回的值为 -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 unit of work 过程使用，以验证 UPDATE 或 DELETE 语句匹配预期的行数，并且对于文档中 配置版本计数器 的 ORM 版本控制功能也至关重要。

英文

Both Update and Delete support the ability to return the number of rows matched after the statement proceeds, for statements that are invoked using Core Connection, i.e. Connection.execute(). Per the caveats mentioned below, this value is available from the CursorResult.rowcount attribute:

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

小技巧

The CursorResult class is a subclass of Result which contains additional attributes that are specific to the DBAPI cursor object. An instance of this subclass is returned when a statement is invoked via the Connection.execute() method. When using the ORM, the Session.execute() method returns an object of this type for all INSERT, UPDATE, and DELETE statements.

Facts about CursorResult.rowcount:

• The value returned is the number of rows matched by the WHERE clause of the statement. It does not matter if the row were actually modified or not.

• CursorResult.rowcount is not necessarily available for an UPDATE or DELETE statement that uses RETURNING, or for one that uses an executemany execution. The availability depends on the DBAPI module in use.

• In any case where the DBAPI does not determine the rowcount for some type of statement, the returned value will be -1.

• SQLAlchemy pre-memoizes the DBAPIs cursor.rowcount value before the cursor is closed, as some DBAPIs don’t support accessing this attribute after the fact. In order to pre-memoize cursor.rowcount for a statement that is not UPDATE or DELETE, such as INSERT or SELECT, the Connection.execution_options.preserve_rowcount execution option may be used.

• Some drivers, particularly third party dialects for non-relational databases, may not support CursorResult.rowcount at all. The CursorResult.supports_sane_rowcount cursor attribute will indicate this.

• “rowcount” is used by the ORM unit of work process to validate that an UPDATE or DELETE statement matched the expected number of rows, and is also essential for the ORM versioning feature documented at 配置版本计数器.

将 RETURNING 与 UPDATE、DELETE 结合使用

Using RETURNING with UPDATE, DELETE

中文

与 Insert 构造类似，Update 和 Delete 也支持 RETURNING 子句，使用 Update.returning() 和 Delete.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

英文

Like the Insert construct, Update and Delete also support the RETURNING clause which is added by using the Update.returning() and Delete.returning() methods. When these methods are used on a backend that supports RETURNING, selected columns from all rows that match the WHERE criteria of the statement will be returned in the Result object as rows that can be iterated:

>>> 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 的进一步阅读

Further Reading for UPDATE, DELETE

中文

参见

UPDATE / DELETE 的API文档:

• Update

• Delete

基于 ORM 的 UPDATE 和 DELETE:

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

英文

参见

API documentation for UPDATE / DELETE:

• Update

• Delete

ORM-enabled UPDATE and DELETE:

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

SQLAlchemy 1.4 / 2.0 Tutorial

Next Tutorial Section: 使用 ORM 进行数据操作