Configuration Files¶

Configuration files can supply most of the same settings as command-line options (plus some additional ones); only the script name itself must be on the command line.

execsql automatically reads up to four execsql.conf files, if present, from these locations in order:

The system-wide application data directory. This is /etc on Linux, and %APPDATA% on Windows.

The user-specific configuration directory. This is a directory named .config under the user's home directory on both Linux and Windows.

The directory where the script file is located.

The directory from which execsql was started.

Later files augment or override earlier ones; CLI arguments override the merged result.

An explicit configuration file can also be specified with the --config FILE command-line option. This file is loaded after all four implicit search paths, so its values take precedence over system, user, script-directory, and working-directory config files. CLI arguments still override everything. The --config file may chain additional configs via its [config] section, just like any other config file.

To generate a starter configuration file with all options commented out and documented, use:

execsql --init-config > execsql.conf

In addition, execsql will read additional configuration files if they are specified in any of the standard configuration files (see below).

Configuration files use the INI file format. Section names are case sensitive and must be all in lowercase. Property names are not case sensitive. Property values are read as-is and may or may not be case sensitive, depending on their use. Comments can be included in configuration files; each comment line must start with the "#" character.

The section and property names that may be used in a configuration file are listed below.

Section `connect`¶

db_type: The type of database. This is equivalent to the "-t" command-line option, and the same list of single-character codes are the only valid property values.
server: The database server name. This is equivalent to the second command-line argument for client-server databases.
db: The database name. This is equivalent to the third command-line argument for client-server databases. The alias database is also accepted.
db_file: The name of the database file. This is equivalent to the second command-line argument for file-based databases.
port: The port number for the client-server database. This is equivalent to the "-p" command-line option.
username: The name of the database user, for client-server databases. This is equivalent to the "-u" command-line option.
access_username: The name of the database user, for MS-Access databases only. When using MS-Access, a password will be prompted for only if this configuration option is set or the "-u" command-line option is used, regardless of the setting of the username configuration parameter.
password_prompt: Indicates whether or not execsql should prompt for the user's password. The property value should be either "Yes" or "No". The default is "Yes". This is equivalent to the "-w" command-line option.
use_keyring: When set to "Yes" (the default) and the keyring Python package is installed, execsql checks the OS credential store (macOS Keychain, Windows Credential Manager, or Linux SecretService) before prompting for a password. After a successful interactive prompt the password is automatically stored in the keyring for future use. Set to "No" to disable keyring integration entirely. Install with pip install execsql2[auth].
new_db: Indicates whether or not execsql should create a new PostgreSQL or SQLite database to connect to.

Section `encoding`¶

database

The database encoding to use. This is equivalent to the -e command-line option.

script

The script encoding to use. This is equivalent to the -f command-line option.

import

Character encoding for data imported with the IMPORT metacommand. This is equivalent to the -i command-line option.

output

Character encoding for data exported with the EXPORT metacommand. This is equivalent to the -g command-line option.

error_response

How to handle conditions where input or output files have incompatible encodings. If not specified, incompatible encodings will cause an error to occur, and execsql will halt. The property values you can use for this setting are:

"ignore": The inconvertible character will be omitted.
"replace": The inconvertible character will be replaced with a question mark.
"xmlcharrefreplace": The inconvertible character will be replaced with the equivalent HTML entity.
"backslashreplace": The inconvertible character will be replaced with an escape sequence consisting of decimal digits, preceded by a backslash.

Section `input`¶

access_use_numeric

Whether or not to translate decimal (numeric) data types to double precision when the IMPORT or COPY metacommands construct a CREATE TABLE statement for MS-Access. This property value should be either "Yes" or "No." The default value is "No".

boolean_int

Whether or not to consider integer values of 0 and 1 as Booleans when scanning data during import or copying. The property value should be either "Yes" or "No". The default value is "Yes". By default, if a data column contains only values of 0 and 1, it will be considered to have a Boolean data type. By setting this value to "No", such a column will be considered to have an integer data type. This is equivalent to the "-b" command-line option.

boolean_words

