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 doesn’t interoperate with Python.
Notable improvements include:
You can invoke this shell programmatically - very useful for development and debugging
Output is in colour
Tab completion is available
Nicer text dump output, including metadata like user_version
All open APSW
connections
are available and you can switch between them.py command gets you a Python REPL or runs one line of Python code
Very useful autoimport and find commands
Notes
To interrupt the shell press Control-C. (On Windows if you press Control-Break then the program will be terminated.)
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).
Command Line Usage
You can use the shell directly from the command line.
Usage: python3 -m apsw [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. If omitted or an empty
string then an in-memory database is created.
OPTIONS include:
-init filename read/process named file
-echo print commands before execution
-[no]header turn headers on or off
-bail stop after hitting the first error
-interactive force interactive I/O (command editing and colour)
-batch force batch I/O (no banners or editing)
-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'
-jsonl set output mode to 'jsonl'
-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 interactive colour output
Programmatic Usage
You can also use the shell programmatically (or even interactively and programmatically at the same time). See the example for using the API.
To quickly invoke the shell similar to the Python debugger, do this:
apsw.shell.Shell(db=database_of_interest).cmdloop()
You can use .connection to switch amongst connections. Press Control-D at the prompt (Control-Z on Windows) will exit the shell.
Commands
In addition to executing SQL, these are the commands available with
their description. Commands are distinguished from SQL by having a
leading .
(period) - for example:
.help
.mode qbox
.find winchester
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). This command figures out the separator and csv dialect automatically. 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.
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.
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.)
cd ?DIR?
Changes current directory
If no directory supplied then change to home directory
changes ON|OFF
Show changes from last SQL and total changes (default OFF)
After executing SQL that makes changes, the number of affected rows is displayed as well as a running count of all changes.
close
Closes the current database
Use .open to open a database, or .connection to switch to another connection
colour SCHEME
Selects a colour scheme from default, off
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 colour scheme.
connection ?NUMBER?
List connections, or switch active connection
This covers all connections, not just those started in this shell. Closed connections are not shown.
databases
Lists names and files of attached databases
dbconfig ?NAME VALUE?
Show all dbconfig, or set a specific one
With no arguments lists all settings. Supply a name and integer value to change. For example:
.dbconfig enable_fkey 1
dbinfo ?NAME?
Shows summary and file information about the database
This includes the numbers of tables, indices etc, as well as fields from the
files as returned by apsw.ext.dbinfo()
.
NAME
defaults to main
, and can be the attached name of a database.
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.
Note that if you are dumping virtual tables such as used by the FTS5 module then they may use other tables to store information. For example if you create a FTS5 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.
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.
encoding ENCODING
Set the encoding used for new files opened via .output and imports
SQLite and APSW/Python 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.
This command affects files opened after setting the encoding as well as imports.
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.
exit ?CODE?
Exit this program with optional exit code
find value ?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 value will be treated as a string and/or integer if possible. If value
contains %
or _
then it is also treated as a like pattern.
This command can take a long time to execute needing to scan all of the relevant tables, rows, and columns.
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
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.
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 ...
indices TABLE
Lists all indices on table TABLE
load FILE ?ENTRY?
Loads a SQLite extension library
Note: Extension loading may not be enabled in the SQLite library version you are using.
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
log ON|OFF
Shows SQLite log messages (default off)
mode MODE ?OPTIONS?
Sets output mode to one of box column columns csv html insert json jsonl line lines list python qbox table tabs tcl
box: Outputs using line drawing and auto sizing columns
columns: 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.
csv: 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.
html: HTML table style
insert: Lines as SQL insert statements. The table name is “table” unless you specified a different one as the second parameter to the .mode command.
json: Output a JSON array. Blobs are output as base64 encoded strings.
jsonl: Output as JSON objects, newline separated. Blobs are output as base64 encoded strings.
lines: One value per line in the form ‘column = value’ with a blank line between rows.
list: All items on one line with separator
python: Tuples in Python source form for each row
qbox: Outputs using line drawing and auto sizing columns quoting values
table: Outputs using ascii line drawing and strongly sanitized text
tcl: Outputs TCL/C style strings using current separator
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
open ?OPTIONS? ?FILE?
Opens a database connection
Options are:
- --wipe
Closes any existing connections in this process referring to the same file and deletes the database file, journals etc before opening
- --vfs VFS
Which vfs to use when opening
If FILE
is omitted then a memory database is opened
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).
parameter CMD …
Maintain named bindings you can use in your queries.
Specify a subcommand:
list -- shows current bindings
clear -- deletes all bindings
unset NAME -- deletes named binding
set NAME VALUE -- sets binding to VALUE
The value must be a valid SQL literal or expression. For example 3 will be an
integer 3 while '3'
will be a string.
Example::
.parameter set floor 10.99
.parameter set text 'Acme''s Glove'
SELECT * FROM sales WHERE price > $floor AND description != $text;
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.
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 not complete 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.
py ?PYTHON?
Starts a python REPL or runs the Python statement provided
The namespace provided includes apsw
for the module, shell
for this
shell and db
for the current database.
Using the .output command does not affect output from this command. You can write to shell.stdout and shell.stderr.
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 db
refers to the current database, shell
refers to the instance of the shell and apsw
is the apsw module.
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.
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 use %
for wildcards.
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).
shell CMD ARGS…
Run CMD ARGS in a system shell
Note that output goes to the process standard output, not whatever the shell .output command has configured.
show
Show the current values for various settings.
tables ?PATTERN?
Lists names of tables matching LIKE pattern
This also returns views.
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.
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.
version
Displays SQLite, APSW, and Python version information
vfsinfo
Shows detailed information about the VFS for the database
vfslist
Shows detailed information about all the VFS available
vfsname
VFS name for database, or attached names
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.
Shell class
This is the API should you want extend the shell with your own commands and output modes. 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 apsw.shell.Shell(stdin: TextIO | None = None, stdout: TextIO | None = None, stderr: TextIO | None = None, encoding: str = 'utf8', args: list[str] | None = None, db: Connection | None = 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
Errors and diagnostics are only ever sent to error output (self.stderr) and never to the regular output (self.stdout).
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 for columns).
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.
- 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.
- class PositionRow(source)[source]
Wraps an iterator so you know if a row is first, last, both, or neither
- class Row(is_first: bool, is_last: bool, row: apsw.SQLiteValues, columns: tuple[str, ...])[source]
Returned by
Shell.PositionRow
- row: apsw.SQLiteValues
- cmdloop(intro=None, transient=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.
transient – Additional message about being connected to a transient in memory database
- 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(before, after)[source]
Writes the difference between before 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_complete_line()[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.
- get_line(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.
- 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()
- 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.
- 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(command)[source]
Processes a dot command.
It is split into parts using
shlex.split()
- process_complete_line(command)[source]
Given some text will call the appropriate method to process it (eg
process_sql()
orprocess_command()
)
- process_sql(sql: str, 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.
- set_encoding(enc)[source]
Saves enc as the default encoding, after verifying that it is valid. You can also include :error to specify error handling - eg ‘cp437:replace’
- apsw.shell.main() None [source]
Call this to run the interactive shell. It automatically passes in sys.argv[1:] and exits Python when done.