Using Python, how to take multiple tables returned from a SQL query?

Question

I am trying to use Python to call a SQL query, with pyodbc.

It worked fine in the following way:

import pyodbc
import pandas.io.sql as psql

server_name  = 'My_Server'
database_name = 'My_DB'

conn = pyodbc.connection("driver={SQL Server};server=" + server_name + ";database=" + database_name + ";trusted_connection=true")

sql_command = """ EXEC MY_DB.dbo.some_proc"""

df = psql.read_frame(sql_command, conn)

It was ok when some_proc returns only one table. But what can I do if some_proc returns multiple tables, e.g. two tables?

Many thanks.

Answer 1

Borrowed from Stored Procedure Multiple Tables - PYODBC - Python

Ensure you have SET NOCOUNT ON in the stored procedure or none of this will work.

The following creates a list of dataframes where each index is a table returned from the stored procedure.

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

Then reference df_list[0].head() etc