sqlite nested transactions fail with "(sqlite3.OperationalError) no such savepoint"

Using savepoints via the sqlite3 command line tool works fine; but doing it via SQLAlchemy gives a "no such savepoint" error.

$ cat requirements.txt
PyMySQL
SQLAlchemy

$ cat savepoint.py
from __future__ import absolute_import, division, print_function, unicode_literals
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
import os

if os.path.exists("test.db"): os.unlink("test.db")
engine = create_engine("sqlite:///test.db", echo=True)
session_factory = sessionmaker(bind=engine)
session = session_factory()
session.execute("create table foo (val int)")

session.execute("insert into foo values (1)")
session.begin_nested()
session.execute("insert into foo values (2)")
session.rollback()
session.execute("insert into foo values (3)")
session.commit()
print(repr(session.execute("select * from foo")))

$ python savepoint.py
2015-10-22 17:12:28,535 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2015-10-22 17:12:28,536 INFO sqlalchemy.engine.base.Engine ()
2015-10-22 17:12:28,536 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2015-10-22 17:12:28,537 INFO sqlalchemy.engine.base.Engine ()
2015-10-22 17:12:28,539 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2015-10-22 17:12:28,540 INFO sqlalchemy.engine.base.Engine create table foo (val int)
2015-10-22 17:12:28,541 INFO sqlalchemy.engine.base.Engine ()
2015-10-22 17:12:28,556 INFO sqlalchemy.engine.base.Engine insert into foo values (1)
2015-10-22 17:12:28,557 INFO sqlalchemy.engine.base.Engine ()
2015-10-22 17:12:28,558 INFO sqlalchemy.engine.base.Engine SAVEPOINT sa_savepoint_1
2015-10-22 17:12:28,559 INFO sqlalchemy.engine.base.Engine ()
2015-10-22 17:12:28,569 INFO sqlalchemy.engine.base.Engine insert into foo values (2)
2015-10-22 17:12:28,570 INFO sqlalchemy.engine.base.Engine ()
2015-10-22 17:12:28,570 INFO sqlalchemy.engine.base.Engine ROLLBACK TO SAVEPOINT sa_savepoint_1
2015-10-22 17:12:28,571 INFO sqlalchemy.engine.base.Engine ()
Traceback (most recent call last):
  File "savepoint.py", line 15, in <module>
    session.rollback()
  File "/home/brian/savepoint/venv/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 765, in rollback
    self.transaction.rollback()
  File "/home/brian/savepoint/venv/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 419, in rollback
    transaction._rollback_impl()
  File "/home/brian/savepoint/venv/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 449, in _rollback_impl
    t[1].rollback()
  File "/home/brian/savepoint/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1563, in rollback
    self._do_rollback()
  File "/home/brian/savepoint/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1625, in _do_rollback
    self._savepoint, self._parent)
  File "/home/brian/savepoint/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 717, in _rollback_to_savepoint_impl
    self.engine.dialect.do_rollback_to_savepoint(self, name)
  File "/home/brian/savepoint/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 441, in do_rollback_to_savepoint
    connection.execute(expression.RollbackToSavepointClause(name))
  File "/home/brian/savepoint/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "/home/brian/savepoint/venv/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/brian/savepoint/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/home/brian/savepoint/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
    context)
  File "/home/brian/savepoint/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
    exc_info
  File "/home/brian/savepoint/venv/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/home/brian/savepoint/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/home/brian/savepoint/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such savepoint: sa_savepoint_1 [SQL: u'ROLLBACK TO SAVEPOINT sa_savepoint_1']

But from the sqlite3 CLI tool:

$ sqlite3 test.db
SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> insert into foo values (1);
sqlite> SAVEPOINT sa_savepoint_1;
sqlite> insert into foo values (2);
sqlite> ROLLBACK TO SAVEPOINT sa_savepoint_1;
sqlite> select * from foo;
1
sqlite> commit;
sqlite>

It's nothing to do with creating the table, because if you run the python script again with these two lines commented out, you get the same error.

#if os.path.exists("test.db"): os.unlink("test.db")
...
#session.execute("create table foo (val int)")

Environment:

ubuntu 14.04.3 x86_64
pip picked up the following versions:

Downloading/unpacking PyMySQL (from -r requirements.txt (line 1))
  Downloading PyMySQL-0.6.7-py2.py3-none-any.whl (69kB): 69kB downloaded
Downloading/unpacking SQLAlchemy (from -r requirements.txt (line 2))
  Downloading SQLAlchemy-1.0.9.tar.gz (4.7MB): 4.7MB downloaded

>>> import sqlite3 as s
>>> s.sqlite_version
'3.8.2'

$ dpkg-query -l | grep -i sqlite
ii  libaprutil1-dbd-sqlite3:amd64       1.5.3-1                          amd64        Apache Portable Runtime Utility Library - SQLite3 Driver
ii  libsqlite3-0:amd64                  3.8.2-1ubuntu2.1                 amd64        SQLite 3 shared library
ii  sqlite3                             3.8.2-1ubuntu2.1                 amd64        Command line interface for SQLite 3

Comments (4)