[FIXED] (Python Cmd) sqlite3 commit not occuring without restarting the application

Issue

I am having an issue with sqlite3 and the python cmd library. I am making a simple shell app to generate a random sample of warmup exercises from a database using sqlite3. Here is the full code as it is fairly short :

import cmd
import sqlite3
from random import randint, sample

# Connect database & create cursor
db = sqlite3.connect('warmupsdb')
cursor = db.cursor()

# Get every exercise in the database
warmupsdb = cursor.execute("SELECT * FROM warmups").fetchall()

# Get the length of the database for error checking
warmupslen = len(warmupsdb)



class warmups(cmd.Cmd):
    intro = "Hello World !"
    prompt = "warmups > "
    """Simple cmd program."""

    def do_gen (self, number):
        if is_validInt(number):
            number = int(number)
            gen = sample(warmupsdb, number)
            for output in gen:
                print(output[1])
        else:
            print("Please provide a valid number.")

    def do_add(self, text):
        cursor.execute("INSERT INTO warmups (warmupName) VALUES (?)", (text,))
        db.commit()

    def do_exit(self,line):
        db.close()
        return True

if __name__ == '__main__':
    warmups().cmdloop()

My issue arises in the do_add()function, no error arises but the committed changes do not appear in the application until I restart it. For example if my database has the sample :

"Test 1", "Test 2", "Test 3"

And I use the command

add Test 4

The gen command will never give back "Test 4" as it seems it is not yet in the database which is not the behavior I intended, I would like to be able to generate samples that have been added during the current instance.

I have tried to find information in the documentation of sqlite3 and cmd, and also on this specific issue but there isn't a lot of information on the usage of the cmd library in conjunction with an sql database.

Solution

The items are added to the database, but you use old data fetched at the beginning of script with SELECT statement.

warmupsdb = cursor.execute("SELECT * FROM warmups").fetchall()

You need to update the data you operate on. E.g. function do_add can be extended with the following refresh of data:

global warmupsdb
warmupsdb = cursor.execute("SELECT * FROM warmups").fetchall()

Answered By - przemL