Stored Procedure Multiple Tables - PYODBC - Python

Question

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)

Buzzkillionair · Accepted Answer · 2022-12-20 02:11:07Z

5

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

edited Dec 20, 2022 at 2:11

Buzzkillionair

3791 gold badge3 silver badges21 bronze badges

answered Jun 4, 2018 at 15:27

Parfait

108k19 gold badges102 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

StackTheEast · Accepted Answer · 2019-10-25 21:18:04Z

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()

answered Oct 25, 2019 at 21:18

StackTheEast

1014 bronze badges

1 Comment

Buzzkillionair Over a year ago

I added/recommended the edit you made

Collectives™ on Stack Overflow

Stored Procedure Multiple Tables - PYODBC - Python

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related