[mod_python] MySQL DB connection sharing solution

The trouble with most MySQL connection "sharing" methods is that they use the
same connection for multiple threads. This will not work when using scrolling
cursors, you can have only one of those open per connection. If you allow
users to run arbitrary queries, you don't want them to allocate 100 megabytes
of buffer space on the server when doing a "SELECT * FROM customer, orders"
(forgetting to join them...) so they are a must have.

Solution I came up with is to "give" a Connection object to each thread, and
when done, return it to the pool. If a connection is not in the pool, a new
one is made. With a bit of locking, you can have as many threads as you like,
and they won't interfere. The connections are stored in a dictionary, using
the usr, password, host and database as entry 'hash' (which I made a string
for easy debugging).
The entries are a list, when connecting the first object is taken from the
list, and appended again when the handler is done with it. It checks if the
connection is still valid when returning it (so a handler CAN do a db.close()
to dispose of a connection if that is neccesary).

### Connection sharing code ###

dblist = {}
_lock = threading.Lock()

def connectdb(req):
    environ = req.subprocess_env
    dbentry = '%s:%s@%s/%s' % (environ['DBUSER'],
                               environ['DBPWD'],
                               environ['DBHOST'],
                               environ['DBNAME'])
    req.dbentry = dbentry
    _lock.acquire()
    try:
        list = dblist.get(dbentry, [])
        if len(list):
            result = list[0]
            del list[0]
        else:
            import ops_sql
            dblist[dbentry] = list
            result = ops_sql.db_connect(environ)
    finally:
        _lock.release()
    return result

def donedb(req, db):
    if db and (not db._db.closed):
        _lock.acquire()
        try:
            dblist[req.dbentry].append(db)
        finally:
            _lock.release()

### In the handler(req) routine ###

           db = None
            if 'form' in expected:
                form = util.FieldStorage(req)
                kwargs['form'] = form
                req.form = form
            if 'db' in expected:
                db = connectdb(req)
                kwargs['db'] = db
            try:
                apply(module.main, (), kwargs)
            finally:
                donedb(req, db)



--
Mike Looijmans
ASML: http://www5nl.asml.nl/~mlooijma
Private: http://www.milosoftware.com