MySQL: Get column name or alias from query

Question

I'm not asking for the SHOW COLUMNS command.

I want to create an application that works similarly to heidisql, where you can specify an SQL query and when executed, returns 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
#===================================================================

try:
    db = MySQLdb.connect(host="myhost", user="myuser", passwd="mypass",db="mydb")
except MySQLdb.Error, e:
    print "Error %d: %s" % (e.args[0], e.args[1])
    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:
        break
    print "%s %s %s\n" % (row[0], row[1], row[2])

cursor.close()
db.close()

cursor.column_names

Amin
– Amin

2019-05-21 14:52:59 +00:00
Commented May 21, 2019 at 14:52 — Amin
– Amin, Commented May 21, 2019 at 14:52

user625477 · Accepted Answer · 2011-02-20 18:13:48Z

303

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]

answered Feb 20, 2011 at 18:13

user625477

3,2962 gold badges17 silver badges8 bronze badges

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

5 Comments

Amin Over a year ago

cursor.column_names does it too

Yaroslav Nikitenko Over a year ago

In MySQLdb 1.2.5 cursor.column_names is missing.

SteveS Over a year ago

@YaroslavNikitenko .column_names is gone? I need to use .description to get the column names?

Yaroslav Nikitenko Over a year ago

@SteveS from what I wrote last year it seems that :)

dibery Over a year ago

To make it shorter: field_names = next(zip(*cursor.description))

Josh Correia · Accepted Answer · 2023-12-20 07:07:40Z

48

This is the same as thefreeman's answer but in a more 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()]

pprint.pprint(result)

edited Dec 20, 2023 at 7:07

Josh Correia

4,4103 gold badges42 silver badges67 bronze badges

answered Feb 23, 2013 at 19:23

James

10.6k5 gold badges34 silver badges40 bronze badges

Comments

juandesant · Accepted Answer · 2022-01-13 17:34:31Z

35

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

fields = [field_md[0] for field_md in cursor.description]
result = [dict(zip(fields,row)) for row in cursor.fetchall()]

You can get a single column with list comprehension over the result:

extensions = [row['ext'] for row in result)

or filter results using an additional if in the list comprehension:

large = [row for row in result if row['filesize'] > 1024 and row['filesize'] < 4096]

or accumulate values for filtered columns:

totalTxtSize = reduce(
        lambda x,y: x+y,
        filter(lambda x: x['ext'].lower() == 'txt', result)
)

edited Jan 13, 2022 at 17:34

answered Jul 8, 2013 at 18:59

juandesant

7559 silver badges18 bronze badges

4 Comments

cs_alumnus Over a year ago

Thorough. Is there an advantage to using fetchall over iterating on the cursor?

juandesant Over a year ago

@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.

pdem Over a year ago

Doesn't work with me, only the first row is converterted, slightly adaption to make it work: fields = [i[0] for i in cursor.description] then result = [dict(zip(fields,row)) for row in cursor.fetchall()] wil work

Patrick H. Over a year ago

Very nice. You could also create a DataFrame from result with pd.DataFrame.from_records and do all that filtering and aggregation in pandas. Although, if you are up to using pandas, it might be easier to use sqlalchemy and pd.read_sql in the first place.

thefreeman · Accepted Answer · 2012-12-25 05:15:35Z

14

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.

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

answered Dec 25, 2012 at 5:15

thefreeman

1,03511 silver badges13 bronze badges

1 Comment

Surya prakash Patel Over a year ago

is there any other simple way, why it does not return value with fields?

Eugene · Accepted Answer · 2016-11-01 22:56:17Z

10

You could also use MySQLdb.cursors.DictCursor. This turns your result set into a python list of python dictionaries, although it uses a special cursor, thus technically less portable than the accepted answer. Not sure about speed. Here's the edited original code that uses this.

#!/usr/bin/python -u

import MySQLdb
import MySQLdb.cursors

#===================================================================
# connect to mysql
#===================================================================

