This is a pre-production deployment of Warehouse, however changes made here WILL affect the production instance of PyPI.
Latest Version Dependencies status unknown Test status unknown Test coverage unknown
Project Description

execsql.py is a Python program that applies a SQL script stored in a text file to a PostgreSQL, MS-Access, SQLite, MS-SQL-Server, MySQL, MariaDB, or Firebird database, or to an ODBC DSN. execsql.py also supports a set of special commands (metacommands) that can import and export data, copy data between databases, and conditionally execute SQL statements and metacommands. These metacommands make up a control language that works the same across all supported database management systems (DBMSs). The metacommands are embedded in SQL comments, so they will be ignored by other script processors (e.g., psql for Postgres and sqlcmd for SQL Server). The metacommands make up a toolbox that can be used to create both automated and interactive data processing applications.

Capabilities

You can use the execsql program to:

  • Import data from text files or OpenDocument spreadsheets into a database.
  • Copy data between different databases, even databases using different types of database management systems.
  • Export tables and views as formatted text, comma-separated values (CSV), tab-separated values (TSV), OpenDocument spreadsheets, HTML tables, JSON, LaTeX tables, or unformatted (e.g., binary) data.
  • View tables and views on the console or in a GUI dialog window.
  • Conditionally execute different SQL commands and metacommands based on the DBMS in use, the database in use, data values, user input, and other conditions. Conditional execution can be used with the INCLUDE metacommand to implement loops.
  • Implement cross-database transactions.
  • Use simple dynamically-created data entry forms to get user input.
  • Write messages to the console or to a file during the processing of a SQL script, using metacommands embedded in SQL comments. These messages can be used to display the progress of the script or create a custom log of the operations that have been carried out or results obtained. Status messages and data exported in text format can be combined in a single text file. Data tables can be exported in a text format that is compatible with Markdown pipe tables, so that script output can be converted into a variety of document formats.
  • Write more modular and maintainable SQL code by factoring repeated code out into separate scripts, parameterizing the code using substitution variables, and using the INCLUDE metacommand to merge the modules into a single script.
  • Standardize the SQL scripting language used for different types of database management systems.
  • Merge multiple elements of a workflow–e.g., data loading, summarization, and reporting–into a single script for better coupling of related steps and more secure maintenance.
  • Use “CREATE QUERY…” and “CREATE TEMPORARY QUERY…” statements in Access, which are not natively supported.

execsql is inherently a command-line program that can operate in a completely non-interactive mode (except for password prompts). Therefore, it is suitable for incorporation into a toolchain controlled by a shell script (on Linux), batch file (on Windows), or other system-level scripting application. When used in this mode, the only interactive elements will be password prompts; passwords are not accepted on the command line or as arguments to the CONNECT metacommand. However, several metacommands can be used to generate interactive prompts and data displays, so execsql scripts can be written to provide some user interactivity.

In addition, execsql automatically maintains a log that documents key information about each run of the program, including the databases that are used, the scripts that are run, and the user’s choices in response to interactive prompts. Together, the script and the log provide documentation of all actions carried out that may have altered data.

Requirements

The execsql program uses third-party Python libraries to communicate with different database and spreadsheet software. These libraries must be installed to use those programs with execsql. Only those libraries that are needed, based on the command line arguments and metacommands, must be installed. The libraries required for each database or spreadsheet application are:

  • PosgreSQL: psycopg2.
  • SQL Server: pydobc.
  • MS-Access: pydobc and pywin32.
  • MySQL or MariaDB: pymysql.
  • Firebird: fdb.
  • DSN connections: pyodbc.
  • OpenDocument spreadsheets: odfpy.

Connections to SQLite databases are made using Python’s standard library, so no additional software is needed.

Major Revisions

