1
2
3 """Interactive SQLite shell.
4
5 This script provides an interactive shell interface to an SQLite database.
6 The user can enter an arbitrary SQL command (supported by SQLite) using a
7 GNU Readline interface (if available), and the shell displays table of results
8 in a reasonably formatted ASCII table (or in another specified format).
9
10 Motivation
11 ----------
12
13 The intent is for SQLite to approach the same kind of easy-to-use interactive
14 tools present in a more elaborate MySQL setup (for example). To the author,
15 the nice advantage of SQLite is zero configuration to get the database
16 running: all you need is the single SQLite database file. With the inclusion
17 of SQLite in Python 2.5+, Python is all you need for access.
18
19 Usage
20 -----
21
22 The intended use of this script is from a command-line shell, as in::
23
24 ./sqlite_shell.py database_filename
25
26 or::
27
28 python sqlite_shell.py database_filename
29
30 You can also start the interactive shell from another Python program
31 by calling the `shell` function.
32
33 Although originally intended for purely ASCII interaction, it is often
34 helpful to have the results table of an SQL query in HTML format, for easy
35 cutting and pasting into the web (e.g., a blog or wiki). You can get
36 HTML-formatted tables using the ``-H`` or ``--html`` option.
37
38 To get a list of all command-line options; use ``sqlite_shell.py --help``.
39
40 Customization
41 -------------
42
43 If you want to add support to `shell` for your own format, just update the
44 `formats` dictionary, or pass in your format function directly into `shell`.
45 A format function takes two arguments, a table represented by a list of tuples
46 where each tuple is a row, and a tuple of column descriptions (titles), and
47 returns a newline-terminated string representation of the table.
48 The `formats` dictionary maps string names of formats to format functions.
49 To add support for additional command-line options, you can modify the
50 global `optparser` object (an instance of `optparse.OptionParser`).
51
52 License
53 -------
54
55 This software is written by `Erik Demaine`_ and distributed under the
56 `MIT license`_:
57
58 .. _`Erik Demaine`: http://theory.csail.mit.edu/~edemaine/
59 .. _`MIT license`: http://www.opensource.org/licenses/mit-license.php
60
61 Copyright (c) 2006-2007 Erik D. Demaine
62
63 Permission is hereby granted, free of charge, to any person obtaining a copy
64 of this software and associated documentation files (the "Software"), to deal
65 in the Software without restriction, including without limitation the rights
66 to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
67 copies of the Software, and to permit persons to whom the Software is
68 furnished to do so, subject to the following conditions:
69
70 The above copyright notice and this permission notice shall be included in all
71 copies or substantial portions of the Software.
72
73 THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
74 IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
75 FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
76 AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
77 LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
78 OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
79 SOFTWARE.
80 """
81
82 import optparse, sys, warnings
83 import sqlite3 as sqlite
84 try:
85 import readline
86 except ImportError:
87 warnings.warn ('Could not import readline; line editing limited.')
88
109
125
126 formats = {
127 'ascii': format_ascii,
128 'html': format_html,
129 }
130
131 -def shell (db, format = 'ascii'):
132 """Interactive SQLite shell.
133
134 This function interactively executes SQL commands on the given SQLite
135 database `db`, specified as either an open SQLite connection or as a
136 string specifying the filename of a SQLite database to open.
137 You can also specify a `format`, either a string which refers to an
138 entry in the global `formats` dictionary, or a format function.
139 A format function takes two arguments, a table represented by a list of
140 tuples where each tuple is a row, and a tuple of column descriptions
141 (titles), and returns a newline-terminated string representation of the
142 table.
143 """
144 if isinstance (format, str):
145 format = formats[format]
146 if isinstance (db, str):
147 db = sqlite.connect (db)
148 try:
149 return shell (db, format)
150 finally:
151 db.close ()
152 while True:
153 try:
154 command = raw_input ('[sqlite] ')
155 if command.lower ().strip () in ['quit', 'exit']:
156 print 'Goodbye.'
157 break
158 while not sqlite.complete_statement (command):
159 line = raw_input ('........ ')
160 if not line: break
161 command += '\n' + line
162 cursor = db.execute (command)
163 rows = cursor.fetchall ()
164 if rows:
165 descriptions = tuple (describe[0] for describe in cursor.description)
166 print format (rows, descriptions),
167 except EOFError:
168 print
169 print 'Aborting: EOF'
170 break
171 except KeyboardInterrupt:
172 print
173 print 'Aborting: ^C'
174 break
175 except sqlite.OperationalError, e:
176 print
177 print 'SQL Error:', e
178 print
179
180 optparser = optparse.OptionParser (usage = '%prog [options] database')
181 optparser.add_option ('-a', '--ascii', dest = 'format',
182 action = 'store_const', const = 'ascii', default = 'ascii',
183 help = 'output tables in ASCII (default)')
184 optparser.add_option ('-H', '--html', dest = 'format',
185 action = 'store_const', const = 'html',
186 help = 'output tables in HTML (for easy cut-and-paste)')
187
189 """Main program driven by command-line options.
190
191 This function uses the global `optparser` object (an instance of
192 `optparse.OptionParser`) to parse arguments, so you can add additional
193 options to that object.
194 """
195 options, args = optparser.parse_args ()
196 if len (args) != 1: optparser.error ('Expecting exactly one database')
197 shell (args[0], **options.__dict__)
198
199 if __name__ == '__main__': main ()
200