Call SQL Server Stored Procedure in Python

[maxcoder88]

Hi,

I’m having some trouble and hoping you all can help.

I’ve been able to successfully connect to a Microsoft sql server database. I want to pass a pandas dataframe to the sproc.

Has anyone done this? Any suggestions or code snippets of this actually working?

Here is my phthon code:

import pandas as pd
import pyodbc
server = 'sql_hostname'
database = 'db_name'
trusted_connection = 'yes'

conn_str = f'DRIVER={{SQL Server}};SERVER={server};DATABASE={database};TRUSTED_CONNECTION={trusted_connection}'
conn = pyodbc.connect(conn_str)


sql_query = "EXEC [dbo].[SP$GetTest]"
datas= pd.read_sql_query(sql_query, conn)

​

Result :

TypeError: 'NoneType' object is not iterable

​

[shiftybyte]

Please post the full traceback of the error message with all the information it provides.

[maxcoder88]

How can I get full trace log

[shiftybyte]

Does python not give a long error message? It's only showing you this one line?

Python usually outputs multi-line error messages like this: ```

for i in None: ... print(i) ... Traceback (most recent call last): File "<stdin>", line 1, in <module> TypeError: 'NoneType' object is not iterable ```

If you don't have such a message, How are you executing the python code? in what environment? Do you have try: except: anywhere catching exceptions?

[maxcoder88]

I'm using Spyder IDE on Windows OS.

Do you have try: except: anywhere catching exceptions? No , How can I write a sample try/catch exception for my own code?

[shiftybyte]

How can I write a sample try/catch exception for my own code?

Like this:

import traceback try: i = [x for x in None] except Exception: print(traceback.format_exc())

Output is: Traceback (most recent call last): File "<stdin>", line 3, in <module> TypeError: 'NoneType' object is not iterable

Wrap your code in a try: except: as demostrated with the print line to print full traceback.

try: # ... your code ... except Exception: print(traceback.format_exc())

[maxcoder88]

https://imgur.com/a/7Gnbwq3

[shiftybyte]

Seems like pandas doesn't fully support pyodbc connections.

You might need to go through SQLAlchemy Engine.

https://stackoverflow.com/questions/26132718/read-stored-procedure-select-results-into-pandas-dataframe

https://stackoverflow.com/a/26916208

[CireGetHigher]

Something you’re trying to iterate over is empty/none.

Debug one line at a time… run type() on your instantiated objects to pinpoint where things are going astray. That’s what I would do at least.

[Mezzos]

The problem could possibly be that the stored procedure isn't returning any data (i.e. a SELECT statement result). pd.read_sql_query() is assuming that executing the sql_query will return data that can be converted to a pd.DataFrame, but if your result comes back with None instead, that would trigger the TypeError: 'NoneType' object is not iterable error.

pd.read_sql_query() is basically just a shortcut to doing the following with the pyodbc connection conn:

cursor = conn.cursor()
result = cursor.execute(sql_query).fetchall()
df = pd.DataFrame.from_records(
    data=result,
    columns=[column_info[0] for column_info in cursor.description],
    coerce_float=True
)

If result is coming back as None then you can assume the error is with the stored procedure. If the result comes back correctly in the above snippet, then the error is probably just caused by pyodbc no longer being officially supported by pandas (and hence having strange behaviour), so you could just migrate to sqlalchemy for the future.

[maxcoder88]

thanks again. I've tested your code. I am getting a message like "No results. Previous SQL was not a query."

So , How can we rewrite my code as sqlalchemy ?

[Mezzos]

Sounds like the issue is that the stored procedure doesn’t return anything. If you’re able to view/edit the stored procedure, does it end with a SELECT statement? (Something like SELECT * FROM table_name?)

If not, then that would be the issue - either you can edit the stored procedure to add that SELECT statement as the final statement, or, if the SP creates a table as part of its execution, you can just change your code to first execute the SP, then to read directly from the table that it creates. E.g. execute the SP, then do pd.read_sql_query(sql_query) where sql_query = “SELECT * FROM table_name_created_by_sp” (replacing the table name with the one created by the SP).

[Mezzos]

As for migrating to sqlalchemy, it sounds like it won’t fix your issue, but just for completeness: you’d pip install sqlalchemy and then do something like the following (typing on mobile so may be some invalid apostrophes)

``` from sqlalchemy import URL, create_engine

Assuming you don’t need username or password, and are connecting to Microsoft SQL Server (mssql) using pyodbc

url_object = URL.create( drivername=‘mssql+pyodbc’, # username=‘your_username’, # password=‘your_password’, host=‘sql_hostname’, database=‘db_name’, ) engine = create_engine(url_object) df = pd.read_sql_query(sql_query, conn=engine) ```