1

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.

ChangeMyName
  • 7,018
  • 14
  • 56
  • 93
  • 1
    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 Aug 21 '15 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 Aug 21 '15 at 15:53

1 Answers1

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

StackTheEast
  • 101
  • 4