使用 UPDATE 和 DELETE 语句


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


ORM 读取器 - 正如 使用 INSERT 语句 中提到的情况，当与 ORM 一起使用时，Update 和 Delete作通常是从 Session 内部调用的 object 作为 work process 单元的一部分。


但是，与 Insert 不同的是，Update 和 Delete 结构也可以直接与 ORM 一起使用，使用称为“支持 ORM 的更新和删除”的模式;因此，熟悉这些结构对于 ORM 使用很有用。这两种使用方式都在 使用工作单元模式 更新 ORM 对象 和 使用 Unit of Work 模式删除 ORM 对象。


update（） SQL 表达式构造


update（） 函数会生成一个新的 Update 表示 SQL 中的 UPDATE 语句，该语句将更新表中的现有数据。


与 insert（） 结构一样，有一个“传统”形式的 update（）的 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 共享的方法相同 构建。 通常可以使用列名 keyword 参数。


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（） construct 可用于设置绑定参数;这些替换了 该 Literal 值通常会变为：

>>> 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 语句可以使用 correlated 子查询。子查询可用于可能放置列表达式的任何位置：

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


更新。。发件人


一些数据库（如 PostgreSQL 和 MySQL）支持语法“UPDATE FROM”，其中其他表可以直接在特殊的 FROM 子句中声明。当其他 table 位于语句的 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 特定的语法可以 UPDATE 多个表。这要求我们在 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


参数顺序更新


另一个仅限 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 子句中使用相关子查询以及特定于后端的多表语法，例如 DELETE FROM.。在 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 获取受影响的行数


Update 和 Delete 都支持在语句继续后返回匹配的行数的能力，对于使用 Core Connection 调用的语句，即 Connection.execute（） 的 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 游标对象的其他属性。 此子类的一个实例是 当通过 Connection.execute（） 方法。使用 ORM 时， Session.execute（） 方法为所有 INSERT、UPDATE 和 DELETE 语句返回此类型的对象。


关于 CursorResult.rowcount 的事实：


  • 返回的值是语句的 WHERE 子句匹配的行数。该行是否实际被修改并不重要。


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


  • 在 DBAPI 未确定某种类型语句的行数的任何情况下，返回的值将为 -1。


  • SQLAlchemy 在游标关闭之前预先记住 DBAPI 的 cursor.rowcount 值，因为某些 DBAPI 不支持在事后访问此属性。为了预先记住 cursor.rowcount 的语句，即 不是 UPDATE 或 DELETE，例如 INSERT 或 SELECT，则 Connection.execution_options.preserve_rowcount execution 选项。


  • 某些驱动程序（尤其是非关系数据库的第三方方言）可能根本不支持 CursorResult.rowcount。 这 CursorResult.supports_sane_rowcount cursor 属性将指示这一点。


  • “rowcount” 被 ORM 工作单元流程用来验证 UPDATE 或 DELETE 语句与预期的行数匹配，并且 对于记录在 配置版本计数器。


将 RETURNING 与 UPDATE、DELETE 一起使用


与 Insert 构造一样，Update 和 Delete 还支持使用 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 查询指南 中