What if I don't close the database connection in Python SQLite

treecoder picture treecoder · Mar 5, 2012 · Viewed 45.2k times · Source

I am doing something like this...

conn = sqlite3.connect(db_filename)

with conn:
    cur = conn.cursor()
    cur.execute( ... )

with automatically commits the changes. But the docs say nothing about closing the connection.

Actually I can use conn in later statements (which I have tested). Hence it seems that the context manager is not closing the connection.

Do I have to manually close the connection. What if I leave it open?

EDIT

My findings:

  • The connection is not closed in the context manager, I have tested and confirmed it. Upon __exit__, the context manager only commits the changes by doing conn.commit()
  • with conn and with sqlite3.connect(db_filename) as conn are same, so using either will still keep the connection alive
  • with statement does not create a new scope, hence all the variables created inside the suite of with will be accessible outside it
  • Finally, you should close the connection manually

Answer

Roger Binns picture Roger Binns · Mar 8, 2012

In answer to the specific question of what happens if you do not close a SQLite database, the answer is quite simple and applies to using SQLite in any programming language. When the connection is closed explicitly by code or implicitly by program exit then any outstanding transaction is rolled back. (The rollback is actually done by the next program to open the database.) If there is no outstanding transaction open then nothing happens.

This means you do not need to worry too much about always closing the database before process exit, and that you should pay attention to transactions making sure to start them and commit at appropriate points.