Showing all posts tagged Python

Here’s how to build an inventory app connected to a SQLite database using Python and tkinter. This is a basic GUI (graphical user interface) to view, edit, and calculate specific inventory sums. The example below is for an inventory of supplies to compliment small-scale shop keeping tasks. View the Github repository here.

1. Set up the initial SQLite database with desired column names.

First, we’ll have to create a SQLite database to connect to if one does not already exist. Import sqlite3 and contextlib to start.

import sqlite3

Create a connection to a database. In this instance, a new database will be created if one does not already exist with this name.

connection = sqlite3.connect("inventory.db")
cursor = connection.cursor()

Establish a cursor with the connection which we use to execute the creation of desired database columns. After ‘CREATE TABLE’, provide a name for the table, in this case it is ‘items’. In parentheses, list the desired column names followed by the data types to store each in. The full list of data type options for SQLite can be found here.

cursor.execute("CREATE TABLE items (name TEXT, quantity INTEGER, price INTEGER)")

Commit the changes to the database, and close the connection.

connection.commit()
connection.close() 

2. Create the main window.

Import the necessary packages (tkinter and sqlite3).

from tkinter import *
import sqlite3

Form the initial window for the application. Specify the dimensions using geometry and the title which will be in the header for the window.

window = Tk()
window.geometry("400x450")
window.title("Inventory Summary")

3. Create the entry fields, labels, functions, and buttons to access the database.

Add a Record to the Database

Create entry boxes and associated labels for the database columns (name, quantity, and price). The entry function creates an entry field within the specified window. The label function gives a label to the feature.

item_name = Entry(window, width=20)
item_name.grid(row=0, column=1, pady=2, sticky=W)
item_quantity = Entry(window, width=20)
item_quantity.grid(row=1, column=1, pady=2, sticky=W)
item_price = Entry(window, width=20)
item_price.grid(row=2, column=1, pady=2, sticky=W)

item_name_label = Label(window, text='Name ')
item_name_label.grid(row=0, column=0, pady=2, sticky=E)
item_quantity_label = Label(window,  text='Quantity ')
item_quantity_label.grid(row=1, column=0, pady=2, sticky=E)
item_price_label = Label(window, text ='Price ($) ')
item_price_label.grid(row=2,column=0, pady=2, sticky=E)

Write a function to carry out adding the new record to the database. Each function follows the same basic format where we create a connection to the database and set up the cursor. We use insert for the values entered in the form, close the connection, and then clear out the entries.

def submit():
    connection = sqlite3.connect("inventory.db")
    cursor = connection.cursor()
    cursor.execute("INSERT INTO items(name,quantity,price) VALUES (?,?,?)",(item_name.get(),item_quantity.get(),item_price.get()))
    connection.commit()
    connection.close()
    item_name.delete(0, END)
    item_quantity.delete(0, END)
    item_price.delete(0, END)

Create a button to click to add the record to the database.

submit_btn = Button(window, text="Add Record to Database", command=submit)
submit_btn.grid(row=3, column=0, columnspan=2, pady=2)

Show Records

Create the function to print out the records in the database. This selects all columns, including their original IDs from the SQLite table. Provide formatting for the display of data, including customizations for the price to come out in standard United State Dollar (USD).

def query():
    connection = sqlite3.connect("inventory.db")
    cursor = connection.cursor()
    cursor.execute("SELECT *, oid FROM items")
    records = cursor.fetchall()
    print(records)
    print_records = ''
    for record in records:
        print_records += str(record[0]) + ", " + str(record[1]) + " items, $" + "{:.2f}".format(float(record[2])) + ", ID" + "\t" + str(record[3]) +"\n"
    query_label = Label(window, text=print_records)
    query_label.grid(row=5, column=0, columnspan=2)
    connection.commit()
    connection.close()

Create a button to show the database’s records.

query_btn = Button(window, text="Show Records", command=query)
query_btn.grid(row=4, column=0, columnspan=2, pady=2)

Update a Record

The update record feature runs based on the ID specified in the ‘Select ID’ field.

select_box=Entry(window, width=20)
select_box.grid(row=6, column=1, pady=2, sticky=W)

select_box_label = Label(window, text='Select ID ')
select_box_label.grid(row=6, column=0, pady=2, sticky=E)

Then, I create two functions: one for actually updating the database, and another for creating the separate window where this action takes place. The separate window incorporates many of the same elements we already have in the primary window.

