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 tha

Solution 1:

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 inenumerate(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 inenumerate(column_names)})

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

writer.save()

Solution 2:

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 AsStringDim Database_Name AsStringDim User_ID AsStringDim Password AsStringDim SQLStr AsStringDim 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 hereSet 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 spreadsheetFor iCols = 0To rs.Fields.Count - 1
        Worksheets("Sheet1").Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
    NextWith Worksheets("sheet1").Range("a2:z500") ' Enter your sheet name and range here'.ClearContents
        .CopyFromRecordset rs
    EndWith'            Tidy up
    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = NothingEndSub