[Sqlalchemy-tickets] Issue #3561: sqlite nested transactions fail with "(sqlite3.OperationalError)
Brought to you by:
zzzeek
|
From: Brian C. <iss...@bi...> - 2015-10-22 17:12:53
|
New issue 3561: sqlite nested transactions fail with "(sqlite3.OperationalError) no such savepoint" https://bitbucket.org/zzzeek/sqlalchemy/issues/3561/sqlite-nested-transactions-fail-with Brian Candler: 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 ~~~ |