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

Question

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.

Answer 1

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

Answer 2

You have at global scope:

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

When this is executed warmupsdb will be set to a list of all the rows that are currently in the database at startup time. No matter how many rows you may be adding to the database during execution, you have not modified warmupsdb. Yet your method gen is returning a sample taken from this initial list of rows. So it's only when you restart the code and warmupsdb is set again that the rows added in the previous run will be reflected.

I would suggest code such as the following:

import cmd
import sqlite3
from random import randint, sample

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

    def __init__(self):
        cmd.Cmd.__init__(self)
        self._db = sqlite3.connect('warmupsdb')

    def do_gen (self, number):
        try:
            number = int(number)
            if number < 1:
                raise ValueError('Non-positive number')
        except ValueError:
            print("Please provide a valid positive number.")
        else:
            # Get every exercise in the database
            cursor = self._db.cursor()
            warmupsdb = cursor.execute("SELECT * FROM warmups").fetchall()
            gen = sample(warmupsdb, number)
            for output in gen:
                print(output[1])

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

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

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

Note how the sqlite connection, being crucial to the operation of class warmups, is now encapsulated in that class.

I would also suggest you take a look at the PEP 8 Style Guide.