Nested transactions with SQLAlchemy and sqlite

Question

I'm writing an application in python using sqlalchemy (and Elixir) 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?

Answer 1

SQLAlchemy uses pysqlite to interact with an SQLite database, if I'm not mistaken pysqlite will by default wrap up any query you send it in a transaction.

The answer might lie in correctly setting the isolation level when you connect.

Some discussion about that over here

Answer 2

Although sqlite does appear to support nested transactions via SAVEPOINT, it's only as of version 3.6.8, released 2009 Jan 12. Python, at least up to v2.6, uses earlier versions:

c:\svn\core\apps\general>python
Python 2.6.2 (r262:71605, Apr 14 2009, 22:40:02) [MSC v.1500 32 bit (Intel)] on win32
>>> import sqlite3 as s
>>> s.sqlite_version
'3.5.9'

I believe you can install PySqlite yourself and the latest appears to support v3.6.12. I can't say for sure this will solve your problem though, but I believe the answer explains why it's not working for you now.