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
  (Contributed by Andrea Oliveri in :gh:`134004`.)


 sqlite3
 -------

 * Support SQL 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 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 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
 fromcontextlibimportcontextmanager

 try:
 from_sqlite3importSQLITE_KEYWORDS
 exceptImportError:
 SQLITE_KEYWORDS= ()

 _completion_matches= []


 def_complete(text,state):
 global_completion_matches

 ifstate==0:
 text_upper=text.upper()
 _completion_matches= [cforcinSQLITE_KEYWORDSifc.startswith(text_upper)]
 try:
 return_completion_matches[state]+" "
 exceptIndexError:
 returnNone


 @contextmanager
 defcompleter():
 try:
 importreadline
 exceptImportError:
 yield
 return

 old_completer=readline.get_completer()
 try:
 readline.set_completer(_complete)
 ifreadline.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 sys
 import textwrap
 import unittest

 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()
 @force_not_colorized_test_class
 class Completion(unittest.TestCase):
    PS1 = "sqlite> "

    @classmethod
    def setUpClass(cls):
        _sqlite3 = import_module("_sqlite3")
        if not hasattr(_sqlite3, "SQLITE_KEYWORDS"):
            raise unittest.SkipTest("unable to determine SQLite keywords")

        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
            from sqlite3.__main__ import main

            readline.parse_and_bind("set colored-completion-prefix off")
            main()
        """)
        return run_pty(script, input_, env)

    def test_complete_sql_keywords(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)

    @unittest.skipIf(sys.platform.startswith("freebsd"),
                    "Two actual tabs are inserted when there are no matching"
                    " completions in the pseudo-terminal opened by run_pty()"
                    " on FreeBSD")
    def test_complete_no_match(self):
        input_ = b"xyzzy\t\t\b\b\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"})
        lines = output.decode().splitlines()
        indices = (
            i for i, line in enumerate(lines, 1)
            if line.startswith(f"{self.PS1}xyzzy")
        )
        line_num = next(indices, -1)
        self.assertNotEqual(line_num, -1)
        # Completions occupy lines, assert no extra lines when there is nothing
        # to complete.
        self.assertEqual(line_num, len(lines))

    def test_complete_no_input(self):
        from _sqlite3 import SQLITE_KEYWORDS

        script = textwrap.dedent("""
            import readline
            from sqlite3.__main__ import main

            # Configure readline to ...:
            # - hide control sequences surrounding each candidate
            # - hide "Display all xxx possibilities? (y or n)"
            # - hide "--More--"
            # - show candidates one per line
            readline.parse_and_bind("set colored-completion-prefix off")
            readline.parse_and_bind("set colored-stats off")
            readline.parse_and_bind("set completion-query-items 0")
            readline.parse_and_bind("set page-completions off")
            readline.parse_and_bind("set completion-display-width 0")

            main()
        """)
        input_ = b"\t\t.quit\n"
        output = run_pty(script, input_, env={"NO_COLOR": "1"})
        lines = output.decode().splitlines()
        indices = [
            i for i, line in enumerate(lines)
            if line.startswith(self.PS1)
        ]
        self.assertEqual(len(indices), 2)
        start, end = indices
        candidates = [l.strip() for l in lines[start+1: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)
 {
 #if SQLITE_VERSION_NUMBER >= 3024000
    int count = sqlite3_keyword_count();
    PyObject *keywords = PyTuple_New(count);
    if (keywords == NULL) {
        return -1;
    }
    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;
        }
        PyTuple_SET_ITEM(keywords, i, kwd);
    }
    return PyModule_Add(module, "SQLITE_KEYWORDS", keywords);

 error:
    Py_DECREF(keywords);
    return -1;
 #else
    return 0;
 #endif
 }

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

    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		@@ -134,6 +134,13 @@ shelve
		(Contributed by Andrea Oliveri in :gh:`134004`.)


		sqlite3
		-------

		* Support SQL keyword completion in the :mod:`sqlite3` command-line interface.
		(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 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 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,42 @@
		fromcontextlibimportcontextmanager

		try:
		from_sqlite3importSQLITE_KEYWORDS
		exceptImportError:
		SQLITE_KEYWORDS= ()

		_completion_matches= []


		def_complete(text,state):
		global_completion_matches

		ifstate==0:
		text_upper=text.upper()
		_completion_matches= [cforcinSQLITE_KEYWORDSifc.startswith(text_upper)]
		try:
		return_completion_matches[state]+" "
		exceptIndexError:
		returnNone


		@contextmanager
		defcompleter():
		try:
		importreadline
		exceptImportError:
		yield
		return

		old_completer=readline.get_completer()
		try:
		readline.set_completer(_complete)
		ifreadline.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 sys
		import textwrap
		import unittest

		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,98 @@ 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
		@force_not_colorized_test_class
		class Completion(unittest.TestCase):
		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):
		_sqlite3 = import_module("_sqlite3")
		if not hasattr(_sqlite3, "SQLITE_KEYWORDS"):
		raise unittest.SkipTest("unable to determine SQLite keywords")

		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
		from sqlite3.__main__ import main

		readline.parse_and_bind("set colored-completion-prefix off")
		main()
		""")
		return run_pty(script, input_, env)

		def test_complete_sql_keywords(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)

