0

I have a stored proc in SQL Server called test.storedproc

My py script is as follows

import pyodbc
import pandas as pd
conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=PMI0x8x\xxx;'
                      'Database=Warehouse;'
                      'Trusted_Connection=yes;'
                      )

cmd_prod_executesp = """EXEC Test.storedproc"""
cursor = conn.cursor()
cursor.execute(cmd_prod_executesp)
conn.commit()

This runs successfully. However, how do I get the data which is in the storedproc as a dataframe?I tried -

dfo = pd.read_sql_query(cmd_prod_executesp, conn)
Traceback (most recent call last):

  File "<ipython-input-70-06220d2e8e81>", line 1, in <module>
    dfo = pd.read_sql_query(cmd_prod_executesp, conn)

  File "C:\Users\xx\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\io\sql.py", line 383, in read_sql_query
    chunksize=chunksize,

  File "C:\Users\xx\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\io\sql.py", line 1744, in read_query
    columns = [col_desc[0] for col_desc in cursor.description]

TypeError: 'NoneType' object is not iterable

How do I read stored proc data into a dfo?

Joe Tha
  • 163
  • 9
  • The SP isn't in SSMS, its in SQL Server - SSMS is just a client UI. Also, please quote any errors verbatim. – Dale K May 12 '22 at 03:43
  • Good question, but the site policy is that people don't need to explain, so all you know is that based on a mouse over, someone thinks its unclear, not useful or missing research. – Dale K May 12 '22 at 04:44
  • 1
    thank you Dale. if you found this question good, please upvote it. As you said. its easy for people to just downvote but it could lead to a question ban on my account. It would really help if they just suggested improvements to the questions like you have, instead of downvotes.Thank you again – Joe Tha May 12 '22 at 04:48
  • Have you read the [pandas.read_sql_query documentation](https://pandas.pydata.org/docs/reference/api/pandas.read_sql_query.html) yet? Is `conn` an SQLAlchemy connectable? Seems like this [previous SO answer](https://stackoverflow.com/a/26916208/390122) could be relevant. – AlwaysLearning May 12 '22 at 07:39
  • 1
    this is T-SQL pyodbc connector.havent tried with sqlalchemy until today. I tried the previous SO answer. but what do i define as my_param? @AlwaysLearning – Joe Tha May 12 '22 at 15:59

1 Answers1

0

Not enough rep to comment or I would (since this isn't an explicit answer) but you could use the sp to run the data into a temp table then select * from temp table and then the df = pd.read_sql(sql, con=connection) should work just fine.

If you aren't as handy with sql... here is a topic which shows how to run sp into temp table. link

systematical
  • 43
  • 5