pyodbc calling Stored Procedures error

Question

When calling stored procedure with pyodbc it return nothing, and therefore cursor.fetchall() gives error:

ProgrammingError                          Traceback (most recent call last)
<ipython-input-23-d35fc1caa8d6> in <module>()
----> 1 cursor.fetchall()

ProgrammingError: No results.  Previous SQL was not a query.

I tried in few was and it is all the same:

conn = pyodbc.connect(r'DSN=<DSN>;UID=<UID>;PWD=<pswd>;DATABASE=<database>;')
cursor = conn.cursor()

and than

cursor.execute('Stored_procedure 'param1', 'param2', 'param3'')
cursor.fetchall()

OR

cursor.execute("{CALL Stored_procedure (?,?,?)}", (p1, p2, p3))

or with pandas

pd.read_sql("exec Stored_procedure p1, p2, p3", conn)

I have no problem to get table data using:

df = pd.read_sql('SELECT * FROM Config', conn)

or to get the data with sqlcmd

sqlcmd -S <DSN> -U <user> -P <password> -d <database> -Q "exec Stored_procedure 'p1', 'p2', 'p3'" -o sqlcms_exec.txt

Any ideas? Thanks

user1889297 · Answer 1 · 2018-08-07T06:57:41.673

The problem is that there is more than 1 table in the output. solution:

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

pyodbc calling Stored Procedures error

1 Answers1