def update():
    connection = sqlite3.connect("inventory.db")
    cursor = connection.cursor()
    record_id = select_box.get()

    cursor.execute(
        'UPDATE items SET name=?, quantity=?, price=? WHERE oid=?',
        (item_name_editor.get(),item_quantity_editor.get(),item_price_editor.get(),record_id)
    )
    connection.commit()
    connection.close()
    editor.destroy()

def edit():
    global editor
    editor = Tk()
    editor.geometry("450x125")
    editor.title("Edit Inventory")
    connection = sqlite3.connect("inventory.db")
    cursor = connection.cursor()
    record_id = select_box.get()

    cursor.execute("SELECT * FROM items WHERE oid=?",(record_id))
    records = cursor.fetchall()

    global item_name_editor
    global item_quantity_editor
    global item_price_editor

    item_name_editor = Entry(editor, width=20)
    item_name_editor.grid(row=0, column=1, sticky=W)
    item_quantity_editor = Entry(editor, width=20)
    item_quantity_editor.grid(row=1, column=1, sticky=W)
    item_price_editor = Entry(editor, width=20)
    item_price_editor.grid(row=2, column=1, sticky=W)

    item_name_label_editor = Label(editor, text='Name ')
    item_name_label_editor.grid(row=0, column=0, sticky=E)
    item_quantity_label_editor = Label(editor,  text='Quantity ')
    item_quantity_label_editor.grid(row=1, column=0, sticky=E)
    item_price_label_editor = Label(editor, text ='Price ($) ')
    item_price_label_editor.grid(row=2,column=0, sticky=E)

    for record in records:
        item_name_editor.insert(0, record[0])
        item_quantity_editor.insert(0, record[1])
        item_price_editor.insert(0, record[2])
    save_btn = Button(editor, text="Save Record", command=update)
    save_btn.grid(row=11, column=0, columnspan=2, pady=10, padx=10, ipadx=145)
    connection.commit()
    connection.close()

Create the button for updating records.

edit_btn = Button(window, text="Update Record", command=edit)
edit_btn.grid(row=11, column=0, columnspan=2, pady=2)

Delete a Record

This function runs based on the ID specified in the ‘Select ID’ form.

def delete():
    connection = sqlite3.connect("inventory.db")
    cursor = connection.cursor()
    cursor.execute("DELETE from items WHERE oid=?",(select_box.get()))
    connection.commit()
    connection.close()

Create the button to remove a record from the database.

delete_btn = Button(window, text="Delete Record", command=delete)
delete_btn.grid(row=12, column=0, columnspan=2, pady=2)

4. Create a calculator button to inform updates.

One feature I wanted was a calculator for the price of a quantity within the total price of an item. For example, if I used 3 of item A, how much from the total price for that inventory would I potentially deduct. This uses the same ‘Select ID’ field mentioned above.

def calc_price():
    connection = sqlite3.connect("inventory.db")
    cursor = connection.cursor()
    cursor.execute("SELECT * FROM items WHERE oid=?",(select_box.get()))
    records = cursor.fetchall()
    price_sum = []
    for record in records:
        price_sum.append(round((record[2] / record[1]) * int(price_calc.get()),2))
    global calc_sum_label
    calc_sum_label = Label(window, text=price_sum)
    calc_sum_label.grid(row=9, column=0, columnspan=2, pady=2)
    connection.commit()
    connection.close()

After running the ‘Calculate Price Sum’ button, the output must be cleared each time before making another calculation.

def clear_output():
    connection = sqlite3.connect("inventory.db")
    cursor = connection.cursor()
    calc_sum_label.destroy()
    connection.commit()
    connection.close()

Create the entry and label for the price sum function.

price_calc=Entry(window, width=20)
price_calc.grid(row=7, column=1, pady=2, sticky=W)

price_calc_label = Label(window, text='Quantity for Price Sum ')
price_calc_label.grid(row=7, column=0, pady=2, sticky=E)

Create the ‘Caclulate Price Sum’ button and the ‘Clear Output’ button.

calculate_price_btn = Button(window, text="Calculate Price Sum", command=calc_price)
calculate_price_btn.grid(row=8, column=0, columnspan=2, pady=2)

clear_output_btn = Button(window, text="Clear Output", command=clear_output)
clear_output_btn.grid(row=10, column=0, columnspan=2, pady=2)

Layout with Tkinter

There are two methods for layout with tkinter: grid and pack. I use the grid method, which allows the app to be designed using column and row placement. I use additional arguments like column span to use more than one column for placement, and sticky to keep items to either the west or east sides of the specified columns.