Robert Rawlins - Tamed Technology wrote:
Essentially I only have a single database attached to my application but
I’m looking for the best way to handle its connection so thought I would
come and get your thoughts and opinions.
Within my applications I’ll have many classes which access the database,
I’m wondering to what level I should extract the database connection. [...]
Be sure to wrap the actual "getting the connection object".
Should I create a new database connection and close it for every method
which calls the database? Should I create the connection to the DB when
I construct the class and place the cursor in the self scope? Or should
I create an application wide connection to the database and inject the
connection into all the classes which require it? [...]
That's what I'd do. But I'd not inject it and rather just get it (like
with get_con() in my example code).
I've attached example code that shows how DB-API database access can be
simplified.
-- Gerhard
from __future__ import with_statement
import sqlite as sqlite3
# Draft for a helper module for no-bullshit DB-API usage.
_con = None
# This is a very simple implementation of get_con(). You could implement
# anything here, including storing one connection object per thread using
# thread-local storage, or returning a wrapped object that uses a database
# connection pool. get_con() could also read the configuration from a database
# file.
def get_con():
global _con
if _con is None:
_con = sqlite3.connect(":memory:")
cur = _con.cursor()
cur.execute("create table test(id integer primary key, data)")
_con = WrappedConnection(_con)
return _con
# A wrapper for DB-API connection objects to make them less cumbersome to use.
# pysqlite and AFAIK cx_Oracle behave exactly like this already AFAIK.
class WrappedConnection:
def __init__(self, real_con):
self.real_con = real_con
# __enter__ and __exit__ for being able to automatically do the right thing
# when used with a Python 2.5 "with" statement.
def __enter__(self):
return self
def __exit__(self, excType, excValue, excTraceback):
if excType is None and excValue is None and excTraceback is None:
self.real_con.commit()
else:
self.real_con.rollback()
# execute() and executemany() in the connection object directly; these two
# just return the cursor object. You'll never directly need to create any
# stupid cursor() objects yourself like this.
def execute(self, *args, **kwargs):
cur = self.real_con.cursor()
cur.execute(*args, **kwargs)
return cur
def executemany(self, *args, **kwargs):
cur = self.real_con.cursor()
cur.execute(*args, **kwargs)
return cur
# make sure we can access the wrapped attributes of the original connection
# object
def __getattr__(self, attr):
return getattr(self.real_con, attr)
def test():
with get_con() as con:
con.execute("insert into test(data) values (3)")
con.execute("insert into test(data) values (4)")
for row in con.execute("select * from test").fetchall():
print row
if __name__ == "__main__":
test()
_______________________________________________
DB-SIG maillist - [email protected]
http://mail.python.org/mailman/listinfo/db-sig
