return column names from pyodbc execute() statement

from pandas import DataFrame
import pyodbc


cnxn = pyodbc.connect(databasez)
cursor.execute("""SELECT ID, NAME AS Nickname, ADDRESS AS Residence FROM tablez""")
DF = DataFrame(cursor.fetchall())

This is fine to populate my pandas DataFrame. But how do I get

DF.columns = ['ID', 'Nickname', 'Residence']

straight from cursor? Is that information stored in cursor at all?

72790 次浏览

小开

最佳答案

您可以从光标描述中获取列:

columns = [column[0] for column in cursor.description]

小开

在前一个答案的基础上，在熊猫的背景下，我发现这正是我所期望的:

DF.columns = DataFrame(np.matrix(cursor.description))[0]

小开

最近的熊猫有一个更高层次的 read_sql功能，可以做到这一点

import pyodbc
import pandas as pd


cnxn = pyodbc.connect(databasez)
DF = pd.read_sql_query("SELECT ID, NAME AS Nickname, ADDRESS AS Residence FROM tablez", cnxn)

小开

如果您遇到 Matti John提供的代码中的 NoneType错误，请确保从数据库中检索到数据的 cursor.description调用之后。举个例子:

cursor = cnxn.cursor()
cursor.execute("SELECT * FROM my_table")
columns = [column[0] for column in cursor.description]

这东西帮我修好了。