错误： ("IM005"，"[IM005] [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_DBC failed (0

Question

我正在我的pyodbc脚本中运行此代码，我正在尝试进行并行性

templst = [lineitem, orders, partsupp, region, cur_cur, T1, T2]
connstr = [DRIVER={libdbodbc17.so};host=lint16muthab.phl.sap.corp:8766;UID=dba;PWD=sql;CharSet=utf8, DRIVER={libdbodbc17.so};host=localhost:8767;UID=dba;PWD=sql;CharSet=utf8,
DRIVER={libdbodbc17.so};host=localhost:8768;UID=dba;PWD=sql;CharSet=utf8,
DRIVER={libdbodbc17.so};host=localhost:8769;UID=dba;PWD=sql;CharSet=utf8, DRIVER={libdbodbc17.so};host=localhost:8770;UID=dba;PWD=sql;CharSet=utf8]
def extract_single(q, cursorconn):
while True:
try:
tableName = q.get_nowait()
time.sleep(3)
qry2 = "Select * FROM %s"% (tableName)
print " extraction done of table:%s done by cursor:%s"%(tableName,cursorconn)
except Queue.Empty:
return
def main():
q = multiprocessing.Queue()
for item in templst:
q.put(item) # add items to queue
process = []
for i in xrange(5):
p = multiprocessing.Process(target=extract_single, args=(q, connstr[i]))
process.append(p)
p.start()
for p in process:
p.join()
if __name__ == '__main__':
main()

输出如下：

extraction done of table:lineitem done by cursor:DRIVER={libdbodbc17.so};host=lint16muthab.phl.sap.corp:8766;UID=dba;PWD=sql;CharSet=utf8

提取完成表：由光标完成的订单：DRIVER={libdbodbc17.so};主机=本地主机：8767;UID=dba;PWD=sql;字符集=utf8

extraction done of table:partsupp done by cursor:DRIVER={libdbodbc17.so};host=localhost:8768;UID=dba;PWD=sql;CharSet=utf8
extraction done of table:region done by cursor:DRIVER={libdbodbc17.so};host=localhost:8769;UID=dba;PWD=sql;CharSet=utf8
extraction done of table:cur_cur done by cursor:DRIVER={libdbodbc17.so};host=localhost:8770;UID=dba;PWD=sql;CharSet=utf8
extraction done of table:T2 done by cursor:DRIVER={libdbodbc17.so};host=localhost:8767;UID=dba;PWD=sql;CharSet=utf8
extraction done of table:T1 done by cursor:DRIVER={libdbodbc17.so};host=lint16muthab.phl.sap.corp:8766;UID=dba;PWD=sql;CharSet=utf8

但是当我将我的提取函数修改为

def extract_single(q, cursorconn):
while True:
try:
tableName = q.get_nowait()
time.sleep(3)
conn = pyodbc.connect(cursorconn, timeout=0)
cursor = connvar.cursor()
qry2 = "Select * FROM %s"% (tableName)
cursor.execute(qry2).fetchall()
print " extraction done of table:%s done by cursor:%s"%(tableName,cursorconn)
except Queue.Empty:
return

我收到错误，无法打开连接和光标，以便我可以执行此查询并将数据转储到文件中。

2Process Process-1:
Traceback (most recent call last):
File "/sybopt/software/python/python/lib/python2.7/multiprocessing/process.py", line 258, in _bootstrap
self.run()
File "/sybopt/software/python/python/lib/python2.7/multiprocessing/process.py", line 114, in run
self._target(*self._args, **self._kwargs)
File "conn3.py", line 86, in extract_single
Process Process-2:
Traceback (most recent call last):
connvar = pyodbc.connect(cursorconn, timeout=0)
File "/sybopt/software/python/python/lib/python2.7/multiprocessing/process.py", line 258, in _bootstrap
Error: ('IM005', "[IM005] [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_DBC failed (0) (SQLDriverConnect)")
self.run()
File "/sybopt/software/python/python/lib/python2.7/multiprocessing/process.py", line 114, in run
self._target(*self._args, **self._kwargs)
File "conn3.py", line 86, in extract_single
connvar = pyodbc.connect(cursorconn, timeout=0)
Error: ('IM005', "[IM005] [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_DBC failed (0) (SQLDriverConnect)")
Process Process-3:
Traceback (most recent call last):
File "/sybopt/software/python/python/lib/python2.7/multiprocessing/process.py", line 258, in _bootstrap
self.run()
File "/sybopt/software/python/python/lib/python2.7/multiprocessing/process.py", line 114, in run
self._target(*self._args, **self._kwargs)
File "conn3.py", line 86, in extract_single
connvar = pyodbc.connect(cursorconn, timeout=0)

对于所有连接。请让我知道如何解决此错误。使用游标对象时出错

我想使用我以列表形式存储的游标对象，而不是再次打开新连接并且每次打开游标。 游标列表将如下所示

<pyodbc.Cursor object at 0x7fcd9b47f270> <pyodbc.Cursor object at 0x7fcd9b47f330> <pyodbc.Cursor object at 0x7fcd9b47f390> <pyodbc.Cursor object at 0x7fcd9b47f3f0> <pyodbc.Cursor object at 0x7fcd9b47f450>

所以我有 5 个光标对象 和表格列表同上

def extract_single(q, cursorconn):
while True:
try:
tableName = q.get_nowait()
qry2 = "Select count(*) FROM %s"%(tableName)
cursorconn.execute(qry2)
rowcnt = cursorconn.fetchone()[0]
print " rows in tempdsc=",rowcnt
print " extraction done of table:%s done by cursor:%s"%(tableName,cursorconn)
except Queue.Empty:
return

def main():
q = multiprocessing.Queue()
for item in tempdsclst:
q.put(item) # add items to queue
process = []
for i in xrange(5):
p = multiprocessing.Process(target=extract_single, args=(q, curlst[i]))
process.append(p)
p.start()
for p in process:
p.join()

它给出的错误如下

Process Process-1:
Traceback (most recent call last):
File "/sybopt/software/python/python/lib/python2.7/multiprocessing/process.py", line 258, in _bootstrap
Process Process-2:
Traceback (most recent call last):
self.run()
File "/sybopt/software/python/python/lib/python2.7/multiprocessing/process.py", line 258, in _bootstrap
File "/sybopt/software/python/python/lib/python2.7/multiprocessing/process.py", line 114, in run
self._target(*self._args, **self._kwargs)
File "conn4.py", line 87, in extract_single
cursorconn.execute(qry2)
Error: ('HY000', 'The driver did not supply an error!')
self.run()
File "/sybopt/software/python/python/lib/python2.7/multiprocessing/process.py", line 114, in run
self._target(*self._args, **self._kwargs)
File "conn4.py", line 87, in extract_single
cursorconn.execute(qry2)
Error: ('HY000', 'The driver did not supply an error!')
Process Process-3:
Traceback (most recent call last):
File "/sybopt/software/python/python/lib/python2.7/multiprocessing/process.py", line 258, in _bootstrap
self.run()
File "/sybopt/software/python/python/lib/python2.7/multiprocessing/process.py", line 114, in run
self._target(*self._args, **self._kwargs)
File "conn4.py", line 87, in extract_single
cursorconn.execute(qry2)
Error: ('HY000', 'The driver did not supply an error!')
Process Process-4:
Traceback (most recent call last):
File "/sybopt/software/python/python/lib/python2.7/multiprocessing/process.py", line 258, in _bootstrap
self.run()
File "/sybopt/software/python/python/lib/python2.7/multiprocessing/process.py", line 114, in run
self._target(*self._args, **self._kwargs)
File "conn4.py", line 87, in extract_single
cursorconn.execute(qry2)
Error: ('HY000', 'The driver did not supply an error!')
Process Process-5:
Traceback (most recent call last):
File "/sybopt/software/python/python/lib/python2.7/multiprocessing/process.py", line 258, in _bootstrap
self.run()
File "/sybopt/software/python/python/lib/python2.7/multiprocessing/process.py", line 114, in run
self._target(*self._args, **self._kwargs)
File "conn4.py", line 87, in extract_single
cursorconn.execute(qry2)
Error: ('HY000', 'The driver did not supply an error!')

Answer 1

除了您遇到的问题之外，根据您之前的问题，我假设cursorconn是一个已经打开的连接，将为每个查询重复使用。由于它现在看起来只是一个连接字符串(尽管在您的代码中它似乎不是一个实际的字符串，这与列表templst??? 中的项的情况相同(，因此您应该在while True:语句之前进行调用conn = pyodbc.connect(cursorconn, timeout=0)，以便可以将同一连接重用于多个查询，然后您应该在从函数返回之前关闭连接。

我认为问题出在语句cursor = connvar.cursor()，应该是：cursor = conn.cursor()。因此，请尝试以下操作：

conn = pyodbc.connect(cursorconn, timeout=0)
while True:
try:
tableName = q.get_nowait()
time.sleep(3) # why is this here?
cursor = conn.cursor()
qry2 = "Select * FROM %s"% (tableName)
cursor.execute(qry2).fetchall()
print " extraction done of table:%s done by cursor:%s"%(tableName,cursorconn)
cursor.close() # should probably add this
except Queue.Empty:
return
finally:
conn.close()