Version Date Features
1.8.14.0 2016-11-13 Added evaluation of numeric types in input. Added ‘empty_strings’ configuration parameter and metacommand.
1.8.13.0 2016-11-07 Added the “-b” command-line option and configuration parameter.
1.8.12.0 2016-10-22 Added the RM_SUB metacommand.
1.8.11.0 2016-10-19 Added the SET COUNTER metacommand.
1.8.10.2 2016-10-17 Added $RUN_ID system variable. Modified to recognize as text any imported data that contains only numeric values but where the first digit of any value is a zero.
1.8.8.0 2016-09-28 Added $CURRENT_ALIAS, $RANDOM, and $UUID system variables.
1.8.4.0 2016-08-13 Added logging of database close when autocommit is off. Added import from MS-Excel. Corrected parsing of numeric time zones.
1.7.3.0 2016-08-05 Added $OS system variable.
1.7.2.0 2016-06-11 Added DIRECTORY_EXISTS conditional and option to automatically make directories used by the EXPORT metacommand.
1.7.0.0 2016-05-20 Added NEWER_DATE and NEWER_FILE conditionals.
1.6.0.0 2016-05-15 Added CONSOLE SAVE metacommand. Added DSN connections. Added COPY QUERY and EXPORT QUERY metacommands.
1.4.0.0 2016-04-30 Added a GUI console with a status bar and progress bar to which WRITE output and ex exported text will be written.
1.3.3.0 2016-04-09 Additions to ‘Save as…’ options in PROMPT DISPLAY metacommand, and date/time values exported to ODS.
1.3.2.0 2016-02-28 Enabled the use of a backslash as a line continuation character for SQL statements.
1.3.1.0 2016-02-20 Added PROMPT ENTRY_FORM and LOG metacommands.
1.2.15.0 2016-02-14 Added $DB_NAME, $DB_NEED_PWD, $DB_SERVER, and $DB_USER system variables. Added RAW as an export format for binary data. Added a PASSWORD keyword to the PROMPT ENTER_SUB metacommand. Allowed a password to be used in the CONNECT metacommand for Access. Other minor improvements and debugging.
1.2.10.0 2016-01-23 Added ENCODING keyword to IMPORT metacommand. Added TIMER metacommand and $TIMER system variable.
1.2.8.0 2016-01-11 Suppressed column headers when EXPORTing to CSV and TSV with APPEND.
1.2.7.1 2016-01-03 Modified import of integers to Postgres; added the AUTOCOMMIT metacommand and modified the BATCH metacommand; changed to explicitly roll back any uncommitted changes on exit; miscellaneous debugging.
1.2.4.0 2015-11-21 Added connections to PostgreSQL, SQL Server, MySQL, MariaDB, SQLite, and Firebird. Added numerous metacommands and conditional tests. Added reading of configuration files.
0.4.4.0 2010-06-20 Added INCLUDE, WRITE, EXPORT, SUB, EXECUTE, HALT, and IF (HASROWS, SQL_ERROR) metacommands
0.3.1.0 2008-12-19 Executes SQL against Access, captures output of the last statement.
Release History

Release History

1.8.14.0

This version

History Node

TODO: Figure out how to actually get changelog content.

Changelog content for this version goes here.

Donec et mollis dolor. Praesent et diam eget libero egestas mattis sit amet vitae augue. Nam tincidunt congue enim, ut porta lorem lacinia consectetur. Donec ut libero sed arcu vehicula ultricies a non tortor. Lorem ipsum dolor sit amet, consectetur adipiscing elit.

Show More

1.8.13.0

History Node

TODO: Figure out how to actually get changelog content.

Changelog content for this version goes here.

Donec et mollis dolor. Praesent et diam eget libero egestas mattis sit amet vitae augue. Nam tincidunt congue enim, ut porta lorem lacinia consectetur. Donec ut libero sed arcu vehicula ultricies a non tortor. Lorem ipsum dolor sit amet, consectetur adipiscing elit.

Show More

1.8.12.1

History Node

TODO: Figure out how to actually get changelog content.

Changelog content for this version goes here.

