Surprising choices_provider behavior in 4.0 when using sqlite3

[bknotwell]

People upgrading single-threaded applications to 4.0 may be surprised. Reproduce with the following methodology (tested with Python3.14.5 on OSX):

1. Start the shell.
2. Type t and then TAB TAB and a SQLite error occurs.

Failed query: SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 8421351040 and this is thread id 6186856448.

MWE:

#!/usr/bin/env python3
"""A simple cmd2 application."""
from cmd2 import Cmd, Cmd2ArgumentParser, with_argparser, Choices
import sqlite3

class FirstApp(Cmd):
    # possibly unwise workaround
    # conn = sqlite3.connect(':memory:', check_same_thread=False)
    conn = sqlite3.connect(':memory:')
    conn.executescript('''
        CREATE TABLE xxx (X INTEGER);
        INSERT INTO  xxx (X) values(3);
        INSERT INTO  xxx (X) values(4);
        INSERT INTO  xxx (X) values(5);
        INSERT INTO  xxx (X) values(6);
    ''')

    """A simple cmd2 application."""
    def do_hello_world(self, _: cmd2.Statement):
        self.poutput(f'Hello World: {self.conn}')

    def _t_completer(self):
        try:
            ids = list(map(lambda x: x[0], self.conn.execute('SELECT X FROM xxx').fetchall()))
            return Choices.from_values(ids)
        except sqlite3.Error as exc:
            self.perror(f'Failed query: {exc}')

    set_t_parser = Cmd2ArgumentParser(description='Test')
    set_t_parser.add_argument('ttt', help='ttt help', choices_provider=_t_completer)
    @with_argparser(set_t_parser)
    def do_t(self, args):
        self.poutput(args.ttt)

if __name__ == '__main__':
    import sys
    
    c = FirstApp()
    sys.exit(c.cmdloop())

[tleonhardt]

@bknotwell Thank you very much for bringing this behavior to our attention!

This surprising behavior is happening because in version 4.0.0, cmd2 switched to using prompt-toolkit under the hood for providing completions (instead of readline). prompt-toolkit executes tab completions , including your choices_provider, in a background thread to prevent blocking the interactive CLI. By default, Python's sqlite3 module restricts database connection objects from being accessed in any thread other than the one where they were created (check_same_thread=True).

Ways I can think of to fix this include:

1. Disable check_same_thread in your application - this is the simplest option and should work in most cases
   Because the underlying C implementation of SQLite is compiled with thread safety enabled in Python 3, it is generally safe to share the connection across threads as long as you aren't running complex concurrent write transactions. You can disable the Python-level thread check by adding check_same_thread=False.

class FirstApp(Cmd):
    # Change the connection instantiation to:
    conn = sqlite3.connect(":memory:", check_same_thread=False)

2. Use a Shared-Cache URI & Thread-Local Connections - more complex but should work in all cases
   If you want to strictly maintain the check_same_thread protection while using an in-memory database, you can use SQLite's shared cache. This allows you to create a new connection inside the background thread that points to the exact same in-memory database created by the main thread.

class FirstApp(Cmd):
     # 1. Update the main connection to use a shared memory URI:
    conn = sqlite3.connect("file:memdb1?mode=memory&cache=shared", uri=True)
    # ... setup tables ...
   
    def _t_completer(self):
        try:
            # 2. Create a new, thread-local connection inside the completer:
            with sqlite3.connect("file:memdb1?mode=memory&cache=shared", uri=True) as local_conn:
                ids = [x[0] for x in local_conn.execute("SELECT X FROM xxx").fetchall()]
            return Choices.from_values(ids)
        except sqlite3.Error as exc:
            self.perror(f"Failed query: {exc}")

3. Provide an option to alter cmd2's threaded completion behavior by setting complete_in_thread to False - not recommended
   cmd2's threaded completion behavior is hardcoded to True during its PromptSession initialization to ensure the UI doesn't stutter on slow autocomplete queries, so modifying the application's data-access layer as shown in the two options above is likely the better option. If for some reason neither of those seem acceptable, we could provide an option within cmd2 to allow an application author to bypass the default behavior and perform completion on the main thread.

@kmvanbrunt Do you have any further thoughts or suggestions?