back to listing index

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

[web search]
Original source (www.upfrontsystems.co.za)
Tags: database python sqlalchemy
Clipped on: 2013-06-13

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

by Izak Burger posted on Aug 19, 2011 03:02 PM last modified Aug 19, 2011 03:02 PM —

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.