使用 UPDATE 和 DELETE 语句

到目前为止，我们已经介绍了 Insert，这样我们就可以将一些数据放入我们的数据库，然后花了大量时间讨论 Select，它处理用于从数据库检索数据的各种使用模式。在本节中，我们将介绍 Update 和 Delete 构造函数，它们用于修改现有行以及删除现有行。本节将从以核心为中心的视角介绍这些构造函数。

ORM 阅读者 - 正如在 使用 INSERT 语句 中提到的，Update 和 Delete 操作在与 ORM 一起使用时通常是在 Session 对象中作为 工作单元 过程的一部分内部调用的。

然而，与 Insert 不同，Update 和 Delete 构造函数也可以直接与 ORM 一起使用，使用一种称为“ORM 启用的更新和删除”的模式；出于这个原因，熟悉这些构造函数对于 ORM 使用很有用。两种使用方式都在部分 使用工作单元模式更新 ORM 对象 和 使用工作单元模式删除 ORM 对象 中进行了讨论。

update() SQL 表达式构造函数

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 支持 UPDATE 的所有主要 SQL 形式，包括针对表达式的更新，在这里我们可以使用 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 的其他技术包括

相关更新

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)

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 == "[email protected]")
...     .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 == "[email protected]")
...     .values(
...         {
...             user_table.c.fullname: "Pat",
...             address_table.c.email_address: "[email protected]",
...         }
...     )
... )
>>> 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

参数顺序更新

另一个 MySQL 特定的行为是，UPDATE 的 SET 子句中参数的顺序实际上会影响每个表达式的计算。对于这种情况，Update.ordered_values() 方法接受一个元组序列，以便可以控制该顺序 [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)

delete() SQL 表达式构造函数

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

多表删除

与 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 == "[email protected]")
... )
>>> 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 在光标关闭之前预先记忆了 DBAPI 的 cursor.rowcount 值，因为某些 DBAPI 不支持在事后访问此属性。为了预先记忆非 UPDATE 或 DELETE 语句（如 INSERT 或 SELECT）的 cursor.rowcount，可以使用 Connection.execution_options.preserve_rowcount 执行选项。

  • 某些驱动程序，尤其是针对非关系型数据库的第三方方言，可能根本不支持 CursorResult.rowcount。 CursorResult.supports_sane_rowcount 光标属性将指示这种情况。

  • “rowcount” 用于 ORM 工作单元 过程，以验证 UPDATE 或 DELETE 语句是否匹配预期行数，并且对于在 配置版本计数器 中记录的 ORM 版本控制功能也至关重要。

在 UPDATE、DELETE 中使用 RETURNING

类似于 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

UPDATE、DELETE 的进一步阅读

另请参阅

UPDATE/DELETE 的 API 文档

启用 ORM 的 UPDATE 和 DELETE

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