Grabbing Jay's example module and running with the idea, here's a version of
DBPool that adds some neat-o features...
1. If the database module is thread-safe, it will willingly return database
connections despite their possibly being in use by another thread. If it
is not thread-safe, it will use Jay's Queue-based setup.
2. The connections aren't actually returned by DBPool.getConnection(), a class
class PooledConnection is returned. PooledConnection serves two functions;
to forward all function calls to the actual connection and to trap the
close() call and make it return the conection to the pool (it's up to
the database pool wether to actually do anything with it. The thread-safe
version doesn't do anything, since the connection was never removed from
the pool).
3. It accepts database connection parameters in the DBPool.__init__ method ...
this probably breaks support for using it as a Can. Is it possible to pass
arguments to a Can's init method somehow? If not, that should probably be
added ... (also, looking at the way DBPool.__init__ forwards the arguments
to the database module's connect function might be a good example of how
to let the Can stuff do this).
Comments suck. Anyways, I'm probably too dumb at this point to make any sense.
Enjoy. Oh yeah ... this module will only work with DB-API 2.0 modules. But
are there really any 1.0 modules left anyway?
And of course, I haven't tested the non thread-safe version, because all my
databases have threadsafe modules.
===[SNIP]===
import MySQLdb
class UnsupportedError:
pass
class PooledConnection:
def __init__(self, pool, db):
self.db = db
self.pool = pool
def close(self):
print "closing..."
self.pool.returnConnection(self)
def __getattr__(self, name):
return getattr(self.db, name)
class DBPool:
def __init__(self, dbModule, num, *args, **kwargs):
if dbModule.threadsafety == 0:
raise UnsupportedError, "Database module does not supported any level of threading."
elif dbModule.threadsafety == 1:
from Queue import Queue
self.queue = Queue(num)
self.addConnection = self._unthreadsafe_addConnection
self.getConnection = self._unthreadsafe_getConnection
self.returnConnection = self._unthreadsafe_addConnection
elif dbModule.threadsafety >= 2:
self.nextCon = 0
self.connections = []
self.addConnection = self._threadsafe_addConnection
self.getConnection = self._threadsafe_getConnection
self.returnConnection = self._threadsafe_returnConnection
for i in range(num):
if args and kwargs:
con = apply(dbModule.connect, args, kwargs)
elif args:
con = apply(dbModule.connect, args, {})
elif kwargs:
con = apply(dbModule.connect, (), kwargs)
self.addConnection(con)
# threadsafe/unthreadsafe refers to the database _module_, not THIS class..
# this class is definitely threadsafe
def _threadsafe_addConnection(self, con):
self.connections.append(con)
def _threadsafe_getConnection(self):
con = PooledConnection(self, self.connections[self.nextCon])
self.nextCon = self.nextCon + 1
if self.nextCon >= len(self.connections):
self.nextCon = 0
return con
def _threadsafe_returnConnection(self, con):
return
# we'd MUCH rather use the other versions, but oh well..
def _unthreadsafe_addConnection(self, con):
self.queue.put(con.db)
def _unthreadsafe_getConnection(self):
return PooledConnection(self, self.queue.get())
# really really lame example usage...
if __name__ == '__main__':
pool = DBPool(MySQLdb, 5, host='xxx', user='xxx', passwd='xxx', db='xxx')
# for mxODBC, this would look something like:
# pool = DBPool(mxODBC, "My_DSN:some:params", user='myUser', password='myPass')
for i in range(15):
db = pool.getConnection()
cursor = db.cursor()
cursor.execute("SELECT * FROM users")
print cursor.fetchall()
db.close()
--
Dan Green
|