Deleting on NULL to right of left outer join in SQLAlchemy

Question

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)

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