CODEWITHSUNDEEP
pythonsqlsql-serverpandaspyodbc
Ratha
Ratha

Reputation: 9692

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

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

Upvotes: 0

Views: 1939

Answers (2)

ASH
ASH

Reputation: 20342

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

Upvotes: -1

GiovaniSalazar
GiovaniSalazar

Reputation: 2094

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

Upvotes: 3

Related Questions