/usr/share/doc/python-apsw/html/_sources/shell.txt is in python-apsw-doc 3.7.6.3-r1-1build1.
This file is owned by root:root, with mode 0o644.
The actual contents of the file can be viewed below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 | .. currentmodule:: apsw
.. _shell:
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
<http://www.sqlite.org/sqlite.html>`__ 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.
Commands
========
In addition to executing SQL, these are the commands available:
.. help-begin:
.. code-block:: text
.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 csv html insert
json line list python tabs tcl
.nullvalue STRING Print STRING in place of null values
.output FILENAME Send output to FILENAME (or stdout)
.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
.. help-end:
Command Line Usage
==================
You can use the shell directly from the command line. Invoke it like
this::
$ python -c "import apsw;apsw.main()" [options and arguments]
The following command line options are accepted:
.. usage-begin:
.. code-block:: text
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
.. usage-end:
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
<http://docs.python.org/library/readline.html>`__. Fortunately there
is one at http://ipython.scipy.org/moin/PyReadline/Intro which works.
However if this :class:`Shell` offers no completions it will start
matching filenames even if they make no sense in the context.
For Windows users you won't get colour output unless you install
`colorama <http://pypi.python.org/pypi/colorama>`__
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.
You can also use the shell programmatically (or even interactively and
programmatically at the same time). See the :ref:`example
<example-shell>` for using the API.
Unicode
=======
SQLite only works with `Unicode
<http://en.wikipedia.org/wiki/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. (For example on Unix like systems
the LC_CTYPE environment variable is sometimes used. On Windows it
can find out the `code page
<http://en.wikipedia.org/wiki/Code_page>`__.) You can override this
autodetection by setting the PYTHONIOENCODING environment variable.
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
<http://www.joelonsoftware.com/articles/Unicode.html>`__ 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 <http://en.wikipedia.org/wiki/Monkey_patch>`__
the shell as needed.
.. autoclass:: apsw.Shell
:members:
:undoc-members:
|