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 Type	Postgres	MariaDB	SQL Server	Firebird	MS-Access	SQLite
Timestamp with time zone	1184
Timestamp	1184	7		type 'datetime.datetime'		None
Datetime		12	class 'datetime.datetime'		class 'datetime.datetime'	None
Date	1082	10	class 'datetime.date'	type 'datetime.date'	class 'datetime.datetime'	None
Time	1083	11	class 'datetime.time'	type 'datetime.time'	class 'datetime.datetime'	None
Boolean	16	16	class 'bool'		class 'bool'
Small integer	21	1	class 'int'		class 'int'
Integer	23	2	class 'int'	type 'int'	class 'int'	None
Long integer	20	3	class 'int'	type 'long'		None
Single	701	4	class 'float'	type 'float'	class 'float'	None
Double precision	701	5	class 'float'	type 'float'	class 'float'	None
Decimal	1700	0	class 'decimal.Decimal'	class 'decimal.Decimal'		None
Currency	790			class 'decimal.Decimal'	class 'decimal.Decimal'
Character	1042		class 'str'	type 'str'	class 'str'	None
Character varying	1043	15	class 'str'	type 'str'	class 'str'	None
Text	25		class 'str'	type 'str'	class 'str'	None
Binary / BLOB	17	249,250,251,252	class '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.

execsql

execsql is a Python application that reads a text file of SQL commands and executes them against a database. The supported databases are:

• PostgreSQL
• MS-Access
• MS-SQL Server
• SQLite
• MySQL or MariaDB
• Firebird
• ODBC DSN connections.

In addition to executing SQL statements, execsql also implements a number of metacommands that allow:

• Import of data from text files and OpenDocument spreadsheets
• Export of data to delimited text, HTML, JSON, LaTeX tables, and OpenDocument spreadsheets
• Copying of data between databases, even of different DBMS types
• Conditional execution of SQL code and metacommands based on data values or user input.

execsql allows variables to be defined and used to dynamically modify SQL code and metacommands. An INCLUDE metacommand can be used to modularize SQL scripts and facilitate code re-use. Variables can be used to parameterize included scripts. Conditional inclusion of scripts can be used to implement loops. Automatically incrementing counter variables can be used to control loops or generate unique values to be used in input or output.

execsql is fundamentally a command-line application that can be used to automate database operations, either by itself or as part of a toolchain that may include other steps to either pre-process data before loading, or post-process or analyze data that are extracted from a database. However, execsql also has the ability to present data to the user in dialog boxes, request several types of input from the user either on the terminal or in GUI dialogs, and display messages to the user either on the terminal or in a GUI console. These features allow flexible interactive applications to be created.

A guiding principle in the development of execsql is documentation of all data operations. The use of SQL scripts, rather than ad-hoc interactive operations in a GUI, is key to meeting that goal. In addition, execsql logs all usage information, including databases used, script files executed (including script file version information), variable assignments, user input, and errors. Chain-of-custody procedures are used in some disciplines to ensure traceability from collection through (typically) laboratory analysis. However, no formal chain of custody procedures ordinarily are applied to data during or after entry into a database. SQL script files and execsql log files can be used to produce a complete record of data operations so that traceability of data can be extended into the database environment.

execsql and its documentation are available from the Python Package Index (PyPI). Note that the search functionality in PyPI and in pip search is broken, and will return links to obsolete versions of execsql that are no longer available. The direct link will take you to the latest available version.