sqlalchemy.exc.ResourceClosedError:选择后插入时此连接已关闭 [英] sqlalchemy.exc.ResourceClosedError: This Connection is closed when inserting after select

问题描述

我正在从 SQLite 数据库执行 select()，然后执行 insert():

I am doing a select() from a SQLite database and then an insert():

engine = create_engine('sqlite:///testdb.db')
metadata = MetaData(bind=engine)
test = Table('test', metadata, autoload=True)

# Select all from pending_data
sel = select([test])
res = engine.execute(sel)

print res

# do an insert into pending_data
test.insert()\
    .values(info='blah')\
    .execute()

当我的代码执行插入行时，出现此错误:

When my code executes the insert line, I get this error:

sqlalchemy.exc.ResourceClosedError: This Connection is closed

但是，如果我将 res 转换为这样的列表:

However, if I convert my res into a list like so:

res = list(engine.execute(sel))

我的代码运行良好.这是怎么回事?

My code runs fine. What's going on here?

推荐答案

SQLAlchemy 有两个必须了解的概念:连接和引擎.一个引擎可以支持多个同时连接.在您的示例中，您将表绑定到引擎.现在，每当您调用 .execute 时，都会为您执行的每个查询创建一个新连接.但是 sqlite3 只允许 1 个同时连接".

SQLAlchemy has two concepts that one must be aware of: connections and engines. An engine can support many simultaneous connections. In your example you bind the table to an engine. Now, whenever you call .execute, a new connection is created for each single query that you execute. But sqlite3 only allows 1 simultaneous "connection".

解决此问题的最佳方法是创建连接并显式使用它，而不是引擎自动创建的连接；并使用带有 with 语句的连接，以确保连接将在块的末尾关闭:

The best way to fix this is to create the connection and use it explicitly instead of the automatically created connections of the engine; and to use the connection with the with statement, which ensures that the connection would be closed at the end of the block:

engine = create_engine('sqlite:///testdb.db')
metadata = MetaData(bind=engine)
test = Table('test', metadata, autoload=True)

with engine.connect() as connection:
    # Select all from pending_data
    sel = select([test])
    res = connection.execute(sel)

    # do an insert into pending_data
    connection.execute(test.insert().values(info='blah'))

<小时>

要理解这种行为，发生错误是因为您在隐式创建和持有的集合中持有一个活动游标(这由 res 变量引用；直到您使用它或关闭它, 或者删除对它的引用，游标和连接将处于活动状态，并且数据库将被锁定).

---

To understand this behaviour, the error occurs because you are holding an active cursor within an implicitly created and held collection (this is referred to by the res variable; until you consume it, or close it, or drop references to it, the cursor and thus the connection will be alive, and the database will be locked).

当您执行 list(res) 时，您正在消耗游标并被 SQLAlchemy 关闭；如果结果的引用计数降为 0，也会发生同样的情况.

When you execute list(res), you are consuming the cursor and it gets closed by SQLAlchemy; the same would happen if the reference count of the result would drop to 0.

您也可以尝试以下操作来了解这一点，它们会按您的预期工作:

You can also try the following to see the point, they'd work as you expected:

res = engine.execute(sel)
print(res)
res.close()  # close explicitly

或

res = engine.execute(sel)
print(res)
del res  # drop the only reference to res

因此总是完全使用 ResultProxy 或显式关闭它，或在完成后删除对它的引用.

Thus always consume the ResultProxy entirely or close it explicitly, or drop references to it when you're done.

如果您重复使用相同的连接，这不是问题；仅当您创建与 sqlite3 数据库的新连接时(postgresql、mysql、oracle 等也可以处理此问题).

And this is not a problem if you reuse the same connection; only whenever you create a new connection to an sqlite3 database (postgresql, mysql, oracle etc handle this fine too).

这篇关于sqlalchemy.exc.ResourceClosedError:选择后插入时此连接已关闭的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！