How to commit a transaction even when sqlalchemy thinks the session is clean â Upfront Systems[web search]
How to commit a transaction even when sqlalchemy thinks the session is clean
This happens when you call session.execute() or session.connection().execute()
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.