当使用pyodbc调用存储过程时,它不会返回任何内容,因此cursor.fetchall()会产生错误:
ProgrammingError Traceback (most recent call last)
<ipython-input-23-d35fc1caa8d6> in <module>()
----> 1 cursor.fetchall()
ProgrammingError: No results. Previous SQL was not a query.我试过几次,都是一样的:
conn = pyodbc.connect(r'DSN=<DSN>;UID=<UID>;PWD=<pswd>;DATABASE=<database>;')
cursor = conn.cursor()而且比
cursor.execute('Stored_procedure 'param1', 'param2', 'param3'')
cursor.fetchall()或
cursor.execute("{CALL Stored_procedure (?,?,?)}", (p1, p2, p3))或者是熊猫
pd.read_sql("exec Stored_procedure p1, p2, p3", conn)使用以下方法获取表数据没有问题:
df = pd.read_sql('SELECT * FROM Config', conn)或使用sqlcmd获取数据。
sqlcmd -S <DSN> -U <user> -P <password> -d <database> -Q "exec Stored_procedure 'p1', 'p2', 'p3'" -o sqlcms_exec.txt有什么想法吗?谢谢
发布于 2018-08-07 06:42:07
问题是输出中有一个以上的表。解决办法:
import pyodbc
import pandas as pd
conn = pyodbc.connect('DSN=<DSN>;UID=<UID>;PWD=<pswd>;DATABASE=<database>;')
sql = "exec Storeed_Procedure 'p1', 'p2', 'p3'"
cursor = conn.cursor()
cursor.execute(sql)
for i in range(50):
cursor.nextset() # go to next table
try:
f = cursor.fetchall()
headers = [h[0] for h in cursor.description]
return pd.DataFrame.from_records(f, columns=headers)
except:
continue https://stackoverflow.com/questions/51706011
复制相似问题