How can I get column types width MySQLdb?

Question

Hello, how can I get text description for column types with MySQLdb?

I know that cursor.description contains number which represents type of column and also there is module FIELD_TYPE.* with int constants like FIELD_TYPE.ENUM=247

For example how can I get name of column type if I know that it is '3'?

Answer 1

select * from information_schema.tables

contains everything you wanna know about tables :) .. example output (i used the \G switch for prettier formatting):

....
*************************** 87. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: phpmyadmin
     TABLE_NAME: pma_table_coords
     TABLE_TYPE: BASE TABLE
         ENGINE: MyISAM
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 29
 AVG_ROW_LENGTH: 39
    DATA_LENGTH: 1156
MAX_DATA_LENGTH: 281474976710655
   INDEX_LENGTH: 4096
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2010-02-09 11:19:59
    UPDATE_TIME: 2010-08-15 19:21:37
     CHECK_TIME: 2010-08-16 18:34:18
TABLE_COLLATION: utf8_bin
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: Table coordinates for phpMyAdmin PDF output
...

SHOW COLUMNS FROM table; displays everything you wanna know about columsn...

mysql> show columns from pma_designer_coords;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| db_name    | varchar(64) | NO   | PRI |         |       |
| table_name | varchar(64) | NO   | PRI |         |       |
| x          | int(11)     | YES  |     | NULL    |       |
| y          | int(11)     | YES  |     | NULL    |       |
| v          | tinyint(4)  | YES  |     | NULL    |       |
| h          | tinyint(4)  | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

Answer 2

Read here for the list of column types:

http://mysql-python.sourceforge.net/MySQLdb-1.2.2/public/MySQLdb.constants.FIELD_TYPE-module.html

and then create a dictionary from the provided list:

field_type = {
 0: 'DECIMAL',
 1: 'TINY',
 2: 'SHORT',
 3: 'LONG',
 4: 'FLOAT',
 5: 'DOUBLE',
 6: 'NULL',
 7: 'TIMESTAMP',
 8: 'LONGLONG',
 9: 'INT24',
 10: 'DATE',
 11: 'TIME',
 12: 'DATETIME',
 13: 'YEAR',
 14: 'NEWDATE',
 15: 'VARCHAR',
 16: 'BIT',
 246: 'NEWDECIMAL',
 247: 'INTERVAL',
 248: 'SET',
 249: 'TINY_BLOB',
 250: 'MEDIUM_BLOB',
 251: 'LONG_BLOB',
 252: 'BLOB',
 253: 'VAR_STRING',
 254: 'STRING',
 255: 'GEOMETRY' }