Automate Opening and Closing the Database Connection

Question

I am writing a class for database queries with SQLite3 in my application. Most of the methods of the class are very similar to this:

def getPrice(self, symbol, date):
    date = dt.datetime.strptime(date, '%Y-%m-%d')
    conn = sqlite3.connect('stocks.db')
    curs =conn.cursor()
    curs.execute('''SELECT close FROM prices WHERE symbol = ? AND date = ?;''', (symbol, date))
    close = curs.fetchall()
    curs.close()
    return close

The only difference is the database query and the number of arguments. Is there a possibility to abstract the opening and closing of the database connection away?

I know that it would be probably easier to use a ORM like SQLAlchemy. But I want to understand how I solve this kind of problem in general, not only in relation to databases.

Thanks for your suggestions!

EDIT: This post basically answers my question.

score 4 · Accepted Answer · answered Dec 25 '11 at 16:33

4

First. You'll be much, much happier with one -- and only one -- global connection. Configuration changes are much easier if you do this in exactly one place.

Second, use the with statement and the context manager library.

from contextlib import closing
from my_database_module import the_global_connection

def getPrice(
    with closing(the_global_connection.cursor())
        curs.execute('''SELECT close FROM prices WHERE symbol = ? AND date = ?;''', (symbol, date))
        close = curs.fetchall()
    return close

Your database module looks like this:

import sqlite3
the_global_connection = sqlite3.connect( "stocks.db" )

This gives you the ability to change databases, or database server technology in exactly one place.

answered Dec 25 '11 at 16:33

S.Lott

384,516
81
508
779

contextlib sounds promissing. I will look into this. A Global Connection is also an aspect which I will implement. Thanks. – Randomtheories Dec 25 '11 at 16:59
I've got another question about the global DB-connection. When I build my own database-class "database" I do have to instantiate and call this class every time I want a connection: `db = database()` and `conn=db.the_global_connection`. Is there some easier way with less code? Or maybe a good example how to work with different classes? Sorry, if that is trivial to you, but I just started with OOP. – Randomtheories Dec 25 '11 at 20:51
"and call this class every time I want a connection: db = database()". A single, global connection *object* is what I'm telling you to do. A single, global connection object. Only create one instance of the db connection. – S.Lott Dec 26 '11 at 15:03

score 2 · Answer 2 · edited May 23 '17 at 12:29

Note that as of Python2.6, sqlite.connect returns a context manager:

Connection objects can be used as context managers that automatically commit or rollback transactions. In the event of an exception, the transaction is rolled back; otherwise, the transaction is committed:

Therefore, do not decorate the connection with contextlib.closing -- otherwise, you will lose the commit/rollback behavior and instead only get the connection.close() called upon exiting the with-statement.

Per PEP249:

... closing a connection without committing the changes first will cause
an implicit rollback to be performed.

So the commit/rollback behavior is much more useful than simply calling close.

You could use a context manager:

import contextlib

def query(sql,args):
    with contextlib.closing(sqlite3.connect('stocks.db')) as conn:
        curs = conn.cursor()
        curs.execute(sql,args))
        close = curs.fetchall()
        return close

def getPrice(self, symbol, date):
    date = dt.datetime.strptime(date, '%Y-%m-%d')
    sql = '''SELECT close FROM prices WHERE symbol = ? AND date = ?'''
    args = (symbol, date)
    return query(sql, args)

Since you have many functions like getPrice which differ only by the SQL and arguments, you could reduce the repetitious boiler-plate code by defining the query function.

You could also define a context manager to rollback the connection on errors and commit as well as close upon exiting the with block. An example of this (for MySQL) can be found here, adapting it to sqlite3 should not be difficult..

Reference:

The contextlib.closing decorator

While that's an improvement, in particular regarding resource management, it doesn't save much code over what OP presented. — , Dec 25 '11 at 16:36

score 1 · Answer 3 · answered Dec 25 '11 at 16:29

1

Encapsulate that logic into an object, pass that object to the data access object and ask it to call the methods.

Aspects or decorators might be a good way to do things.

You don't mention pooling or transactions. Think about those as well.

answered Dec 25 '11 at 16:29

duffymo

305,152
44
369
561

Could you explain what you mean by aspects? I never heard of this concept. Thanks for the other ideas. – Randomtheories Dec 25 '11 at 16:56
Aspect-oriented programming. In Python they're called decorators: http://wiki.python.org/moin/PythonDecorators – duffymo Dec 25 '11 at 17:01

Automate Opening and Closing the Database Connection

3 Answers3