Donec et mollis dolor. Praesent et diam eget libero egestas mattis sit amet vitae augue. Nam tincidunt congue enim, ut porta lorem lacinia consectetur. Donec ut libero sed arcu vehicula ultricies a non tortor. Lorem ipsum dolor sit amet, consectetur adipiscing elit.

Show More

1.8.12.0

History Node

TODO: Figure out how to actually get changelog content.

Changelog content for this version goes here.

Donec et mollis dolor. Praesent et diam eget libero egestas mattis sit amet vitae augue. Nam tincidunt congue enim, ut porta lorem lacinia consectetur. Donec ut libero sed arcu vehicula ultricies a non tortor. Lorem ipsum dolor sit amet, consectetur adipiscing elit.

Show More

1.8.11.0

History Node

TODO: Figure out how to actually get changelog content.

Changelog content for this version goes here.

Donec et mollis dolor. Praesent et diam eget libero egestas mattis sit amet vitae augue. Nam tincidunt congue enim, ut porta lorem lacinia consectetur. Donec ut libero sed arcu vehicula ultricies a non tortor. Lorem ipsum dolor sit amet, consectetur adipiscing elit.

Show More

1.8.10.1

History Node

TODO: Figure out how to actually get changelog content.

Changelog content for this version goes here.

Donec et mollis dolor. Praesent et diam eget libero egestas mattis sit amet vitae augue. Nam tincidunt congue enim, ut porta lorem lacinia consectetur. Donec ut libero sed arcu vehicula ultricies a non tortor. Lorem ipsum dolor sit amet, consectetur adipiscing elit.

Show More

1.8.10.0

History Node

TODO: Figure out how to actually get changelog content.

Changelog content for this version goes here.

Donec et mollis dolor. Praesent et diam eget libero egestas mattis sit amet vitae augue. Nam tincidunt congue enim, ut porta lorem lacinia consectetur. Donec ut libero sed arcu vehicula ultricies a non tortor. Lorem ipsum dolor sit amet, consectetur adipiscing elit.

Show More

1.8.4.0

History Node

TODO: Figure out how to actually get changelog content.

Changelog content for this version goes here.

Donec et mollis dolor. Praesent et diam eget libero egestas mattis sit amet vitae augue. Nam tincidunt congue enim, ut porta lorem lacinia consectetur. Donec ut libero sed arcu vehicula ultricies a non tortor. Lorem ipsum dolor sit amet, consectetur adipiscing elit.

Show More

1.7.3.0

History Node

TODO: Figure out how to actually get changelog content.

Changelog content for this version goes here.

Donec et mollis dolor. Praesent et diam eget libero egestas mattis sit amet vitae augue. Nam tincidunt congue enim, ut porta lorem lacinia consectetur. Donec ut libero sed arcu vehicula ultricies a non tortor. Lorem ipsum dolor sit amet, consectetur adipiscing elit.

Show More

1.7.2.2

History Node

TODO: Figure out how to actually get changelog content.

Changelog content for this version goes here.

Donec et mollis dolor. Praesent et diam eget libero egestas mattis sit amet vitae augue. Nam tincidunt congue enim, ut porta lorem lacinia consectetur. Donec ut libero sed arcu vehicula ultricies a non tortor. Lorem ipsum dolor sit amet, consectetur adipiscing elit.

Show More

1.7.2.0

History Node

TODO: Figure out how to actually get changelog content.

Changelog content for this version goes here.

Donec et mollis dolor. Praesent et diam eget libero egestas mattis sit amet vitae augue. Nam tincidunt congue enim, ut porta lorem lacinia consectetur. Donec ut libero sed arcu vehicula ultricies a non tortor. Lorem ipsum dolor sit amet, consectetur adipiscing elit.

Show More

1.7.0.1

History Node

TODO: Figure out how to actually get changelog content.

Changelog content for this version goes here.

Donec et mollis dolor. Praesent et diam eget libero egestas mattis sit amet vitae augue. Nam tincidunt congue enim, ut porta lorem lacinia consectetur. Donec ut libero sed arcu vehicula ultricies a non tortor. Lorem ipsum dolor sit amet, consectetur adipiscing elit.

