Storing procurement data in database

Inserting input data to Procurement table of MySQL database

Collecting product id and name

From plus2_product table we will collect product names ( p_name) and product id ( p_id ). Product names will be used as options for the combobox and on selection of the option we will trigger one function my_upd() to display the Product name and product ID.

From the MySQL table we will collect the product name and product id and using that we will create one dictionary ( my_dict). The product id ( p_id ) are the keys of the dictionary and the values of the dictionary ( name of the products ) will be used as options of the combobox by using values parameter.

Inserting procurement goods details in MySQL table using product name and id through user inputs

Connection to MySQL database

from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://userid:password@localhost/db_name")

Query to get p_id and p_name and then create the dictionary.

query = "SELECT p_id,p_name FROM plus2_product"
my_data = list(my_conn.execute(query))  # collect data from table 

my_dict = {}#Create a dictionary with key as id and product name as value 
for row in my_data:
    my_dict[row[0]] = row[1]  # p_id as key and product name as value

Integrating product names as options of the combobox.

cb1 = ttk.Combobox(
    my_w,textvariable=sel,values=list(my_dict.values()),bootstyle="success"
)

On change event of Combobox

We used one StringVar() sel and assigned it to textvariable option of the Combobox cb1. textvariable=sel.
To trigger the on change event of Combobox we used trace() method.

sel.trace("w", my_upd)  # on change of Combobox cb1

Getting product id and product name

def my_upd(*args):
    global p_id, p_name # global variables 
    l1.config(text="") # remove all previous text 
    for i, j in my_dict.items(): # Key and value of the dictionary 
        if j == sel.get(): # if slected option matches with dictionary value
            p_id, p_name = i, j # set the p_id and p_value
            l1.config(text=" Product ID: " + str(i) + " \n Product: " + j)
            break # come out of the for loop

Inserting input data

We have plus2_product_receive table where all input data of procurement will be stored. Here we have 5 inputs we are sending to table and the autoincrement column id will be generated by MySQL.

We are sending Query and data part separately to prevent injection attack.

def my_insert():
    global p_id, p_name
    query = "INSERT INTO  plus2_product_receive (p_id,product,price,qty,dt) \
            VALUES(%s,%s,%s,%s,%s)"
    my_data = (p_id, p_name, price_v.get(), quantity_v.get(), dt.entry.get())
    print(query, my_data)
    id = my_conn.execute(query, my_data)
    l1.config(text="Data inserted, ID: " + str(id.lastrowid), bootstyle="success")
    l1.after(3000, lambda: my_reset())

Reset the inputs

To prepare for next input data, after inserting all the data will be reset after 3 seconds ( 3000 milliseconds ). Check the last line in above code where after() is used to create a delay for 3 seconds.

def my_reset():
    cb1.delete(0, "end")
    price_v.set(0)
    quantity_v.set(1)
    l1.config(text="Ready", bootstyle="inverse-warning")

import tkinter as tk
import ttkbootstrap as ttk

my_w = tk.Tk()
my_w.geometry("550x450")  # Size of the window
my_w.title("www.plus2net.com")  # Adding a title

from sqlalchemy import create_engine

my_conn = create_engine("mysql+mysqldb://root:test@localhost/my_tutorial")
query = "SELECT p_id,p_name FROM plus2_product"
my_data = list(my_conn.execute(query))
my_dict = {}
p_id, p_name = 0, ""

for row in my_data:
    my_dict[[row][0][0]] = row[1]  # Create a dictionary with key as id


def my_upd(*args):
    global p_id, p_name
    l1.config(text="")
    for i, j in my_dict.items():
        if j == sel.get():
            p_id, p_name = i, j
            l1.config(text=" Product ID: " + str(i) + " \n Product: " + j)
            break


