How to ensure sqlite db connections get closed during debugging?

Question

I am working on some code that connects to an sqlite database. In the process of debugging said code, I am running into problems where I have hanging open connections to the database because some error prevented the execution of the close commands. Because db and c are defined within a function, I can't then find and close those objects from the command line. They are like orphaned connections or something, but in any case they prevent me from doing anything else with the database until I close and reopen my interactive console. Here's what it looks like:

def something()
    db=sqlite3.connect('mydatabase')
    c=db.cursor()

    somecode
    lots of different things happening in here
    this may have errors
    thus stopping execution

    db.commit()
    c.close()
    db.close()

I have tried a try/except clause with the final closing operations in a "finally" block, but that prevents exceptions from being raised back to the interactive output when I'm debugging, and things fail "silently" (maybe I'm not doing that part right?). Is there a better way to do this?

Piotr Hajduga · Accepted Answer · 2013-03-22 11:23:13Z

5

Generally it's good to use with...as statement:

with sqlite.connect(...) as db:
    with db.cursor() as c:
        ...

The with statement guarantees that close() will be called on an object either when with statement ends or exception is raised. And even when return or yield are called from the inside.

Read more here: http://docs.python.org/2/reference/compound_stmts.html#with

edited Mar 22, 2013 at 11:23

answered Mar 21, 2013 at 15:25

Piotr Hajduga

6144 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

andy Over a year ago

thanks. this is simpler than the try/finally approach and seems less prone to making "silent" errors. nice and clean. cheers

juniper- Over a year ago

Does db.cursor() actually return a context manager? I get AttributeError: __enter__ when I try the above.

Maximilian Burszley Oct 6 at 18:53

This answer is wrong. with on a sqlite3.Connection only commits the statement, it does not close the connection. You need to explicitly call .close() or use contextlib.closing around .connect()

Community · Accepted Answer · 2017-05-23 12:23:12Z

As pointed out by Piotr, use of the with statement makes for much more efficient code, though it does not explicitly close the connection to the database, if this is what is desired by the user. This was found by a similar question here.

What using the with statement does do is run either the con.commit() method if the code block inside the with statement is executed without error; or the con.rollback() method if an exception is encountered.

Example from http://docs.python.org/2/library/sqlite3.html

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("create table person (id integer primary key, firstname varchar unique)")

with con:
    con.execute("insert into person(firstname) values (?)", ("Joe",))

# If Successful, con.commit() is called automatically afterwards
# else con.rollback() is called after the with block finishes with an exception, 
# the exception is still raised and must be caught

try:
    with con:
        con.execute("insert into person(firstname) values (?)", ("Joe",))
except sqlite3.IntegrityError:
    print "couldn't add Joe twice"

Note the use of the shortcut method con.execute() which is a method of the database connection object. This implicitly creates the cursor object for you and returns the result, and so requires even less code to be written.

CL. · Accepted Answer · 2013-03-21 15:19:44Z

0

To clean up resources, use only finally:

db = sqlite.connect(...)
try:
    ...
    c = db.cursor()
    try:
        ...
    finally:
        c.close()
    ...
finally:
    db.close()

answered Mar 21, 2013 at 15:19

CL.

182k18 gold badges241 silver badges282 bronze badges

1 Comment

andy Over a year ago

i think this would work, but the with...as... approach seems better. and i think i am still having problems with how i do try/except/finally because i keep getting those silent errors. thanks though

Maximilian Burszley · Accepted Answer · 2025-10-06 19:01:46Z

0

In order to correct the other answers, the sqlite3.Connection object does not provide a closing context manager. It functions as a shortcut to commit any changes to the data. If you want to automatically close the connection, you want to

Use a try/finally block with conn.close()
Wrap your conn with contextlib.closing()

This information is now explicitly referenced in the documentation:

https://docs.python.org/3/library/sqlite3.html#sqlite3-connection-context-manager

answered Oct 6 at 19:01

Maximilian Burszley

20k7 gold badges39 silver badges68 bronze badges

Collectives™ on Stack Overflow

How to ensure sqlite db connections get closed during debugging?

4 Answers 4

3 Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related