try:
    db = MySQLdb.connect(host='myhost', user='myuser', passwd='mypass', db='mydb', cursorclass=MySQLdb.cursors.DictCursor)
except MySQLdb.Error, e:
    print 'Error %d: %s' % (e.args[0], e.args[1])
    sys.exit(1)

#===================================================================
# query select from table
#===================================================================

cursor = db.cursor()

sql = 'SELECT ext, SUM(size) AS totalsize, COUNT(*) AS filecount FROM fileindex GROUP BY ext ORDER BY totalsize DESC;'

cursor.execute(sql)
all_rows = cursor.fetchall()

print len(all_rows) # How many rows are returned.
for row in all_rows: # While loops always make me shudder!
    print '%s %s %s\n' % (row['ext'], row['totalsize'], row['filecount'])

cursor.close()
db.close()

Standard dictionary functions apply, for example, len(row[0]) to count the number of columns for the first row, list(row[0]) for a list of column names (for the first row), etc. Hope this helps!

answered Nov 1, 2016 at 22:56

Eugene

1,65915 silver badges21 bronze badges

2 Comments

panofish Over a year ago

Good point, but I was writing a utility where the query varies and I need to support the field order in the query. Dictionary cursor has no ordering to the fields.

Martin Burch Over a year ago

What we need is an ordereddict stackoverflow.com/questions/33504938/pymysql-and-ordereddict

MikeL · Accepted Answer · 2018-09-14 20:56:09Z

9

This is only an add-on to the accepted answer:

def get_results(db_cursor):
    desc = [d[0] for d in db_cursor.description]
    results = [dotdict(dict(zip(desc, res))) for res in db_cursor.fetchall()]
    return results

where dotdict is:

class dotdict(dict):
    __getattr__ = dict.get
    __setattr__ = dict.__setitem__
    __delattr__ = dict.__delitem__

This will allow you to access much easier the values by column names.
Suppose you have a user table with columns name and email:

cursor.execute('select * from users')
results = get_results(cursor)
for res in results:
  print(res.name, res.email)

answered Sep 14, 2018 at 20:56

MikeL

5,66145 silver badges44 bronze badges

Comments

Milovan Tomašević · Accepted Answer · 2020-09-28 13:31:31Z

6

Something similar to the proposed solutions, only the result is json with column_header : vaule for db_query ie sql.

cur = conn.cursor()
cur.execute(sql)
res = [dict((cur.description[i][0], value) for i, value in enumerate(row)) for row in cur.fetchall()]

output json example:

[
   {
      "FIRST_ROW":"Test 11",
      "SECOND_ROW":"Test 12",
      "THIRD_ROW":"Test 13"
   },
   {
      "FIRST_ROW":"Test 21",
      "SECOND_ROW":"Test 22",
      "THIRD_ROW":"Test 23"
   }
]

answered Sep 28, 2020 at 13:31

Milovan Tomašević

8,9812 gold badges58 silver badges49 bronze badges

Comments

Amin · Accepted Answer · 2019-05-21 14:51:07Z

3

Try:

cursor.column_names

mysql connector version:

mysql.connector.__version__
'2.2.9'

answered May 21, 2019 at 14:51

Amin

1119 bronze badges

1 Comment

alex Over a year ago

maybe it works for MySQL, but didn't work for sqlite for me.

Daniel DiPaolo · Accepted Answer · 2011-02-15 22:09:54Z

2

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

answered Feb 15, 2011 at 22:09

Daniel DiPaolo

56.6k14 gold badges120 silver badges117 bronze badges

Comments

Brian Minton · Accepted Answer · 2019-07-08 20:42:51Z

1

You can also do this to just get the field titles:

table = cursor.description
check = 0
for fields in table:
    for name in fields:
        if check < 1:
            print(name),
        check +=1
    check =0

edited Jul 8, 2019 at 20:42

Brian Minton

3,8573 gold badges38 silver badges43 bronze badges

