Python not automatically closing a connection to sqlite3 after exiting WITH statement?

Question

Here's a piece of code running on Python 3.10.5 -

with sqlite3.connect("test.db") as db:
    cursor = db.cursor()
    cursor.execute("""CREATE TABLE IF NOT EXISTS testtable(id integer PRIMARY KEY, name text);""")
    cursor.execute("""INSERT INTO testtable(id, name) VALUES (1, "XYZ")""")
    db.commit()
        

cursor.execute("select * from testtable")
for x in cursor.fetchall():
    print(x)

Now, as per my understanding of the way WITH statement works, the connection to the database will be automatically closed at the end of the code block without me needing to explicitly call db.close(), similar to how file I/O operations are handled. This would mean the next cursor.execute() fails, since it's outside the statement and hence, invalid.

However, it still runs! I'm able to see the contents of the table. What is happening here?

9769953 · Accepted Answer · 2024-10-03 06:28:01Z

1

sqlite3.connect does not work as a self-closing context manager (like for files).

You can use the resulting connection object as a context manager; see this part of the Python sqlite3 documentation, but for transactions instead.

That documentation also has this note:

The context manager neither implicitly opens a new transaction nor closes the connection. If you need a closing context manager, consider using contextlib.closing().

So try with

import sqlite3
import contextlib

with contextlib.closing(sqlite3.connect("test.db")) as db:
    cursor = db.cursor()
    cursor.execute("""CREATE TABLE IF NOT EXISTS testtable(id integer PRIMARY KEY, name text);""")
    cursor.execute("""INSERT INTO testtable(id, name) VALUES (1, "XYZ")""")
    db.commit()


cursor.execute("select * from testtable")
for x in cursor.fetchall():
    print(x)

which raises this exception:

Traceback (most recent call last):
  File "/Users/evert/scratch/sqlitewith.py", line 12, in <module>
    cursor.execute("select * from testtable")
sqlite3.ProgrammingError: Cannot operate on a closed database.

edited Oct 3, 2024 at 6:28

answered Oct 2, 2024 at 15:57

9769953

12.5k3 gold badges31 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

chepner Over a year ago

Just to note, connect does return a context manager. That context is a transaction, though, not a connection. The same connection can be used with multiple with statements, each starting a transaction that is committed or rolled back at the end of the with statement.

steliosbl · Accepted Answer · 2024-10-02 15:58:09Z

0

Context management for files (with file(...) as x) automatically closes the file when the block is exited. However, in general, developers are free to use contexts to manage whatever operation they want.

In the case of sqlite3, using the database connection object in a with block creates database transactions. This is useful because the transactions are automatically committed when the with block is exited normally, or automatically rolled-back if there is an exception. The connection itself is not automatically closed at the end of the block. See the sqlite3 docs and this relevant past StackOverflow thread.

If you want the connection to be closed automatically, see this answer by glglgl.

answered Oct 2, 2024 at 15:58

steliosbl

8,9275 gold badges33 silver badges57 bronze badges

Collectives™ on Stack Overflow

Python not automatically closing a connection to sqlite3 after exiting WITH statement?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related