return column names from pyodbc execute() statement

PythonPandasPyodbc

Python Problem Overview

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?

Python Solutions

Solution 1 - Python

You can get the columns from the cursor description:

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

Solution 2 - Python

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)

Solution 3 - Python

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.

Solution 4 - Python

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]
Categories
Recommended Python Solutions
Recommended Pandas Solutions
Recommended Pyodbc Solutions

Previous Solution:

PG::Error: SELECT DISTINCT, ORDER BY expressions must appear in select list

Ruby on-RailsRubyRuby on-Rails-3PostgresqlDistinct

Next Solution:

Redirect / return to same (previous) page in Django?

DjangoDjango Views

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestiondmviannaView Question on Stackoverflow
Solution 1 - PythonMatti JohnView Answer on Stackoverflow
Solution 2 - Pythonphoenix10kView Answer on Stackoverflow
Solution 3 - PythonMorten WehlastView Answer on Stackoverflow
Solution 4 - PythondmviannaView Answer on Stackoverflow