Sqlalchemy 简明教程

SQLAlchemy Core - Multiple Table Deletes

在本章中，我们将研究类似于使用多表更新功能的多表删除表达式。

In this chapter, we will look into the Multiple Table Deletes expression which is similar to using Multiple Table Updates function.

在许多 DBMS 方言中，DELETE 语句的 WHERE 子句中可以引用多个表格。对于 PG 和 MySQL，使用了 “DELETE USING” 语法；对于 SQL Server，使用 “DELETE FROM” 表达式引用多个表格。SQLAlchemy delete() 构造隐式支持这两种模式，通过在 WHERE 子句中指定多个表格，如下所示：

More than one table can be referred in WHERE clause of DELETE statement in many DBMS dialects. For PG and MySQL, “DELETE USING” syntax is used; and for SQL Server, using “DELETE FROM” expression refers to more than one table. The SQLAlchemy delete() construct supports both of these modes implicitly, by specifying multiple tables in the WHERE clause as follows −

stmt = users.delete().\
   where(users.c.id == addresses.c.id).\
   where(addresses.c.email_address.startswith('xyz%'))
conn.execute(stmt)

在 PostgreSQL 后端，上面语句生成的 SQL 将呈现为：

On a PostgreSQL backend, the resulting SQL from the above statement would render as −

DELETE FROM users USING addresses
WHERE users.id = addresses.id
AND (addresses.email_address LIKE %(email_address_1)s || '%%')

如果将此方法与不支持此行为的数据库一起使用，编译器将引发 NotImplementedError。

If this method is used with a database that doesn’t support this behaviour, the compiler will raise NotImplementedError.