Adding Procured products to table

";require "templates/body_start.php";?>

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.



Connection to MySQL database

from sqlalchemy import create_enginemy_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")

Full code : Copy to clipboard

import tkinter as tkimport ttkbootstrap as ttkmy_w = tk.Tk()my_w.geometry("550x450")  # Size of the windowmy_w.title("www.plus2net.com")  # Adding a titlefrom sqlalchemy import create_enginemy_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 iddef 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)            breakdef 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 cb1my_w.mainloop()  # Keep the window open

Adding Product Search option to Combobox

Seaching Combobox Options »
Full code : Copy to clipboard

import tkinter as tkimport ttkbootstrap as ttkmy_w = tk.Tk()my_w.geometry("560x450")  # Size of the windowmy_w.title("www.plus2net.com")  # Adding a titlefrom sqlalchemy import create_enginefrom sqlalchemy.sql import textmy_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_iddef 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)            breakdef 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 Comboboxcb1.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
TreeviewTreeview insert Treeview parent child nodeSelect -Edit-update MySQL Product table using Treeview » Query window & displaying records in Treeview »