Wednesday, September 18, 2019

Python DB-API 'type_code' Values

The Python DB-API specifies seven cursor attributes, the second of which is a type_code that should describe the data type of the column.  The DB-API specifies a set of type objects that should be used for the type_code value.  However, different DBMSs report various types of values for the type codes.  The type of information provided as the type_code by several different DBMSs, for several common data types, is shown in the following table.

Data TypePostgresMariaDBSQL ServerFirebirdMS-AccessSQLite
Timestamp with time zone1184
Timestamp11847type 'datetime.datetime'None
Datetime12class 'datetime.datetime'class 'datetime.datetime'None
Date108210class ''type ''class 'datetime.datetime'None
Time108311class 'datetime.time'type 'datetime.time'class 'datetime.datetime'None
Boolean1616class 'bool'class 'bool'
Small integer211class 'int'class 'int'
Integer232class 'int'type 'int'class 'int'None
Long integer203class 'int'type 'long'None
Single7014class 'float'type 'float'class 'float'None
Double precision7015class 'float'type 'float'class 'float'None
Decimal17000class 'decimal.Decimal'class 'decimal.Decimal'None
Currency790class 'decimal.Decimal'class 'decimal.Decimal'
Character1042class 'str'type 'str'class 'str'None
Character varying104315class 'str'type 'str'class 'str'None
Text25class 'str'type 'str'class 'str'None
Binary / BLOB17249,250,251,252class 'bytearray'type 'str'type 'bytearray'None

The Python data types are for Python 2; for Python 3, the 'long' data type will be 'int' instead.

The following libraries were used to connect to the DBMSs:
  • Postgres: psycopg2
  • MariaDB: pymysql
  • SQL Server: pyodbc
  • Firebird: fdb
  • MS-Access: pyodbc
  • SQLite: sqlite3

The integer type_code values used by Postgres are the same as those in the pg_types system table, except for single-precision floating point and character data types, for which the pg_types values are 700 and 8, respectively.

No comments:

Post a Comment