answered Jul 8, 2019 at 20:20

Adam Rhoades

111 bronze badge

1 Comment

Cryo Over a year ago

Very nice idea thanks! I really wanted to get the columns as a result of a query, and your suggestion provides a nice way <cursor>.execute('describe <table>') and then fetch

Joooeey · Accepted Answer · 2023-01-19 17:15:14Z

1

cursor.column_names is a nice and simple one.

edited Jan 19, 2023 at 17:15

Joooeey

4,1653 gold badges44 silver badges59 bronze badges

answered Jan 19, 2023 at 15:22

Dan Lenehan

192 bronze badges

1 Comment

snakecharmerb Over a year ago

Already suggested in this answer from 2019.

Barmar · Accepted Answer · 2025-01-02 19:47:18Z

I'm newer to python but needed similar functionality on an API I'm building. Here is my call_proc function. Hopefully it helps someone.

# https://dev.mysql.com/doc/connector-python/en/
import mysql.connector

def call_proc(proc_args, proc_name):
    """
    Calls a stored procedure using the mysql.connector callproc method
    :param proc_args: a list of name value pair dictionaries containing the proc arguments
    :param proc_name: a string containing the name of the stored procedure
    :return: returns a list of result sets, each set being a list of rows, each row being
        a dictionary with the column name as the key and the value returned from mysql as 
        the value - this can be mapped to the standard json api response format in the 
        calling method (e.g. { data: { // json data here } }) 
    """

    # Establish a connection to the database
    mysql_connection = mysql.connector.connect(
      host="localhost",
      user="root",
      password="p@$$w0rd",
      database="databaseName"
    )

    # Create a cursor object
    mysql_cursor = mysql_connection.cursor()

    # Call the stored procedure
    mysql_cursor.callproc(proc_name, [arg.get('arg_value') for arg in proc_args])

    # Build response object - associate column names with returned values - works with 
    # multiple result sets
    response = [];

    # Iterate each result set in response
    for stored_result in mysql_cursor.stored_results():
        result_set = []

        row_names = stored_result.column_names

        # Iterate each row in the result set
        for row in stored_result.fetchall():
            result_row = {}

            # Iterate each item in the row
            for index, item in enumerate(row):
                result_row[row_names[index]] = item

            result_set.append(result_row)

        response.append(result_set)
        
    # Close the cursor and connection
    mysql_cursor.close()
    mysql_connection.close()

    return response

The question is about MySQLdb, not MySQL-Connector. It doesn't have cursor.column_names (or didn't when the question was written, I don't know if this has been added since then). What does this answer add that isn't in the other answers?

ThorGirl · Accepted Answer · 2021-11-23 06:17:56Z

-1

column_names = cursor.field_names

answered Nov 23, 2021 at 6:17

ThorGirl

1

1 Comment

snakecharmerb Over a year ago

This is the same as this existing answer

abhay patil · Accepted Answer · 2022-03-19 04:16:47Z

-1

found an easy way of having colums like sql using pymysql and pandas

import pymysql
import pandas as pd

db  = pymysql.connect(host="myhost", user="myuser", passwd="mypass", db="mydb")

query  = """SELECT ext
              SUM(size) as totalsize,
              COUNT(*) as filecount
            FROM fileindex
            GROUP BY ext 
            ORDER BY totalsize DESC;
         """
df = pd.read_sql_query(query,db)

the DataFrame will have column names ext,totalsize,filecount by default no need to do additional stuff.

for example in my case:

answered Mar 19, 2022 at 4:16

abhay patil

895 bronze badges

Collectives™ on Stack Overflow

MySQL: Get column name or alias from query

14 Answers 14

5 Comments

Comments

4 Comments

1 Comment

2 Comments

Comments

Comments

1 Comment

Comments

1 Comment

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

14 Answers 14

5 Comments

Comments

4 Comments

1 Comment

2 Comments

Comments

Comments

1 Comment

Comments

1 Comment

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related