How to commit a transaction even when sqlalchemy thinks the session is clean

• zope

For the most part I try not to bother with low-level SQL, even though I'm quite proficient in doing the occasional complex join operation. But occasionally you just want to delete a whole bunch of records quickly. In this specific instance, I just wanted to delete large numbers of users stored in a postgresql database and used in zope by means of pas.plugins.sqlalchemy.

I tried to do it the obvious way.

from pas.plugins.sqlalchemy import model
from z3c.saconfig import named_scoped_session
Session = named_scoped_session("pas.plugins.sqlalchemy")
session = Session()
session.execute(delete(model.User.__table__))

It didn't work. The records were all still there. So I turned statement logging on in the database to see what it was doing, and I noticed that at the end of the transaction, it always calls ROLLBACK. Google suggested that this sneaky low-level modification of the database does not mark the session as dirty, and therefore it is not committed. Eventually I dumbed down my search string and found this solution, which I will now (re)share with the world. If you are using zope.sqlalchemy, all you need to do is this:

from zope.sqlalchemy import mark_changed
mark_changed(session)

Its even documented on the project page on pypi.