CODEWITHSUNDEEP
mysqlsql
samuelbrody1249
samuelbrody1249

Reputation: 4767

Mysql field and data type from query

to get the field name in mysql I can grab it from cursor.desription:

>>> self.cursor.description                                  
# (('date', 253, 0, 30, 30, 31, 0), ('name', 253, 0, 768, 768, 0, 0), ('content_type_id', 253, 0, 90, 90, 0, 1), ('release_year', 2, 0, 6, 6, 0, 1))                                   
>>> fields = [item[0] for item in self.cursor.description]

Is there a way to get the inferred data type, for example DATE, VARCHAR, etc. ?

I am guessing it is the second column as all varchars are 253, but what are all the type codes?

Upvotes: 0

Views: 86

Answers (1)

danblack
danblack

Reputation: 14666

The description returns:

(column_name,
 type,
 None,
 None,
 None,
 None,
 null_ok,
 column_flags)

The less documented codes for the type and the easier to read version

  DECIMAL = 0x00,
  TINY = 0x01,
  SHORT = 0x02,
  LONG = 0x03,
  FLOAT = 0x04,
  DOUBLE = 0x05,
  NULL_ = 0x06,
  TIMESTAMP = 0x07,
  LONGLONG = 0x08,
  INT24 = 0x09,
  DATE = 0x0a,
  TIME = 0x0b,
  DATETIME = 0x0c,
  YEAR = 0x0d,
  NEWDATE = 0x0e,
  VARCHAR = 0x0f,
  BIT = 0x10,
  MYSQL_TYPE_TIMESTAMP2 = 0x11, /* added */
  MYSQL_TYPE_JSON = 0xf5, /* added */
  NEWDECIMAL = 0xf6, /* corrected based on harder to read  - https://bugs.mysql.com/bug.php?id=99416 */
  ENUM = 0xf7,
  SET = 0xf8,
  TINY_BLOB = 0xf9,
  MEDIUM_BLOB = 0xfa,
  LONG_BLOB = 0xfb,
  BLOB = 0xfc,
  VAR_STRING = 0xfd,
  STRING = 0xfe,
  GEOMETRY = 0xff

update: easier to read version had bug differences - https://bugs.mysql.com/bug.php?id=99416

Upvotes: 1

Related Questions