returns different columns depending on my version of MySQL. How can I work out which column in the results corresponds to a particular column name? Can I get a list of column names?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
The query
SHOW TABLE STATUS;
returns different columns depending on my version of MySQL. How can I work out which column in the results corresponds to a particular column name? Can I get a list of column names?
That is an excellent question.
Python 2.4.1 (#1, Jun 19 2005, 02:36:59)
[GCC 3.4.4 (Gentoo 3.4.4, ssp-3.4.4-1.0, pie-8.7.8)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import MySQLdb
>>> db=MySQLdb.connect(read_default_file="~/.my.cnf",db='test')
>>> c=db.cursor()
>>> c.execute("SHOW TABLE STATUS")
8L
>>> c.description
(('Name', 253, 10, 64, 64, 0, 0), ('Engine', 253, 6, 64, 64, 0, 1), ('Version', 8, 2, 21, 21, 0, 1), ('Row_format', 253, 7, 10, 10, 0, 1), ('Rows', 8, 2, 21, 21, 0, 1), ('Avg_row_length', 8, 4, 21, 21, 0, 1), ('Data_length', 8, 5, 21, 21, 0, 1), ('Max_data_length', 8, 1, 21, 21, 0, 1), ('Index_length', 8, 1, 21, 21, 0, 1), ('Data_free', 8, 1, 21, 21, 0, 1), ('Auto_increment', 8, 2, 21, 21, 0, 1), ('Create_time', 12, 19, 19, 19, 0, 1), ('Update_time', 12, 0, 19, 19, 0, 1), ('Check_time', 12, 0, 19, 19, 0, 1), ('Collation', 253, 15, 64, 64, 0, 1), ('Checksum', 8, 0, 21, 21, 0, 1), ('Create_options', 253, 0, 255, 255, 0, 1), ('Comment', 253, 21, 80, 80, 0, 0))
>>> columns = [ t[0] for t in c.description ]
>>> print columns
['Name', 'Engine', 'Version', 'Row_format', 'Rows', 'Avg_row_length', 'Data_length', 'Max_data_length', 'Index_length', 'Data_free', 'Auto_increment', 'Create_time', 'Update_time', 'Check_time', 'Collation', 'Checksum', 'Create_options', 'Comment']
>>> for row in c:
... d = dict(zip(columns, row))
... print d
...
{'Engine': 'InnoDB', 'Row_format': 'Compact', 'Update_time': None, 'Rows': 16L, 'Checksum': None, 'Name': 'foo2', 'Check_time': None, 'Index_length': 0L, 'Auto_increment': 17L, 'Data_length': 16384L, 'Create_options': '', 'Avg_row_length': 1024L, 'Data_free': 0L, 'Version': 10L, 'Create_time': datetime.datetime(2005, 5, 11, 21, 57, 47), 'Collation': 'utf8_general_ci', 'Comment': 'InnoDB free: 11264 kB', 'Max_data_length': 0L}
...
>>>
Another solution is to use DictCursor:
>>> from MySQLdb.cursors import DictCursor
>>> c=db.cursor(DictCursor)
>>> c.execute("""SHOW TABLE STATUS""")
8L
>>> row = c.fetchone()
>>> print row
{'Engine': 'InnoDB', 'Row_format': 'Compact', 'Update_time': None, 'Rows': 16L, 'Checksum': None, 'Name': 'foo2', 'Check_time': None, 'Index_length': 0L, 'Auto_increment': 17L, 'Data_length': 16384L, 'Create_options': '', 'Avg_row_length': 1024L, 'Data_free': 0L, 'Version': 10L, 'Create_time': datetime.datetime(2005, 5, 11, 21, 57, 47), 'Collation': 'utf8_general_ci', 'Comment': 'InnoDB free: 11264 kB', 'Max_data_length': 0L}
>>> print row['Name']
foo2
>>>
(what can I say? I like it when people give me money.)