return column names from pyodbc execute() statement

Question

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?

Matti John · Accepted Answer · 2012-10-03 11:23:18Z

168

You can get the columns from the cursor description:

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

answered Oct 3, 2012 at 11:23

Matti John

20.7k7 gold badges46 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Max Candocia Over a year ago

In case anyone is curious what the other values in .description mean: github.com/mkleehammer/pyodbc/wiki/Cursor#attributes

RufusVS Over a year ago

When I tried this, I got a TypeError: 'NoneType' object is not iterable.

johnDanger Over a year ago

A "NoneType" TypeError will occur when the description attribute is None due to no rows being returned, as described here: code.google.com/archive/p/pyodbc/wikis/Cursor.wiki

joris · Accepted Answer · 2015-06-13 10:48:51Z

Recent pandas have a higher level read_sql functions that can do this for you

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)

StardustGogeta · Accepted Answer · 2019-07-30 16:25:09Z

In case you are experiencing the NoneType error from the code provided by Matti John, make sure to make the cursor.description call after you have retrieved data from the database. An example:

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

This fixed it for me.

dmvianna · Accepted Answer · 2012-10-03 23:38:18Z

6

Improving on the previous answer, in the context of pandas, I found this does exactly what I expect:

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

answered Oct 3, 2012 at 23:38

dmvianna

15.8k18 gold badges81 silver badges110 bronze badges

1 Comment

Khanakia May 16 at 6:57

ValueError: Length mismatch: Expected axis has 1 elements, new values have 7 elements Output is truncated. View as a scrollable element or open in a text editor. Adjust cell output settings...

Collectives™ on Stack Overflow

return column names from pyodbc execute() statement

4 Answers 4

3 Comments

Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

1 Comment

Linked

Related