使用 UPDATE 和 DELETE 语句

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

ORM 阅读器 - 正如在 使用 INSERT 语句 中提到的情况一样,UpdateDelete 操作在使用 ORM 时,通常是从 Session 对象内部作为 工作单元 过程的一部分调用的。

然而,与 Insert 不同,UpdateDelete 构造也可以直接与 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 支持所有主要的 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 的其他技术包括

相关更新

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

参数排序更新

另一个仅 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 == "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 获取受影响的行数

UpdateDelete 都支持在语句执行后返回匹配的行数,对于使用 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 子句匹配的行数。行是否实际被修改并不重要。

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

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

  • SQLAlchemy 在游标关闭之前预先记忆 DBAPI 的 cursor.rowcount 值,因为某些 DBAPI 不支持在此之后访问此属性。为了为非 UPDATE 或 DELETE 的语句 (例如 INSERT 或 SELECT) 预先记忆 cursor.rowcount,可以使用 Connection.execution_options.preserve_rowcount 执行选项。

  • 某些驱动程序,特别是用于非关系数据库的第三方方言,可能根本不支持 CursorResult.rowcountCursorResult.supports_sane_rowcount 游标属性将指示这一点。

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

将 RETURNING 与 UPDATE、DELETE 一起使用

Insert 构造类似,UpdateDelete 也支持 RETURNING 子句,该子句通过使用 Update.returning()Delete.returning() 方法添加。当在支持 RETURNING 的后端上使用这些方法时,将以可迭代的行形式在 Result 对象中返回语句的 WHERE 标准匹配的所有行中选定的列

>>> 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 查询指南