Python Tkinter Admin Panel for Product Management with SQLite Database

"from tkinter import *\nfrom tkinter import messagebox\nfrom tkinter.font import Font\nfrom tkinter import ttk\n# import mysql.connector as mysql\nimport sqlite3\n\n# Creating Tkinter Window\nAdmin = Tk()\nAdmin.geometry("1330x750")\nAdmin.resizable(0, 0)\nAdmin.iconbitmap("./images/Logo.ico")\nAdmin.title("Admin")\n\n# Creating Mysql connection\ndbconn = sqlite3.connect("./Database/RSgroceries.db")\n\n# Create a cursor to give commands\ncursor = dbconn.cursor()\n\n# Create Tables\n# category Table\ncursor.execute(""CREATE TABLE if not exists category(\ncategory varchar(100) NOT NULL primary key\n )\n "")\ndbconn.commit()\ncursor.execute(""CREATE TABLE if not exists products(\n product_id int not null primary key,\n product_name varchar(100) not null,\n product_rate int not null,\n category varchar(100) not null references category(category)\n )\n "")\ndbconn.commit()\n\ncursor.execute("SELECT * FROM products")\nprod_1 = cursor.fetchall()\n# print(prod_1)\ndbconn.commit()\n\n\n\n# Fixing GUI Background\nBackground = PhotoImage(file="./images/Admin_bg.png")\nBg_label = Label(Admin, image=Background)\nBg_label.place(x=0, y=0, relwidth=1, relheight=1)\n\n# Creating invoice\ntable = ttk.Treeview(Admin)\ntable["columns"] = ("ID","Product Name","Category", "Rate")\n\ntable.column("#0", width=0,stretch=NO)\ntable.column("#1", width=50,anchor="center")\ntable.column("#2", width=230,anchor="center")\ntable.column("#3",width=230,anchor="center")\ntable.column("#4",width=120,anchor="center")\n\ntable.heading("#0",text="")\ntable.heading("#1",text="ID")\ntable.heading("#2",text="Product Name")\ntable.heading("#3",text="Category")\ntable.heading("#4",text="Rate")\ntable.place(relx=0.50,rely=0.1139,height=528.8, width=630)\n\nScroll_invoice = Scrollbar(orient="vertical",command=table.yview)\ntable.configure(yscroll=Scroll_invoice.set)\nScroll_invoice.place(relx=0.961,rely=0.1140, height=527.3)\n\nfor row in prod_1:\n table.insert("",index="end",values=(row[0],row[1],row[3],row[2]))\n# Defining Exit function\ndef Exit():\n sure = messagebox.askyesno("Exit","Are you sure you want to exit?", parent=Admin)\n if sure == True:\n Admin.destroy()\n # adm.destroy()\n\n\n# Creating logout button\nlogout_img = PhotoImage(file="./images/logout.png")\nlogout_button = Button(Admin, image=logout_img, borderwidth=0,relief="flat",overrelief="flat", command=Exit)\nlogout_button.place(relx=0.0155, rely=0.038,width=39,height=31)\n\n\n# Creating all the required widgets\n# Creating text variables\ncat = StringVar()\npro_name = StringVar()\npro_rate = StringVar()\n\nfont_1 = Font(family="Calibri",size=15,weight="bold")\n# All Entry widgets\n# Product Category Widget\nEntry_1 = Entry(Admin,font=font_1,relief="flat",bg="#fefffe")\nEntry_1.place(relx=0.043,rely=0.622,width=423,height=50)\n\n# Product Rate Widget\nEntry_2 = Entry(Admin, font=font_1,relief="flat",bg="#fefffe")\nEntry_2.place(relx=0.043,rely=0.780,width=423,height=50)\n\n# Product Name Widget\nEntry_3 = Entry(Admin,font=font_1,relief="flat",bg="#fefffe")\nEntry_3.place(relx=0.043,rely=0.463,width=423,height=50)\n\n# Product Id Widget\nEntry_4 = Entry(Admin,font=font_1,relief="flat",bg="#fefffe")\nEntry_4.place(relx=0.043,rely=0.3205,width=423,height=50)\n\n# Search code Entry Widget\nEntry_5 = Entry(Admin, font=font_1,relief="flat",bg="#fefafa")\nEntry_5.place(relx=0.161,rely=0.115,width=255,height=40)\n\n\n# Defining all the required functions\n# CREATING FUNCTION TO REMOVE UNWANTED CATEGORY\ndef unwanted_cat():\n category_delete_1 = table.get_children()\n categories_avail = []\n for rec in category_delete_1:\n values = table.item(rec).get("values")[2]\n categories_avail.append(values)\n cursor.execute("SELECT category FROM category")\n cat_t = cursor.fetchall()\n all_cat = []\n for i in cat_t:\n all_cat.append(i[0])\n available_category = []\n for fin in all_cat:\n if fin in categories_avail:\n available_category.append(fin)\n else:\n pass\n cursor.execute("DROP TABLE category")\ndbconn.commit()\n # Creating table product if not exist\n cursor.execute(""CREATE TABLE if not exists category(\n category varchar(100) NOT NULL primary key\n )\n "")\ndbconn.commit()\n for last in available_category:\n try:\n cursor.execute("INSERT INTO category VALUES('{}')").format(last)\n dbconn.commit()\n except sqlite3.IntegrityError:\n pass\n\n\n# Add to cart\ndef add_to_cart():\n # Creating table product if not exist\n cursor.execute(""CREATE TABLE if not exists category(\n category varchar(100) NOT NULL primary key\n )\n "")\n cursor.execute(""CREATE TABLE if not exists products(\n product_id int not null primary key,\n product_name varchar(100) not null,\n product_rate int not null,\n category varchar(100) not null references category(category)\n )\n "")\ndbconn.commit()\n all_rec = table.get_children()\n ids = []\n for rec in all_rec:\n values = table.item(rec).get("values")[0]\n ids.append(values)\n if (Entry_2.get().isdigit() or Entry_2.get()==""):\n try:\n if Entry_1.get() != "" and Entry_2.get() != "" and Entry_3.get() != "" and Entry_4.get() != "":\n n = messagebox.askyesno("Add to Market", "Are you sure you want to add it to the Market?")\n if n == 1:\n cursor.execute("SELECT product_id FROM products")\n id_check = cursor.fetchall()\n id_check_fin = []\n dbconn.commit()\n if (int(Entry_4.get()),) in id_check:\n messagebox.showerror("Error", "Product id already in the market")\n else:\n table.insert("", index="end", values=(Entry_4.get(), Entry_3.get(), Entry_1.get(), Entry_2.get())\n cursor.execute("INSERT INTO products VALUES(:product_id, :product_name, :product_rate, :category)",\n {\n "product_id": Entry_4.get(),\n "product_name": Entry_3.get(),\n "product_rate": Entry_2.get(),\n "category": Entry_1.get()\n }\n )\n cursor.execute("SELECT category FROM category")\n categories_db = cursor.fetchall()\n categories = []\n for i in categories_db:\n categories.append(i[0])\n if Entry_1.get() not in categories:\n cursor.execute("INSERT INTO category VALUES(:category)",\n {"category": Entry_1.get()})\ndbconn.commit()\n else:\n pass\n dbconn.commit()\n Entry_1.delete(0, END)\n Entry_2.delete(0, END)\n Entry_3.delete(0, END)\n Entry_4.delete(0, END)\n unwanted_cat()\n\n else:\n pass\n else:\n messagebox.showerror("Error", "Please fill the details")\n except ValueError:\n messagebox.showerror("Error", "Please enter correct product ID!")\n else:\n Entry_2.delete(0, END)\n messagebox.showerror("Error", "Please enter correct quantity!")\n\n# Update\ndef update():\n # Creating table product if not exist\n cursor.execute(""CREATE TABLE if not exists category(\n category varchar(100) NOT NULL primary key\n )\n "")\n cursor.execute(""CREATE TABLE if not exists products(\n product_id int not null primary key,\n product_name varchar(100) not null,\n product_rate int not null,\n category varchar(100) not null references category(category)\n )\n "")\ndbconn.commit()\n\n Button_1.configure(state="active")\n if Entry_1.get() != "" and Entry_2.get() != "" and Entry_3.get() != "" and Entry_4.get() != "":\n cursor.execute("SELECT product_id FROM products")\n id_check = cursor.fetchall()\ndbconn.commit()\n if (int(Entry_4.get()),) in id_check:\n all_rows = table.get_children()\n k = []\n for i in all_rows:\n if table.item(i).get("values")[0] == int(Entry_4.get()):\n k.append(i)\n else:\n pass\n table.item(k[0], text="", values=(int(Entry_4.get()) ,Entry_3.get(), Entry_1.get(), Entry_2.get())\n cursor.execute(""\n UPDATE products SET product_name = '{}', category = '{}', product_rate = {} WHERE product_id = {}""\n .format(Entry_3.get(), Entry_1.get(), Entry_2.get(), int(Entry_4.get()))\ndbconn.commit()\n cursor.execute("SELECT category FROM category")\n categories_db = cursor.fetchall()\n categories = []\n for i in categories_db:\n categories.append(i[0])\n if Entry_1.get() not in categories:\n cursor.execute("INSERT INTO category VALUES(:category)",\n {"category": Entry_1.get()})\ndbconn.commit()\n Entry_1.delete(0, END)\n Entry_2.delete(0, END)\n Entry_3.delete(0, END)\n Entry_4.delete(0, END)\n unwanted_cat()\n\n else:\n messagebox.showerror("Error", "Product ID not in the market")\n else:\n messagebox.showerror("Error", "Fill all the details")\n\n# Clear\ndef clear():\n # Creating table product if not exist\n cursor.execute(""CREATE TABLE if not exists category(\n category varchar(100) NOT NULL primary key\n )\n "")\n cursor.execute(""CREATE TABLE if not exists products(\n product_id int not null primary key,\n product_name varchar(100) not null,\n product_rate int not null,\n category varchar(100) not null references category(category)\n )\n "")\ndbconn.commit()\n\n Entry_1.delete(0, END)\n Entry_2.delete(0, END)\n Entry_3.delete(0, END)\n Entry_4.delete(0, END)\n Button_1.configure(state="active")\n unwanted_cat()\n\n# Select Item\ndef select_item():\n # Creating table product if not exist\n cursor.execute(""CREATE TABLE if not exists category(\n category varchar(100) NOT NULL primary key\n )\n "")\n cursor.execute(""CREATE TABLE if not exists products(\n product_id int not null primary key,\n product_name varchar(100) not null,\n product_rate int not null,\n category varchar(100) not null references category(category)\n )\n "")\ndbconn.commit()\n\n items_n = table.selection()\n if len(items_n)>1:\n messagebox.showerror("Error", "Two or more items are selected")\n else:\n if items_n == ():\n messagebox.showerror("Error", "No Item(s) selected")\n else:\n Entry_1.delete(0, END)\n Entry_2.delete(0, END)\n Entry_3.delete(0, END)\n Entry_4.delete(0, END)\n sel_item = []\n for i in items_n:\n k = table.item(i, "values")\n for j in k:\n sel_item.append(j)\n Entry_4.insert(0, sel_item[0])\n Entry_3.insert(0, sel_item[1])\n Entry_2.insert(0, sel_item[3])\n Entry_1.insert(0, sel_item[2])\n unwanted_cat()\n Button_1.configure(state="disabled")\n\n# Delete item(s)\ndef delete_many():\n # Creating table product if not exist\n cursor.execute(""CREATE TABLE if not exists category(\n category varchar(100) NOT NULL primary key\n )\n "")\n cursor.execute(""CREATE TABLE if not exists products(\n product_id int not null primary key,\n product_name varchar(100) not null,\n product_rate int not null,\n category varchar(100) not null references category(category)\n )\n "")\ndbconn.commit()\n\n items_n = table.selection()\n if items_n == ():\n messagebox.showerror("Error", "No Item(s) selected")\n\n else:\n n = messagebox.askyesno("Delete item(s)","Are you sure you want to delete the selected item(s)?")\n if n == 1:\n pro_id = []\n for i in items_n:\n k = table.item(i, "values")\n pro_id.append(k[0])\n for rows_n in items_n:\n table.delete(rows_n)\n for row in pro_id:\n cursor.execute("DELETE FROM products WHERE product_id={}").format(row)\ndbconn.commit()\n unwanted_cat()\n else:\n pass\n\n# Clear All\ndef clear_all():\n # Creating table product if not exist\n cursor.execute(""CREATE TABLE if not exists category(\n category varchar(100) NOT NULL primary key\n )\n "")\n cursor.execute(""CREATE TABLE if not exists products(\n product_id int not null primary key,\n product_name varchar(100) not null,\n product_rate int not null,\n category varchar(100) not null references category(category)\n )\n "")\ndbconn.commit()\n\n if table.get_children() == ():\n messagebox.showerror("Error","No Items in the Market")\n else:\n n = messagebox.askyesno("Clear All", "Are you sure you want to clear all the items?")\n if n == 1:\n for rows in table.get_children():\n table.delete(rows)\n cursor.execute("DROP TABLE products")\ndbconn.commit()\n unwanted_cat()\n else:\n pass\n\ndef search_id():\n if Entry_5.get() == "":\n messagebox.showerror("Error", "Enter ID to search")\n else:\n id = int(Entry_5.get())\n cursor.execute("SELECT product_id FROM products")\n id_check = cursor.fetchall()\ndbconn.commit()\n all_rows = table.get_children()\n row = []\n for i in all_rows:\n if table.item(i).get("values")[0] == id:\n row.append(i)\n if row == []:\n messagebox.showerror("Error", "No product with ID {}").format(id)\n else:\n Button_1.configure(state="disabled")\n for j in row:\n Entry_1.delete(0, END)\n Entry_2.delete(0, END)\n Entry_3.delete(0, END)\n Entry_4.delete(0, END)\n values = table.item(j).get("values")\n Entry_4.insert(0, values[0])\n Entry_3.insert(0, values[1])\n Entry_2.insert(0, values[3])\n Entry_1.insert(0, values[2])\n\n Entry_5.delete(0, END)\n unwanted_cat()\n\n\n\n\n# All Button Widgets\n# Non-Table widgets\n# Add to Market\nButton_1 = Button(Admin, text="Add to market", relief="flat", bg="#fe1716",fg="black",borderwidth=0,font=font_1,command=add_to_cart)\nButton_1.configure(activebackground="#fe1716")\nButton_1.place(relx=0.04325,rely=0.878,width=135,height=43)\n\n# Modify\nButton_2 = Button(Admin, text="Update", relief="flat", bg="#fe1716", fg="black", borderwidth=0, font=font_1, command=update)\nButton_2.configure(activebackground="#fe1716")\nButton_2.place(relx=0.161, rely=0.878, width=135, height=43)\n\n# Clear\nButton_3 = Button(Admin, text="Clear", relief="flat", bg="#fe1716", fg="black", borderwidth=0, font=font_1,command=clear)\nButton_3.configure(activebackground="#fe1716")\nButton_3.place(relx=0.278, rely=0.878, width=135, height=43)\n\n# Search\nsearch_img = PhotoImage(file="./images/search.png")\nsearch_button = Button(Admin, image=search_img, borderwidth=0,relief="flat",overrelief="flat", command=search_id)\nsearch_button.place(relx=0.3713, rely=0.1175)\n\n# Table widgets\n# Select\nButton_4 = Button(Admin, text="Select", relief="flat", bg="#fe1716", fg="black", borderwidth=0, font=font_1,command=select_item)\nButton_4.configure(activebackground="#fe1716")\nButton_4.place(relx=0.512, rely=0.8855, width=135, height=43)\n\n# Delete item(s)\nButton_5 = Button(Admin, text="Delete item(s)", relief="flat", bg="#fe1716",fg="black",borderwidth=0,font=font_1, command=delete_many)\nButton_5.configure(activebackground="#fe1716")\nButton_5.place(relx=0.686,rely=0.8855,width=135,height=43)\n\n# Clear All\nButton_6 = Button(Admin, text="Clear All", relief="flat", bg="#fe1716", fg="black", borderwidth=0, font=font_1, command=clear_all)\nButton_6.configure(activebackground="#fe1716")\nButton_6.place(relx=0.862, rely=0.8855, width=135, height=43)\n\n\nAdmin.protocol("WM_DELETE_WINDOW", Exit)\n\nAdmin.mainloop()\n