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

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