cursor.description“type_code”与数据库字段类型的对应关系

Using a MySQL back-end and basically want to determine the field type of tables from the type_code in the cursor.description tuples...

使用MySQL后端,基本上想要从cursor.description元组中的type_code确定表的字段类型...

What I get is a bunch of different numbers... and by comparing my tables with the type_code values I can manually put together a set of correspondences... but I have many more types than the type objects documented in my Python book (Beazley), namely STRING, BINARY, NUMBER, DATETIME, ROWID.

我得到的是一堆不同的数字......通过将我的表与type_code值进行比较,我可以手动将一组对应关系放在一起......但是我的类型对象比我的Python书中记录的类型对象多得多(Beazley) ),即STRING,BINARY,NUMBER,DATETIME,ROWID。

I presume there are therefore different type_codes being given to things like DECIMAL, UNSIGNED INT, etc... but I'm just surprised not to be able to find any info out here or on the Net generally.

因此我认为因为DECIMAL,UNSIGNED INT等等给出了不同的type_codes ...但我很惊讶不能在这里或网上找到任何信息。

What I want to do, by the way, is to automate the process whereby input (in a GUI grid connected to a MySQL table for example) determines what type of data the table is expecting for that column, and parses and checks it to find out whether this is a legal value.

顺便说一下,我想要做的是自动化进程,在该过程中,输入(例如,在连接到MySQL表的GUI网格中)确定表对该列期望的数据类型,并解析并检查它以查找这是否是合法的价值。

3 个解决方案

#1

The basic type codes as described in your book are defined by the DB-API specification.

书中描述的基本类型代码由DB-API规范定义。

The type_code must compare equal to one of Type Objects defined below.

type_code必须等于下面定义的Type对象之一。

The trick here is that there can be multiple different type codes that all compare equal to the same type object.

这里的技巧是可以有多个不同的类型代码,它们都比较相同的类型对象。

>>> MySQLdb.constants.FIELD_TYPE.TIMESTAMP
7
>>> MySQLdb.constants.FIELD_TYPE.DATETIME
12
>>> MySQLdb.constants.FIELD_TYPE.TIMESTAMP==MySQLdb.DATETIME
True
>>> MySQLdb.constants.FIELD_TYPE.DATETIME==MySQLdb.DATETIME
True
>>> MySQLdb.DATETIME
DBAPISet([12, 7])

(How this magic is implemented is outlined in the note about DBAPITypeObject in the DB-API spec. A more conventional interface might have done this with subclasses...)

(在DB-API规范中关于DBAPITypeObject的注释中概述了如何实现这种魔法。更传统的接口可能已经用子类完成了这个...)

This allows MySQLdb to offer richer information about the column than just whether it's a date-and-time type, whilst still allowing a simple test for a string vs a number.

这允许MySQLdb提供有关列的更丰富的信息,而不仅仅是它是一个日期和时间类型,同时仍然允许对字符串与数字进行简单测试。

Of course if you start comparing against MySQLdb.constants.FIELD_TYPE types directly you are relying on MySQLdb functionality that won't port to other databases.

当然,如果您开始直接与MySQLdb.constants.FIELD_TYPE类型进行比较,那么您将依赖于不会移植到其他数据库的MySQLdb功能。

#2

If you are using MySQLdb, then the MySQLdb.constants.FIELD_TYPE module contains constants for each field type.

如果您使用的是MySQLdb,则MySQLdb.constants.FIELD_TYPE模块包含每种字段类型的常量。

>>> print dir(MySQLdb.constants.FIELD_TYPE)
['BIT', 'BLOB', 'CHAR', 'DATE', 'DATETIME', 'DECIMAL', 'DOUBLE', 'ENUM', 
 'FLOAT', 'GEOMETRY', 'INT24', 'INTERVAL', 'LONG', 'LONGLONG', 'LONG_BLOB', 
 'MEDIUM_BLOB', 'NEWDATE', 'NEWDECIMAL', 'NULL', 'SET', 'SHORT', 'STRING', 
 'TIME', 'TIMESTAMP', 'TINY', 'TINY_BLOB', 'VARCHAR', 'VAR_STRING', 'YEAR',
 '__builtins__', '__doc__', '__file__', '__name__', '__package__']

For example, the a typecode of 5 indicates it is a MySQL double

例如,一个5的类型代码表明它是一个MySQL双

>>> MySQLdb.constants.FIELD_TYPE.DOUBLE 
5

This module is noted in the documentation.

该模块在文档中说明。

#3

If you are interested in getting a dictionary of how ids map to descriptions:

如果您有兴趣获取有关ID如何映射到描述的字典:

>>> ft = MySQLdb.constants.FIELD_TYPE
>>> d = {getattr(ft, k): k for k in dir(ft) if not k.startswith('_')}
>>> d
{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'}

#1