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.

a query does not return a table; it returns a result set. you can write queries that take more than one table into account. what would be your use case? — hiro protagonist
– hiro protagonist, Commented Aug 21, 2015 at 15:47
@hiroprotagonist The SQL query is very simple, which is basically a combination of two select * from tab_xxx. However, due to the development requirement, it can be only called once. So it returns two sets of results at the same time. — ChangeMyName
– ChangeMyName, Commented Aug 21, 2015 at 15:53

StackTheEast · Accepted Answer · 2019-10-23 20:15:52Z

3

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

answered Oct 23, 2019 at 20:15

StackTheEast

1014 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related