Shell

The shell provides a convtext interface 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, as does
                        setting NO_COLOR environment variable

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 backup bail cd changes close colour connection databases dbconfig dbinfo dump echo encoding exceptions exit

find ftsq header help import indices load log mode nullvalue open output pages pages_svg parameter print

prompt py read restore schema separator shell show tables timeout timer version vfsinfo vfslist vfsname width

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 file headers.

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.

ftsq TABLE query

Issues the query against the named FTS5 table

The top 20 results are shown. Text after the table name is used exactly as the query - do not extra shell quote it.

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).

pages SCOPE

Shows page usage summary in human units

SCOPE is a number 0, 1, or 2.

0 - shows the database as a whole. 1 - groups by each table, including its indices. 2 - shows each table and index separately.

pages-svg ?OUTFILENAME?

Shows space usage in a graphic

If you do not specify a filename, then a temporary file is created and the browser invoked to show it.

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. SQLite statistics are also included.

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)

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

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)

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, ...])

Returned by Shell.PositionRow

columns: tuple[str, ...]

is_first: bool

is_last: bool

row: apsw.SQLiteValues

cmdloop(intro=None, transient=None)

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)

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() or complete_sql() as appropriate saving the results for subsequent calls.

complete_command(line, token, beg, end)

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)

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

fixup_backslashes(s)

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()

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='')

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()

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()

Deal with keyboard interrupt (typically Control-C). It will interrupt() the database and print”^C” if interactive.

log_handler(code, message)

Called with SQLite log messages when logging is ON

pop_input()

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()

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)

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)

Processes a dot command.

It is split into parts using shlex.split()

process_complete_line(command)

Given some text will call the appropriate method to process it (eg process_sql() or process_command())

process_sql(sql: str, bindings=None, internal=False, summary=None)

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)

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()

Saves the current input parameters to a stack. See pop_input().

push_output()

Saves the current output settings onto a stack. See pop_output() for more details as to why you would use this.

set_encoding(enc)

Saves enc as the default encoding, after verifying that it is valid. You can also include :error to specify error handling - eg ‘cp437:replace’

usage()

Returns the usage message.

write(dest, text)

Writes text to dest. dest will typically be one of self.stdout or self.stderr.

write_error(text)

Writes text to self.stderr colouring it

write_value(value, fmt=apsw.format_sql_value)

Writes colourized value to self.stdout converting to text with fmt

apsw.shell.main() → None

Call this to run the interactive shell. It automatically passes in sys.argv[1:] and exits Python when done.