Jun 6, 2025 · May 4, 2025 · May 4, 2025 · May 4, 2025 · May 4, 2025 · May 4, 2025
diff --git a/Doc/whatsnew/3.15.rst b/Doc/whatsnew/3.15.rst
  class, and migrated the output to the HTML5 standard.
  (Contributed by Jiahao Li in :gh:`134580`.)


 sqlite3
 -------

 * Support keyword completion in the :mod:`sqlite3` command-line interface.
  (Contributed by Long Tan in :gh:`133393`.)


 ssl
 ---

diff --git a/Lib/sqlite3/__main__.py b/Lib/sqlite3/__main__.py
 from textwrap import dedent
 from _colorize import get_theme, theme_no_color

 from ._completer import enable_completer


 def execute(c, sql, suppress_errors=True, theme=theme_no_color):
    """Helper that wraps execution of SQL code.
            execute(con, args.sql, suppress_errors=False, theme=theme)
        else:
            # No SQL provided; start the REPL.
            console = SqliteInteractiveConsole(con, use_color=True)
            try:
                import readline  # noqa: F401
            except ImportError:
                pass
            console.interact(banner, exitmsg="")
            with enable_completer():
                console = SqliteInteractiveConsole(con, use_color=True)
                console.interact(banner, exitmsg="")
    finally:
        con.close()

diff --git a/Lib/sqlite3/_completer.py b/Lib/sqlite3/_completer.py
 from _sqlite3 import SQLITE_KEYWORDS
 from contextlib import contextmanager

 _completion_matches = []


 def _complete(text, state):
    global _completion_matches
    if state == 0:
        text_upper = text.upper()
        _completion_matches = [c for c in SQLITE_KEYWORDS if c.startswith(text_upper)]
    try:
        return _completion_matches[state] + " "
    except IndexError:
        return None


 @contextmanager
 def enable_completer():
    try:
        import readline
    except ImportError:
        yield
        return

    old_completer = readline.get_completer()
    try:
        readline.set_completer(_complete)
        if readline.backend == "editline":
            # libedit uses "^I" instead of "tab"
            command_string = "bind ^I rl_complete"
        else:
            command_string = "tab: complete"
        readline.parse_and_bind(command_string)
        yield
    finally:
        readline.set_completer(old_completer)
diff --git a/Lib/test/test_sqlite3/test_cli.py b/Lib/test/test_sqlite3/test_cli.py
 """sqlite3 CLI tests."""
 import sqlite3
 import textwrap
 import unittest

 from _sqlite3 import SQLITE_KEYWORDS
 from sqlite3.__main__ import main as cli
 from test.support.import_helper import import_module
 from test.support.os_helper import TESTFN, unlink
 from test.support.pty_helper import run_pty
 from test.support import (
    captured_stdout,
    captured_stderr,
    captured_stdin,
    force_not_colorized_test_class,
    requires_subprocess,
 )


            self.assertIn('\x1b[1;35mOperationalError (SQLITE_ERROR)\x1b[0m: '
                          '\x1b[35mnear "sel": syntax error\x1b[0m', err)


 @requires_subprocess()
 class CompletionTest(unittest.TestCase):
    PS1 = "sqlite> "

    @classmethod
    def setUpClass(cls):
        readline = import_module("readline")
        if readline.backend == "editline":
            raise unittest.SkipTest("libedit readline is not supported")

    def write_input(self, input, env=None):
        script = textwrap.dedent("""
            import readline
            readline.parse_and_bind("set colored-completion-prefix off")
            from sqlite3.__main__ import main; main()
        """)
        return run_pty(script, input, env)

    def test_keyword_completion(self):
        # List candidates starting with 'S', there should be multiple matches.
        input = b"S\t\tEL\t 1;\n.quit\n"
        output = self.write_input(input)
        self.assertIn(b"SELECT", output)
        self.assertIn(b"SET", output)
        self.assertIn(b"SAVEPOINT", output)
        self.assertIn(b"(1,)", output)

        # Keywords are completed in upper case for even lower case user input
        input = b"sel\t\t 1;\n.quit\n"
        output = self.write_input(input)
        self.assertIn(b"SELECT", output)
        self.assertIn(b"(1,)", output)

    def test_nothing_to_complete(self):
        input = b"xyzzy\t\t\b\b\b\b\b.quit\n"
        # set NO_COLOR to disable coloring for self.PS1
        output = self.write_input(input, env={"NO_COLOR": "1"})
        output_lines = output.decode().splitlines()
        line_num = next((i for i, line in enumerate(output_lines, 1)
                         if line.startswith(f"{self.PS1}xyzzy")), -1)
        self.assertNotEqual(line_num, -1)
        # completions occupy lines, assert no extra lines when there is nothing
        # to complete
        self.assertEqual(line_num, len(output_lines))

    def test_completion_for_nothing(self):
        script = textwrap.dedent("""
            import readline
            readline.parse_and_bind("set colored-completion-prefix off")
            # hide control sequences surrounding each candidate
            readline.parse_and_bind("set colored-stats off")
            # hide "Display all xxx possibilities? (y or n)"
            readline.parse_and_bind("set completion-query-items 0")
            # hide "--More--"
            readline.parse_and_bind("set page-completions off")
            # show candidates one per line
            readline.parse_and_bind("set completion-display-width 0")
            from sqlite3.__main__ import main; main()
        """)
        input = b"\t\t.quit\n"
        output = run_pty(script, input, env={"NO_COLOR": "1"})
        output_lines = output.decode().splitlines()
        indices = [i for i, line in enumerate(output_lines)
                   if line.startswith(self.PS1)]
        self.assertEqual(len(indices), 2)
        start, end = indices[0] + 1, indices[1]
        candidates = list(map(str.strip, output_lines[start:end]))
        self.assertEqual(candidates, sorted(SQLITE_KEYWORDS))


 if __name__ == "__main__":
    unittest.main()
diff --git a/Misc/ACKS b/Misc/ACKS
 Geoff Talvola
 Anish Tambe
 Musashi Tamura
 Long Tan
 William Tanksley
 Christian Tanzer
 Steven Taschuk
diff --git a/Misc/NEWS.d/next/Library/2025-05-05-03-14-08.gh-issue-133390.AuTggn.rst b/Misc/NEWS.d/next/Library/2025-05-05-03-14-08.gh-issue-133390.AuTggn.rst
 Support keyword completion in the :mod:`sqlite3` command-line interface.
diff --git a/Modules/_sqlite/module.c b/Modules/_sqlite/module.c
 #include "microprotocols.h"
 #include "row.h"
 #include "blob.h"
 #include "util.h"

 #if SQLITE_VERSION_NUMBER < 3015002
 #error "SQLite 3.15.2 or higher required"
    return NULL;
 }

 static int
 add_keyword_tuple(PyObject *module)
 {
    int count = sqlite3_keyword_count();
    PyObject *keywords = PyTuple_New(count);
    if (keywords == NULL) {
        goto error;
    }
    for (int i = 0; i < count; i++) {
        const char *keyword;
        int size;
        int result = sqlite3_keyword_name(i, &keyword, &size);
        if (result != SQLITE_OK) {
            pysqlite_state *state = pysqlite_get_state(module);
            set_error_from_code(state, result);
            goto error;
        }
        PyObject *kwd = PyUnicode_FromStringAndSize(keyword, size);
        if (!kwd) {
            goto error;
        }
        if (PyTuple_SetItem(keywords, i, kwd) < 0) {
            goto error;
        }
    }
    return PyModule_Add(module, "SQLITE_KEYWORDS", keywords);

 error:
    Py_XDECREF(keywords);
    return -1;
 }

 static int
 add_integer_constants(PyObject *module) {
 #define ADD_INT(ival)                                           \
        goto error;
    }

    /* Set the keyword tuple */
    if (add_keyword_tuple(module) < 0) {
        goto error;
    }

    if (PyModule_AddStringConstant(module, "sqlite_version", sqlite3_libversion())) {
        goto error;
    }
Original file line number	Diff line number	Diff line change
Expand Up		@@ -96,6 +96,14 @@ difflib
		class, and migrated the output to the HTML5 standard.
		(Contributed by Jiahao Li in :gh:`134580`.)


		sqlite3
		-------

		* Support keyword completion in the :mod:`sqlite3` command-line interface.
erlend-aasland marked this conversation as resolved. OutdatedShow resolvedHide resolved
		(Contributed by Long Tan in :gh:`133393`.)


		ssl
		---

Expand Down
Original file line number	Diff line number	Diff line change
Expand Up		@@ -12,6 +12,8 @@
		from textwrap import dedent
		from _colorize import get_theme, theme_no_color

		from ._completer import enable_completer


		def execute(c, sql, suppress_errors=True, theme=theme_no_color):
		"""Helper that wraps execution of SQL code.
