Surprising choices_provider behavior in 4.0 when using sqlite3

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

Start the shell.
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())

@bknotwell

@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:

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)

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}")

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?

@bknotwell

@bknotwell Let us know what you think regarding the options discussed.

If you want, I think we would be happy to make threaded completion optional within cmd2.

I'd allow the user to set complete_in_thread to False when they create a shell object and keep the current multi-threaded mode as the default.

We should also ensure that a "mixed-mode"--recursive Cmd2 objects should coexist if their complete_in_thread settings are different.

@bknotwell

@bknotwell Can you please elaborate on what you mean by this? I don't follow.

We should also ensure that a "mixed-mode"--recursive Cmd2 objects should coexist if their complete_in_thread settings are different.

Imagine I have a Cmd2 shell that has a "sub-mode" that instantiates a new Cmd2 shell which means I have four test scenarios. Two of the scenarios are mixed ((Y, N) (N,Y)):

----------------------------------------
 *multi-threaded(default)*  *in-thread* 
----------------------------------------
 Y                          Y           
 Y                          N           
 N                          Y           
 N                          N           
----------------------------------------

Apologies for the confusing labels on the table. They should've been labeled main shell and sub shell with Y|N representing multi-threaded and in-thread respectively.

cmd2 isn't designed to support nested sub-applications like that. I'm not saying that it can't be done and we haven't done anything intentionally to preclude it. However, this goes strongly against the design principles on which this framework is based.

cmd2 supports Modular Commands via CommandSets. We also support the ability for dynamically enabling and disabling CommandSets (which can include sub-commands). On top of this we support Command Categories and the ability to enable and disable these. With these mechanisms you can effectively implement applications with "modes" without relying on nested cmd2.Cmd applications.

We aren't going to tell you how to use cmd2 - if you are using it with "sub-modes" and its working for you, then great. But this is never something we intentionally set out to support and if it is working by "accident", we don't promise to not break it in the future.

Interesting; I'll look into CommandSets.

BTW: in my experience (limited to a single-app that had 3 different modes), a mainloop called from a mainloop works as expected.

Our basic philosophy is that we want cmd2 applications to have a flat command hierarchy - i.e. you don't need to be in a certain "mode" to have access to any available command - they are all available from the top level.

That being said, we absolutely do want to support stateful applications where some commands/sub-commands are available or unavailable based on various state variables.

Navigation Menu

Repository navigation

Surprising choices_provider behavior in 4.0 when using sqlite3 #1680

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Surprising choices_provider behavior in 4.0 when using sqlite3 #1680

Description

Activity

tleonhardt commented on Jun 7, 2026

tleonhardt commented on Jun 9, 2026

bknotwell commented on Jun 9, 2026

bknotwell commented on Jun 9, 2026

tleonhardt commented on Jun 9, 2026

bknotwell commented on Jun 9, 2026

bknotwell commented on Jun 9, 2026

tleonhardt commented on Jun 9, 2026

bknotwell commented on Jun 9, 2026

tleonhardt commented on Jun 9, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions