使用 SQLAlchemy 和 sqlite 的嵌套事务

发布于 2024-08-09 08:05:30 字数 558 浏览 8 评论 0 原文

我正在使用 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 支持 SAVEPOINTS (http://www.sqlite.org/lang_savepoint.html）。

如何让嵌套事务发挥作用？

原文

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).

How do I get nested transactions to work?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

远昼 2024-08-16 08:05:30

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

经过几个小时的头撞桌子后，我在文档中发现了这一部分：

http://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#serialized-隔离-保存点-事务-ddl

在数据库锁定行为/并发部分中，我们参考
pysqlite 驱动程序的一系列问题阻止了一些
SQLite 的功能是否正常工作。 pysqlite DBAPI 驱动程序
有几个长期存在的错误，影响其正确性
交易行为。在其默认操作模式下，SQLite
诸如可串行化隔离、事务性 DDL 等功能
SAVEPOINT 支持是非功能性的，并且为了使用这些
功能，必须采取解决方法。

问题本质上是驾驶员试图事后猜测
用户的意图，无法启动交易，有时甚至结束交易
过早地，以尽量减少 SQLite 数据库文件
锁定行为，即使 SQLite 本身使用“共享”锁
只读活动。

SQLAlchemy 默认选择不改变此行为，因为它是
pysqlite 驱动程序的长期预期行为；如果并且当
pysqlite 驱动程序尝试修复这些问题，这将是更多
SQLAlchemy 默认值的驱动程序。

好消息是，通过一些事件，我们可以实现
通过完全禁用 pysqlite 的功能来完全支持事务
并发出“开始我们自己”。这是使用两个事件来实现的
听众：
from sqlalchemy import create_engine, 事件

引擎 = create_engine("sqlite:///myfile.db")

@event.listens_for（引擎，“连接”）
def do_connect（dbapi_connection，connection_record）：
    # 完全禁用 pysqlite 发出 BEGIN 语句。
    # 还可以阻止它在任何 DDL 之前发出 COMMIT。
    dbapi_connection.isolation_level = 无

@event.listens_for（引擎，“开始”）
def do_begin(conn):
    # 发出我们自己的 BEGIN
    conn.execute("开始")

添加上面的听众完全解决了我的问题！

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

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

我还发现记录 SQL 语句很有帮助（这在上面的示例中使用）：

调试（显示）由 SQLAlchemy 发送到数据库的 SQL 命令

I've run into this issue using nested transactions, using Python 3 on Windows. I'm using SQLite version 3.8.11, so SAVEPOINT should be supported. Apparently installing pysqlite isn't an option for me as it doesn't support Python 3.

After hours of banging my head against the desk I came across this section in the documentation:

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")

Adding the listeners above completely resolved the issue for me!

I've published a full working example as a gist:

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

I also found logging the SQL statements helpful (this is used in the above example):

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

回复收藏 0 原文

鲸落 2024-08-16 08:05:30

尽管 sqlite 似乎确实支持通过 SAVEPOINT 进行嵌套事务，但仅从 2009 年 1 月 12 日发布的版本 3.6.8 开始。 Python，至少到 v2.6，使用早期版本：

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'

我相信你可以安装 PySqlite你自己，最新的似乎支持v3.6.12。我不能肯定地说这会解决你的问题，但我相信答案解释了为什么它现在不适合你。

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.

回复收藏 0 原文