Show More

1.7.0.0

History Node

TODO: Figure out how to actually get changelog content.

Changelog content for this version goes here.

Donec et mollis dolor. Praesent et diam eget libero egestas mattis sit amet vitae augue. Nam tincidunt congue enim, ut porta lorem lacinia consectetur. Donec ut libero sed arcu vehicula ultricies a non tortor. Lorem ipsum dolor sit amet, consectetur adipiscing elit.

Show More

1.6.0.0

History Node

TODO: Figure out how to actually get changelog content.

Changelog content for this version goes here.

Donec et mollis dolor. Praesent et diam eget libero egestas mattis sit amet vitae augue. Nam tincidunt congue enim, ut porta lorem lacinia consectetur. Donec ut libero sed arcu vehicula ultricies a non tortor. Lorem ipsum dolor sit amet, consectetur adipiscing elit.

Show More

1.4.4.0

History Node

TODO: Figure out how to actually get changelog content.

Changelog content for this version goes here.

Donec et mollis dolor. Praesent et diam eget libero egestas mattis sit amet vitae augue. Nam tincidunt congue enim, ut porta lorem lacinia consectetur. Donec ut libero sed arcu vehicula ultricies a non tortor. Lorem ipsum dolor sit amet, consectetur adipiscing elit.

Show More

1.4.2.0

History Node

TODO: Figure out how to actually get changelog content.

Changelog content for this version goes here.

Donec et mollis dolor. Praesent et diam eget libero egestas mattis sit amet vitae augue. Nam tincidunt congue enim, ut porta lorem lacinia consectetur. Donec ut libero sed arcu vehicula ultricies a non tortor. Lorem ipsum dolor sit amet, consectetur adipiscing elit.

Show More

1.4.0.0

History Node

TODO: Figure out how to actually get changelog content.

Changelog content for this version goes here.

Donec et mollis dolor. Praesent et diam eget libero egestas mattis sit amet vitae augue. Nam tincidunt congue enim, ut porta lorem lacinia consectetur. Donec ut libero sed arcu vehicula ultricies a non tortor. Lorem ipsum dolor sit amet, consectetur adipiscing elit.

Show More

1.3.3.0

History Node

TODO: Figure out how to actually get changelog content.

Changelog content for this version goes here.

Donec et mollis dolor. Praesent et diam eget libero egestas mattis sit amet vitae augue. Nam tincidunt congue enim, ut porta lorem lacinia consectetur. Donec ut libero sed arcu vehicula ultricies a non tortor. Lorem ipsum dolor sit amet, consectetur adipiscing elit.

Show More

1.3.2.0

History Node

TODO: Figure out how to actually get changelog content.

Changelog content for this version goes here.

Donec et mollis dolor. Praesent et diam eget libero egestas mattis sit amet vitae augue. Nam tincidunt congue enim, ut porta lorem lacinia consectetur. Donec ut libero sed arcu vehicula ultricies a non tortor. Lorem ipsum dolor sit amet, consectetur adipiscing elit.

Show More

Download Files

Download Files

TODO: Brief introduction on what you do with files - including link to relevant help section.

File Name & Checksum SHA256 Checksum Help Version File Type Upload Date
execsql-1.8.14.0.tar.gz (202.3 kB) Copy SHA256 Checksum SHA256 Source Nov 13, 2016

Supported By

WebFaction WebFaction Technical Writing Elastic Elastic Search Pingdom Pingdom Monitoring Dyn Dyn DNS HPE HPE Development Sentry Sentry Error Logging CloudAMQP CloudAMQP RabbitMQ Heroku Heroku PaaS Kabu Creative Kabu Creative UX & Design Fastly Fastly CDN DigiCert DigiCert EV Certificate Rackspace Rackspace Cloud Servers DreamHost DreamHost Log Hosting