how do i fetch all results from SQL stored procedure from python into a dataframe using pdreadsqlque-CodePudding

i have a MySQL stored procedure that retuns 7000 rows and11 columns of data.I am able to execute this stored procedure in SQL. How do i return all this data

I tried -

import pandas as pd 
import pyodbc
conn = pyodbc.connect('Driver={SQL Server};'
'Server=ABC003\PROD;''Databse=Warehouse;''Trusted_Connection=yes;')
dfo =pd.read_sql_query('SELECT * FROM EXEC dbo.storedproc',conn)

but this doesnt work. i receive incorrect syntax next to the keyword 'EXEC'

My final objective is to read all the data into a dataframe. im not familiar with alternatives like sqlalchemy yet and not sure it applies in my case because i am coding in T-SQL and not SQL-Lite. is there no way to do this through pyodbc library or pandas read sql query?

CodePudding user response：

Try

import pandas as pd 
import pyodbc
conn = pyodbc.connect('Driver={SQL Server};'
'Server=ABC003\PROD;''Databse=Warehouse;''Trusted_Connection=yes;')cursor = conn.cursor()
cursor.execute('SET NOCOUNT ON;EXEC dbo.storedproc')
results = cursor.fetchall()
dfo = pd.DataFrame.from_records(results)

This will get all your data into a dataframe irrespective of params,unfiltered. Let me know if this works. Thanks