如何将具有多个结果集的SQL Server存储过程的输出保存到Pandas的每个数据框中 [英] How to save output of SQL Server stored procedure with multiple result sets into each dataframe in pandas

问题描述

我有一个SQL Server存储过程，该过程返回3个单独的表.

I have a SQL Server stored procedure that returns 3 separate tables.

如何使用熊猫将每个表存储在不同的数据框中?

How can I store each of this table in different data-frame using pandas?

类似的东西:

• df1 -第一张桌子
• df2 -第二张表
• df3 -第三张表

• df1 - first table
• df2 - second table
• df3 - third table

我应该从哪里开始看?

谢谢

import pandas as pd 
import pyodbc 
from datetime import datetime


param = datetime(year=2019,month=7,day=31)
query = """EXECUTE [dbo].PythonTest_USIC_TreatyYear_ReportingPackage  @AsOFDate = '{0}'""".format(param)
conn = pyodbc.connect('DRIVER={SQL Server};server=myserver;DATABASE=mydatabase;Trusted_Connection=yes;')
df = pd.read_sql_query(query, conn)
print(df.head())

推荐答案

您应该能够仅遍历结果集，将它们转换为DataFrame，然后将这些DataFrame附加到列表中.例如，给定存储过程

You should be able to just iterate through the result sets, convert them to DataFrames, and append those DataFrames to a list. For example, given the stored procedure

CREATE PROCEDURE dbo.MultiResultSP 
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT 1 AS [UserID], N'Gord' AS [UserName]
    UNION ALL
    SELECT 2 AS [UserID], N'Elaine' AS [UserName];

    SELECT N'pi' AS [Constant], 3.14 AS [Value]
    UNION ALL
    SELECT N'sqrt_2' AS [Constant], 1.41 AS [Value]
END

Python代码如下所示:

the Python code would look something like this:

data_frames = []
crsr = cnxn.cursor()
crsr.execute("EXEC dbo.MultiResultSP")
result = crsr.fetchall()
while result:
    col_names = [x[0] for x in crsr.description]
    data = [tuple(x) for x in result]  # convert pyodbc.Row objects to tuples
    data_frames.append(pd.DataFrame(data, columns=col_names))
    if crsr.nextset():
        result = crsr.fetchall()
    else:
        result = None

# check results
for df in data_frames:
    print(df)
    print()
""" console output:

   UserID UserName
0       1     Gord
1       2   Elaine

  Constant Value
0       pi  3.14
1   sqrt_2  1.41

"""

这篇关于如何将具有多个结果集的SQL Server存储过程的输出保存到Pandas的每个数据框中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！