Expand DownExpand Up		@@ -136,12 +138,9 @@ def main(*args):
		execute(con, args.sql, suppress_errors=False, theme=theme)
		else:
		# No SQL provided; start the REPL.
		console = SqliteInteractiveConsole(con, use_color=True)
		try:
		import readline # noqa: F401
		except ImportError:
		pass
		console.interact(banner, exitmsg="")
		with enable_completer():
		console = SqliteInteractiveConsole(con, use_color=True)
		console.interact(banner, exitmsg="")
		finally:
		con.close()

Expand Down
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1,37 @@
		from _sqlite3 import SQLITE_KEYWORDS
		from contextlib import contextmanager

		_completion_matches = []


		def _complete(text, state):
		global _completion_matches
		if state == 0:
		text_upper = text.upper()
		_completion_matches = [c for c in SQLITE_KEYWORDS if c.startswith(text_upper)]
		try:
		return _completion_matches[state] + " "
		except IndexError:
		return None


		@contextmanager
		def enable_completer():
		try:
		import readline
		except ImportError:
		yield
		return

		old_completer = readline.get_completer()
		try:
		readline.set_completer(_complete)
		if readline.backend == "editline":
		# libedit uses "^I" instead of "tab"
tanloong marked this conversation as resolved. Show resolvedHide resolved
		command_string = "bind ^I rl_complete"
		else:
		command_string = "tab: complete"
