Cleaning up an internal pysqlite connection on object destruction

Question

I have an object with an internal database connection that's active throughout its lifetime. At the end of the program's run, the connection has to be committed and closed. So far I've used an explicit close method, but this is somewhat cumbersome, especially when exceptions can happen in the calling code.

I'm considering using the __del__ method for closing, but after some reading online I have concerns. Is this a valid usage pattern? Can I be sure that the internal resources will be freed in __del__ correctly?

This discussion raised a similar question but found no satisfactory answer. I don't want to have an explicit close method, and using with isn't an option, because my object isn't used as simply as open-play-close, but is kept as a member of another, larger object, that uses it while running in a GUI.

C++ has perfectly working destructors where one can free resources safely, so I would imagine Python has something agreed-upon too. For some reason it seems not to be the case, and many in the community vow against __del__. What's the alternative, then?

Esplanade

Answer 1

You can make a connection module, since modules keep the same object in the whole application, and register a function to close it with the atexit module

# db.py:
import sqlite3
import atexit

con = None

def get_connection():
    global con
    if not con:
        con = sqlite3.connect('somedb.sqlite')
    atexit.register(close_connection, con)
    return con

def close_connection(some_con):
    some_con.commit()
    some_con.close()

# your_program.py
import db
con = db.get_connection()
cur = con.cursor()
cur.execute("SELECT ...")

This sugestion is based on the assumption that the connection in your application seems like a single instance (singleton) which a module global provides well.

If that's not the case, then you can use a destructor.

However destructors don't go well with garbage collectors and circular references (you must remove the circular reference yourself before the destructor is called) and if that's not the case (you need multiple connections) then you can go for a destructor. Just don't keep circular references around or you'll have to break them yourself.

Also, what you said about C++ is wrong. If you use destructors in C++ they are called either when the block that defines the object finishes (like python's with) or when you use the delete keyword (that deallocates an object created with new). Outside that you must use an explicit close() that is not the destructor. So it is just like python - python is even "better" because it has a garbage collector.

Funny

Isn't this kind of untidy in a 'leaky abstraction' way for the oject that uses such a connection? Why can't I just use a destructor like in C++?? – Esplanade

@eliben: it can. However destructors don't go well with garbage collectors and circular references (you must remove the circular reference yourself before the destructor is called) and the connection in your application seems like a single instance (singleton) which a module global provides well. If that's not the case (you need multiple connections) then you can go for a destructor. Just don't keep circular references around or you'll have to break them yourself. – Funny

re C++'s destructors, not exactly. Coding with correct RAII, the pointer to the resource would be kept as a smart pointer (perhaps reference counted) that gets deallocated itself when the count reaches 0 in a guaranteed manner. However, this requires extra machinery (the smart pointer) as well – Esplanade

@eliben: Even using RAII, the resource is only deallocated and its destructor called when it falls out of scope (like python's with). If you plug in some reference counting package, it behaves exactly like python's destructor (i.e. you have problems with circular references). – Funny

@nosklo: With RAII, I create an instance variable which is a smart pointer. It is destroyed automatically when the containing objects goes out of scope (when all its instance variables are destroyed), so this isn't like 'with' in Python, IMHO. – Esplanade

I think you need to check for an exception, or the if not con won't work. – Ashelyashen

Answer 2

Read up on the with statement. You're describing its use case.

You'll need to wrap your connection in a "Context Manager" class that handles the __enter__ and __exit__ methods used by the with statement.

See PEP 343 for more information.

---

Edit

"my object isn't used as simply as open-play-close, but is kept as a member of another, larger object"

class AnObjectWhichMustBeClosed( object ):
    def __enter__( self ):
        # acquire
    def __exit__( self, type, value, traceback ):
        # release
    def open( self, dbConnectionInfo ):
        # open the connection, updating the state for __exit__ to handle.

class ALargerObject( object ):
    def __init__( self ):
        pass
    def injectTheObjectThatMustBeClosed( self, anObject ):
        self.useThis = anObject

class MyGuiApp( self ):
    def run( self ):
        # build GUI objects
        large = ALargeObject()
        with AnObjectWhichMustBeClosed() as x:
            large.injectTheObjectThatMustBeClosed( x )
            mainLoop()

Some folks call this "Dependency Injection" and "Inversion of Control". Other folks call this the Strategy pattern. The "ObjectThatMustBeClosed" is a strategy, plugged into some larger object. The assembly is created at a top-level of the GUI app, since that's usually where resources like databases are acquired.

Myrica

I've mentioned that I can't used 'with' here, and explained why, in the question itself. Am I missing something? Can you explain how 'with' can be effective employed when the object is held as an instance in a large GUI-based class that does work based on events? – Esplanade

@S.Lott: Could it be a SO bug? My question has 4 paragraphs - the third mentions 'with' and GUI – Esplanade

While original, I feel that your solution is somewhat an overkill. Imagine that I have 10 such objects that need to be closed... All I needed was a destructor!! – Esplanade

@eliben: Perhaps your wrapper for these 10 objects needs to be some kind of container? – Myrica