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

Question

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 1

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.

Answer 2

You have a valid underlying concern here, however it's also important to understand how sqlite operates too:

1. connection open
    2. transaction started
        3. statement executes
    4. transaction done
5. connection closed

in terms of data correctness, you only need to worry about transactions and not open handles. sqlite only holds a lock on a database inside a transaction(*) or statement execution.

however in terms of resource management, e.g. if you plan to remove sqlite file or use so many connections you might run out of file descriptors, you do care about open out-of-transaction connections too.

there are two ways a connection is closed: either you call .close() explicitly after which you still have a handle but can't use it, or you let the connection go out of scope and get garbage-collected.

if you must close a connection, close it explicitly, according to Python's motto "explicit is better than implicit."

if you are only checking code for side-effects, letting a last variable holding reference to connection go out of scope may be acceptable, but keep in mind that exceptions capture the stack, and thus references in that stack. if you pass exceptions around, connection lifetime may be extended arbitrarily.

caveat programmator, sqlite uses "deferred" transactions by default, that is the transaction only starts when you execute a statement. In the example above, transaction runs from 3 to 4, rather than from 2 to 4.

Answer 3

You can use a with block like this:

from contextlib import closing
import sqlite3

def query(self, db_name, sql):
    with closing(sqlite3.connect(db_name)) as con, con,  \
            closing(con.cursor()) as cur:
        cur.execute(sql)
        return cur.fetchall()

• connects
• starts a transaction
• creates a db cursor
• performs the operation and returns the results
• closes the cursor
• commits/rolls-back the transaction
• closes the connection

all safe in both happy and exceptional cases

Answer 4

This is the code that I use. The Connection and the Cursor will automatically close thanks to contextlib.closing(). The Connection will automatically commit thanks to the context manager.

import sqlite3
import contextlib

def execute_statement(statement):
    with contextlib.closing(sqlite3.connect(path_to_file)) as conn: # auto-closes
        with conn: # auto-commits
            with contextlib.closing(conn.cursor()) as cursor: # auto-closes
                cursor.execute(statement)

Answer 5

Your version leaves conn in scope after connection usage.

EXAMPLE:

your version

    conn = sqlite3.connect(db_filename) #DECLARE CONNECTION OUT OF WITH BLOCK

    with conn:                          #USE CONNECTION IN WITH BLOCK
        cur = conn.cursor()
        cur.execute( ... )

   #conn variable is still in scope, so you can use it again

new version

    with sqlite3.connect(db_filename) as conn:  #DECLARE CONNECTION AT START OF WITH BLOCK
        cur = conn.cursor()
        cur.execute( ... )   

   #conn variable is out of scope, so connection is closed 
   # MIGHT BE IT IS NOT CLOSED BUT WHAT  Avaris SAID!
   #(I believe auto close goes for with block)

Answer 6

For managing a connection to a database I usually do this,

# query method belonging to a DB manager class

def query (self, sql):
    con = sqlite3.connect(self.dbName)
    with con:
        cur = con.cursor()
        cur.execute(sql)
        res = cur.fetchall()
    if con:
        con.close()

    return res

doing so, I'm sure that the connection is explicitly closed.