如何在PostgreSQL的SQLAlchemy中编写不带子查询的UPDATE FROM

Question

我正在编写一个查询，通过计算表列的值与另一个表的列之间的差来更新表列。在原始SQL中，它看起来如下：

UPDATE
products
SET
quantity = products.quantity - order_items.quantity
FROM
order_items
WHERE
order_items.order_id = %(order_id_1) s
AND products.code = order_items.product_code;

我查看了SQLAlchemy文档，发现了一个关于update表达式的部分：

WHERE子句可以引用多个表。对于支持此功能的数据库，将生成UPDATE FROM子句，或者在MySQL上生成多表更新。该语句将在不支持多表更新语句的数据库上失败。

我试图实现文档中所说的查询：

query = (
products_table.update()
.values(quantity=products_table.c.quantity - order_items_table.c.quantity)
.where(
products_table.c.code
== select([order_items_table.c.product_code])
.where(
and_(
order_items_table.c.order_id == order_id,
order_items_table.c.product_code == products_table.c.code,
)
)
.as_scalar()
)
)

但我得到的不是一个简洁的UPDATE ... SET ... FROM表达式：

from sqlalchemy.dialects import postgresql
str(query.compile(dialect=postgresql.dialect()))

UPDATE
products
SET
quantity =(products.quantity - order_items.quantity)
WHERE
products.code = (
SELECT
order_items.product_code
FROM
order_items
WHERE
order_items.order_id = %(order_id_1) s
AND order_items.product_code = products.code
)

此外，此SQL查询并不完全正确，并且没有所需的FROM语句。

因此，我试图弄清楚我的查询表达式出了什么问题，以及是否有可能在没有子查询的情况下实现SQLAlchemy中的原始SQL。有什么想法吗？

版本

• 操作系统：MacOS
• Python：3.8.x
• SQL炼金术：1.3.20
• 数据库：PostgreSQL 11.x

提前感谢

Answer 1

我似乎误解了文档。我发布的链接解释了相反的情况，并提供了为不支持UPDATE FROM的数据库编写查询的示例。

我刚刚写了以下查询：

query = (
products_table.update()
.values(quantity=products_table.c.quantity - order_items_table.c.quantity)
.where(products_table.c.code == order_items_table.c.product_code)
.where(order_items_table.c.order_id == order_id)
)

并生成正确的SQL:

UPDATE
products
SET
quantity =(products.quantity - order_items.quantity)
FROM
order_items
WHERE
products.code = order_items.product_code
AND order_items.order_id = %(order_id_1) s