According to the Python DB API, one of the fields in cursor.description returns a type_code; however these are always numbers. How do I convert from type_code (eg. 1) to a data type (eg. TEXT)?
2 Answers
The numbers appear to correspond to the Python data type of the items in the row tuple(s) returned by the Cursor object, namely:
1 : STRING type
2 : BINARY type
3 : NUMBER type
4 : DATETIME type
5 : ROWID type
2 Comments
marengaz
is there a built in enum or something i can use to map these datatypes? similar to MySQLdb.constants.FIELD_TYPE
Adam Tauno Williams
Note that type 3 "NUMBER" can be either an integer or a floating point value. When I have a value type of DECIMAL in the result the type code returned is 5.
As of pymssql 2.3.8, the get_api_coltype() function
translates the fine-grained type codes ("DB-LIB types" in pymssql into the coarser DB-API types:
cdef int get_api_coltype(int coltype):
if coltype in (SQLBIT, SQLINT1, SQLINT2, SQLINT4, SQLINT8, SQLINTN,
SQLFLT4, SQLFLT8, SQLFLTN):
return NUMBER
elif coltype in (SQLMONEY, SQLMONEY4, SQLMONEYN, SQLNUMERIC,
SQLDECIMAL):
return DECIMAL
elif coltype in (SQLDATETIME, SQLDATETIM4, SQLDATETIMN):
return DATETIME
elif coltype in (SQLVARCHAR, SQLCHAR, SQLTEXT):
return STRING
else:
return BINARY
The fine-grained type codes come from the TDS wire protocol (used by Microsoft SQL Server and its predecessor Sybase), and the source code for FreeTDS (which pymssql is based on), contains a list of their numerical values in tds_type_names.
The relatively large number of data types supported by Microsoft SQL Server are thus combined into a smaller set of groupings defined by Python's DB-API:
STRING(1)NUMBER(2)DATETIME(3)BINARY(4)DECIMAL(5)1
These 5 values are what users of pymssql will find in the cursor.description tuples. For example:
(('StringCol', 1, None, None, None, None, None),
('BooleanCol', 3, None, None, None, None, None),
('NumCol1', 3, None, None, None, None, None),
('NumCol2', 3, None, None, None, None, None))
Unfortunately, there does not seem to be a way to access these fine-grained type codes from current versions of pymssql, though it seems that it would be technically quite simple to add this feature.
1 An earlier answer said that typecode 5 should be interpreted as ROWID, but the pymssql source code clearly shows that it should be interpreted as meaning "decimal."
Comments
