How do you express a multi-page 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 using ORM, I get 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 ORM so that the session is updated with changes before the update command completes.

Answer 1

Here's how you can send a multi-page update using 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) 

I could not find the link in the Query API documentation, but from the documentation for updating the Core multi table we see that

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

and it seems to also expand to the ORM query API, which is not surprising since ORM is built on top of Core. Summary request:

 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 mistake, I suspect you have something in the lines

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

which, since error conditions are not accepted. You must pass the FROM table implicitly in the WHERE clause, for example. filter() in the request API.

To attain

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

you will have to change synchronize_session respectively to 'fetch' or 'evaluate' .