使用 session.query 通过 SQLAlchemy ORM 更新连接表 [英] Update joined table via SQLAlchemy ORM using session.query

问题描述

使用 MySQL，我想生成此 SQL:

Working with MySQL, I'd like to generate this SQL:

UPDATE tableA
INNER JOIN tableB
ON tableA.some_id = tableB.some_id
SET tableA.foo = 1
WHERE tableB.bar IN ('baz','baaz')

这是我的 SQLAlchemy 查询:

This is my SQLAlchemy query:

session.query(tableA).join(tableB, tableA.some_id == tableB.some_id) \
                     .filter(tableB.bar.in_(['baz','baaz']))\
                     .update({tableA.foo: 1})

但是它生成的SQL是这样的(多表更新，没有连接条件，这不是我想要的):

But the SQL it generates is this (a multi-table update, with no join condition, which is not what I want):

UPDATE tableA, tableB 
SET tableA.foo = 1
WHERE tableB.bar IN ('baz','baaz')

我尝试将 .join 更改为另一个 .filter 以指定连接条件，但这并没有解决问题.如何强制这个简单的更新语句进行正确的连接?

I've tried changing the .join into another .filter to specify the join condition, that didn't solve the problem. How do I force this simple update statement to do the proper join?

推荐答案

从 0.7.4 版本开始 sqlalchemy.sql.expression.update 确实允许您在 WHERE 子句中引用多个表.有了这个，你可以构建和执行一个表达式:

As of version 0.7.4 sqlalchemy.sql.expression.update does allow you to refer to multiple tables in the WHERE clause. With this, you could build and execute an expression like:

users.update().values(name='ed').where(
        users.c.name==select([addresses.c.email_address]).\
                    where(addresses.c.user_id==users.c.id).\
                    as_scalar()
        )

(直接来自上面链接的示例)

ValAyal 的问题实际上是因为 Query.update() 不支持 Query.join().不幸的是，在 0.9.1 之前，它会默默地生成类似于上面共享的 ValAyal 的查询.0.9.1 的变更日志说明 指出该行为被修改为发出警告:

The problem ValAyal is having is actually because Query.join() is not supported with Query.update(). Unfortunately, until 0.9.1 this was silently generating queries like the one ValAyal shared above. The changelog notes for 0.9.1 notes that the behavior was modified to emit a warning:

[orm] [bug] 查询不支持连接、子选择或特殊的 FROM使用 Query.update() 或 Query.delete() 方法时的子句；如果像 Query.join() 这样的方法，而不是默默地忽略这些字段或 Query.select_from() 已被调用，发出警告.作为1.0.0b5 这将引发错误.

[orm] [bug] Query doesn’t support joins, subselects, or special FROM clauses when using the Query.update() or Query.delete() methods; instead of silently ignoring these fields if methods like Query.join() or Query.select_from() has been called, a warning is emitted. As of 1.0.0b5 this will raise an error.

参考:#3349

我们实际上在今晚工作的地方遇到了这个问题，发现我们的代码实际上发出了以下警告(表示它将在 1.0 中出错):

We actually ran into this where I work just this evening and found that our code is, in fact, emitting the following warning (which says it will an error in 1.0):

SAWarning: Can't call Query.update() or Query.delete() when join(), outerjoin(), select_from(), or from_self() has been called.  This will be an exception in 1.0
  self._validate_query_state()

在我们的例子中，我们选择将更新转换为一个选择和一个表的更新.

In our case, we opted to convert the update into a select and an update to one table.

这篇关于使用 session.query 通过 SQLAlchemy ORM 更新连接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！