CODEWITHSUNDEEP
pythonclassdatabase-connection
Randomtheories
Randomtheories

Reputation: 1290

Automate Opening and Closing the Database Connection

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.

Upvotes: 0

Views: 3455

Answers (3)

unutbu
unutbu

Reputation: 880399

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:

Upvotes: 2

S.Lott
S.Lott

Reputation: 391952

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.

Upvotes: 4

duffymo
duffymo

Reputation: 308958

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.

Upvotes: 1

Related Questions