Take the 2-minute tour ×

I'm not asking for the SHOW COLUMNS command.

I want to create an application that works similiarly to heidisql, where you can specify an SQL query and when you execute it ... you get a result set with rows and columns representing your query result. The column names in the result set should match your selected columns as defined in your SQL query.

In my Python program (using MySQLdb) my query returns only the row and column results, but not the column names. In the following example the column names would be ‘ext’, ‘totalsize’, and ‘filecount’. The SQL would eventually be external from the program.

The only way I can figure to make this work, is to write my own SQL parser logic to extract the selected column names.

Is there an easy way to get the column names for the provided SQL? Next I'll need to know how many columns does the query return?

# Python

import MySQLdb

# connect to mysql

    db = MySQLdb.connect(host="myhost", user="myuser", passwd="mypass",db="mydb")
except MySQLdb.Error, e:
    sys.exit (1)

# query select from table

cursor = db.cursor ()   

cursor.execute ("""\
     select ext,
        sum(size) as totalsize,
        count(*) as filecount
     from fileindex
    group by ext
    order by totalsize desc;

while (1):
    row = cursor.fetchone ()
    if row == None:
    print "%s %s %s\n" % (row[0], row[1], row[2])

share|improve this question
Thanks Daniel DiPaulo ... that's the answer I was seeking. I just wrote my own sql parser so that I could extract the select column names / aliases. Works fine. –  panofish Feb 16 '11 at 20:38
Thanks to user625477... that is the better answer. Why didn't stackoverflow email me of the updated answers? –  panofish Apr 25 '13 at 14:01

5 Answers 5

up vote 49 down vote accepted

cursor.description will give you a tuple of tuples where [0] for each is the column header.

num_fields = len(cursor.description)
field_names = [i[0] for i in cursor.description]
share|improve this answer
Why is this answer not marked correct? This is clearly what the OP needs. –  thefreeman Dec 25 '12 at 5:05
I never got an email of the updated answer. –  panofish Apr 25 '13 at 13:57

I think this should do what you need (builds on the answer above) . I am sure theres a more pythony way to write it, but you should get the general idea.

columns = cursor.description
result = []
for value in cursor.fetchall():
    tmp = {}
    for (index,column) in enumerate(value):
        tmp[columns[index][0]] = column
share|improve this answer

This is the same as thefreeman but more in pythonic way using list and dictionary comprehension

columns = cursor.description 
result = [{columns[index][0]:column for index, column in enumerate(value)} for value in cursor.fetchall()]

share|improve this answer

Similar to @James answer, a more pythonic way can be:

fields = map(lambda x:x[0], cursor.description)
result = [dict(zip(fields,row))   for row in cursor.fetchall()]

You can get a single column with map over the result:

extensions = map(lambda x: x['ext'], result)

or filter results:

filter(lambda x: x['filesize'] > 1024 and x['filesize'] < 4096, result)

or accumulate values for filtered columns:

totalTxtSize = reduce(
        lambda x,y: x+y,
        filter(lambda x: x['ext'].lower() == 'txt', result)
share|improve this answer
Thorough. Is there an advantage to using fetchall over iterating on the cursor? –  kzarns Sep 24 at 17:33
@kzarns: I'd say that making the example shorter, and getting a full list. You might create a generator function instead that iterates over the cursor, using yield, and might use less memory. –  juandesant Sep 26 at 15:32

Looks like MySQLdb doesn't actually provide a translation for that API call. The relevant C API call is mysql_fetch_fields, and there is no MySQLdb translation for that

share|improve this answer

Your Answer


