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: Optional[TextIO] = None, stdout=None, stderr=None, encoding: str = 'utf8', args=None, db=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

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

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)

Runs the main interactive command loop.

Parameters

intro – Initial text banner to display instead of the default. Make sure you newline terminate it.

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

display_timing(b4, after)

Writes the difference between b4 and after to self.stderr. The data is dictionaries returned from get_resource_usage().

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

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

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

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.

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

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)

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

process_sql(sql, 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’

Raises an exception on invalid encoding or error

usage()

Returns the usage message. Make sure it is newline terminated

write(dest, text)

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

main() → None

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