python - How do you express a multi table update (UPDATE FROM) in SQLAlchemy ORM?

Question

CREATE TABLE foo (
    name text NOT NULL,
    deleted_at timestamp without time zone
);

CREATE TABLE bar (
    name text NOT NULL,
    status_id int
);

UPDATE bar set status_id=1
FROM foo
WHERE status_id <> 1 AND foo.name = bar.name AND foo.deleted_at is null;

When I try to do this with the ORM, I end up with this error

InvalidRequestError: Can't call Query.update() or Query.delete() when join(), outerjoin(), select_from(), or from_self() has been called

I want to use the ORM so that the session will be updated with the changes before the update command completes.

Answer 1

Here's how you'd issue your multi table update using the ORM:

session.query(Bar).
    filter(Bar.status_id != 1,
           Bar.name == Foo.name,
           Foo.deleted_at.is_(None)).
    update({Bar.status_id: 1}, synchronize_session=False)

The footnote in the Query API documentation for update links to the Core multi table update docs:

The SQLAlchemy update() construct supports both of these modes implicitly, by specifying multiple tables in the WHERE clause

which expands to the ORM Query API as well, which is no surprise as the ORM is built on top of Core. The resulting query is:

UPDATE bar SET status_id=%(status_id)s
FROM foo
WHERE bar.status_id != %(status_id_1)s
  AND bar.name = foo.name
  AND foo.deleted_at IS NULL

From your error I suspect you have something along the lines of

session.query(Bar).select_from(Foo)...update(...)

which as the error states is not accepted. You have to pass the FROM table implicitly in the WHERE clause, e.g. filter() in the Query API.

To achieve

I'm wanting to use the ORM so that the session will be updated with the changes before the update command completes.

you'll have to change the synchronize_session accordingly to either 'fetch' or 'evaluate'.