sqlalchemy.exc.ResourceClosedError: This Connection is closed when inserting after select

Question

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

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?

Solution

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

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

---

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

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

or

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

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

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