Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit 161b306

Browse filesBrowse files
tanloongtanloong
authored
gh-133390: sqlite3 CLI completion for tables, columns, indices, triggers, views, functions, schemata (GH-136101)
1 parent 5d2edf7 commit 161b306
Copy full SHA for 161b306

5 files changed

+202-15Lines changed: 202 additions & 15 deletions

File tree

Expand file treeCollapse file tree
Open diff view settings
Filter options
Expand file treeCollapse file tree
Open diff view settings
Collapse file

‎Doc/whatsnew/3.15.rst‎

Copy file name to clipboardExpand all lines: Doc/whatsnew/3.15.rst
+3Lines changed: 3 additions & 0 deletions
  • Display the source diff
  • Display the rich diff
Original file line numberDiff line numberDiff line change
@@ -498,6 +498,9 @@ sqlite3
498498
details.
499499
(Contributed by Stan Ulbrych and Łukasz Langa in :gh:`133461`.)
500500

501+
* Table, index, trigger, view, column, function, and schema completion on <tab>.
502+
(Contributed by Long Tan in :gh:`136101`.)
503+
501504

502505
ssl
503506
---
Collapse file

‎Lib/sqlite3/__main__.py‎

Copy file name to clipboardExpand all lines: Lib/sqlite3/__main__.py
+1-1Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -143,7 +143,7 @@ def main(*args):
143143
execute(con, args.sql, suppress_errors=False, theme=theme)
144144
else:
145145
# No SQL provided; start the REPL.
146-
with completer():
146+
with completer(con):
147147
console = SqliteInteractiveConsole(con, use_color=True)
148148
console.interact(banner, exitmsg="")
149149
finally:
Collapse file

‎Lib/sqlite3/_completer.py‎

Copy file name to clipboardExpand all lines: Lib/sqlite3/_completer.py
+71-7Lines changed: 71 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,4 @@
1+
from _sqlite3 import OperationalError
12
from contextlib import contextmanager
23

34
try:
@@ -10,32 +11,95 @@
1011
_completion_matches = []
1112

1213

13-
def _complete(text, state):
14+
def _complete(con, text, state):
1415
global _completion_matches
1516

1617
if state == 0:
17-
if text.startswith('.'):
18-
_completion_matches = [c for c in CLI_COMMANDS if c.startswith(text)]
18+
if text.startswith("."):
19+
_completion_matches = [
20+
c + " " for c in CLI_COMMANDS if c.startswith(text)
21+
]
1922
else:
2023
text_upper = text.upper()
21-
_completion_matches = [c for c in SQLITE_KEYWORDS if c.startswith(text_upper)]
24+
_completion_matches = [
25+
c + " " for c in SQLITE_KEYWORDS if c.startswith(text_upper)
26+
]
27+
28+
cursor = con.cursor()
29+
schemata = tuple(row[1] for row
30+
in cursor.execute("PRAGMA database_list"))
31+
# tables, indexes, triggers, and views
32+
# escape '_' which can appear in attached database names
33+
select_clauses = (
34+
f"""\
35+
SELECT name || ' ' FROM \"{schema}\".sqlite_master
36+
WHERE name LIKE REPLACE(:text, '_', '^_') || '%' ESCAPE '^'"""
37+
for schema in schemata
38+
)
39+
_completion_matches.extend(
40+
row[0]
41+
for row in cursor.execute(
42+
" UNION ".join(select_clauses), {"text": text}
43+
)
44+
)
45+
# columns
46+
try:
47+
select_clauses = (
48+
f"""\
49+
SELECT pti.name || ' ' FROM "{schema}".sqlite_master AS sm
50+
JOIN pragma_table_xinfo(sm.name,'{schema}') AS pti
51+
WHERE sm.type='table' AND
52+
pti.name LIKE REPLACE(:text, '_', '^_') || '%' ESCAPE '^'"""
53+
for schema in schemata
54+
)
55+
_completion_matches.extend(
56+
row[0]
57+
for row in cursor.execute(
58+
" UNION ".join(select_clauses), {"text": text}
59+
)
60+
)
61+
except OperationalError:
62+
# skip on SQLite<3.16.0 where pragma table-valued function is
63+
# not supported yet
64+
pass
65+
# functions
66+
try:
67+
_completion_matches.extend(
68+
row[0] for row in cursor.execute("""\
69+
SELECT DISTINCT UPPER(name) || '('
70+
FROM pragma_function_list()
71+
WHERE name NOT IN ('->', '->>') AND
72+
name LIKE REPLACE(:text, '_', '^_') || '%' ESCAPE '^'""",
73+
{"text": text},
74+
)
75+
)
76+
except OperationalError:
77+
# skip on SQLite<3.30.0 where function_list is not supported yet
78+
pass
79+
# schemata
80+
text_lower = text.lower()
81+
_completion_matches.extend(c for c in schemata
82+
if c.lower().startswith(text_lower))
83+
_completion_matches = sorted(set(_completion_matches))
2284
try:
23-
return _completion_matches[state] + " "
85+
return _completion_matches[state]
2486
except IndexError:
2587
return None
2688

