0

Have to say that I am new to python.

I have SQL stored procedure that returns two tables.

How can I save each table in separate dataframe: something like

df1 = first table

df2 = second table

The code below returns one dataframe with two tables, so how can I modify the code and return two dataframes?

Thanks to @GordThompson I was able to retrieve one dataframe with two tables(outputs)

Now, Instead of a loop, somehow I just need to create df1, then call nextset(). Then create df2 and call nextset() and so on.

But I can't understand where to place the right code to achieve this goal. Please help.

import pandas as pd 
import pyodbc 
from datetime import datetime

myparam = datetime.strptime('Jul 31 2019  1:33PM', '%b %d %Y %I:%M%p')
query = "{call [dbo].PythonTest_USIC_TreatyYear_ReportingPackage(?)}"
conn = pyodbc.connect('DRIVER={SQL Server};server=myserver;DATABASE=mydb;Trusted_Connection=yes;')
data_frames = []
cursor = conn.cursor()
cursor.execute(query, myparam)
result = cursor.fetchall()
#print(result)
while result:
    col_names = [x[0] for x in cursor.description] # this gives a tuple of tuples where where [0] for each is the column header
    data = [tuple(x) for x in result] # convert pyodbs.Row objects to tuples
    data_frames.append(pd.DataFrame(data, columns=col_names))
    if cursor.nextset()==True:
        result = cursor.fetchall()
    else:
        result = None


for df in data_frames:  #looping through data_frames
    print(df)
    print()

cursor.close()
conn.close()

enter image description here

Serdia
  • 4,242
  • 22
  • 86
  • 159
  • you should fix your indentation in the code you've show. Indentation is important in python. – Valentino Sep 27 '19 at 19:07
  • see also here, may be useful: https://stackoverflow.com/questions/39835770/read-data-from-pyodbc-to-pandas – Valentino Sep 27 '19 at 19:11
  • Oh, thanks for noticing. Updated – Serdia Sep 27 '19 at 19:11
  • "I was able to retrieve one dataframe with two tables(outputs)" (in the [previous answer](https://stackoverflow.com/a/58116197/2144390)) - No, you retrieved a `list` containing two independent DataFrames. You could refer to them individually by simply saying `df1 = data_frames[0]` and `df2 = data_frames[1]`. `df1` and `df2` are just pointers to the DataFrame objects, so you are not wasting memory by maintaining multiple copies. – Gord Thompson Sep 27 '19 at 19:12
  • Oh! Got it! @GordThompson Thank you for your patience. Please post an answer. Thanks – Serdia Sep 27 '19 at 19:25

0 Answers0