大 pandas 是否支持将多个表中的数据读取到一个数据框中? [英] Does pandas support reading data from multiple tables into a dataframe?
本文介绍了大 pandas 是否支持将多个表中的数据读取到一个数据框中?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在使用熊猫将SQLl输出读取到数据帧中.我正在调用一个存储过程,该存储过程返回表输出.以下代码可以正常工作.如果我的存储过程返回多个表输出[1],那么如何从数据帧中读取这些输出.我想将不同的表输出写入不同的Excel工作表
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
推荐答案
我希望这可以为您提供帮助:
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()
这篇关于大 pandas 是否支持将多个表中的数据读取到一个数据框中?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文