Whether or not to recognize only full words as Booleans. If this value is "No" (the default), then values of "Y", "N", "T", and "F" will be recognized as Booleans. If this value is "Yes", then only "Yes", "No", "True", and "False" will be recognized as Booleans. This setting is independent of the boolean_int setting.

clean_column_headers

Whether or not to replace non-alphanumeric characters in column headers with the underscore character when data are IMPORTed. The default value is "No". If this is set to "Yes", any characters in a column header except letters, digits, and the underscore character will be replaced by the underscore character.

This setting is also applied to the conversion of spreadsheet names to table names when multiple worksheets are IMPORTed.

create_column_headers

Whether or not to create column headers if they are missing from an input file. The default value is "No". If this is set to "Yes", missing column headers will be created as "Col" followed by the column number. If the delete_empty_columns value is set to "Yes", empty columns will be deleted and so no column headers will be synthesized regardless of this setting.

dedup_column_headers

Whether or not to make duplicated column headers unique by appending an underscore and the column number. Evaluation of the equivalence of column headers is case-insensitive. The default value is "No".

delete_empty_columns

Whether or not to delete entire columns from imported data tables when the column headers are missing. The value should be either "Yes" or "No". The default is "No". Column headers are considered to be missing when they are absent or consist only of spaces.

empty_rows

Determines whether empty rows in the input are added to a data table by the IMPORT and COPY metacommands. The property value should be either "Yes" or "No". The default, "Yes", allows empty rows to be added to a table (subject to non-null and check constraints on the table). When this is set to "No", rows that contain no data will not be added to the table. An empty string is considered to be data, so when this is used, the empty_strings setting will ordinarily also have to be used. The metacommand CONFIG EMPTY_ROWS can also be used to change this configuration item.

empty_strings

Determines whether empty strings in the input are preserved or, alternatively, will be replaced by NULL. The property value should be either "Yes" or "No". The default, "Yes", indicates that empty strings are allowed. A value of "No" will cause all empty strings to be replaced by NULL. When this is set to "No", a string value consisting of a sequence of zero or more space characters will be considered to be an empty string. There is no command-line option corresponding to this configuration parameter, but the metacommand CONFIG EMPTY_STRINGS can also be used to change this configuration item.

fold_column_headers

Whether or not to fold (convert) the case of all column headers to lowercase or uppercase, or to leave them unchanged when data are IMPORTed. Valid values are "No" (the default), "Lower", and "Upper". Case does not matter in the specification.

This setting is also applied to the conversion of spreadsheet names to table names when multiple worksheets are IMPORTed.

import_buffer

The size of the import buffer, in kilobytes, to use with the IMPORT metacommand. This is equivalent to the -z command-line option. This value is only used when the fast file reading capability of PostgreSQL is used.

import_progress_interval

Controls how often row-count progress is written to the execution log during IMPORT operations. Set to a positive integer N to log a status line every N rows (e.g. import_progress_interval = 10000). The default is 0 (silent). When enabled, a final completion line (e.g. "IMPORT into schema.table complete: 1000000 rows imported.") is also written. Supported for all database adapters.

import_common_columns_only

Determines whether the IMPORT metacommand will import data from a CSV file when the file has more data columns than the target table. The property value should be either "Yes" or "No". The default, "No", indicates that the target table must have all of the columns present in the CSV file; if the target table has fewer columns, an error will result. A property value of "Yes" will result in import of only the columns in common between the CSV file and the target table. The legacy alias import_only_common_columns is also accepted.

import_row_buffer

The number of data rows to be buffered from a data source when importing data using the IMPORT metacommand, and when a DBMS-specific fast file importing method can't be used. The setting value must be a positive integer greater than zero. The default value is 1000 rows.

max_int

Establishes the maximum value that will be assigned an integer data type when the IMPORT or COPY metacommands create a new data table. Any column with integer values less than or equal to this value (max_int) and greater than or equal to -1 × max_int - 1 will be considered to have an 'integer' type. Any column with values outside this range will be considered to have a 'bigint' type. The default value for max_int is 2147483647. The max_int value can also be altered within a script using the CONFIG MAX_INT metacommand.

