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

User · Answer

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

User · Answer

Try   cursor column names   mysql connector version   mysql connector   version    2 2 9

User · Answer

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

User · 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     pprint pprint result

User · Answer

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

User · Answer

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

User · 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    gt  1024 and x  filesize    lt  4096  result    or accumulate values for filtered columns   totalTxtSize   reduce          lambda x y  x y          filter lambda x  x  ext   lower       txt   result

User · Answer

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  lt  1              print name           check   1     check  0

User · Answer

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

User · Answer

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                quot FIRST ROW quot   quot Test 11 quot          quot SECOND ROW quot   quot Test 12 quot          quot THIRD ROW quot   quot Test 13 quot                    quot FIRST ROW quot   quot Test 21 quot          quot SECOND ROW quot   quot Test 22 quot          quot THIRD ROW quot   quot Test 23 quot

[python] MySQL: Get column name or alias from query

Examples related to python

Examples related to mysql

Examples related to mysql-python