Deleting on NULL to right of left outer join in SQLAlchemy
python, sqlalchemy
Solution
The exception is telling you exactly how to fix the problem, you need to specify a `synchronize_session` as one of `"fetch"` or `False`. Sqlalchemy is trying to avoid doing some extra work, by updating the state of the objects attached to the `session` to reflect the changes in the database by applying the delete directly to the python objects.
It unfortunately isn't able to do that for this query, so you need to tell it to either "fetch" the active `Artist`s to see if they were deleted, or to just ignore it, and leave the session in an invalid state.
You'll also run into a hiccup with the `in_(query.all())` production; since sqlalchemy doesn't know how to bind lists of tuple's, and is also an imperfect recreation of the original query. Just `in_(query)` is sufficient.
Problem
I have a `Track` table and an `Artist` table. The `Artist` table has an `id` and the `Track` table has a foreign key, `artist_id`. I want to delete all artists that have no associated track. In SQL: ``` delete from artists where id in (select artists.id from artists left outer join tracks on tracks.artist_id = artists.id where tracks.id is null); ``` which works perfectly. But when I try to duplicate this in SQLAlchemy: ``` artists = session.query(Artist.id).outerjoin((Track, Artist.id == Track.artist_id)).filter(Track.id == None) print('deleting %d unused artists' % artists.count()) session.query(Artist).filter(Artist.id.in_(artists.all())).delete() ``` the `print()` works fine (and shows the correct number of rows) BUT the delete gives the error: ``` ... sqlalchemy.orm.evaluator.UnevaluatableError: Cannot evaluate clauselist with operator <function comma_op at 0x2d3e0d8> During handling of the above exception, another exception occurred: ... "Could not evaluate current criteria in Python. " sqlalchemy.exc.InvalidRequestError: Could not evaluate current criteria in Python. Specify 'fetch' or False for the synchronize_session parameter. ``` So how do I do this in SQLAlchemy? I don't mind if the approach is different, as long as I can delete all artists that belong to no track. PS I also tried `artists.delete()` (while selecting for Artist instances, instead of ids as above) which also gives an error - in that case the outer join is "lost" and the SQL is inconsistent. Update In case this is useful for anyone, there's a much simpler approach than the outer join if you have a backref (`artists`, below) in your model: ``` session.query(Artist).filter(Artist.tracks == None).delete(synchronize_session=False) ```