Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
2.1k views
in Technique[技术] by (71.8m points)

python - SQLAlchemy - Cannot evaluate BinaryExpression with operator

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...