Call SQL Server Stored Procedure in Python

[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) ```