def my_insert():
    global p_id, p_name
    query = "INSERT INTO  plus2_product_receive (p_id,product,price,qty,dt) \
            VALUES(%s,%s,%s,%s,%s)"
    my_data = (p_id, p_name, price_v.get(), quantity_v.get(), dt.entry.get())
    print(query, my_data)
    id = my_conn.execute(query, my_data)
    l1.config(text="Data inserted, ID: " + str(id.lastrowid), bootstyle="success")
    l1.after(3000, lambda: my_reset())


def my_reset():
    cb1.delete(0, "end")
    price_v.set(0)
    quantity_v.set(1)
    l1.config(text="Ready", bootstyle="inverse-warning")


font1 = ["Times", 50, "normal"]
l_top = ttk.Label(
    my_w,
    text="Procurement",
    bootstyle="inverse-info",
    font=font1,
    anchor="center",
    width=15,
)
l_top.grid(row=0, column=1, columnspan=3, padx=20, pady=10)
l_product = ttk.Label(
    text="Product", bootstyle="inverse-primary", anchor="sw", width=15, font=20
)
l_product.grid(row=1, column=1, padx=10, pady=10)

l_price = ttk.Label(
    text="Price", bootstyle="inverse-primary", anchor="sw", width=15, font=20
)
l_price.grid(row=2, column=1, padx=10, pady=10)

l_qty = ttk.Label(
    text="Quantity", bootstyle="inverse-primary", anchor="sw", width=15, font=20
)
l_qty.grid(row=3, column=1, padx=10, pady=10)

l_date = ttk.Label(
    text="Date", bootstyle="inverse-primary", anchor="sw", width=15, font=20
)
l_date.grid(row=4, column=1, padx=10, pady=10)

sel = tk.StringVar()
cb1 = ttk.Combobox(
    my_w, textvariable=sel, values=list(my_dict.values()), bootstyle="success"
)
cb1.grid(row=1, column=2, padx=5, pady=10)
price_v = ttk.DoubleVar(value=0)
price = ttk.Entry(my_w, textvariable=price_v, bootstyle="success")
price.grid(row=2, column=2, padx=10, pady=10)
quantity_v = ttk.IntVar(value=1)
quantity = ttk.Entry(my_w, textvariable=quantity_v, bootstyle="success")
quantity.grid(row=3, column=2, padx=10, pady=10)
dt = ttk.DateEntry(my_w, dateformat="%Y-%m-%d", bootstyle="success")
dt.grid(row=4, column=2, padx=10, pady=10)

l1 = ttk.Label(my_w, text="Details", bootstyle="inverse-warning", font=12, width=20)
l1.grid(row=1, column=3)

b1 = ttk.Button(my_w, text="Submit", command=lambda: my_insert(), bootstyle="success")
b1.grid(row=5, column=2, padx=10, pady=20)

sel.trace("w", my_upd)  # Unchange of Combobox cb1

my_w.mainloop()  # Keep the window open

Adding Product Search option to Combobox

Seaching Combobox Options

import tkinter as tk
import ttkbootstrap as ttk

my_w = tk.Tk()
my_w.geometry("560x450")  # Size of the window
my_w.title("www.plus2net.com")  # Adding a title

from sqlalchemy import create_engine
from sqlalchemy.sql import text
my_conn = create_engine("mysql+mysqldb://id:pw@localhost/my_db")
query = "SELECT p_id,p_name FROM plus2_product"
my_data = list(my_conn.execute(query))
my_dict = {}
p_id, p_name = 0, ""

for row in my_data:
    my_dict[row[0]] = row[1]  # dictionay with key as p_id


def my_upd(*args):
    my_dict = {}
    query = text("SELECT p_id,p_name FROM plus2_product WHERE p_name like :e1")
    # data=cb1.get()
    print(query)
    my_data = list(my_conn.execute(query, e1="%" + cb1.get() + "%"))
    cb1["values"] = []
    for row in my_data:
        my_dict[row[0]] = row[1]
    print(my_data)
    cb1["values"] = list(my_dict.values())
    l1.config(text="Number records : " + str(len(my_data)))


