Using decorators for database access with psycopg2

Question

I am constructing a model that does large parts of its calculations in a Postgresql database (for performance reasons). It looks somewhat like this:

def sql_func1(conn):
    # prepare some data, crunch some number, etc.
    curs = conn.cursor()
    curs.execute("SOME SQL COMMAND")
    curs.commit()
    curs.close()

if __name__ == "__main__":
    connection = psycopg2.connect(dbname='name', user='user', password='pass', host='localhost', port=1234)
    sql_func1(conn)
    sql_func2(conn)
    sql_func3(conn)
    connection.close()

The script uses around 30 individual functions like sql_func1. Obviously it is a little awkward to manage the connection and cursor in each function all the time. Thus I started using a decorator as described here. Now I can simply wrap sql_func1 with a decorator @db_connect and pass the connection from there. However, that means I am opening and closing the connection all the time, which is not good practice either. The psycopg2 FAQ says:

Creating a connection can be slow (think of SSL over TCP) so the best practice is to create a single connection and keep it open as long as required. It is also good practice to rollback or commit frequently (even after a single SELECT statement) to make sure the backend is never left “idle in transaction”. See also psycopg2.pool for lightweight connection pooling.

Could you please give me some insights which would be an ideal practice im my case. Should I rather use a decorator that passes the cursor object instead of the connection? If so, please provide a code sample for the decorator. As I am rather new to programming, please let me also know in case you think my overall approach is wrong.

Answer 1

What about storing the connection in a global variable without closing it in the finally block? Something like this (according to the example you linked):

cnn = None
def with_connection(f):

    def with_connection_(*args, **kwargs):
        global cnn
        if not cnn:
            cnn = psycopg.connect(DSN)
        try:
            rv = f(cnn, *args, **kwargs)
        except Exception, e:
            cnn.rollback()
            raise
        else:
            cnn.commit() # or maybe not        
        return rv

    return with_connection_