Re: multithreaded db access

[Byron Platt]

Not sure this is the best way to do things performance wise but it works for me.

import MySQLdb
from threading import Lock

# -- constants -----------------------------------------------------------------
db_name = 'mydatabase'                                   # database name

# -- queries -------------------------------------------------------------------
queries = dict({
'getproc1': 'CALL getproc1();',
'addproc2': 'CALL addproc2(%d, "%s", %d, "%s");'
'addproc3': 'CALL addproc3("%s", "%s", "%s", "%s", "%s", "%s");',
.
.
etc
.
.
})

# -- thread safe database functions --------------------------------------------
lock = Lock()

class db():
    def __init__(self, db_host, db_usr, db_pwd):
        self.db_host = db_host
        self.db_usr = db_usr
        self.db_pwd = db_pwd

    def open(self):
        self.connection = MySQLdb.connect(host = self.db_host,
                                          user = self.db_usr,
                                          passwd = self.db_pwd,
                                          db = db_name)
        
    def close(self):
        self.connection.close()

    def commit(self):
        lock.acquire()
        try:
            self.connection.commit()
        finally:
            lock.release()

    def getproc1(self):
        row = None
        lock.acquire()
        try:
            cursor = self.connection.cursor()
            cursor.execute(queries['getproc1'])
            row = cursor.fetchone()
            cursor.close()
        finally:
            lock.release()
        return row

    def addproc2(self, arg1, arg2, arg3, arg4):
        lock.acquire()
        try:
            cursor = self.connection.cursor()
            cursor.execute(queries['addproc2'] % (arg1, arg2, arg3, arg4))
            cursor.close()
        finally:
            lock.release()
        
    def addproc3(self, argstuple):
        lock.acquire()
        try:
            cursor = self.connection.cursor()
            cursor.execute(queries['addproc3'] % argstuple)
            cursor.close()
        finally:
            lock.release()

.
.
etc
.
.

Edited 2 time(s). Last edit at 07/27/2009 07:54PM by Byron Platt.