使用Python执行存储过程时，如何保留列标题？

发布于 2025-01-28 06:27:01 字数 894 浏览 1 评论 0原文

运行下面显示的代码后，我从存储过程中收到所有数据。但是，未返回列名：

import pandas as pd
import pyodbc

conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=PMI0317\Prod;'
                      'Database=Warehouse;'
                      'Trusted_Connection=yes;'
                      )

cursor = conn.cursor()
cursor.execute('SET NOCOUNT ON;EXEC Test.storedproc')
results = cursor.fetchall()
df = pd.DataFrame.from_records(results)
df

输出[113]：

             0   1                  2   3    4    5   ... 15  16  17  18 19 20
0       9593746   0  COOKCHILDRENS.ORG   1  1.0  Dog  ...  0   0   0   0  0  2
1       9593723   0          gmail.com   1  1.0  Dog  ...  0   0  12  16  0  0
list(df.columns)
Out[114]: [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20]

如您所见，它返回我的数据，但列名称为数字。为什么会发生这种情况？我需要更改结果功能以保留原始列名称？

原文

After I run my code shown below, I receive all the data from my stored procedure. However, the column names are not returned:

import pandas as pd
import pyodbc

conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=PMI0317\Prod;'
                      'Database=Warehouse;'
                      'Trusted_Connection=yes;'
                      )

cursor = conn.cursor()
cursor.execute('SET NOCOUNT ON;EXEC Test.storedproc')
results = cursor.fetchall()
df = pd.DataFrame.from_records(results)
df

Output[113]:

             0   1                  2   3    4    5   ... 15  16  17  18 19 20
0       9593746   0  COOKCHILDRENS.ORG   1  1.0  Dog  ...  0   0   0   0  0  2
1       9593723   0          gmail.com   1  1.0  Dog  ...  0   0  12  16  0  0
list(df.columns)
Out[114]: [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20]

As you see, it returns my data but column names are changes to numeric. why does this happen? What do I need to change in results function to retain original column names?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

伏妖词 2025-02-04 06:27:01

您可以从光标描述中添加列名：

df = pd.DataFrame.from_records(results, columns = [column[0] for column in cursor.description])

或者，您可以使用read_sql并直接加载到pandas：

conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=PMI0317\Prod;'
                      'Database=Warehouse;'
                      'Trusted_Connection=yes;'
                      )

df = pd.read_sql('exec Test.storedproc',conn)

you can add the column name from cursor description:

df = pd.DataFrame.from_records(results, columns = [column[0] for column in cursor.description])

alternatively, you could use read_sql and directly load into pandas :

conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=PMI0317\Prod;'
                      'Database=Warehouse;'
                      'Trusted_Connection=yes;'
                      )

df = pd.read_sql('exec Test.storedproc',conn)

回复收藏 0 原文

~没有更多了~