Nested transactions with SQLAlchemy and sqlite
Jon
with sqlite as the database backend. I start a new transaction using
the code session.begin_transaction(), but when I call session.rollback
() I get the following error:
sqlalchemy.exceptions.OperationalError: (OperationalError) no such
savepoint: sa_savepoint_1 u'ROLLBACK TO SAVEPOINT sa_savepoint_1' []
I also get a similar error calling session.commit(). From what I can
tell, sqlite supports SAVEPOINTS (http://www.sqlite.org/
lang_savepoint.html).
So, how do I get nested transactions to work?
I've also posted the question on stack overflow, so you can reply
there if you like, or here, I don't mind.
Michael Bayer
our current tests disable SAVEPOINT testing for the sqlite3 DBAPI, as
it does not appear to understand the "SAVEPOINT" instruction. If I
enable a simple SAVEPOINT test, I get this output:
sqlalchemy.engine.base.Engine.0x...fd50: INFO: BEGIN
sqlalchemy.engine.base.Engine.0x...fd50: INFO: INSERT INTO query_users
(user_id, user_name) VALUES (?, ?)
sqlalchemy.engine.base.Engine.0x...fd50: INFO: [1, 'user1']
sqlalchemy.engine.base.Engine.0x...fd50: INFO: SAVEPOINT sa_savepoint_1
sqlalchemy.engine.base.Engine.0x...fd50: INFO: []
sqlalchemy.engine.base.Engine.0x...fd50: INFO: INSERT INTO query_users
(user_id, user_name) VALUES (?, ?)
sqlalchemy.engine.base.Engine.0x...fd50: INFO: [2, 'user2']
sqlalchemy.engine.base.Engine.0x...fd50: INFO: RELEASE SAVEPOINT
sa_savepoint_1
sqlalchemy.engine.base.Engine.0x...fd50: INFO: []
and then the same error you're getting, i.e.
OperationalError: (OperationalError) no such savepoint: sa_savepoint_1
u'RELEASE SAVEPOINT sa_savepoint_1' []
If you can research for us the correct way for the pysqlite DBAPI to
understand a SAVEPOINT conversation, it can be implemented.
Jon Black
Whilst the solution doesn't help, it does mention that it's not supported in pysqlite. So perhaps we need to talk with them?