Does pandas support reading data from multiple tables into a dataframe?

Question

I'm using pandas to read SQLl output into a dataframe. I'm calling a stored procedure which returns a table output. Following code works fine.If my stored procedure return more than one table outputs[1], How can I read those from dataframe. I want to write different table outputs into different excel sheets

query='exec [aa].[dbo].[sp_cc]?,?'
        df = pd.read_sql(query, cnxn, params=[start,end)

        writer = pd.ExcelWriter('output.xlsx')
        df.to_excel(writer, index=False, sheet_name='customers')
        writer.save()

[1]

CREATE procedure [dbo].[usp_vvvv] (....)
  BEGIN
  SET NOCOUNT ON
    .....
    select  *
    FROM #_temp_client_details
    select *
    FROM #_temp_address_details
    select *
    FROM #_temp_invoice_details

    drop table #_temp_client_details
    drop table #_temp_address_details
    drop table #_temp_invoice_details
    ....
    END TRY
    BEGIN CATCH
    ..
     END CATCH
    END

GiovaniSalazar · Accepted Answer · 2019-12-03 04:55:11Z

3

I hope this can help you :

import pandas as pd
import pyodbc

conn = pyodbc.connect('driver={SQL Server};server=xxx.xxx.x.xxx;uid=myuser;pwd=mypass;database=mybd;autocommit=True')
cursor = conn.cursor()
cursor.execute('exec usp_with_2_select')

writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')

column_names = [col[0] for col in cursor.description]

df1_data = []
for row in cursor.fetchall():
    df1_data.append({name: row[i] for i, name in enumerate(column_names)})

df1 = pd.DataFrame(df1_data)
print(df1)
df1.to_excel(writer,'sheet1')

# this for pass the next result
cursor.nextset ()

df2_data = []
for row in cursor.fetchall():
    df2_data.append({name: row[i] for i, name in enumerate(column_names)})

df2 = pd.DataFrame(df2_data)
print(df2)
df2.to_excel(writer,'sheet2')

writer.save()

answered Dec 3, 2019 at 4:55

GiovaniSalazar

2,1042 gold badges11 silver badges16 bronze badges

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

7 Comments

Ratha Over a year ago

can i achieve same behaviour with pd.read_sql()? Not with cursor. Im using read_sql, since it is easy to get column names all at once.So I can dump the output easily into an excel sheet

GiovaniSalazar Over a year ago

I was trying with read_sql..but my problem was cursor.nextset ()

Ratha Over a year ago

SO you are saying read_sql, doesn't support multiple dataset?

Ratha Over a year ago

It is working..Mybad, i was calling different storedprocedure

Gord Thompson Over a year ago

+1, but you really should re-load column_names after calling .nextset since the second result set could have different column names.

|

ASH · Accepted Answer · 2019-12-14 03:29:53Z

Why do you need Pandas for this? You can go from SQL Server directly to Excel many different ways. Here is one concept that will work for you. There are many ways to skin this cat...

Sub ADOExcelSQLServer()
     ' Carl SQL Server Connection
     '
     ' FOR THIS CODE TO WORK
     ' In VBE you need to go Tools References and check Microsoft Active X Data Objects 2.x library
     '

    Dim Cn As ADODB.Connection
    Dim Server_Name As String
    Dim Database_Name As String
    Dim User_ID As String
    Dim Password As String
    Dim SQLStr As String
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset

    Server_Name = "your_server_name" ' Enter your server name here
    Database_Name = "NORTHWND" ' Enter your database name here
    User_ID = "" ' enter your user ID here
    Password = "" ' Enter your password here
    SQLStr = "SELECT * FROM [Customers]" ' Enter your SQL here

    Set Cn = New ADODB.Connection
    Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
    ";Uid=" & User_ID & ";Pwd=" & Password & ";"

    rs.Open SQLStr, Cn, adOpenStatic
     ' Dump to spreadsheet
    For iCols = 0 To rs.Fields.Count - 1
        Worksheets("Sheet1").Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
    Next
    With Worksheets("sheet1").Range("a2:z500") ' Enter your sheet name and range here
        '.ClearContents
        .CopyFromRecordset rs
    End With
     '            Tidy up
    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing
End Sub

Collectives™ on Stack Overflow

Does pandas support reading data from multiple tables into a dataframe?

2 Answers 2

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related