def my_disp(*args):
    global p_id, p_name
    l1.config(text="")
    for i, j in my_dict.items():
        if j == sel.get():
            p_id, p_name = i, j
            l1.config(text=" Product ID: " + str(i) + " \n Product: " + j)
            break


def my_insert():
    global p_id, p_name
    query = "INSERT INTO plus2_product_receive (p_id,product,price,qty,dt) \
           VALUES(%s,%s,%s,%s,%s)"
    data = (p_id, p_name, price_v.get(), quantity_v.get(), dt.entry.get())
    # print(query,my_data)
    id = my_conn.execute(query, data)
    l1.config(text="Data inserted ID: " + str(id.lastrowid), bootstyle="success")
    l1.after(3000, lambda: my_reset())


def my_reset():
    cb1.delete(0, "end")
    price_v.set(0)
    quantity_v.set(1)
    l1.config(text="Ready", bootstyle="inverse-warning")


font1 = ["Times", 50, "normal"]
l_top = ttk.Label(
    my_w,
    text="Procurement",
    bootstyle="inverse-info",
    font=font1,
    anchor="center",
    width=15,
)
l_top.grid(row=0, column=1, columnspan=3, padx=20, pady=10)
l_product = ttk.Label(
    text="Product", bootstyle="inverse-primary", anchor="sw", width=15, font=20
)
l_product.grid(row=1, column=1, padx=10, pady=10)

l_price = ttk.Label(
    text="Price", bootstyle="inverse-primary", anchor="sw", width=15, font=20
)
l_price.grid(row=2, column=1, padx=10, pady=10)

l_qty = ttk.Label(
    text="Quantity", bootstyle="inverse-primary", anchor="sw", width=15, font=20
)
l_qty.grid(row=3, column=1, padx=10, pady=10)

l_date = ttk.Label(
    text="Date", bootstyle="inverse-primary", anchor="sw", width=15, font=20
)
l_date.grid(row=4, column=1, padx=10, pady=10)

sel = tk.StringVar()
cb1 = ttk.Combobox(
    my_w, textvariable=sel, values=list(my_dict.values()), bootstyle="success"
)
cb1.grid(row=1, column=2, padx=5, pady=10)
price_v = ttk.DoubleVar(value=0)
price = ttk.Entry(my_w, textvariable=price_v, bootstyle="success")
price.grid(row=2, column=2, padx=10, pady=10)
quantity_v = ttk.IntVar(value=1)
quantity = ttk.Entry(my_w, textvariable=quantity_v, bootstyle="success")
quantity.grid(row=3, column=2, padx=10, pady=10)
dt = ttk.DateEntry(my_w, dateformat="%Y-%m-%d", bootstyle="success")
dt.grid(row=4, column=2, padx=10, pady=10)

l1 = ttk.Label(my_w, text="Details", bootstyle="inverse-warning", font=12, width=20)
l1.grid(row=1, column=3)

b1 = ttk.Button(my_w, text="Submit", command=lambda: my_insert(), bootstyle="success")
b1.grid(row=5, column=2, padx=10, pady=20)
#sel.trace("w", my_upd)  # trigger on change event of Combobox
cb1.bind("<KeyRelease>", my_upd)
cb1.bind("<FocusOut>", my_disp)
my_w.mainloop()  # Keep the window open

Invoice Generation
Display MySQL records in Treeview Pagination of MySQL records in Treeview
Displaying MySQL records using Entry or Label Delete MySQL record
Treeview Treeview insert Treeview parent child node Select -Edit-update MySQL Product table using Treeview Query window & displaying records in Treeview

Python Tkinter Dictionary Python- Tutorials

Subscribe to our YouTube Channel here

Adding Procured products to table

Collecting product id and name

On change event of Combobox

Inserting input data

Reset the inputs

Adding Product Search option to Combobox

Subscribe