views:

215

answers:

2

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?

A: 

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

Dirk Stoop
I want nested transactions, where you can rollback without losing anything that has changed in the outer transaction. I'm not sure that pysqlite can do that automatically.Are you suggesting that using SAVEPOINTS are not possible, or just suggesting another (possible) way to solve the problem?
rioch
+1  A: 

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.

Peter Hansen
sqlite3 version is not bound to python version
iny
@iny, not quite sure what your point is. Python's version of sqlite (i.e. the one included with Python) is of course bound to a particular version of sqlite. The other way around... well I certainly don't say that above.
Peter Hansen