MySQL: Get column name or alias from query
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()
Solution 1:
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]
Solution 2:
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)
Solution 3:
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)
)