Python And MySQL - Insert Update Delete Search And Display Data On Tkinter TreeView

How To Insert Update Delete And Show Records In TreeView Using Python Tkinter

Python And MySQL - Insert Update Delete Search And Display Data On Tkinter TreeView


In this Python Tkinter CRUD Tutorial With MySQL Database we will see How To:
- insert data in mysql database.
- remove selected record from mysql database.
- edit selected record from mysql database.
- search for a specific record by id.
- display mysql database records in a tkinter treeview.






Project Source Code:

import tkinter as tk
from tkinter import *
from tkinter import ttk
from tkinter import messagebox
import mysql.connector


connection = mysql.connector.connect(host='localhost', user='root', port='3306',
password='', database='test_py')
c = connection.cursor()

root = Tk()

frame = tk.Frame(root)
frame_btns = tk.Frame(frame)

label_id = tk.Label(frame, text="ID:", font=('verdana',14))
entry_id = tk.Entry(frame, font=('verdana',14))

label_fname = tk.Label(frame, text="First Name:", font=('verdana',14))
entry_fname = tk.Entry(frame, font=('verdana',14))

label_lname = tk.Label(frame, text="Last Name:", font=('verdana',14))
entry_lname = tk.Entry(frame, font=('verdana',14))

label_email = tk.Label(frame, text="Email:", font=('verdana',14))
entry_email = tk.Entry(frame, font=('verdana',14))

label_age = tk.Label(frame, text="Age:", font=('verdana',14))
entry_age = tk.Entry(frame, font=('verdana',14))

button_add = tk.Button(frame_btns, text="Add", font=('verdana',14), bg='green',
fg='#ffffff')
button_edit = tk.Button(frame_btns, text="Edit", font=('verdana',14), bg='blue',
fg='#ffffff')
button_remove = tk.Button(frame_btns, text="Remove", font=('verdana',14), bg='red',
fg='#ffffff')
button_search = tk.Button(frame_btns, text="Search", font=('verdana',14), bg='orange',
fg='#ffffff')

trv = ttk.Treeview(frame, columns=(1,2,3,4,5), height=15, 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.heading(1, text="ID")
trv.heading(2, text="First Name")
trv.heading(3, text="Last Name")
trv.heading(4, text="Email")
trv.heading(5, text="Age")

def add():
fname = entry_fname.get().strip() # remove white space
lname = entry_lname.get().strip()
email = entry_email.get().strip()
age = entry_age.get().strip()
vals = (fname,lname,email,age)

if(len(fname) > 0 and len(lname) > 0 and len(email) > 0 and int(age) > 10):
c.execute("INSERT INTO `users_2`(`firstname`, `lastname`, `email`, `age`)
VALUES (%s,%s,%s,%s)", vals)
connection.commit()
messagebox.showinfo('Add','User Info Has Been Added')
displayUsers()
else:
messagebox.showwarning('Add','Incorrrect Data')


def edit():
user_id = entry_id.get().strip()
fname = entry_fname.get().strip()
lname = entry_lname.get().strip()
email = entry_email.get().strip()
age = entry_age.get().strip()
if(len(fname) > 0 and len(lname) > 0 and len(email) > 0 and int(age) > 10):
vals = (fname,lname,email,age,user_id)
c.execute("UPDATE `users_2` SET `firstname`=%s,`lastname`=%s,`email`=%s,
`age`=%s WHERE `id`=%s", vals)
connection.commit()
messagebox.showinfo('Edit','User Info Has Been Edited')
displayUsers()
else:
messagebox.showwarning('Edit','Incorrrect Data')

def remove():
user_id = entry_id.get().strip()
c.execute("DELETE FROM `users_2` WHERE `id` = " + user_id)
connection.commit()
messagebox.showinfo('Delete','User Info Has Been Deleted')
displayUsers()

def search():
user_id = entry_id.get().strip()
c.execute("SELECT * FROM `users_2` WHERE `id` = " + user_id)
user = c.fetchone()
# clear fields
entry_fname.delete(0, END)
entry_lname.delete(0, END)
entry_email.delete(0, END)
entry_age.delete(0, END)
if user:
# display data
entry_fname.insert(0, user[1])
entry_lname.insert(0, user[2])
entry_email.insert(0, user[3])
entry_age.insert(0, user[4])
else:
messagebox.showwarning('User','No User Found')




def displayUsers():
# clear treeview
for row in trv.get_children():
trv.delete(row)
# populate treeview
c.execute("SELECT * FROM `users_2`")
users = c.fetchall()

for user in users:
trv.insert('', END, values=user)



button_add['command'] = add
button_edit['command'] = edit
button_remove['command'] = remove
button_search['command'] = search

displayUsers()

label_id.grid(row=0, column=0, sticky='e')
entry_id.grid(row=0, column=1)

label_fname.grid(row=1, column=0, sticky='e')
entry_fname.grid(row=1, column=1)

label_lname.grid(row=2, column=0, sticky='e')
entry_lname.grid(row=2, column=1)

label_email.grid(row=3, column=0, sticky='e')
entry_email.grid(row=3, column=1)

label_age.grid(row=4, column=0, sticky='e')
entry_age.grid(row=4, column=1)

frame_btns.grid(row=5, column=0, columnspan=2)
button_add.grid(row=0, column=0, padx=10, pady=10)
button_edit.grid(row=0, column=1, padx=10, pady=10)
button_remove.grid(row=0, column=2, padx=10, pady=10)
button_search.grid(row=0, column=3, padx=10, pady=10)


trv.grid(row=0, column=3, rowspan=5, padx=10, pady=10)

frame.grid(row=0, column=0)

root.mainloop()


///////////////OUTPUT:


Python Tkinter And MySQL - Insert Update Delete Search And Display

Python Tkinter And MySQL - Insert Update Delete Search And Display Records
Python Tkinter Insert Button To MySQL

Python Tkinter And MySQL - Insert Update Delete Search And Show Records
Python Insert Button To MySQL 2



if you want the source code click on the download button below













Share this

Related Posts

Previous
Next Post »