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?

I wish I could upvote this question 100,000 times. – LunchBox Jun 12 '20 at 05:27 — LunchBox, Jun 12 '20 at 05:27

score 121 · Accepted Answer · answered Oct 03 '12 at 11:23

121

You can get the columns from the cursor description:

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

answered Oct 03 '12 at 11:23

Matti John

15,554
7
35
38

12

In case anyone is curious what the other values in `.description` mean: https://github.com/mkleehammer/pyodbc/wiki/Cursor#attributes – Max Candocia Jan 18 '18 at 19:55
2

When I tried this, I got a TypeError: 'NoneType' object is not iterable. – RufusVS Sep 17 '18 at 15:36
1

A "NoneType" TypeError will occur when the ```description``` attribute is ```None``` due to no rows being returned, as described here: https://code.google.com/archive/p/pyodbc/wikis/Cursor.wiki – johnDanger Sep 04 '19 at 21:35

score 39 · Answer 2 · edited Jun 13 '15 at 10:48

39

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)

edited Jun 13 '15 at 10:48

joris

106,362
32
216
184

answered Jul 05 '13 at 14:52

phoenix10k

490
4
6

score 9 · Answer 3 · edited Jul 30 '19 at 16:25

9

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.

edited Jul 30 '19 at 16:25

StardustGogeta

2,644
2
16
32

answered Jul 30 '19 at 16:14

Morten Wehlast

91
1
2

score 5 · Answer 4 · answered Oct 03 '12 at 23:38

5

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 03 '12 at 23:38

dmvianna

12,370
17
69
101

return column names from pyodbc execute() statement

4 Answers4

Linked

Related