python cx_Oracle.Connection 代理在进程之间共享 - 在共享连接对象上创建游标时出现错误

Question

我正在尝试使用 python 在 Oracle 上编写一个加载应用程序，并且我需要一些并发性。我通过共享子进程使用的连接池来做到这一点，但在进入之前，我尝试将一个简单的 Connection 对象从管理器进程共享给子进程。

使用代理对象正确共享连接对象，但是当我尝试在此连接上创建游标时，我得到如下信息:>

并且光标无法使用。

这是我的代码:

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()

我得到以下输出:

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

有人可以告诉我如何克服这个问题吗？

谢谢， 爱奥努特

Answer 1

问题在于游标无法跨越进程之间的边界传递。所以你需要包装execute方法。像这样的东西。当然，您需要扩展它来处理绑定(bind)变量等。

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()