Updating a joined table with SQLAlchemy ORM using session.query

Question

Working with MySQL, I would 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') 

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

But the SQL that it creates is this (update with multiple tables, without join condition, which is not what I want):

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

I tried changing the .join to another .filter to indicate a join condition that did not solve the problem. How to get this simple update statement to make the correct connection?

Answer 1

Starting with version 0.7.4, sqlalchemy.sql.expression.update allows you to reference multiple tables in the WHERE clause. With this, you can create 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() ) 

(example directly from the link above)

ValAyal's problem is that Query.join() not supported by Query.update() . Unfortunately, prior to 0.9.1, this silently generated queries similar to the one described above by ValAyal. change notes for 0.9.1 notes that the behavior has been changed to issue a warning:

[orm] [error] Query does not support joins, subqueries, or special FROMs when using the Query.update () or Query.delete () methods; instead of silently ignoring these fields if methods such as Query.join () or Query.select_from () are issued a warning. Starting from 1.0.0b5 this will cause an error.

Links: # 3349

We actually ran into this when I was working that evening, and found that our code, in fact, issued the following warning (which says about the 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 decided to convert the update into a selection and update into one table.

Answer 2

I think I had 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)