Tkinter GUI to display MySQL table records and on selection delete record after user confirmation
For deleting record we will add delete function to our display record script. Here are the additional steps we will add to our display records script.
We will add one button to each row or record to trigger the delete operation.
The button once clicked will pass the record id to a function which will delete the particular record.
Show one confirmation message to the user asking to confirm the operation.
Based on confirmation or rejection, the delete operation should be executed.
After the delete operation we will refresh the view ( by deleting and adding all widgets ) so a fresh data is taken from backend MySQL database reflecting the changes by displaying the balance records.
Download the zip file with source codes at the end of this tutorial.
Adding delete button to each record
The function display() is used to execute the SQL to collect the records and display data along with one delete button. Each time this function is called then the fresh data is displayed.
Once the delete button is pressed, it carries the student id as parameter to another function del_data(k). Check this code added to each delete button. command=lambda k=student[0]: del_data(k)
After deleting any record this function display() is called to show the records again.
def display():
my_cursor=my_conn.execute("SELECT * FROM student limit 0,10")
i=0
for student in my_cursor:
for j in range(len(student)):
e = Label(my_w,width=8,fg='blue', text=student[j],
relief='ridge', anchor="w")
e.grid(row=i, column=j)
# show the delete button
e = tk.Button(my_w,width=5,text='Del',fg='red',relief='ridge',
anchor="w",command=lambda k=student[0]:del_data(k))
e.grid(row=i, column=5)
i=i+1
Delete record
The function del_data(s_id) is executed on click of the button ( as explained above ) and the student id ( s_id ) is received as parameter.
We will show one confirmation window asking user to confirm the operation. Read more on how to show message and collect the user choice here.
Based on the user confirmation the query to delete the row is executed. After
deletion we removed all widgets and then call the function display() to refresh the data.
def del_data(s_id): # delete record
try:
my_var=msg.askyesnocancel("Delete Record",
"Are you sure ? ",icon='warning')
print(my_var)
if my_var:
query="DELETE FROM student WHERE id=%s"
my_data=[s_id]
my_conn.execute(query,my_data)
print("Row Deleted ")
for row in my_w.grid_slaves():# remove widgets
row.grid_forget()
display() # refresh the list
except SQLAlchemyError as e:
error = str(e.__dict__['orig'])
print(error)
We defined my_connas connection object
Collect the data from student_profile table by using SQLALchemy.
Full code is here
import tkinter as tk
from tkinter import *
from tkinter import messagebox as msg
from sqlalchemy import create_engine
from sqlalchemy.exc import SQLAlchemyError
my_w = tk.Tk()
my_w.geometry("360x350")
my_conn = create_engine("mysql+mysqldb://root:test@localhost/my_tutorial")
def display():
my_cursor=my_conn.execute("SELECT * FROM student limit 0,10")
i=0
for student in my_cursor:
for j in range(len(student)):
e = Label(my_w,width=8,fg='blue', text=student[j],
relief='ridge', anchor="w")
e.grid(row=i, column=j)
# show the delete button
e = tk.Button(my_w,width=5,text='Del',fg='red',relief='ridge',
anchor="w",command=lambda k=student[0]:del_data(k))
e.grid(row=i, column=5)
i=i+1
display()
def del_data(s_id): # delete record
try:
my_var=msg.askyesnocancel("Delete Record",
"Are you sure ? ",icon='warning')
print(my_var)
if my_var:
query="DELETE FROM student WHERE id=%s"
my_data=[s_id]
my_conn.execute(query,my_data)
print("Row Deleted ")
for row in my_w.grid_slaves():# remove widgets
row.grid_forget()
display() # refresh the list
except SQLAlchemyError as e:
error = str(e.__dict__['orig'])
print(error)
my_w.mainloop()