Using pyodbc in multi processing code

Question

I am using pyodbc to build an application which get access to and manipulate SQL Server 2008 R2 in a multi-process method.

I firstly designed an DataEngine class as follows:

class DataEngine:
    def __init__(self, serverName, databaseName):
        self.serverName = serverName
        self.databaseName = databaseName
        self.cn = pyodbc.connect("driver={SQL Server};server="+ serverName + ";database=" + databaseName + ";trusted_connection=true") 
        self.cursor = self.cn.cursor()

Then a DataMapper class:

class DataMapper:
    def __init__(self, dataEngine):
        self.dataEngine = dataEngine    
        self.cursor = dataEngine.get_cursor()

And I have defined a function which calls a .exe file with arguments:

def func(dataEngine, dataMapper, buildRunId, queueNumber, ServerName, DatabaseName, ExecutableFilePath, DLLFilePath):
    os.system(ExecutableFilePath + " " + str(inputRowId) + " " + str(ServerName) + " " + str(DatabaseName) +" " + DLLFilePath)

Then the function func has been wrapped into four separate processes:

queue_0 = Process(target = func, args = (dataEngine, dataMapper, buildRunId, 0, ServerName, DatabaseName))
queue_1 = Process(target = func, args = (dataEngine, dataMapper, buildRunId, 1, ServerName, DatabaseName))
queue_2 = Process(target = func, args = (dataEngine, dataMapper, buildRunId, 2, ServerName, DatabaseName))
queue_3 = Process(target = func, args = (dataEngine, dataMapper, buildRunId, 3, ServerName, DatabaseName))

queue_0.start()
queue_1.start()
queue_2.start()
queue_3.start()

queue_0.join()
queue_1.join()
queue_2.join()
queue_3.join()

However, when the above code is running, I've got following error message:

Traceback (most recent call last):
  File "<string>", line 1, in <module>
  File "C:\Python27\lib\multiprocessing\forking.py", line 347, in main
    self = load(from_parent)
  File "C:\Python27\lib\pickle.py", line 1378, in load
    return Unpickler(file).load()
  File "C:\Python27\lib\pickle.py", line 858, in load
    dispatch[key](self)
  File "C:\Python27\lib\pickle.py", line 1083, in load_newobj
    obj = cls.__new__(cls, *args)
TypeError: object.__new__(pyodbc.Cursor) is not safe, use pyodbc.Cursor.__new__()
Exception AttributeError: "DataEngine instance has no attribute 'cursor'" in <bound method DataEngine.__del__ of <dataengine.DataEngine instance at 0x000000000451FC48>> ignored

May I know how to deal with this problem?

Thanks.

dano · Accepted Answer · 2014-09-15 15:33:05Z

3

You're going to need make separate DB connections for each multiprocessing.Process instance. The connection and cursor objects you're storing in each DataEngine instance are not picklable, so you can't send them between processes. The easiest thing to do would be to call a function that creates the dataEngine instance in the child process, and then calls MatlabBuildIndex:

def create_conn_and_call_buildindex(run_id, x, server_name, database_name):
    data_engine = DataEngine(server_name, database_name)
    data_mapper = DataMapper(data_engine)
    MatlabBuildIndex(data_engine, data_mapper, run_id, x, server_name, database_name)

...

queue_0 = Process(target=create_conn_and_call_buildindex, args = (buildRunId, 0, ServerName, DatabaseName))
...

answered Sep 15, 2014 at 15:33

dano

95.6k21 gold badges234 silver badges231 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Max Candocia Over a year ago

This requires establishing a new connection each time you invoke this, though. If you are making hundreds of calls for a given tuple of connections, this can be grossly inefficient.

Collectives™ on Stack Overflow

Using pyodbc in multi processing code

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related