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?

Answer 1

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.