python - SQLAlchemy 和 sqlite 的嵌套事务

Question

我正在使用 SQLAlchemy(和 Elixir)用 Python 编写一个应用程序，并将 SQLite 作为数据库后端。我使用代码 session.begin_transaction() 开始一个新事务，但是当我调用 session.rollback() 时，我收到以下错误:

sqlalchemy.exceptions.OperationalError: (OperationalError) no such savepoint: sa_savepoint_1 u'ROLLBACK TO SAVEPOINT sa_savepoint_1' []

我在调用 session.commit() 时也遇到了类似的错误。据我所知，sqlite 支持保存点(http://www.sqlite.org/lang_savepoint.html)。

如何使嵌套事务起作用？

Answer 1

我在 Windows 上使用 Python 3 使用嵌套事务时遇到过这个问题。我使用的是 SQLite 版本 3.8.11，因此应该支持 SAVEPOINT。显然安装 pysqlite 不适合我，因为它不支持 Python 3。

在用头撞 table 几个小时后，我在文档中看到了这个部分:

http://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#serializable-isolation-savepoints-transactional-ddl

In the section Database Locking Behavior / Concurrency, we refer to the pysqlite driver’s assortment of issues that prevent several features of SQLite from working correctly. The pysqlite DBAPI driver has several long-standing bugs which impact the correctness of its transactional behavior. In its default mode of operation, SQLite features such as SERIALIZABLE isolation, transactional DDL, and SAVEPOINT support are non-functional, and in order to use these features, workarounds must be taken.

The issue is essentially that the driver attempts to second-guess the user’s intent, failing to start transactions and sometimes ending them prematurely, in an effort to minimize the SQLite databases’s file locking behavior, even though SQLite itself uses “shared” locks for read-only activities.

SQLAlchemy chooses to not alter this behavior by default, as it is the long-expected behavior of the pysqlite driver; if and when the pysqlite driver attempts to repair these issues, that will be more of a driver towards defaults for SQLAlchemy.

The good news is that with a few events, we can implement transactional support fully, by disabling pysqlite’s feature entirely and emitting BEGIN ourselves. This is achieved using two event listeners:

from sqlalchemy import create_engine, event

engine = create_engine("sqlite:///myfile.db")

@event.listens_for(engine, "connect")
def do_connect(dbapi_connection, connection_record):
    # disable pysqlite's emitting of the BEGIN statement entirely.
    # also stops it from emitting COMMIT before any DDL.
    dbapi_connection.isolation_level = None

@event.listens_for(engine, "begin")
def do_begin(conn):
    # emit our own BEGIN
    conn.execute("BEGIN")

添加上面的监听器完全解决了我的问题!

我已经发布了一个完整的工作示例作为要点:

https://gist.github.com/snorfalorpagus/c48770e7d1fcb9438830304c4cca24b9

我还发现记录 SQL 语句很有帮助(这在上面的示例中使用):

Debugging (displaying) SQL command sent to the db by SQLAlchemy