only_strings

Determines whether data imported with the IMPORT metacommand and the NEW or REPLACEMENT keywords will have their data types evaluated (the default) or whether all the data columns will be treated as text (character, character varying, or text). The default value is "No"; if this is set to "Yes", data will be imported as text.

replace_newlines

Replaces newline characters that are in text values on IMPORT. Every sequence of a newline and any surrounding whitespace is replaced with a single space character.

show_progress

Whether or not to display a rich progress bar during long-running IMPORT operations. The property value should be either "Yes" or "No". The default is "No". This can also be enabled via the --progress CLI flag or the CONFIG SHOW_PROGRESS metacommand. Requires the rich Python package.

scan_lines

The number of lines of a data file to scan to determine the quoting character and delimiter character used. This is equivalent to the -s command-line option. The default value is 100.

trim_column_headers

Whether or not to remove leading and/or trailing spaces and underscores from column headers when data are IMPORTed. Valid values are 'none', "both", "left", and "right". The default value is "none". Trimming is done after any cleaning of column headers. Trimming a leading underscore may invalidate a column header that would otherwise start with a digit.

trim_strings

Removes any leading and trailing whitespace from text data on IMPORT.

Section `output`¶

log_write_messages: Specifies whether output of the WRITE metacommand will also be written to execsql's log file. The property value should be either "Yes" or "No". The default is "No". This configuration property can also be controlled within a script with the CONFIG LOG_WRITE_MESSAGES metacommand.
make_export_dirs: The output directories used in the EXPORT and WRITE metacommands will be automatically created if they do not exist (and the user has the necessary permission). The property value should be either "Yes" or "No". This is equivalent to the "-d" command-line option.
quote_all_text: Controls whether all text values written to a delimited text file by the EXPORT metacommand will be quoted. The property value should be either "Yes" or "No"--the default is "No".
outfile_open_timeout: How long (in seconds) the WRITE background process keeps retrying when a target file is held open by another process (backup, sync, etc.); output is buffered during the wait. If the timeout expires (or the script ends first) any pending output is lost and an error is written to stderr. Default: 600.
export_row_buffer: The number of data rows to be buffered from the database when exporting data. Larger values result in faster exports, up to a point, and at a diminishing rate of return. Larger values also require more memory. The setting value must be a positive integer greater than zero. The default value is 1000 rows. This value cannot be customized when using DuckDB.
hdf5_text_len: The length to be assigned to columns that have the 'text' data type when data are exported in the HDF5 format. The default is 1000.
css_file: The URI of a CSS file to be included in the header of an HTML file created with the EXPORT metacommand. If this is specified, it will replace the CSS styles that execsql would otherwise use.
css_styles: A set of CSS style specifications to be included in the header of an HTML file created with the EXPORT metacommand. If this is specified, it will replace the CSS styles that execsql would otherwise use. Both css_file and css_style may be specified; if they are, they will be included in the header of the HTML file in that order.
template_processor: Template engine for EXPORT … WITH TEMPLATE. Default is Python's string.Template, applied once per row with $column_name placeholders. Set to jinja (the only other valid value) to render the full result set in one pass via Jinja2, exposing the data as headers (list of column names) and datatable (list of row dicts) so the template can loop, filter, and conditionalize. Requires jinja2.
zip_buffer_mb: The size of the internal buffer used when the EXPORT metacommand exports data to a zipfile, in Mb. The default value is 10. The buffer should be at least as large as the largest data row to be exported. This value typically has little effect on performance, and only affects memory usage.

CLI-only output options

The --output-dir option sets a default base directory for EXPORT output files. Relative paths in EXPORT metacommands are automatically joined to this directory; absolute paths and stdout are unaffected. This option is only available on the command line — there is no equivalent configuration file setting.

Section `interface`¶

console_height

Specifies the approximate height, in lines of text, for a console window that is created with the CONSOLE ON metacommand.

console_wait_when_done

Controls the persistence of any console window at the completion of the script when the script completes normally. If the property value is set to "Yes" (the default value is "No"), the console window will remain open until explicitly closed by the user. The message "Script complete; close the console window to exit execsql." will be displayed in the status bar. This setting has the same effect as a CONFIG CONSOLE WAIT_WHEN_DONE metacommand.

