Runs a SQL script against a PostgreSQL, MS-Access, SQLite, MS-SQL-Server, MySQL, MariaDB, or Firebird database, or an ODBC DSN. Provides metacommands to import and export data, copy data between databases, conditionally execute SQL and metacommands, and dynamically alter SQL and metacommands with substitution variables. Data can be exported in 13 different formats, including CSV, TSV, ODS, HTML, JSON, LaTeX, and Markdown tables, and using custom templates.
execsql.py is a Python program that runs a SQL script stored in a text file against 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 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.
You can use the execsql program to:
Standard SQL provides no features for interacting with external files or with the user, or for controlling the flow of actions to be carried out based either on data or on user input. execsql provides these features in a way that operates identically across all supported DBMSs on both Linux and Windows.
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. 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.
The documentation includes more than 20 examples showing the use of execsql’s metacommands, in both simple and complex scripts.
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:
Connections to SQLite databases are made using Python’s standard library, so no additional software is needed.
|184.108.40.206||2017-07-04||Passed headers to template processors as a separate object.|
|220.127.116.11||2017-07-01||Added the EMAIL, SUB_ENCRYPT, and SUB_DECRYPT metacommands, and configuration proerties to support emailing. Added the METACOMMAND_ERROR_HALT metacommand, the $METACOMMAND_ERROR_HALT_STATE system variable, and the METACOMMAND_ERROR() conditional. Extended the EXPORT metacommand to allow use of different template processors.|
|18.104.22.168||2017-06-24||Improved speed of IMPORT metacommand for CSV files imported to Postgres and MySQL/MariaDB. Modified the EXPORT…APPEND…AS HTML metacommand to append tables inside the (first) </body> tag.|
|22.214.171.124||2017-05-28||Modified the specifications for the PROMPT ENTRY_FORM to allow checkboxes to be used.|