存储过程多表 - PYODBC - Python [英] Stored Procedure Multiple Tables - PYODBC - Python

查看:45
本文介绍了存储过程多表 - PYODBC - Python的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在尝试执行具有 20 个不同表输出的存储过程.这些输出范围为 3-6 列和 10-100 行.如果不是 pyodbc,我还能如何在没有相同结构的情况下遍历所有这些表?

I am trying to execute a stored Procedure with 20 different table outputs. These outputs range from 3-6 columns and 10-100 rows. If not pyodbc, how else would I be able to iterate through all these tables without the same structure?

connection = pyodbc.connect(r'DRIVER={SQL Server Native Client 
11.0};SERVER=dsdrsossql2;DATABASE=TableauDev;Trusted_Connection=yes;')
sql = "{call dbo.DGGrading}"
cur = connection.cursor()
rows = cur.execute(sql,).fetchall()

columns = [column[0] for column in cur.description]
df = pd.DataFrame.from_records(rows,columns=columns)
print(df)

推荐答案

考虑使用数据框列表并使用 nextset() 访问多个结果集:

Consider using a list of dataframes and access multiple resultsets with nextset():

cur = connection.cursor()
df_list = []

# FIRST RESULTSET
rows = cur.execute(sql).fetchall()
columns = [column[0] for column in cur.description]
df_list.append(pd.DataFrame.from_records(rows, columns=columns))
print(df.head())

# SUBSEQUENT RESULTSETS
while (cur.nextset()): 
   columns = [column[0] for column in cur.description]
   df_list.append(pd.DataFrame.from_records(rows, columns=columns))
   print(df.head())

cur.close()

# RUN ANY DATAFRAME OPERATION BY EACH ITEM OF df_list 
df_list[[1]].describe()
df_list[[2]].head()
df_list[[3]].tail()
df_list[[4]].dtypes
df_list[[5]].columns

这篇关于存储过程多表 - PYODBC - Python的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