console_wait_when_error_halt

Controls the persistence of any console window at the completion of the script if an error occurs. If the property value is set to "Yes" (the default value is "No"), the console window will remain open until explicitly closed by the user after an error occurs. The message "Script error; close the console window to exit execsql." will be displayed in the status bar. This setting has the same effect as a CONFIG CONSOLE WAIT_WHEN_ERROR metacommand.

console_width

Specifies the approximate width, in characters, for a console window that is created with the CONSOLE ON metacommand.

write_warnings

Determines whether warning messages are written to the console as well as to the log file. The default value is "No", indicating that warnings will not be written to the console. If it is set to "Yes", warnings will be written to the console.

write_prefix

Text that will be prefixed to any output from the WRITE metacommand, with a space separator. If substitution variables are used, deferred substitution may be appropriate.

write_suffix

Text that will be appended to any output from the WRITE metacommand, with a space separator. If substitution variables are used, deferred substitution may be appropriate.

gui_level

The level of interaction with the user that should be carried out using GUI dialogs. The property value must be 0, 1, 2, or 3. The meanings of these values are:

0: Do not use any optional GUI dialogs.
1: Use GUI dialogs for password prompts and for the PAUSE metacommand.
2: Also use a GUI dialog if a message is included with the HALT metacommand, and prompt for the initial database to use if no database connection parameters are specified in a configuration file or on the command line.
3: Additionally, open a GUI console when execsql starts.

gui_framework

The GUI framework to use when gui_level is greater than 0. The property value must be either tkinter (the default) or textual. tkinter uses native desktop dialogs via Tk; textual provides a terminal-based UI that works in headless/SSH environments. This can also be set via the --gui-framework command-line option.

Section `email`¶

host

The SMTP host name to be used to transmit email messages sent using the EMAIL metacommand. A host name must be specified to use the EMAIL metacommand.

port

The port number of the SMTP host to use. If this is omitted, port 25 will be used unless either the use_ssl or use_tls configuration properties is also specified, in which case ports 465 or 587 may be used.

username

The name of the user if the SMTP server requires login authentication.

password

An unencrypted password to be used if the SMTP server requires login authentication.

enc_password

An encrypted password to be used if the SMTP server required login authentication. The encrypted version of a password should be exactly as produced by the SUB_ENCRYPT metacommand. A suitably encrypted version of a password can be produced by running the script:

-- !x! prompt enter_sub pw password message "Enter a password to encrypt"
-- !x! sub_encrypt enc_pw !!pw!!
-- !x! write "The encrypted password is: !!enc_pw!!"

If both the password and enc_password configuration properties are used, the enc_password property will take precedence and will be used for SMTP authentication.

Obfuscation only — not real encryption

The enc_password value is produced by a simple XOR operation using keys that are embedded in the execsql source code. Anyone with access to the source or the installed package can decode the password. Treat enc_password values in execsql.conf as plaintext-equivalent.

For production deployments, prefer OS credential stores (e.g. macOS Keychain, Windows Credential Manager, secret-tool on Linux) or environment variables rather than storing passwords in configuration files.

use_ssl

SSL/TLS encryption will be used from the initiation of the connection.

use_tls

SSL/TLS encryption will be used after the initial connection is made using unencrypted text.

email_format

Specifies whether the message will be sent as plain text or as HTML email. The only valid values for this property are "plain" and "html". If not specified, emails will be sent in plain text.

message_css

A set of CSS rules to be applied to HTML email.

Section `config`¶

