A Python sqlite3 context manager gotcha
I’ve neglected this blog for a while – partly due to the chaos of 2020 (which is not great), and partly due to being busy with work (which is good!). Anyway, I’m starting to pick it up again, and I thought I’d start with something that caught me out the other day.
So, let’s start with some fairly simple code using the sqlite3 module from the Python standard library:
import sqlite3 with sqlite3.connect('test.db') as connection: result = connection.execute("SELECT name FROM sqlite_master;") # Do some more SQL queries here # Do something else here
What would you expect the state of the
connection variable to be at the end of that code?
If you thought it would be closed (or possibly even undefined), then you’ve made the same mistake that I made!
I assumed that using
sqlite3.connect as a context manage (in a
with block) would open a connection when you entered the block, and close a connection when you exited the block.
It turns out that’s not the case! According to the documentation:
Connection objects can be used as context managers that automatically commit or rollback transactions. In the event of an exception, the transaction is rolled back; otherwise, the transaction is committed.
That is, it’s not the
connect function that is providing the context manager, it’s the connection object that the functions returns which provides the context manager. And, using a connection object as a context manager handles transactions in the database rather than opening or closing the database connection itself: not what I had imagined.
So, if you want to use the context manager to get the transaction handling, then you need to add an explicit
connection.close() outside of the block. If you don’t need the transaction handling then you can do it the ‘old-fashioned’ way, like this:
import sqlite3 connection = sqlite3.connect('test.db') result = connection.execute("SELECT name FROM sqlite_master;") # Do some more SQL queries here connection.close()
Personally, I think that this is poor design. To replicate the usage of context managers elsewhere in Python (most famously with the
open function), I think a context manager on the
connect call should be used to open/close the database, and there should be a separate call to deal with transactions (like
with connection.transaction():). Anyway, it’s pretty-much impossible to change it now – as it would break too many things – so we’d better get used to the way it is.
For context (and to help anyone Googling for the exact problem I had), I was struggling with getting some tests working on Windows. I’d opened a SQLite database using a context manager, executed some SQL, and then was trying to delete the SQLite file. Windows complained that the file was still open and therefore it couldn’t be deleted – and this was because the context manager wasn’t actually closing the connection.