SQLAlchemy 2.0 文档
使用 UPDATE 和 DELETE 语句¶
到目前为止,我们已经介绍了 Insert
,以便我们可以将一些数据放入数据库,然后在 Select
上花费了大量时间,它处理了用于从数据库检索数据的广泛使用模式。在本节中,我们将介绍 Update
和 Delete
构造,它们用于修改现有行以及删除现有行。本节将从 Core-centric 的角度介绍这些构造。
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 支持所有主要的 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 获取受影响的行数¶
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 子句匹配的行数。行是否实际被修改并不重要。
对于使用 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.rowcount
。CursorResult.supports_sane_rowcount
游标属性将指示这一点。ORM 工作单元 进程使用“rowcount”来验证 UPDATE 或 DELETE 语句是否匹配了预期的行数,并且对于 配置版本计数器 中记录的 ORM 版本控制功能也至关重要。
将 RETURNING 与 UPDATE、DELETE 一起使用¶
与 Insert
构造类似,Update
和 Delete
也支持 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
SQLAlchemy 1.4 / 2.0 教程
下一篇教程: 使用 ORM 进行数据操作
flambé! 龙和 炼金术士 图像设计由 Rotem Yaari 创作并慷慨捐赠。
使用 Sphinx 7.2.6 创建。文档最后生成时间:2025 年 3 月 11 日星期二下午 02:40:17 EDT