erlend-aasland marked this conversation as resolved. Show resolvedHide resolved
		readline.parse_and_bind(command_string)
		yield
		finally:
		readline.set_completer(old_completer)
Original file line number	Diff line number	Diff line change
		@@ -1,14 +1,19 @@
		"""sqlite3 CLI tests."""
		import sqlite3
		import textwrap
		import unittest

		from _sqlite3 import SQLITE_KEYWORDS
picnixz marked this conversation as resolved. OutdatedShow resolvedHide resolved
		from sqlite3.__main__ import main as cli
		from test.support.import_helper import import_module
		from test.support.os_helper import TESTFN, unlink
		from test.support.pty_helper import run_pty
		from test.support import (
		captured_stdout,
		captured_stderr,
		captured_stdin,
		force_not_colorized_test_class,
		requires_subprocess,
		)


Expand DownExpand Up		@@ -200,5 +205,76 @@ def test_color(self):
		self.assertIn('\x1b[1;35mOperationalError (SQLITE_ERROR)\x1b[0m: '
		'\x1b[35mnear "sel": syntax error\x1b[0m', err)


		@requires_subprocess()
picnixz marked this conversation as resolved. Show resolvedHide resolved
		class CompletionTest(unittest.TestCase):
tanloong marked this conversation as resolved. OutdatedShow resolvedHide resolved
		PS1 = "sqlite> "
Copy link Member StanFromIrelandMay 16, 2025 Choose a reason for hiding this comment The reason will be displayed to describe this comment to others.Learn more. This is no longer freely customizable by users via sys.ps1 ? Copy link Contributor erlend-aaslandJun 6, 2025 Choose a reason for hiding this comment The reason will be displayed to describe this comment to others.Learn more. Can you explain your remark. Do you want changes?

		@classmethod
		def setUpClass(cls):
		readline = import_module("readline")
		if readline.backend == "editline":
		raise unittest.SkipTest("libedit readline is not supported")
picnixz marked this conversation as resolved. Show resolvedHide resolved

		def write_input(self, input, env=None):
		script = textwrap.dedent("""
		import readline
		readline.parse_and_bind("set colored-completion-prefix off")
		from sqlite3.__main__ import main; main()
		""")
picnixz marked this conversation as resolved. Show resolvedHide resolved
		return run_pty(script, input, env)

		def test_keyword_completion(self):
picnixz marked this conversation as resolved. OutdatedShow resolvedHide resolved
		# List candidates starting with 'S', there should be multiple matches.
		input = b"S\t\tEL\t 1;\n.quit\n"
		output = self.write_input(input)
		self.assertIn(b"SELECT", output)
		self.assertIn(b"SET", output)
		self.assertIn(b"SAVEPOINT", output)
		self.assertIn(b"(1,)", output)

		# Keywords are completed in upper case for even lower case user input
		input = b"sel\t\t 1;\n.quit\n"
		output = self.write_input(input)
		self.assertIn(b"SELECT", output)
		self.assertIn(b"(1,)", output)

