2

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)
Anthony Richard
  • 127
  • 2
  • 12

2 Answers2

4

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
Buzzkillionair
  • 319
  • 3
  • 18
Parfait
  • 104,375
  • 17
  • 94
  • 125
2

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()
StackTheEast
  • 101
  • 4