Python And MySQL - Project Example Using Tkinter With Source Code
What We Will Use In This Tutorial:
- Python Programming Language .
- Tkinter For Graphicl User Interface.
- MySQL Database.
- VsCode IDE.
- XAMPP.
- PhpMyAdmin.
Project Tutorial
Project Source Code:
import tkinter as tk
from tkinter import *
from tkinter import ttk
from tkinter import filedialog
from PIL import ImageTk, Image
from tkinter import messagebox
import mysql.connector
# create a connection
connection = mysql.connector.connect(host='localhost', user='root', port='3306',
password='', database='new_py_db')
c = connection.cursor()
# create a function to get all products
def getAllProducts():
c.execute("SELECT * FROM `products`")
return c.fetchall()
products = getAllProducts()
p_index = 0
root = Tk()
root.title('Manage Products GUI')
frame_main = tk.Frame(root, bg='#bdc3c7')
frame = tk.Frame(frame_main, bg='#bdc3c7')
frame_btns = tk.Frame(frame, bg='#bdc3c7')
frame_btns_nav = tk.Frame(frame, bg='#bdc3c7')
categories = ['Car','Phone','Bags','Games','PC']
selected = StringVar(root)
id_label = tk.Label(frame, text="ID:", font=('verdana',14), bg='#bdc3c7')
id_entry = tk.Entry(frame, font=('verdana',14))
name_label = tk.Label(frame, text="Name:", font=('verdana',14), bg='#bdc3c7')
name_entry = tk.Entry(frame, font=('verdana',14))
category_label = tk.Label(frame, text="Category:", font=('verdana',14), bg='#bdc3c7')
category_combobox = ttk.Combobox(frame, font=('verdana',14), textvariable=selected,
values=categories)
selected.set(categories[0])
quantity_label = tk.Label(frame, text="Quantity:", font=('verdana',14), bg='#bdc3c7')
quantity_entry = tk.Entry(frame, font=('verdana',14))
price_label = tk.Label(frame, text="Price:", font=('verdana',14), bg='#bdc3c7')
price_entry = tk.Entry(frame, font=('verdana',14))
pic_label = tk.Label(frame, text="Picture:", font=('verdana',14), bg='#bdc3c7')
pic_entry = tk.Entry(frame)
display_pic_label = tk.Label(frame, bg='#bdc3c7')
button_browse_pic = tk.Button(frame, text="Select Image", font=('verdana',14),
bg='#636e72', fg='#ffffff')
search_label = tk.Label(frame, text="Search By Name:", font=('verdana',14),
bg='#bdc3c7')
search_entry = tk.Entry(frame, font=('verdana',14))
button_trv_search = tk.Button(frame, text="Find", font=('verdana',12), bg='orange')
# TreeView
trv = ttk.Treeview(frame, columns=(1,2,3,4,5,6), show='headings')
trv.column(1, anchor=CENTER, stretch=NO, width=100)
trv.column(2, anchor=CENTER, stretch=NO, width=100)
trv.column(3, anchor=CENTER, stretch=NO, width=100)
trv.column(4, anchor=CENTER, stretch=NO, width=100)
trv.column(5, anchor=CENTER, stretch=NO, width=100)
trv.column(6, anchor=CENTER, stretch=NO, width=140)
trv.heading(1, text='ID')
trv.heading(2, text='Name')
trv.heading(3, text='Category')
trv.heading(4, text='Quantity')
trv.heading(5, text='Price')
trv.heading(6, text='Image')
# query buttons
button_add = tk.Button(frame_btns, text="Add", font=('verdana',12), bg='orange')
button_edit = tk.Button(frame_btns, text="Edit", font=('verdana',12), bg='orange')
button_remove = tk.Button(frame_btns, text="Remove", font=('verdana',12), bg='orange')
button_search = tk.Button(frame_btns, text="Search", font=('verdana',12), bg='orange')
# clear entries
button_clear = tk.Button(frame_btns, text="Clear", font=('verdana',12), bg='orange')
# navigation buttons
button_first = tk.Button(frame_btns_nav, text="<<", font=('verdana',14), bg='green',
fg='#ffffff', width=10)
button_last = tk.Button(frame_btns_nav, text=">>", font=('verdana',14), bg='green',
fg='#ffffff', width=10)
button_next = tk.Button(frame_btns_nav, text=">", font=('verdana',14), bg='green',
fg='#ffffff', width=10)
button_previous = tk.Button(frame_btns_nav, text="<", font=('verdana',14), bg='green',
fg='#ffffff', width=10)
frame_main.pack()
frame.grid(row=0, column=0, padx=10, pady=10, sticky='nsew')
id_label.grid(row=0, column=0, sticky='e')
id_entry.grid(row=0, column=1, sticky='w')
name_label.grid(row=1, column=0, sticky='e')
name_entry.grid(row=1, column=1, sticky='w')
category_label.grid(row=2, column=0, sticky='e')
category_combobox.grid(row=2, column=1, sticky='w')
quantity_label.grid(row=3, column=0, sticky='e')
quantity_entry.grid(row=3, column=1, sticky='w')
price_label.grid(row=4, column=0, sticky='e')
price_entry.grid(row=4, column=1, sticky='w')
pic_label.grid(row=5, column=0, sticky='e')
pic_entry.grid(row=5, column=1, sticky='w')
display_pic_label.grid(row=6, column=0, columnspan=2)
button_browse_pic.grid(row=7, column=0, columnspan=2, sticky='nsew')
search_label.grid(row=0, column=3, sticky='e')
search_entry.grid(row=0, column=4, sticky='w')
button_trv_search.grid(row=0, column=5, sticky='nsew')
# add scrollbar to the treeview
sb = Scrollbar(frame, orient=VERTICAL)
trv.config(yscrollcommand=sb.set)
sb.config(command=trv.yview)
trv.grid(row=1, column=3, rowspan=6, columnspan=3, pady=20, sticky='nsew')
sb.grid(row=1, column=6, rowspan=6, sticky='ns', pady=20)
frame_btns.grid(row=8, column=0, columnspan=2, padx=20, pady=20)
button_add.grid(row=0, column=0, padx=5, pady=5)
button_edit.grid(row=0, column=1, padx=5, pady=5)
button_remove.grid(row=0, column=2, padx=5, pady=5)
button_search.grid(row=0, column=3, padx=5, pady=5)
button_clear.grid(row=0, column=4, padx=5, pady=5)
# navigation buttons
frame_btns_nav.grid(row=8, column=3, columnspan=3, padx=20, pady=20)
button_first.grid(row=0, column=0, padx=10)
button_previous.grid(row=0, column=1, padx=10)
button_next.grid(row=0, column=2, padx=10)
button_last.grid(row=0, column=3, padx=10)
# create a function insert a new item
def add():
name = name_entry.get().strip() # .strip() remove white space
category = category_combobox.get().strip()
quantity = quantity_entry.get().strip()
price = price_entry.get().strip()
pic = pic_entry.get().strip()
if(len(name) > 0 and len(category) > 0 and len(quantity) > 0 and len(price) > 0
and len(pic) > 0):
insert_query = "INSERT INTO `products`(`name`, `category`, `quantity`,
`price`, `image_path`) VALUES (%s,%s,%s,%s,%s)"
values = (name, category, quantity, price, pic)
c.execute(insert_query, values)
connection.commit()
messagebox.showinfo('Add', 'Product Added')
displayProducts()
else:
messagebox.showwarning('Add', 'Enter Valid Data')
# set the command to the add button
button_add['command'] = add
# create a function update the selected item
def edit():
p_id = id_entry.get().strip() # .strip() remove white space
name = name_entry.get().strip()
category = category_combobox.get().strip()
quantity = quantity_entry.get().strip()
price = price_entry.get().strip()
pic = pic_entry.get().strip()
if(len(name) > 0 and len(category) > 0 and len(quantity) > 0 and len(price) > 0
and len(pic) > 0):
update_query = "UPDATE `products` SET `name`=%s,`category`=%s,`quantity`=%s,
`price`=%s,`image_path`=%s WHERE `id`=%s"
values = (name, category, quantity, price, pic, p_id)
c.execute(update_query, values)
connection.commit()
messagebox.showinfo('Edit', 'Product Updated')
displayProducts()
else:
messagebox.showwarning('Edit', 'Enter Valid Data')
# set the command to the edit button
button_edit['command'] = edit
# create a function search record by id
def searchById():
p_id = id_entry.get()
search_query = "SELECT * FROM `products` WHERE `id` = " + p_id
c.execute(search_query)
product_data = c.fetchone()
clear()
if product_data:
id_entry.insert(0, product_data[0])
name_entry.insert(0, product_data[1])
category_combobox.insert(0, product_data[2])
quantity_entry.insert(0, product_data[3])
price_entry.insert(0, product_data[4])
pic_entry.insert(0, product_data[5])
# display image
global img
img = Image.open(product_data[5])
img = img.resize((100,100), Image.ANTIALIAS) # resize image
img = ImageTk.PhotoImage(img)
display_pic_label['image'] = img # display image
else:
messagebox.showwarning('No Product','Invalid Product ID')
# set the command to the search button
button_search['command'] = searchById
# create a function remove the selected item
def remove():
p_id = id_entry.get()
try:
delete_query = "DELETE FROM `products` WHERE `id` = " + p_id
c.execute(delete_query)
connection.commit()
messagebox.showinfo('Delete', 'Product Deleted')
displayProducts()
clear()
except:
messagebox.showerror('Error', 'Enter a Valid Product ID')
# set the command to the remove button
button_remove['command'] = remove
# create a function clear all textfields (entries)
def clear():
# clear text
id_entry.delete(0, END)
name_entry.delete(0, END)
category_combobox.delete(0, END)
quantity_entry.delete(0, END)
price_entry.delete(0, END)
pic_entry.delete(0, END)
display_pic_label['image'] = '' # remove image
# set the command to the clear button
button_clear['command'] = clear
# create a function display all records in treeview
def displayProducts():
# clear treeview
for child in trv.get_children():
trv.delete(child)
selectQuery = "SELECT * FROM `products`"
c.execute(selectQuery)
products = c.fetchall()
for row in products:
trv.insert('','end', values=row)
displayProducts()
# create a function search records by name
def searchByName():
# clear treeview
for child in trv.get_children():
trv.delete(child)
val = search_entry.get()
selectQuery = "SELECT * FROM `products` WHERE name like %s"
c.execute(selectQuery, ("%"+val+"%",))
products = c.fetchall()
for row in products:
trv.insert('','end', values=row)
# set the command to the search by name button
button_trv_search['command'] = searchByName
# create a function display the first item
def go_first():
clear()
global p_index
p_index = 0
product = products[p_index]
id_entry.insert(0, product[0])
name_entry.insert(0, product[1])
category_combobox.insert(0, product[2])
quantity_entry.insert(0, product[3])
price_entry.insert(0, product[4])
pic_entry.insert(0, product[5])
# display image
global img
img = Image.open(product[5])
img = img.resize((100,100), Image.ANTIALIAS) # resize image
img = ImageTk.PhotoImage(img)
display_pic_label['image'] = img # display image
# set the command to the show first button
button_first['command'] = go_first
# create a function display the next item
def go_next():
clear()
global p_index
p_index = p_index+1
if p_index > len(products) - 1:
p_index = len(products) - 1
product = products[p_index]
id_entry.insert(0, product[0])
name_entry.insert(0, product[1])
category_combobox.insert(0, product[2])
quantity_entry.insert(0, product[3])
price_entry.insert(0, product[4])
pic_entry.insert(0, product[5])
# display image
global img
img = Image.open(product[5])
img = img.resize((100,100), Image.ANTIALIAS) # resize image
img = ImageTk.PhotoImage(img)
display_pic_label['image'] = img # display image
# set the command to the show next button
button_next['command'] = go_next
# create a function display the previous item
def go_previous():
clear()
global p_index
p_index = p_index-1
if p_index < 0:
p_index = 0
product = products[p_index]
id_entry.insert(0, product[0])
name_entry.insert(0, product[1])
category_combobox.insert(0, product[2])
quantity_entry.insert(0, product[3])
price_entry.insert(0, product[4])
pic_entry.insert(0, product[5])
# display image
global img
img = Image.open(product[5])
img = img.resize((100,100), Image.ANTIALIAS) # resize image
img = ImageTk.PhotoImage(img)
display_pic_label['image'] = img # display image
# set the command to the show previous button
button_previous['command'] = go_previous
# create a function display the last item
def go_last():
clear()
global p_index
p_index = len(products)-1
product = products[p_index]
id_entry.insert(0, product[0])
name_entry.insert(0, product[1])
category_combobox.insert(0, product[2])
quantity_entry.insert(0, product[3])
price_entry.insert(0, product[4])
pic_entry.insert(0, product[5])
# display image
global img
img = Image.open(product[5])
img = img.resize((100,100), Image.ANTIALIAS) # resize image
img = ImageTk.PhotoImage(img)
display_pic_label['image'] = img # display image
# set the command to the show last button
button_last['command'] = go_last
# browse and display image
def selectPicture():
global img
filename = filedialog.askopenfilename(initialdir='/images', title='Select Picture'
, filetypes=(('png images', '*.png'),('jpg images', '*.jpg')))# select image
img = Image.open(filename)
img = img.resize((100,100), Image.ANTIALIAS) # resize image
img = ImageTk.PhotoImage(img)
display_pic_label['image'] = img # display image
pic_entry.delete(0, END) # clear text
pic_entry.insert(0, filename) # display image path
button_browse_pic['command'] = selectPicture
# create a function display the selected item from treeview
def select_Item_From_TreeView(a):
clear()
item = trv.selection()[0]
print(trv.item(item)['values'])
id_entry.insert(0, trv.item(item)['values'][0])
name_entry.insert(0, trv.item(item)['values'][1])
category_combobox.insert(0, trv.item(item)['values'][2])
quantity_entry.insert(0, trv.item(item)['values'][3])
price_entry.insert(0, trv.item(item)['values'][4])
pic_entry.insert(0, trv.item(item)['values'][5])
# display image
global img
img = Image.open(trv.item(item)['values'][5])
img = img.resize((100,100), Image.ANTIALIAS) # resize image
img = ImageTk.PhotoImage(img)
display_pic_label['image'] = img # display image
# set the select command to the treeview
trv.bind('<<TreeviewSelect>>', select_Item_From_TreeView)
root.mainloop()
OUTPUT:
 |
| Python Tkinter MySQL Project - Manage Products |
 |
| Python Tkinter MySQL Project - Get Selected Item From TreeView |
 |
| Python Tkinter MySQL Project - Search By Id |
 |
| Python Tkinter MySQL Project - Search By Name |
if you want the source code click on the download button below
disclaimer: you will get the source code with the database script and to make it work in your machine is your responsibility and to debug any error/exception is your responsibility this project is for the students who want to see an example and read the code not to get and run.