Automated Comparison of Data in Base and Staging Tables

When new data have been received and prepared for merging into existing database tables, a review of the changes to be made is often warranted prior to merging the data.  A set of execsql.py scripts to compare data in base and staging tables is available at https://osdn.net/projects/execsql-compare/.  These scripts use the information_schema views of the database to identify primary key and attribute columns, and to generate the SQL to perform different types of comparisons.  These scripts will therefore work without any customization on any table of any database for any of the supported DBMSs.  Currently supported DBMSs are PostgreSQL, MariaDB/MySQL, and SQL Server.

More extensive documentation, including illustrations of the type of output that can be produced, is available at http://execsql-compare.osdn.io/.

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.

Simplified Creation of a Data Summary Glossary

Data summaries or tables that are exported from a database may use column names that are abbreviated and possibly unclear to some users of the data.  Data exports may also include coded values that are not fully defined within the data table itself.  Definitions of those terms can be helpful to users of the data. One way of providing those definitions is to produce a custom glossary to accompany each data export. Execsql scripts to simplify the creation of such a custom glossary table are available at the following Bitbucket repository: https://bitbucket.org/rdnielsen/execsql_glossary/src/default/.

These scripts require a master glossary table that contains terms and definitions that are to be included in the custom glossary.  The master glossary table can be in a different database than the one from which data are being summarized.  Additional terms and definitions, that are not in the master glossary, can also be added to the custom glossary.  After calling an initialization script that identifies the master glossary table, entries can be added to the custom glossary in any of the following ways:

• By naming a table or view; the column names and definitions will be added to the custom glossary if they are defined in the master glossary.
• By providing a string containing a comma-separated list of terms; all of the terms that have definitions in the master glossary will be added to the custom glossary.
• By providing a term and defintion; these will be added to the custom glossary regardless of whether the term is defined in the master glossary.

A view named glossary is automatically created that selects all items in the custom glossary.

Glossary-creation scripts are available for:

• Postgres
• MariaDB/MySQL
• MS-SQL Server.

Complete documentation of these scripts is available at https://execsql-glossary.readthedocs.io/https://execsql-glossary.readthedocs.io/.

Automation of Upsert Operations with QA Checks and Logging

Scripts that extend the data merging technique illustrated in the post Driving Data Table Merges from the Information Schema add the following capabilities:

• The upsert operation can be applied to multiple tables simultaneously, and will execute SQL update and insert statements in top-down order to maintain referential integrity among the tables.

• Prior to updating data in the base tables with data in the staging tables, the scripts check for:
• Null values in the columns of each staging table that must be non-null in the corresponding base table.
• Duplicate primary key values in the staging tables.
• Invalid foreign keys in the staging tables; foreign key values are checked against both the appropriate base table and any other appropriate staging table.

• No changes are made to the base tables if there are any violations of the non-null checks, primary key checks, or foreign key checks carried out on the staging tables.

• If QA violations are found, the results of all of the QA checks are returned in a table that identifies, for each base table to be updated, each type of QA violation that was found, and the number of such violations (e.g., for foreign key violations) or the number of rows affected in the staging table.

• The results of a successful upsert operation are returned in a table that identifies, for each base table to be updated, the number of rows updated and the number of rows inserted.

• The changes to be made to each base table (updates and inserts) may optionally be displayed in a GUI interface so that the user can review them and approve or disapprove each set of changes.

• All of the steps that are carried out by the scripts (QA checks, update operations, and insert operations) can be logged in a custom text file.  The log can optionally include each SQL statement used to update the database, and also include the data values that are used for each update and insert operation.

• If an execsql console is active, the scripts will use the console's status bar and progress bar to indicate the activity underway.

These scripts do not require any customization for different data models.  They can be applied to any tables in any database in any DBMS that supports the standard SQL information_schema views.  Some operations carried out by these scripts are DBMS-specific, however (such as string aggregation), so the features listed above are implemented in DBMS-specific scripts.  These features have been implemented in scripts for:

• Postgres
• MariaDB/MySQL
• MS-SQL Server.

These scripts are too lengthy to include in this blog post, but are available from the Bitbucket repository at https://bitbucket.org/rdnielsen/execsql_upsert/src/default/.  More complete documentation is available at ReadTheDocs.

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.