execsql

Fork

A Python program that runs a SQL script stored in a text file against a PostgreSQL, MS-Access, SQLite, MS-SQL-Server, MySQL, MariaDB, Firebird, or Oracle database, or an ODBC DSN. execsql also supports a set of special commands (metacommands) that can import and export data, copy data between databases, conditionally execute SQL statements and metacommands, display data to the user, and prompt the user for additional information.

Different DBMSs and DBMS-specific client programs provide different and incompatible extensions to the SQL language, ordinarily to allow interactions with the file system and to allow conditional tests and looping. Some DBMSs do not have any native extensions of this sort. execsql provides these features, as well as features for user interaction, in an identical fashion for all supported DBMSs.

execsql's metacommands make up a control language that works the same across all supported DBMSs, and can be used to create both automated and interactive data processing applications. 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).

execsql also supports substitution variables: words that can be replaced with alternate text anywhere within a SQL statement or metacommand. Substitution variables can be used for configuration settings, data values extracted from the database, or user input.

Whereas SQL is often embedded in programs written in other languages, execsql inverts this paradigm through the use of metacommands embedded in SQL scripts. This approach allows database operations to be interleaved with user interactions and file system access in a way that may be easier to develop, easier to re-use, and more accessible to multiple users than embedded SQL in a high-level programming language.