Why is my flask-sqlalchemy delete query failing

Question

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

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