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 hereWhat 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.
If you found this post useful, please consider buying me a coffee.
This post originally appeared on Robin's Blog.
Categorised as: Programming, Python, Windows
The `contextlib` module also has a handy `closing()` function, which calls `.close()` when exiting the context manager.
This “correctly” does what you think it would have done, except the transaction is now up to you.
“`
import sqlite3
from contextlib import closing
with closing(sqlite3.connect(‘test.db’)) as connection:
with connection: # as transaction
result = connection.execute(“SELECT name FROM sqlite_master;”)
“
contextlib.closing might be useful in this scenario:
https://docs.python.org/3/library/contextlib.html#contextlib.closing
I think this will work:
with closing(sqlite3.connect(‘test.db’)) as c:
pass
This Stack Overflow solution seems useful in this instance:
https://stackoverflow.com/a/67436763/5548526
Here’s the code (not mine):
import sqlite3
from contextlib import contextmanager
@contextmanager
def db_ops(db_name):
conn = sqlite3.connect(db_name)
cur = conn.cursor()
yield cur
conn.commit()
conn.close()