python - 如何将具有多个结果集的 SQL Server 存储过程的输出保存到 pandas 中的每个数据框中

Question

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

如何使用 pandas 将每个表存储在不同的数据框中？

类似于:

• df1 - 第一个表
• df2 - 第二张表
• df3 - 第三张表

我应该从哪里开始看？

谢谢

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

Answer 1

您应该能够遍历结果集，将它们转换为数据帧，并将这些数据帧附加到列表中。例如，给定存储过程

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 代码看起来像这样:

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

"""