picnixz marked this conversation as resolved. Show resolvedHide resolved
		def test_nothing_to_complete(self):
		input = b"xyzzy\t\t\b\b\b\b\b.quit\n"
		# set NO_COLOR to disable coloring for self.PS1
		output = self.write_input(input, env={"NO_COLOR": "1"})
		output_lines = output.decode().splitlines()
		line_num = next((i for i, line in enumerate(output_lines, 1)
		if line.startswith(f"{self.PS1}xyzzy")), -1)
		self.assertNotEqual(line_num, -1)
		# completions occupy lines, assert no extra lines when there is nothing
		# to complete
		self.assertEqual(line_num, len(output_lines))

		def test_completion_for_nothing(self):
		script = textwrap.dedent("""
		import readline
		readline.parse_and_bind("set colored-completion-prefix off")
		# hide control sequences surrounding each candidate
		readline.parse_and_bind("set colored-stats off")
		# hide "Display all xxx possibilities? (y or n)"
		readline.parse_and_bind("set completion-query-items 0")
		# hide "--More--"
		readline.parse_and_bind("set page-completions off")
		# show candidates one per line
		readline.parse_and_bind("set completion-display-width 0")
		from sqlite3.__main__ import main; main()
		""")
		input = b"\t\t.quit\n"
		output = run_pty(script, input, env={"NO_COLOR": "1"})
		output_lines = output.decode().splitlines()
		indices = [i for i, line in enumerate(output_lines)
		if line.startswith(self.PS1)]
		self.assertEqual(len(indices), 2)
		start, end = indices[0] + 1, indices[1]
		candidates = list(map(str.strip, output_lines[start:end]))
		self.assertEqual(candidates, sorted(SQLITE_KEYWORDS))


		if __name__ == "__main__":
picnixz marked this conversation as resolved. Show resolvedHide resolved
		unittest.main()
Original file line number	Diff line number	Diff line change
Expand Up		@@ -1867,6 +1867,7 @@ Neil Tallim
		Geoff Talvola
		Anish Tambe
		Musashi Tamura
		Long Tan
		William Tanksley
		Christian Tanzer
		Steven Taschuk
Expand Down
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1 @@
		Support keyword completion in the :mod:`sqlite3` command-line interface.
Original file line number	Diff line number	Diff line change
Expand Up		@@ -32,6 +32,7 @@
		#include "microprotocols.h"
		#include "row.h"
		#include "blob.h"
		#include "util.h"

		#if SQLITE_VERSION_NUMBER < 3015002
		#error "SQLite 3.15.2 or higher required"
Expand DownExpand Up		@@ -404,6 +405,38 @@ pysqlite_error_name(int rc)
		return NULL;
		}

		static int
		add_keyword_tuple(PyObject *module)
		{
		int count = sqlite3_keyword_count();
erlend-aasland marked this conversation as resolved. Show resolvedHide resolved
		PyObject *keywords = PyTuple_New(count);
		if (keywords == NULL) {
		goto error;
		}
		for (int i = 0; i < count; i++) {
		const char *keyword;
		int size;
		int result = sqlite3_keyword_name(i, &keyword, &size);
Copy link Contributor erlend-aaslandJun 6, 2025 Choose a reason for hiding this comment The reason will be displayed to describe this comment to others.Learn more. For the record: Wecould just`assert` that the result is`SQLITE_OK`. This is just an out-of-bounds check, and we know that we are within bounds here. OTOH, we don't know how the SQLite internals may change, so let's keep it like it is.
		if (result != SQLITE_OK) {
		pysqlite_state *state = pysqlite_get_state(module);
		set_error_from_code(state, result);
		goto error;
		}
		PyObject *kwd = PyUnicode_FromStringAndSize(keyword, size);
		if (!kwd) {
		goto error;
		}
		if (PyTuple_SetItem(keywords, i, kwd) < 0) {
serhiy-storchaka marked this conversation as resolved. OutdatedShow resolvedHide resolved
		goto error;
		}
		}
		return PyModule_Add(module, "SQLITE_KEYWORDS", keywords);

		error:
		Py_XDECREF(keywords);
		return -1;
		}

		static int
		add_integer_constants(PyObject *module) {
		#define ADD_INT(ival) \
Expand DownExpand Up		@@ -702,6 +735,11 @@ module_exec(PyObject *module)
		goto error;
		}

		/* Set the keyword tuple */
erlend-aasland marked this conversation as resolved. OutdatedShow resolvedHide resolved
		if (add_keyword_tuple(module) < 0) {
		goto error;
		}

		if (PyModule_AddStringConstant(module, "sqlite_version", sqlite3_libversion())) {
		goto error;
		}
Expand Down