picnixz marked this conversation as resolved. Show resolvedHide resolved
		@unittest.skipIf(sys.platform.startswith("freebsd"),
		"Two actual tabs are inserted when there are no matching"
		" completions in the pseudo-terminal opened by run_pty()"
		" on FreeBSD")
		def test_complete_no_match(self):
		input_ = b"xyzzy\t\t\b\b\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"})
		lines = output.decode().splitlines()
		indices = (
		i for i, line in enumerate(lines, 1)
		if line.startswith(f"{self.PS1}xyzzy")
		)
		line_num = next(indices, -1)
		self.assertNotEqual(line_num, -1)
		# Completions occupy lines, assert no extra lines when there is nothing
		# to complete.
		self.assertEqual(line_num, len(lines))

		def test_complete_no_input(self):
		from _sqlite3 import SQLITE_KEYWORDS

		script = textwrap.dedent("""
		import readline
		from sqlite3.__main__ import main

		# Configure readline to ...:
		# - hide control sequences surrounding each candidate
		# - hide "Display all xxx possibilities? (y or n)"
		# - hide "--More--"
		# - show candidates one per line
		readline.parse_and_bind("set colored-completion-prefix off")
		readline.parse_and_bind("set colored-stats off")
		readline.parse_and_bind("set completion-query-items 0")
		readline.parse_and_bind("set page-completions off")
		readline.parse_and_bind("set completion-display-width 0")

		main()
		""")
		input_ = b"\t\t.quit\n"
		output = run_pty(script, input_, env={"NO_COLOR": "1"})
		lines = output.decode().splitlines()
		indices = [
		i for i, line in enumerate(lines)
		if line.startswith(self.PS1)
		]
		self.assertEqual(len(indices), 2)
		start, end = indices
		candidates = [l.strip() for l in lines[start+1: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		@@ -1868,6 +1868,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,40 @@ pysqlite_error_name(int rc)
		return NULL;
		}

		static int
		add_keyword_tuple(PyObject *module)
		{
		#if SQLITE_VERSION_NUMBER >= 3024000
		int count = sqlite3_keyword_count();
erlend-aasland marked this conversation as resolved. Show resolvedHide resolved
		PyObject *keywords = PyTuple_New(count);
		if (keywords == NULL) {
		return -1;
		}
		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;
		}
		PyTuple_SET_ITEM(keywords, i, kwd);
		}
		return PyModule_Add(module, "SQLITE_KEYWORDS", keywords);

		error:
		Py_DECREF(keywords);
		return -1;
		#else
		return 0;
		#endif
		}

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

		if (add_keyword_tuple(module) < 0) {
		goto error;
		}

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