flask-sqlalchemy delete query failing with "Could not evaluate current criteria in Python"

Question

I have a query using flask-sqlalchemy in which I want to delete all the stocks from the database where there ticker matches one in a list. This is the current query I have:

Stock.query.filter(Stock.ticker.in_(new_tickers)).delete()

Where new_tickers is a list of str of valid tickers.

The error I am getting is the following:

sqlalchemy.exc.InvalidRequestError: Could not evaluate current criteria in Python: "Cannot evaluate clauselist with operator <function comma_op at 0x1104e4730>". Specify 'fetch' or False for the synchronize_session parameter.

Answer 1

You need to use one of options for bulk delete

Stock.query.filter(Stock.ticker.in_(new_tickers)).delete(synchronize_session=False)
Stock.query.filter(Stock.ticker.in_(new_tickers)).delete(synchronize_session='evaluate')
Stock.query.filter(Stock.ticker.in_(new_tickers)).delete(synchronize_session='fetch')

Basically, SQLAlchemy maintains the session in Python as you issue various SQLAlchemy methods. When you delete entries, how will SQLAlchemy remove any removed rows from the session? This is controlled by a parameter to the delete method, "synchronize_session". synchronize_session has three possible:

• 'evaluate': it evaluates the produced query directly in Python to determine the objects that need to be removed from the session. This is the default and is very efficient, but is not very robust and complicated queries cannot be be evaluated. If it can't evaluate the query, it raises the sqlalchemy.orm.evaluator.UnevaluatableError condition
• 'fetch': this performs a select query before the delete and uses that result to determine which objects in the session need to be removed. This is less efficient (potential much less efficient) but will be able to handle any valid query
• False: this doesn't attempt to update the session, so it's very efficient, however if you continue to use the session after the delete you may get inaccurate results.

Which option you use is very dependent on how your code uses the session. In most simple queries where you just need to delete rows based on a complicated query, False should work fine. (the example in the question fits this scenario)

SQLAlchemy Delete Method Reference

Answer 2

Try it with this code:

Stock.query.filter(Stock.ticker.in_(new_tickers)).delete(synchronize_session=False)

https://docs.sqlalchemy.org/en/latest/orm/query.html?highlight=delete#sqlalchemy.orm.query.Query.delete