python cx_Oracle.Connection proxying to share between process - I get an error when creating a cursor on a shared connection object

Question

I am trying to write a load app on Oracle using python and I neeed some concurrency. I am doing this by sharing a connection pool to be used by child processes but before going into that I tried to share a simple Connection object from a manager process to a child.

The connection object is shared properly using the proxy object, but when I try to create a cursor on this connection I get smth like : >

And the cursor is not usable.

Here is my code :

import cx_Oracle
from multiprocessing import managers
from multiprocessing import current_process
from multiprocessing import Process
import time


#function to setup the connection object in manager process
def setupConnection(user,password,dsn):
    conn = cx_Oracle.connect(user=user,password=password,dsn=dsn)
    return conn

#proxy object for my connection
class connectionProxy(managers.BaseProxy):
    def close(self):
        return self._callmethod('close',args=())
    def ping(self):
        return self._callmethod('ping',args=())
    def cursor(self):
        return self._callmethod('cursor',args=())

#connection manager
class connectionManager(managers.BaseManager): pass

#child process work function
def child(conn_proxy):
    print(str(current_process().name) + "Working on connection : " + str(conn_proxy))
    cur = conn_proxy.cursor()
    print(cur)
    cur.execute('select 1 from dual');

if __name__ == '__main__' :
    #db details
    user = 'N974783'
    password = '12345'
    dsn = '192.168.56.6:1521/orcl'

    #setup manager process and open the connection
    manager = connectionManager()
    manager.register('set_conn',setupConnection,proxytype=connectionProxy,exposed = ('close','ping','cursor'))
    manager.start()

    #pass the connection to the child process
    conn_proxy = manager.set_conn(user=user,password=password,dsn=dsn)
    p = Process(target=child, args=(conn_proxy,),name='oraWorker')
    p.start()
    p.join()

I get the following output:

oraWorker Working on connection : <cx_Oracle.Connection to N974783@192.168.56.6:1521/orcl>  
<cx_Oracle.Cursor on <NULL>> ..
cur.execute('select 1 from dual');  
cx_Oracle.InterfaceError: not open

Can someone give me an idea on how should I get past this ?

Thanks, Ionut

Answer 1

The problem is that cursors cannot be passed across the boundary between processes. So you need to wrap the execute method instead. Something like this. You would need to expand it to handle bind variables and the like, of course.

import cx_Oracle
from multiprocessing import managers
from multiprocessing import current_process
from multiprocessing import Process
import time


class Connection(cx_Oracle.Connection):

    def execute(self, sql):
        cursor = self.cursor()
        cursor.execute(sql)
        return list(cursor)


#function to setup the connection object in manager process
def setupConnection(user,password,dsn):
    conn = Connection(user=user,password=password,dsn=dsn)
    return conn

#proxy object for my connection
class connectionProxy(managers.BaseProxy):
    def close(self):
        return self._callmethod('close',args=())
    def ping(self):
        return self._callmethod('ping',args=())
    def execute(self, sql):
        return self._callmethod('execute', args=(sql,))

#connection manager
class connectionManager(managers.BaseManager):
    pass

#child process work function
def child(conn_proxy):
    print(str(current_process().name) + "Working on connection : " + str(conn_proxy), id(conn_proxy))
    result = conn_proxy.execute('select 1 from dual')
    print("Result:", result)

if __name__ == '__main__' :
    #db details
    user = 'user'
    password = 'pwd'
    dsn = 'tnsentry'

    #setup manager process and open the connection
    manager = connectionManager()
    manager.register('set_conn',setupConnection,proxytype=connectionProxy,exposed = ('close','ping','execute'))
    manager.start()

    #pass the connection to the child process
    conn_proxy = manager.set_conn(user=user,password=password,dsn=dsn)
    p = Process(target=child, args=(conn_proxy,),name='oraWorker')
    p.start()
    p.join()