Pandas IO SQL和具有多个结果集的存储过程 [英] Pandas IO SQL and stored procedure with multiple result sets

问题描述

所以我在本地sql服务器上存储了proc，这将返回多个数据集/表

So I have a stored proc on a local sql server, this returns multiple data sets / tables

通常，在python/pyodbc中，我将使用

Normally, in python / pyodbc I would use

cursor.nextset()
subset1 = cursor.fetchall()
cursor.nextset()
subset2 = cursor.fetchall()

我希望利用ps.io.sql.read_sql并将具有多个结果集的存储过程返回到数据帧中，但是我找不到任何有关如何在关闭内容之前移动光标并获得更多信息的信息关闭.

I wish to make use of the ps.io.sql.read_sql and return the stored procedure with multiple result sets into dataframes, however I can not find anything that references how to move the cursor along and get more information before closing things off.

import pandas as ps

query = "execute raw.GetDetails @someParam = '118'"
conn = myConnection() #connection,cursor

results = ps.io.sql.read_sql(query, con=conn[0])

results.head()

conn[1].close()

推荐答案

以下方法应该起作用:

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine('mysql://{}:{}@{}/{}'.format(username, password, server, database_name))
connection = engine.connect().connection
cursor = self.connection.cursor()

cursor.execute('call storedProcName(%s, %s, ...)', params)

# Results set 1
column_names = [col[0] for col in cursor.description] # Get column names from MySQL

df1_data = []
for row in cursor.fetchall():
    df1_data.append({name: row[i] for i, name in enumerate(column_names)})

# Results set 2
cursor.nextset()
column_names = [col[0] for col in cursor.description] # Get column names from MySQL

df2_data = []
for row in cursor.fetchall():
    df2_data.append({name: row[j] for j, name in enumerate(column_names)})

cursor.close()

df1 = pd.DataFrame(df1_data)
df2 = pd.DataFrame(df2_data)

编辑:我已经在这里更新了代码，以避免必须手动指定列名.

I've updated the code here to avoid having to manually specify the column names.

请注意，原始问题仅指定本地SQL服务器"，而不指定特定类型的SQL Server.该答案适用于MySQL，但我尚未对其进行任何其他测试.

Note that the original question only specifies a "local SQL server", not a specific kind of SQL server. This answer works with MySQL, but I haven't tested it with any other variety.

这篇关于Pandas IO SQL和具有多个结果集的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！