Python Forum

Hello,
When I ran the following code, it got the message "No results. Previous SQL was not a query." How do I get the dataset returned from the stored procedure? Thank you.

conn_str = ('DRIVER={SQL Server};SERVER=' + server + ';DATABASE=' + database + ';Trusted_Connection=yes;')
cnxn = pyodbc.connect(conn_str)

cursor = cnxn.cursor()
sp = "EXEC spTest"
dataset = cursor.execute(sp)
df_Test = dataset.fetchall()
print(df_Test)

Read the docs. Always good advice.

https://learn.microsoft.com/en-us/sql/re...rver-ver16

In this case the info is right near the top.

DECLARE @return_value int
 
EXEC    @return_value = [dbo].[GetCustomerEmail]
        @CustID = 10
 
SELECT  'Return Value' = @return_value

You need to declare a variable. Call the procedure, telling the procedure to put results in the variable. Use the variable to retrieve the results.

Thanks deanhystad,

Those are SQL code. How do I get the dataset in Python? Your example is returning a single value in SQL or Python? It seems in SQL.

I tried the link you provided for the Examples of returning data using a result set. I added RETURN at the end of the stored procedure, but I got the same message "No results. Previous SQL was not a query."

In Python, I just use dateset = the SQL return value (or dataset)?

Thanks again.
Dee

More read the docs? This is pyodbc specific.

https://github.com/mkleehammer/pyodbc/wi...Procedures

Thanks again deanhystad,

That works. It returned the dataset. Now it needs to be converted to a data frame like a table row and column layout. Do you have more reading for me?

I tried pd.DataFrame(rows) but all columns combined in one long column.

Read the string like it is a CSV file. Read about pandas.read_csv() and io.StringIO(str).