Shell¶
The shell provides a convenient way for you to interact with SQLite, perform administration and supply SQL for execution. It is modelled after the shell that comes with SQLite which requires separate compilation and installation.
A number of the quirks and bugs in the SQLite shell are also addressed. It provides command line editing and completion. You can easily include it into your own program to provide SQLite interaction and add your own commands. The autoimport and find commands are also useful.
Notes¶
To interrupt the shell press Control-C. (On Windows if you press Control-Break then the program will be instantly aborted.)
For Windows users you won't have command line editing and completion unless you install a readline module. You can pip install pyreadline3 to get full functionality.
For Windows users, the builtin console support for colour is used. It is enabled by default in current versions of Windows, and a registry key enables for older versions (details).
Commands¶
In addition to executing SQL, these are the commands available with their short help description. Use .help *command* eg (.help autoimport) to get more detailed information.
.autoimport FILENAME ?TABLE? Imports filename creating a table and
automatically working out separators and data
types (alternative to .import command)
.backup ?DB? FILE Backup DB (default "main") to FILE
.bail ON|OFF Stop after hitting an error (default OFF)
.colour SCHEME Selects a colour scheme from default, off
.databases Lists names and files of attached databases
.dump ?TABLE? [TABLE...] Dumps all or specified tables in SQL text format
.echo ON|OFF If ON then each SQL statement or command is
printed before execution (default OFF)
.encoding ENCODING Set the encoding used for new files opened via
.output and imports
.exceptions ON|OFF If ON then detailed tracebacks are shown on
exceptions (default OFF)
.exit Exit this program
.explain ON|OFF Set output mode suitable for explain (default OFF)
.find what ?TABLE? Searches all columns of all tables for a value
.header(s) ON|OFF Display the column names in output (default OFF)
.help ?COMMAND? Shows list of commands and their usage. If
COMMAND is specified then shows detail about that
COMMAND. ('.help all' will show detailed help
about all commands.)
.import FILE TABLE Imports separated data from FILE into TABLE
.indices TABLE Lists all indices on table TABLE
.load FILE ?ENTRY? Loads a SQLite extension library
.mode MODE ?TABLE? Sets output mode to one of column columns csv html
insert json line lines list python tabs tcl
.nullvalue STRING Print STRING in place of null values
.open ?OPTIONS? ?FILE? Closes existing database and opens a different one
.output FILENAME Send output to FILENAME (or stdout)
.print STRING print the literal STRING
.prompt MAIN ?CONTINUE? Changes the prompts for first line and
continuation lines
.quit Exit this program
.read FILENAME Processes SQL and commands in FILENAME (or Python
if FILENAME ends with .py)
.restore ?DB? FILE Restore database from FILE into DB (default
"main")
.schema ?TABLE? [TABLE...] Shows SQL for table
.separator STRING Change separator for output mode and .import
.show Show the current values for various settings.
.tables ?PATTERN? Lists names of tables matching LIKE pattern
.timeout MS Try opening locked tables for MS milliseconds
.timer ON|OFF Control printing of time and resource usage after
each query
.width NUM NUM ... Set the column widths for "column" mode
Command Line Usage¶
You can use the shell directly from the command line. Invoke it like this:
$ python3 -m apsw [options and arguments]
The following command line options are accepted:
Usage: program [OPTIONS] FILENAME [SQL|CMD] [SQL|CMD]...
FILENAME is the name of a SQLite database. A new database is
created if the file does not exist.
OPTIONS include:
-init filename read/process named file
-echo print commands before execution
-[no]header turn headers on or off
-bail stop after hitting an error
-interactive force interactive I/O
-batch force batch I/O
-column set output mode to 'column'
-csv set output mode to 'csv'
-html set output mode to 'html'
-line set output mode to 'line'
-list set output mode to 'list'
-python set output mode to 'python'
-separator 'x' set output field separator (|)
-nullvalue 'text' set text string for NULL values
-version show SQLite version
-encoding 'name' the encoding to use for files
opened via .import, .read & .output
-nocolour disables colour output to screen
Example¶
All examples of using the SQLite shell should work as is, plus you get extra features and functionality like colour, command line completion and better dumps. (The standard SQLite shell does have several more Commands that help with debugging and introspecting SQLite itself.)
You can also use the shell programmatically (or even interactively and programmatically at the same time). See the example for using the API.
Unicode¶
SQLite only works with Unicode strings. All data supplied to it should be Unicode and all data retrieved is Unicode. (APSW functions the same way because of this.)
At the technical level there is a difference between bytes and characters. Bytes are how data is stored in files and transmitted over the network. In order to turn bytes into characters and characters into bytes an encoding has to be used. Some example encodings are ASCII, UTF-8, ISO8859-1, SJIS etc. (With the exception of UTF-8/16/32, other encodings can only map a very small subset of Unicode.)
If the shell reads data that is not valid for the input encoding or cannot convert Unicode to the output encoding then you will get an error. When the shell starts, Python automatically detects the encodings to use for console input and output.
There is also a .encoding command. This sets what encoding is used for any subsequent .read, .import and .output commands but does not affect existing open files and console. When other programs offer you a choice for encoding the best value to pick is UTF8 as it allows full representation of Unicode.
In addition to specifying the encoding, you can also specify the error handling when a character needs to be output but is not present in the encoding. The default is 'strict' which results in an error. 'replace' will replace the character with '?' or something similar while 'xmlcharrefreplace' uses xml entities. To specify the error handling add a colon and error after the encoding - eg:
.encoding iso-8859-1:replace
The same method is used when setting PYTHONIOENCODING.
This Joel on Software article contains an excellent overview of character sets, code pages and Unicode.
Shell class¶
This is the API should you want to integrate the code into your shell. Not shown here are the functions that implement various commands. They are named after the command. For example .exit is implemented by command_exit. You can add new commands by having your subclass have the relevant functions. The doc string of the function is used by the help command. Output modes work in a similar way. For example there is an output_html method and again doc strings are used by the help function and you add more by just implementing an appropriately named method.
Note that in addition to extending the shell, you can also use the .read command supplying a filename with a .py extension. You can then monkey patch the shell as needed.
- class Shell(stdin: TextIO | None = None, stdout=None, stderr=None, encoding: str = 'utf8', args=None, db=None)[source]¶
Implements a SQLite shell
- Parameters:
stdin -- Where to read input from (default sys.stdin)
stdout -- Where to send output (default sys.stdout)
stderr -- Where to send errors (default sys.stderr)
encoding -- Default encoding for files opened/created by the Shell. If you want stdin/out/err to use a particular encoding then you need to provide them already configured that way.
args -- This should be program arguments only (ie if passing in sys.argv do not include sys.argv[0] which is the program name. You can also pass in None and then call
process_args()
if you want to catch any errors in handling the arguments yourself.db -- A existing
Connection
you wish to use
The commands and behaviour are modelled after the interactive shell that is part of SQLite.
You can inherit from this class to embed in your own code and user interface. Internally everything is handled as unicode. Conversions only happen at the point of input or output which you can override in your own code.
Errors and diagnostics are only ever sent to error output (self.stderr) and never to the regular output (self.stdout). This means using shell output is always easy and consistent.
Shell commands begin with a dot (eg .help). They are implemented as a method named after the command (eg command_help). The method is passed one parameter which is the list of arguments to the command.
Output modes are implemented by functions named after the mode (eg output_column).
When you request help the help information is automatically generated from the docstrings for the command and output functions.
You should not use a Shell object concurrently from multiple threads. It is one huge set of state information which would become inconsistent if used simultaneously, and then give baffling errors. It is safe to call methods one at a time from different threads. ie it doesn't care what thread calls methods as long as you don't call more than one concurrently.
- exception Error[source]¶
Class raised on errors. The expectation is that the error will be displayed by the shell as text so there are no specific subclasses as the distinctions between different types of errors doesn't matter.
- cmdloop(intro=None)[source]¶
Runs the main interactive command loop.
- Parameters:
intro -- Initial text banner to display instead of the default. Make sure you newline terminate it.
- command_autoimport(cmd)[source]¶
autoimport FILENAME ?TABLE?: Imports filename creating a table and automatically working out separators and data types (alternative to .import command)
The import command requires that you precisely pre-setup the table and schema, and set the data separators (eg commas or tabs). In many cases this information can be automatically deduced from the file contents which is what this command does. There must be at least two columns and two rows.
If the table is not specified then the basename of the file will be used.
Additionally the type of the contents of each column is also deduced - for example if it is a number or date. Empty values are turned into nulls. Dates are normalized into YYYY-MM-DD format and DateTime are normalized into ISO8601 format to allow easy sorting and searching. 4 digit years must be used to detect dates. US (swapped day and month) versus rest of the world is also detected providing there is at least one value that resolves the ambiguity.
Care is taken to ensure that columns looking like numbers are only treated as numbers if they do not have unnecessary leading zeroes or plus signs. This is to avoid treating phone numbers and similar number like strings as integers.
This command can take quite some time on large files as they are effectively imported twice. The first time is to determine the format and the types for each column while the second pass actually imports the data.
- command_backup(cmd)[source]¶
backup ?DB? FILE: Backup DB (default "main") to FILE
Copies the contents of the current database to FILE overwriting whatever was in FILE. If you have attached databases then you can specify their name instead of the default of "main".
The backup is done at the page level - SQLite copies the pages as is. There is no round trip through SQL code.
- command_bail(cmd)[source]¶
bail ON|OFF: Stop after hitting an error (default OFF)
If an error is encountered while processing commands or SQL then exit. (Note this is different than SQLite shell which only exits for errors in SQL.)
- command_color(cmd=[])¶
colour SCHEME: Selects a colour scheme
Residents of both countries that have not adopted the metric system may also spell this command without a 'u'. If using a colour terminal in interactive mode then output is automatically coloured to make it more readable. Use 'off' to turn off colour, and no name or 'default' for the default.
- command_colour(cmd=[])[source]¶
colour SCHEME: Selects a colour scheme
Residents of both countries that have not adopted the metric system may also spell this command without a 'u'. If using a colour terminal in interactive mode then output is automatically coloured to make it more readable. Use 'off' to turn off colour, and no name or 'default' for the default.
- command_dump(cmd)[source]¶
dump ?TABLE? [TABLE...]: Dumps all or specified tables in SQL text format
The table name is treated as like pattern so you can use % as a wildcard. You can use dump to make a text based backup of the database. It is also useful for comparing differences or making the data available to other databases. Indices and triggers for the table(s) are also dumped. Finally views matching the table pattern name are dumped (it isn't possible to work out which views access which table and views can access multiple tables anyway).
Note that if you are dumping virtual tables such as used by the FTS3 module then they may use other tables to store information. For example if you create a FTS3 table named recipes then it also creates recipes_content, recipes_segdir etc. Consequently to dump this example correctly use:
.dump recipes recipes_%
If the database is empty or no tables/views match then there is no output.
- command_echo(cmd)[source]¶
echo ON|OFF: If ON then each SQL statement or command is printed before execution (default OFF)
The SQL statement or command is sent to error output so that it is not intermingled with regular output.
- command_encoding(cmd)[source]¶
encoding ENCODING: Set the encoding used for new files opened via .output and imports
SQLite and APSW work internally using Unicode and characters. Files however are a sequence of bytes. An encoding describes how to convert between bytes and characters. The default encoding is utf8 and that is generally the best value to use when other programs give you a choice.
You can also specify an error handler. For example 'cp437:replace' will use code page 437 and any Unicode codepoints not present in cp437 will be replaced (typically with something like a question mark). Other error handlers include 'ignore', 'strict' (default) and 'xmlcharrefreplace'.
For the default input/output/error streams on startup the shell defers to Python's detection of encoding. For example on Windows it asks what code page is in use and on Unix it looks at the LC_CTYPE environment variable. You can set the PYTHONIOENCODING environment variable to override this detection.
This command affects files opened after setting the encoding as well as imports.
See the online APSW documentation for more details.
- command_exceptions(cmd)[source]¶
exceptions ON|OFF: If ON then detailed tracebacks are shown on exceptions (default OFF)
Normally when an exception occurs the error string only is displayed. However it is sometimes useful to get a full traceback. An example would be when you are developing virtual tables and using the shell to exercise them. In addition to displaying each stack frame, the local variables within each frame are also displayed.
- command_explain(cmd)[source]¶
explain ON|OFF: Set output mode suitable for explain (default OFF)
Explain shows the underlying SQLite virtual machine code for a statement. You need to prefix the SQL with explain. For example:
explain select * from table;
This output mode formats the explain output nicely. If you do '.explain OFF' then the output mode and settings in place when you did '.explain ON' are restored.
- command_find(cmd)[source]¶
find what ?TABLE?: Searches all columns of all tables for a value
The find command helps you locate data across your database for example to find a string or any references to an id.
You can specify a like pattern to limit the search to a subset of tables (eg specifying 'CUSTOMER%' for all tables beginning with CUSTOMER).
The what value will be treated as a string and/or integer if possible. If what contains % or _ then it is also treated as a like pattern.
This command will take a long time to execute needing to read all of the relevant tables.
- command_headers(cmd)¶
header(s) ON|OFF: Display the column names in output (default OFF)
- command_help(cmd)[source]¶
help ?COMMAND?: Shows list of commands and their usage. If COMMAND is specified then shows detail about that COMMAND. ('.help all' will show detailed help about all commands.)
- command_import(cmd)[source]¶
import FILE TABLE: Imports separated data from FILE into TABLE
Reads data from the file into the named table using the current separator and encoding. For example if the separator is currently a comma then the file should be CSV (comma separated values).
All values read in are supplied to SQLite as strings. If you want SQLite to treat them as other types then declare your columns appropriately. For example declaring a column 'REAL' will result in the values being stored as floating point if they can be safely converted. See this page for more details:
Another alternative is to create a temporary table, insert the values into that and then use casting.
CREATE TEMPORARY TABLE import(a,b,c);
.import filename import
CREATE TABLE final AS SELECT cast(a as BLOB), cast(b as INTEGER), cast(c as CHAR) from import;
DROP TABLE import;
You can also get more sophisticated using the SQL CASE operator. For example this will turn zero length strings into null:
SELECT CASE col WHEN '' THEN null ELSE col END FROM ...
- command_load(cmd)[source]¶
load FILE ?ENTRY?: Loads a SQLite extension library
Note: Extension loading may not be enabled in the SQLite library version you are using.
Extensions are an easy way to add new functions and functionality. For a useful extension look at the bottom of https://sqlite.org/contrib
By default sqlite3_extension_init is called in the library but you can specify an alternate entry point.
If you get an error about the extension not being found you may need to explicitly specify the directory. For example if it is in the current directory then use:
.load ./extension.so
- command_nullvalue(cmd)[source]¶
nullvalue STRING: Print STRING in place of null values
This affects textual output modes like column and list and sets how SQL null values are shown. The default is a zero length string. Insert mode and dumps are not affected by this setting. You can use double quotes to supply a zero length string. For example:
.nullvalue "" # the default .nullvalue <NULL> # rather obvious .nullvalue " t " # A tab surrounded by spaces
- command_open(cmd)[source]¶
open ?OPTIONS? ?FILE?: Closes existing database and opens a different one
Options are: --new which deletes the file if it already exists
If FILE is omitted then a memory database is opened
- command_output(cmd)[source]¶
output FILENAME: Send output to FILENAME (or stdout)
If the FILENAME is stdout then output is sent to standard output from when the shell was started. The file is opened using the current encoding (change with .encoding command).
- command_print(cmd)[source]¶
print STRING: print the literal STRING
If more than one argument is supplied then they are printed space separated. You can use backslash escapes such as n and t.
- command_prompt(cmd)[source]¶
prompt MAIN ?CONTINUE?: Changes the prompts for first line and continuation lines
The default is to print 'sqlite> ' for the main prompt where you can enter a dot command or a SQL statement. If the SQL statement is complete (eg not ; terminated) then you are prompted for more using the continuation prompt which defaults to ' ..> '. Example:
.prompt "command> " "more command> "
You can use backslash escapes such as n and t.
- command_read(cmd)[source]¶
read FILENAME: Processes SQL and commands in FILENAME (or Python if FILENAME ends with .py)
Treats the specified file as input (a mixture or SQL and/or dot commands). If the filename ends in .py then it is treated as Python code instead.
For Python code the symbol 'shell' refers to the instance of the shell and 'apsw' is the apsw module.
- command_restore(cmd)[source]¶
restore ?DB? FILE: Restore database from FILE into DB (default "main")
Copies the contents of FILE to the current database (default "main"). The backup is done at the page level - SQLite copies the pages as is. There is no round trip through SQL code.
- command_schema(cmd)[source]¶
schema ?TABLE? [TABLE...]: Shows SQL for table
If you give one or more tables then their schema is listed (including indices). If you don't specify any then all schemas are listed. TABLE is a like pattern so you can % for wildcards.
- command_separator(cmd)[source]¶
separator STRING: Change separator for output mode and .import
You can use quotes and backslashes. For example to set the separator to space tab space you can use:
.separator " t "
The setting is automatically changed when you switch to csv or tabs output mode. You should also set it before doing an import (ie , for CSV and t for TSV).
- command_tables(cmd)[source]¶
tables ?PATTERN?: Lists names of tables matching LIKE pattern
This also returns views.
- command_timeout(cmd)[source]¶
timeout MS: Try opening locked tables for MS milliseconds
If a database is locked by another process SQLite will keep retrying. This sets how many thousandths of a second it will keep trying for. If you supply zero or a negative number then all busy handlers are disabled.
- command_timer(cmd)[source]¶
timer ON|OFF: Control printing of time and resource usage after each query
The values displayed are in seconds when shown as floating point or an absolute count. Only items that have changed since starting the query are shown. On non-Windows platforms considerably more information can be shown.
- command_width(cmd)[source]¶
width NUM NUM ...: Set the column widths for "column" mode
In "column" output mode, each column is a fixed width with values truncated to fit. Specify new widths using this command. Use a negative number to right justify and zero for default column width.
- complete(token, state)[source]¶
Return a possible completion for readline
This function is called with state starting at zero to get the first completion, then one/two/three etc until you return None. The best implementation is to generate the list when state==0, save it, and provide members on each increase.
The default implementation extracts the current full input from readline and then calls
complete_command()
orcomplete_sql()
as appropriate saving the results for subsequent calls.
- complete_command(line, token, beg, end)[source]¶
Provide some completions for dot commands
- Parameters:
line -- The current complete input line
token -- The word readline is looking for matches
beg -- Integer offset of token in line
end -- Integer end of token in line
- Returns:
A list of completions, or an empty list if none
- complete_sql(line, token, beg, end)[source]¶
Provide some completions for SQL
- Parameters:
line -- The current complete input line
token -- The word readline is looking for matches
beg -- Integer offset of token in line
end -- Integer end of token in line
- Returns:
A list of completions, or an empty list if none
- property db¶
The current
Connection
- display_timing(b4, after)[source]¶
Writes the difference between b4 and after to self.stderr. The data is dictionaries returned from
get_resource_usage()
.
- fixup_backslashes(s)[source]¶
Implements the various backlash sequences in s such as turning backslash t into a tab.
This function is needed because shlex does not do it for us.
- get_resource_usage()[source]¶
Return a dict of various numbers (ints or floats). The .timer command shows the difference between before and after results of what this returns by calling
display_timing()
- getcompleteline()[source]¶
Returns a complete input.
For dot commands it will be one line. For SQL statements it will be as many as is necessary to have a
complete()
statement (ie semicolon terminated). Returns None on end of file.
- getline(prompt='')[source]¶
Returns a single line of input (may be incomplete SQL) from self.stdin.
If EOF is reached then return None. Do not include trailing newline in return.
- handle_exception()[source]¶
Handles the current exception, printing a message to stderr as appropriate. It will reraise the exception if necessary (eg if bail is true)
- handle_interrupt()[source]¶
Deal with keyboard interrupt (typically Control-C). It will
interrupt()
the database and print"^C" if interactive.
- output_column(header, line)[source]¶
Items left aligned in space padded columns. They are truncated if they do not fit. If the width hasn't been specified for a column then 10 is used unless the column name (header) is longer in which case that width is used. Use the .width command to change column sizes.
- output_columns(header, line)¶
Items left aligned in space padded columns. They are truncated if they do not fit. If the width hasn't been specified for a column then 10 is used unless the column name (header) is longer in which case that width is used. Use the .width command to change column sizes.
- output_csv(header, line)[source]¶
Items in csv format (comma separated). Use tabs mode for tab separated. You can use the .separator command to use a different one after switching mode. A separator of comma uses double quotes for quoting while other separators do not do any quoting. The Python csv library used for this only supports single character separators.
- output_insert(header, line)[source]¶
Lines as SQL insert statements. The table name is "table" unless you specified a different one as the second parameter to the .mode command.
- output_json(header, line)[source]¶
Each line as a JSON object with a trailing comma. Blobs are output as base64 encoded strings. You should be using UTF8 output encoding.
- output_line(header, line)[source]¶
One value per line in the form 'column = value' with a blank line between rows.
- output_lines(header, line)¶
One value per line in the form 'column = value' with a blank line between rows.
- pop_input()[source]¶
Restore most recently pushed input parameters (interactive, self.stdin, linenumber etc). Use this if implementing a command like read. Push the current input, read the file and then pop the input to go back to before.
- pop_output()[source]¶
Restores most recently pushed output. There are many output parameters such as nullvalue, mode (list/tcl/html/insert etc), column widths, header etc. If you temporarily need to change some settings then
push_output()
, change the settings and then pop the old ones back.A simple example is implementing a command like .dump. Push the current output, change the mode to insert so we get SQL inserts printed and then pop to go back to what was there before.
- process_args(args)[source]¶
Process command line options specified in args. It is safe to call this multiple times. We try to be compatible with SQLite shell argument parsing.
- Parameters:
args -- A list of string options. Do not include the program as args[0]
- Returns:
A tuple of (databasefilename, initfiles, sqlncommands). This is provided for informational purposes only - they have already been acted upon. An example use is that the SQLite shell does not enter the main interactive loop if any sql/commands were provided.
The first non-option is the database file name. Each remaining non-option is treated as a complete input (ie it isn't joined with others looking for a trailing semi-colon).
The SQLite shell uses single dash in front of options. We allow both single and double dashes. When an unrecognized argument is encountered then
process_unknown_args()
is called.
- process_command(cmd)[source]¶
Processes a dot command. It is split into parts using the shlex.split function which is roughly the same method used by Unix/POSIX shells.
- process_complete_line(command)[source]¶
Given some text will call the appropriate method to process it (eg
process_sql()
orprocess_command()
)
- process_sql(sql, bindings=None, internal=False, summary=None)[source]¶
Processes SQL text consisting of one or more statements
- Parameters:
sql -- SQL to execute
bindings -- bindings for the sql
internal -- If True then this is an internal execution (eg the .tables or .database command). When executing internal sql timings are not shown nor is the SQL echoed.
summary -- If not None then should be a tuple of two items. If the
sql
returns any data then the first item is printed before the first row, and the second item is printed after the last row. An example usage is the .find command which shows table names.
- process_unknown_args(args)[source]¶
This is called when
process_args()
encounters an argument it doesn't understand. Override this method if you want to be able to understand additional command line arguments.- Parameters:
args -- A list of the remaining arguments. The initial one will have had the leading dashes removed (eg if it was --foo on the command line then args[0] will be "foo"
- Returns:
None if you don't recognize the argument either. Otherwise return the list of remaining arguments after you have processed yours.
- push_input()[source]¶
Saves the current input parameters to a stack. See
pop_input()
.
- push_output()[source]¶
Saves the current output settings onto a stack. See
pop_output()
for more details as to why you would use this.
- main() None [source]¶
Call this to run the interactive shell. It automatically passes in sys.argv[1:] and exits Python when done.