2789

2890
@contextmanager
29-
def completer():
91+
def completer(con):
3092
try:
3193
import readline
3294
except ImportError:
3395
yield
3496
return
3597

3698
old_completer = readline.get_completer()
99+
def complete(text, state):
100+
return _complete(con, text, state)
37101
try:
38-
readline.set_completer(_complete)
102+
readline.set_completer(complete)
39103
if readline.backend == "editline":
40104
# libedit uses "^I" instead of "tab"
41105
command_string = "bind ^I rl_complete"
Collapse file

‎Lib/test/test_sqlite3/test_cli.py‎

Copy file name to clipboardExpand all lines: Lib/test/test_sqlite3/test_cli.py
+125-7Lines changed: 125 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -216,10 +216,6 @@ class Completion(unittest.TestCase):
216216

217217
@classmethod
218218
def setUpClass(cls):
219-
_sqlite3 = import_module("_sqlite3")
220-
if not hasattr(_sqlite3, "SQLITE_KEYWORDS"):
221-
raise unittest.SkipTest("unable to determine SQLite keywords")
222-
223219
readline = import_module("readline")
224220
if readline.backend == "editline":
225221
raise unittest.SkipTest("libedit readline is not supported")
@@ -229,12 +225,24 @@ def write_input(self, input_, env=None):
229225
import readline
230226
from sqlite3.__main__ import main
231227
228+
# Configure readline to ...:
229+
# - hide control sequences surrounding each candidate
230+
# - hide "Display all xxx possibilities? (y or n)"
231+
# - show candidates one per line
232232
readline.parse_and_bind("set colored-completion-prefix off")
233+
readline.parse_and_bind("set completion-query-items 0")
234+
readline.parse_and_bind("set page-completions off")
235+
readline.parse_and_bind("set completion-display-width 0")
236+
233237
main()
234238
""")
235239
return run_pty(script, input_, env)
236240

237241
def test_complete_sql_keywords(self):
242+
_sqlite3 = import_module("_sqlite3")
243+
if not hasattr(_sqlite3, "SQLITE_KEYWORDS"):
244+
raise unittest.SkipTest("unable to determine SQLite keywords")
245+
238246
# List candidates starting with 'S', there should be multiple matches.
239247
input_ = b"S\t\tEL\t 1;\n.quit\n"
240248
output = self.write_input(input_)
@@ -254,6 +262,118 @@ def test_complete_sql_keywords(self):
254262
output = self.write_input(input_)
255263
self.assertIn(b".version", output)
256264

265+
def test_complete_table_indexes_triggers_views(self):
266+
input_ = textwrap.dedent("""\
267+
CREATE TABLE _Table (id);
268+
CREATE INDEX _Index ON _table (id);
269+
CREATE TRIGGER _Trigger BEFORE INSERT
270+
ON _Table BEGIN SELECT 1; END;
271+
CREATE VIEW _View AS SELECT 1;
272+
273+
CREATE TEMP TABLE _Temp_table (id);
274+
CREATE INDEX temp._Temp_index ON _Temp_table (id);
275+
CREATE TEMP TRIGGER _Temp_trigger BEFORE INSERT
276+
ON _Table BEGIN SELECT 1; END;
277+
CREATE TEMP VIEW _Temp_view AS SELECT 1;
278+
279+
ATTACH ':memory:' AS attached;
280+
CREATE TABLE attached._Attached_table (id);
281+
CREATE INDEX attached._Attached_index ON _Attached_table (id);
282+
CREATE TRIGGER attached._Attached_trigger BEFORE INSERT
283+
ON _Attached_table BEGIN SELECT 1; END;
284+
CREATE VIEW attached._Attached_view AS SELECT 1;
285+
286+
SELECT id FROM _\t\tta\t;
287+
.quit\n""").encode()
288+
output = self.write_input(input_)
289+
lines = output.decode().splitlines()
290+
indices = [i for i, line in enumerate(lines)
291+
if line.startswith(self.PS1)]
292+
start, end = indices[-3], indices[-2]
293+
candidates = [l.strip() for l in lines[start+1:end]]
294+
self.assertEqual(candidates,
295+
[
296+
"_Attached_index",
297+
"_Attached_table",
298+
"_Attached_trigger",
299+
"_Attached_view",
300+
"_Index",
301+
"_Table",
302+
"_Temp_index",
303+
"_Temp_table",
304+
"_Temp_trigger",
305+
"_Temp_view",
306+
"_Trigger",
307+
"_View",
308+
],
309+
)
310+
start, end = indices[-2], indices[-1]
311+
# direct match with '_Table' completed, no candidates displayed
312+
candidates = [l.strip() for l in lines[start+1:end]]
313+
self.assertEqual(len(candidates), 0)
314+
315+
@unittest.skipIf(sqlite3.sqlite_version_info < (3, 16, 0),
316+
"PRAGMA table-valued function is not available until "
317+
"SQLite 3.16.0")
318+
def test_complete_columns(self):
319+
input_ = textwrap.dedent("""\
320+
CREATE TABLE _table (_col_table);
321+
CREATE TEMP TABLE _temp_table (_col_temp);
322+
ATTACH ':memory:' AS attached;
323+
CREATE TABLE attached._attached_table (_col_attached);
324+
325+
SELECT _col_\t\tta\tFROM _table;
326+
.quit\n""").encode()
327+
output = self.write_input(input_)
328+
lines = output.decode().splitlines()
329+
indices = [
330+
i for i, line in enumerate(lines) if line.startswith(self.PS1)
331+
]
332+
start, end = indices[-3], indices[-2]
333+
candidates = [l.strip() for l in lines[start+1:end]]
334+
335+
self.assertEqual(
336+
candidates, ["_col_attached", "_col_table", "_col_temp"]
337+
)
338+
339+
@unittest.skipIf(sqlite3.sqlite_version_info < (3, 30, 0),
340+
"PRAGMA function_list is not available until "
341+
"SQLite 3.30.0")
342+
def test_complete_functions(self):
343+
input_ = b"SELECT AV\t1);\n.quit\n"
344+
output = self.write_input(input_)
345+
self.assertIn(b"AVG(1);", output)
346+
self.assertIn(b"(1.0,)", output)
347+
348+
# Functions are completed in upper case for even lower case user input.
349+
input_ = b"SELECT av\t1);\n.quit\n"
350+
output = self.write_input(input_)
351+
self.assertIn(b"AVG(1);", output)
352+
self.assertIn(b"(1.0,)", output)
353+
354+
def test_complete_schemata(self):
355+
input_ = textwrap.dedent("""\
356+
ATTACH ':memory:' AS MixedCase;
357+
-- Test '_' is escaped in Like pattern filtering
358+
ATTACH ':memory:' AS _underscore;
359+
-- Let database_list pragma have a 'temp' schema entry
360+
CREATE TEMP TABLE _table (id);
361+
362+
SELECT * FROM \t\tmIX\t.sqlite_master;
363+
SELECT * FROM _und\t.sqlite_master;
364+
.quit\n""").encode()
365+
output = self.write_input(input_)
366+
lines = output.decode().splitlines()
367+
indices = [
368+
i for i, line in enumerate(lines) if line.startswith(self.PS1)
369+
]
370+
start, end = indices[-4], indices[-3]
371+
candidates = [l.strip() for l in lines[start+1:end]]
372+
self.assertIn("MixedCase", candidates)
373+
self.assertIn("_underscore", candidates)
374+
self.assertIn("main", candidates)
375+
self.assertIn("temp", candidates)
376+
257377
@unittest.skipIf(sys.platform.startswith("freebsd"),
258378
"Two actual tabs are inserted when there are no matching"
259379
" completions in the pseudo-terminal opened by run_pty()"
@@ -274,8 +394,6 @@ def test_complete_no_match(self):
274394
self.assertEqual(line_num, len(lines))
275395

276396
def test_complete_no_input(self):
277-
from _sqlite3 import SQLITE_KEYWORDS
278-
279397
script = textwrap.dedent("""
280398
import readline
281399
from sqlite3.__main__ import main
@@ -306,7 +424,7 @@ def test_complete_no_input(self):
306424
self.assertEqual(len(indices), 2)
307425
start, end = indices
308426
candidates = [l.strip() for l in lines[start+1:end]]
309-
self.assertEqual(candidates, sorted(SQLITE_KEYWORDS))
427+
self.assertEqual(candidates, sorted(candidates))
310428
except:
311429
if verbose:
312430
print(' PTY output: '.center(30, '-'))
Collapse file

‎Misc/NEWS.d/next/Library/2025-06-29-22-01-00.gh-issue-133390.I1DW_3.rst‎

Copy file name to clipboard
+2Lines changed: 2 additions & 0 deletions
  • Display the source diff
  • Display the rich diff
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,2 @@
1+
Support table, index, trigger, view, column, function, and schema completion
2+
for :mod:`sqlite3`'s :ref:`command-line interface <sqlite3-cli>`.

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.