Deleting On NULL To Right Of Left Outer Join In SQLAlchemy

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.

Solution 1:

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