config_file: The full name or path to an additional configuration file to be read. If only a path is specified, the name of the configuration file should be execsql.conf. The configuration file specified will be read immediately following the configuration file in which it is named. No configuration file will be read more than once. If the name or path are invalid, this setting will be silently ignored. This setting may include substitution variables; at the time that configuration files are read, however, only environment variables and system variables related to the script name and path are defined.
dao_flush_delay_secs: The number of seconds that execsql should wait between the time that a query is created in Access (which uses DAO) and the time that the next statement is executed using ODBC. This value must be greater than or equal to 5.0. The default is 5.0.
linux_config_file, macos_config_file, win_config_file: Platform-conditional additional configuration files, active only on Linux (sys.platform == "linux"), macOS (sys.platform == "darwin"), and Windows (os.name == "nt") respectively. The named file is loaded immediately after the config that referenced it; same path/precedence rules as config_file. Tilde expansion (~) is supported.
log_sql: When set to "Yes", all executed SQL statements are written to the log file with a sql record type, including the database name, line number, and query text. The property value should be either "Yes" or "No". The default is "No". This can also be enabled via the CONFIG LOG_SQL metacommand.
max_log_size_mb: Maximum size of the log file in megabytes before it is rotated. When set to a positive integer, the log file is rotated to .1 before a new run appends to it if the file size exceeds the configured threshold. The default is 0 (disabled — no rotation).
allow_system_cmd: When set to "No", the SYSTEM_CMD (SHELL) metacommand is disabled. Any script that attempts to execute an OS command will fail with an error. The default is "Yes". This can also be set via the --no-system-cmd CLI flag or allow_system_cmd=False in the library API. See Security — Disabling SYSTEM_CMD for details.
allow_rm_file: When set to "No", the RM_FILE metacommand (which deletes a file) is disabled. Any script that attempts to remove a file will fail with an error. The default is "Yes". This can also be set via the --no-rm-file CLI flag or allow_rm_file=False in the library API. Symmetric with allow_system_cmd.
allow_serve: When set to "No", the SERVE metacommand (which opens a one-shot HTTP server on a local port to deliver a file) is disabled. The default is "Yes". This can also be set via the --no-serve CLI flag or allow_serve=False in the library API. Symmetric with allow_system_cmd.
include_root: Root directory under which INCLUDE and EXECUTE SCRIPT targets must resolve. When set, attempts to include files outside this root via ../, absolute paths, drive letters, or UNC paths are rejected with an error. Default: no containment (any readable path is permitted). See Security — Path containment roots for the full per-handler matrix.
serve_root: Root directory under which SERVE targets must resolve. Same containment semantics as include_root — paths that escape via ../ or absolute references are rejected. Default: no containment.
template_root: Root directory under which Jinja2 / string.Template loader paths must resolve. Same containment semantics as include_root. Default: no containment.
max_substitution_bytes: Maximum byte size of any single substitution-variable expansion, enforced by the substitute_vars() engine to defeat exponential-expansion bombs (a script that nests !!a!! referring to !!b!! referring to !!a!! and so on can otherwise blow past memory). When set to a positive integer, expansions exceeding the threshold raise an error. Default: 10485760 (10 MB).
log_datavars: A value of 'Yes' or 'No' to control whether data variables that are created by the SELECT_SUB, PROMPT SELECT_SUB and PROMPT ACTION metacommands are written to execsql's log file. By default, this is set to 'Yes', so that all data variable assignments are logged. The performance of scripts that make extensive use of these metacommands (e.g., Example 27) can be improved by setting this to 'No'.
user_logfile: Uses an execsql.log file in the user's home directory instead of in the directory from which the script was run. This setting may need to be used if multiple users will be running scripts from the same directory.

Section `variables`¶

There are no fixed properties for this section. All property names and their values that are specified in this section will be used to define substitution variables, just as if a series of SUB metacommands had been used at the beginning of the script. All variables defined in this section will be global.

Sections `include_required` and `include_optional`¶

Both sections list script files to be auto-included before the main script runs, without an explicit INCLUDE. Each property is an integer that specifies inclusion order; the value is a filename. Duplicate integers keep the last assignment, and each file is included at most once.

The difference between the sections is what happens when a file is missing: include_required halts execsql with an error; include_optional silently skips it.

All include_required files run before any include_optional files. Across multiple config files, ordering follows the order in which those config files are read.

Configuration Files¶

Section connect¶

Section encoding¶

Section input¶

Section output¶

Section interface¶

Section email¶

Section config¶

Section variables¶

Sections include_required and include_optional¶