SQLAlchemy - Cannot evaluate BinaryExpression with operator

Question

I am trying to update some records in the table using the following code:

session.query(Post).filter(
    Post.title.ilike("%Regular%")
).update({"status": False})

But the problem is that the code throws the following exception:

InvalidRequestError: Could not evaluate current criteria in Python: "Cannot evaluate BinaryExpression with operator <function ilike_op at 0x7fbb88450ea0>". Specify 'fetch' or False for the synchronize_session parameter.

However, if I pass synchronize_session=False to the update(), it works miraculously.

session.query(Post).filter(
    Post.title.ilike("%Regular%")
).update({"status": False}, synchronize_session=False)

So what the use of synchronize_session?

Answer 1

Query.update is a bulk operation, that is it operates outside of Sqlalchemy's unit of work transaction model.

synchronize_session provides a way to specify whether the update should take into account data that is in the session, but is not in the database.

From the docs:

synchronize_session chooses the strategy to update the attributes on objects in the session. Valid values are:

False - don’t synchronize the session. This option is the most efficient and is reliable once the session is expired, which typically occurs after a commit(), or explicitly using expire_all(). Before the expiration, updated objects may still remain in the session with stale values on their attributes, which can lead to confusing results.

So, with synchronize_session=False, the values updated in the database will not be updated in the session.

'fetch' - performs a select query before the update to find objects that are matched by the update query. The updated attributes are expired on matched objects.

Passing fetch makes sqlalchemy identify values in the session affected by the update, and when they are next accessed sqlalchemy will query the database to get their updated values

'evaluate' - Evaluate the Query’s criteria in Python straight on the objects in the session. If evaluation of the criteria isn’t implemented, an exception is raised.

In your code, you do not specify a value for synchronize_session so the default value, evaluate applies. Sqlalchemy can't find a way to do ilike on your model without delegating to the database so it raises an exception to make the developer decide whether or not to synchronize the values in the session with the values in the database.