The Produce Inventory Assistant (PIA) Project is an easy-to-use program that manages produce inventory information. The program runs via the command line and uses a database to store produce information (name, PLU code, stock, and price). A user can manage the database by entering specific commands at the user prompt.

History

PIA has an interesting history behind it.  It was the first project I worked on when I first started to learn Python. At the time, It was a huge undertaking and it took me one week to get to the point of near completion. It was at this point that I hit a big problem. I could not save the data that I was changing. Every time I started the program, my changes wouldn’t be there. After searching for solutions, I came to the conclusion that I would have to code the program from scratch. Upon starting the project, I knew that I would eventually encounter this problem, but I did not anticipate having to redo the whole program!

I attempted to implement some quick fixes, but they were to no avail. It was then that I decided to stop working on the project. I would complete it when I had a better understanding of Python.

Process

Seven months passed and I concluded that it was time to finish PIA. I understood how to save and manipulate data in a database and figured that I could use what I learned to finally complete PIA. I started working from scratch, using the script from last time as a guide. I first coded the functions that I needed to work with SQLite3, the database I decided to use for the program. I then started to code the functions that were to be called by the user. It was here where I hit my only problem.

I hit an sqlite3.OperationalError attempting to create a second table in the database. At first I thought that I was wrong in assuming that I could have multiple tables in a database. It turned out that I was right in my assumption. The next thing I did was check my SQL statement syntax for I knew that I have made a syntax error before. Everything was proper, and so I took a few minutes to ponder the cause of the error.

It then occurred to me that I could try to follow the traceback error and check the code before the error occurred. After checking the code prior, I noticed why the error was happening:

sql = 'CREATE table order (name, number, price)'

It turned out that I was attempting to create a table called ‘order’ which anyone who works with SQL knows that ‘order’ is actually a SQL statement. I changed the table name to ‘orders’ and thus, the problem was solved!

Code Snippets

There are two functions in the program that I want to note. The first being the following:

def init_produce(conn):
    print("THIS WILL SET ALL DATABASE ITEMS TO THEIR DEFAULTS!!!")
    init = input("Continue [y/n]?: ")

    if init[0].lower() == "y":
        for item in plu_produce.produce:
            name = item["name"]
            code = item["code"]
            stock = int(item["stock"])
            price = float(item["price"])

            sql = 'INSERT into produce (name, code, stock, price) ' /
                   'values ("{0}", "{1}", "{2}", "{3}")'
            sql = sql.format(name, code, stock, price)
            conn.execute(sql)
        print("\nSuccessfully initiated Produce database!")

This function reads the list of dictionaries (called produce) in plu_produce.py (which contains the default values for each produce item). It then inserts each item into the ‘produce’ table so the user can start using the program. This function should only be called once as it sets all items to their default values.

The second function creates an ‘order’ (like a grocery list) for the user via the ‘create order’ command:

def create_order(conn):
    try:
        name = input("Name of Produce [eg. Carrots (Bunch)]: ")
        number = int(input("Number of {}: ".format(name)))
        price = 0.0

        sql = 'SELECT * FROM produce WHERE name="{}"'.format(name.title())
        result = conn.execute(sql)
        produce = result.fetchall()

        for item in produce:
            name = item[0]
            price = float(item[3])

        sql = 'INSERT INTO orders (name, number, price) values ("{0}", "{1}", "{2}")'
        sql = sql.format(name, number, price)
        conn.execute(sql)
    except ValueError:
        print("\nERROR: Input must be an integer!")

    choice = input("\nOrder another item [y/n]?: ")

    if choice[0].lower() == "y":
        create_order(conn)

Once the order is created, the user can then decide to add the order to the database, or subtract the order from the database via the ‘order in’/’order out’ commands. After the user is done, they can delete the order with the ‘del order’ command.

Overall

To my surprise, I completed the project in 1/8^th the time it took me to ‘complete’ it the first time. It was nostalgic to go back and see the initial code for the project, even if it was bad! I am satisfied with the result and I do hope that one day I can take this program a step further with GUI implementation. However, that might be years from now. I guess we will have to wait and see!

The source code for the PIA project can be found here. Note that as of this writing I have not provided a licence for the project, meaning that standard copyright rules apply.