Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I am trying to execute a stored Procedure with 20 different table outputs. These outputs range from 3-6 columns and 10-100 rows. If not pyodbc, how else would I be able to iterate through all these tables without the same structure?

connection = pyodbc.connect(r'DRIVER={SQL Server Native Client 
11.0};SERVER=dsdrsossql2;DATABASE=TableauDev;Trusted_Connection=yes;')
sql = "{call dbo.DGGrading}"
cur = connection.cursor()
rows = cur.execute(sql,).fetchall()
columns = [column[0] for column in cur.description]
df = pd.DataFrame.from_records(rows,columns=columns)
print(df)
Consider using a list of dataframes and access multiple resultsets with nextset():
cur = connection.cursor()
df_list = []
# FIRST RESULTSET
rows = cur.execute(sql).fetchall()
columns = [column[0] for column in cur.description]
df_list.append(pd.DataFrame.from_records(rows, columns=columns))
print(df.head())
# SUBSEQUENT RESULTSETS
while (cur.nextset()): 
   rows = cur.fetchall()
   columns = [column[0] for column in cur.description]
   df_list.append(pd.DataFrame.from_records(rows, columns=columns))
   print(df.head())
cur.close()
# RUN ANY DATAFRAME OPERATION BY EACH ITEM OF df_list 
df_list[[1]].describe()
df_list[[2]].head()
df_list[[3]].tail()
df_list[[4]].dtypes
df_list[[5]].columns
Parfait's code didnt work for me because the 'subsequent resultset' was missing a line of code to pull the next table. I dont have enough points to comment, so here is the code that worked for me.
A few additional things
For MSSQL, CALL is not recognized so EXEC was used
Ensure SET NOCOUNT ON is in the stored procedure or fetchall wont pull in the tables correctly
param1 = "dynamic parameter1"
param2 = "dynamic parameter2"
sql = f"EXEC dbo.StoredProcedure '{param1}', '{param2}'"
cur = cnxn.cursor()
df_list = []
# get First result
rows = cur.execute(sql).fetchall()
columns = [column[0] for column in cur.description]
df_list.append(pd.DataFrame.from_records(rows, columns=columns))
# check for more results
while (cur.nextset()):
   rows = cur.fetchall()
   columns = [column[0] for column in cur.description]
   df_list.append(pd.DataFrame.from_records(rows, columns=columns))
cur.close()
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.