forked from zarino/zqlite
-
Notifications
You must be signed in to change notification settings - Fork 0
/
zqlite.py
executable file
·186 lines (152 loc) · 5.6 KB
/
zqlite.py
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
#!/usr/bin/env python
import os, sys
import readline
import rlcompleter
import sqlite3
import table # this is table.py, in the current directory
from collections import OrderedDict
# Enable tab completion under Mac OS X
# See: http://stackoverflow.com/questions/7116038/
if 'libedit' in readline.__doc__:
readline.parse_and_bind("bind ^I rl_complete")
else:
readline.parse_and_bind("tab: complete")
usage = """Enter an interactive SQLite shell:
$ ./zqlite.py [database-file]
Type ^D, "exit" or "quit" to leave shell.
"""
shell_help = '''
show tables show all tables, views and indices
describe [table-name] show columns in a table
exit / quit / q / ^D leave the zqlite shell
'''
db_file = None
suggestions_first = set(['create','delete','drop','insert','select','update','help','describe','quit','exit','show tables'])
suggestions_later = set(['sqlite_master'])
def main():
args = sys.argv[1:]
if len(args) == 1:
if not os.path.isfile(args[0]):
print >> sys.stderr, "%s is not a valid file path" % args[0]
print ''
print usage
exit()
global db_file
db_file = args[0]
else:
print usage
exit()
# Valid file supplied! Let's show them what's in it.
print database_overview()
# Populate the tab completion suggestions array
table_names = execute_command("select name from sqlite_master where type in ('table', 'view');")
if table_names:
for table_name in table_names:
suggestions_later.update([table_name['name']])
first_row = execute_command("""select * from "%s" limit 1;""" % table_name['name'])
if first_row:
suggestions_later.update(first_row[0].keys())
enter_shell()
def enter_shell():
previous_command_was_control_c = False
readline.set_completer(completer)
while True:
try:
a = raw_input("> ")
except KeyboardInterrupt, e:
# Allow user to ^C for a new console line
# Or ^C twice in a row to quit
if previous_command_was_control_c:
leave_shell()
else:
previous_command_was_control_c = True
sys.stdout.write('^C\n')
except EOFError, e:
# Allow user to ^D out of the console`
leave_shell()
else:
# Reset the ^C history monitor
previous_command_was_control_c = False
if a.strip().lower() in ['exit','.exit','quit','q']:
exit()
elif a.strip().lower().startswith('show tables'):
print database_overview()
elif a.strip().lower().startswith('help'):
print shell_help
elif a.strip().lower().startswith('describe'):
try:
table_name = a.strip().lower().split()[1]
except IndexError, e:
print 'Which table would you like to describe?'
else:
print table_overview(table_name)
else:
result = execute_command(a)
if isinstance(result, list) and len(result):
print table.table(result)
def execute_command(command):
conn = sqlite3.connect(db_file)
cursor = conn.cursor()
try:
result = cursor.execute(command)
except sqlite3.OperationalError, e:
print 'Invalid query'
print e
return False
else:
c = command.lower().strip()
if result.description:
column_names = [ col[0] for col in result.description ]
rows = []
for row in result:
temp_row = OrderedDict()
for i, cell in enumerate(row):
temp_row[column_names[i]] = cell
rows.append(temp_row)
return rows
else:
return None
conn.commit()
conn.close()
def database_overview():
result = execute_command("select type, name, sql from sqlite_master;")
if result:
# Calculate row counts only if db file is less than 1GB
if os.path.getsize(db_file) < 1024 ** 3:
for thing in result:
if thing['type'] in ['table', 'view']:
r2 = execute_command("""select count(*) as n from "%s";""" % thing['name'])
thing['rows'] = int(r2[0]['n'])
else:
thing['rows'] = None
return table.table(result)
def table_overview(table_name):
result = execute_command("""select * from "%s" limit 1;""" % table_name)
overview = []
if result:
for col_name, example_value in result[0].iteritems():
row = OrderedDict()
row['Column name'] = col_name
row['Example value'] = example_value
overview.append(row)
return table.table(overview)
def completer(text, state):
# Readline calls this function repeatedly, incrementing the "state" integer,
# until it returns None. It then uses all previously returned values
# as possible strings for autocompletion.
# So, we create a list of possible completions (based on "text" the user
# has already typed) and return each one in turn, until there are no more.
if len(readline.get_line_buffer().split()) < 2:
suggestions = suggestions_first
else:
suggestions = suggestions_later
options = [i+' ' for i in suggestions if i.startswith(text)]
if state < len(options):
return options[state]
else:
return None
def leave_shell():
sys.stdout.write('\n')
exit()
if __name__ == "__main__":
main()