使用session.query通过SQLAlchemy ORM更新连接表

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

使用MySQL，我想生成这个SQL：

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

This is my SQLAlchemy query:

这是我的SQLAlchemy查询：

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

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

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

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

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?

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

2 个解决方案

#1

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:

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

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

(example straight from the link above)

（例如直接来自上面的链接）

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:

ValAyal的问题实际上是因为Query.update（）不支持Query.join（）。不幸的是，直到0.9.1，这仍然无声地生成像上面分享的ValAyal之类的查询。 0.9.1的更改日志说明已修改行为以发出警告：

[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.

[orm] [bug]在使用Query.update（）或Query.delete（）方法时，Query不支持连接，子选择或特殊的FROM子句;如果调用了Query.join（）或Query.select_from（）之类的方法，则会发出警告，而不是默默地忽略这些字段。从1.0.0b5开始，这将引发错误。

References: #3349

参考文献：＃3349

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):

我们实际上遇到了这个我今晚工作的地方，发现我们的代码实际上发出了以下警告（表示它将在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.

在我们的示例中，我们选择将更新转换为对一个表的选择和更新。

#2

I think I had exactly the same problem. Here is my solution:

我想我有完全相同的问题。这是我的解决方案：

query = update(Model).values(field=123)
query = query.where(Model.parent_model_id == ParentModel.id)
query = query.where(ParentModel.grand_parent_id == GrandParentModel.id)
query = query.where(GrandParentModel.name == 'foobar')
session.execute(query)

#1