0

I am unexpectedly getting IOError: bad message length error when trying to share pyodbc connection across multiple processes, especially when N is more than 4 (no. of cores). Sometimes I also get cPickle.UnpicklingError: invalid load key, '#'., pyodbc.ProgrammingError: ('24000', '[24000] [FreeTDS][SQL Server]Invalid cursor state (0) (SQLExecDirectW)') as errors.

# Import custom python packages
import multiprocessing
import multiprocessing.managers as mm
import pathos.multiprocessing as mp
import pyodbc, datetime, time

class MyConn(object):
    def __init__(self):
        self.conn = None
        self.cursor = None

    def connect_to_db(self):
        self.conn = pyodbc.connect("DSN=cpmeast;UID=dntcore;PWD=dntcorevs2")
        self.cursor = self.conn.cursor()

    def run_qry(self, data):
        print 'Running query', data
        self.cursor.execute("WAITFOR DELAY '00:00:01';select GETDATE(), '"+str(data)+"';")
        l = self.cursor.fetchall()
        _l = []
        for i in l:
            _l.append(list(i))
        print 'Result for query', data, _l
        return _l


class MyManagerClass(object):
    def __init__(self):
        self.result = multiprocessing.Manager().list()

    def read_data(self, *args):
        conn = args[0][0]
        data = args[0][1]
        l = conn.run_qry(data)
        self.result.append(l)

class MyManager(mm.BaseManager):
    pass # Pass is really enough. Nothing needs to be done here.

def main():
    time_start = time.time()
    MyManager.register("MyConn", MyConn)
    manager = MyManager()
    manager.start()
    a = manager.MyConn()
    a.connect_to_db()

    dbm = MyManagerClass()

    pool = mp.ProcessingPool(4)
    jobs = []
    N = 5
    for i in range(N):
        jobs.append((a, str(i)))
    for i in pool.imap(dbm.read_data, jobs):
        print 'result'
    pool.close()
    pool.join()

    print 'Result', dbm.result
    print 'Closed'
    time_stop = time.time()
    msg = 'runtime: {0}'.format(str(datetime.timedelta
                                             (seconds=time_stop-time_start)))
    print msg

if __name__ == '__main__':
    main()
amulllb
  • 3,036
  • 7
  • 50
  • 87
  • Possible duplicate of [Python multiprocessing and database access with pyodbc "is not safe"?](http://stackoverflow.com/questions/1537809/python-multiprocessing-and-database-access-with-pyodbc-is-not-safe) – Gord Thompson Feb 12 '16 at 14:08
  • What is your Driver Manager? – mauro Feb 16 '16 at 13:52

0 Answers0