Deleting record from MySQL table using Treeview Selection

selected_item = trv.selection()[0]

Code Explanation: Breaking Down the Activities

1. Importing Required Modules

from tkinter import ttk
import tkinter as tk
from sqlalchemy import create_engine, text
from sqlalchemy.exc import SQLAlchemyError

• ttk: Used for creating advanced widgets like Treeview.
• create_engine: Establishes a connection with the MySQL database.
• text: Allows us to execute SQL queries with placeholders securely.
• SQLAlchemyError: Captures database-related errors for handling exceptions.

2. Setting Up the Database Connection

my_conn = create_engine("mysql+mysqldb://root:test@localhost/my_tutorial")
my_conn = my_conn.connect()  # Establish a connection to the MySQL database

• my_conn: This is the connection object used to interact with the database.

3. Fetching Data from the Database

r_set = my_conn.execute(text('SELECT * FROM student LIMIT 0,10'))

• r_set: Holds the rows fetched from the database table student.
• The query fetches the first 10 rows from the student table.

4. Setting Up the Main Tkinter Window

my_w = tk.Tk()  # Create the main Tkinter window
my_w.geometry('400x350')  # Set window dimensions
my_w.title('www.plus2net.com')  # Set window title

• my_w: The main application window where widgets will be placed.

5. Creating the Treeview Widget

trv = ttk.Treeview(my_w, selectmode='browse')
trv.grid(row=1, column=1, padx=20, pady=20)

trv['columns'] = ('1', '2', '3', '4', '5')
trv['show'] = 'headings'

trv.column('1', width=30, anchor='c')
trv.column('2', width=80, anchor='c')
trv.column('3', width=80, anchor='c')
trv.column('4', width=80, anchor='c')
trv.column('5', width=80, anchor='c')

trv.heading('1', text='id')
trv.heading('2', text='Name')
trv.heading('3', text='Class')
trv.heading('4', text='Mark')
trv.heading('5', text='Gender')

for dt in r_set:  
    trv.insert("", 'end', iid=dt[0],
               values=(dt[0], dt[1], dt[2], dt[3], dt[4]))

• The Treeview widget is used to display tabular data.
• Columns for ID, Name, Class, Mark, and Gender are configured with appropriate widths and headings.
• The for loop inserts rows of data fetched from the database into the Treeview widget.

6. Adding a Delete Button

b1 = tk.Button(my_w, text='delete row', width=20, bg='yellow',
               command=lambda: my_delete())
b1.grid(row=2, column=1)

• b1: Button widget that triggers the my_delete() function when clicked.

7. Setting Up the Message Label

my_str = tk.StringVar()
l1 = tk.Label(my_w, textvariable=my_str, font=('times', 10, ''))
l1.config(fg='blue')
l1.grid(row=3, column=1)
my_str.set('Message here')

• The label l1 is used to display messages (e.g., success or error) based on operations performed.
• The text for the label is controlled dynamically using the my_str variable.

8. Defining the Delete Function

def my_delete():
    selected_item = trv.selection()[0]  # Get the selected row ID
    try:
        query = "DELETE FROM student WHERE id=:delete_id"
        rs = my_conn.execute(text(query), {'delete_id': selected_item})
        if rs.rowcount == 1:  # Confirm deletion
            my_conn.commit()  # Commit the transaction
            trv.delete(selected_item)  # Remove from Treeview
            l1.config(fg='green')
            my_str.set('Record deleted')
    except SQLAlchemyError as e:
        error = str(e.__dict__.get('orig', e))
        l1.config(fg='red')
        my_str.set(error)
    l1.after(3000, lambda: my_str.set(''))

• The my_delete() function handles deleting the selected row from both the database and the Treeview widget.
• SQLAlchemy is used to execute the DELETE query and commit the transaction.
• Error handling ensures that any issues are displayed to the user.

from tkinter import ttk
import tkinter as tk
from sqlalchemy import create_engine, text
from sqlalchemy.exc import SQLAlchemyError

my_conn = create_engine("mysql+mysqldb://id:pw@localhost/my_db")
my_conn = my_conn.connect()  # connection string or connection object
r_set = my_conn.execute(text('SELECT * FROM student LIMIT 0,10')) 

my_w = tk.Tk()
my_w.geometry('400x350')  # width and height of the window
my_w.title("www.plus2net.com")  # adding title to window
trv = ttk.Treeview(my_w, selectmode='browse')  # treeview object
trv.grid(row=1, column=1, padx=20, pady=20)  # placed on Grid layout
trv["columns"] = ("1", "2", "3", "4", "5")
trv['show'] = 'headings'
# trv['show']='tree'
trv.column("1", width=30, anchor='c')
trv.column("2", width=80, anchor='c')
trv.column("3", width=80, anchor='c')
trv.column("4", width=80, anchor='c')
trv.column("5", width=80, anchor='c')
trv.heading("1", text="id")
trv.heading("2", text="Name")
trv.heading("3", text="Class")
trv.heading("4", text="Mark")
trv.heading("5", text="Gender")

for dt in r_set:  # adding rows of MySQL data to treeview 
    # print(dt)
    trv.insert("", 'end', iid=dt[0],
               values=(dt[0], dt[1], dt[2], dt[3], dt[4]))

b1 = tk.Button(my_w, text='delete row', width=20, bg='yellow',
               command=lambda: my_delete())
b1.grid(row=2, column=1)
my_font = ('times', 10, '')
my_str = tk.StringVar()
l1 = tk.Label(my_w, textvariable=my_str, font=my_font)
l1.config(fg='blue')
l1.grid(row=3, column=1)
my_str.set("Message here")  # print(my_error)

def my_delete():
    selected_item = trv.selection()[0]  # get selected row id
    try:
        query = "DELETE FROM student WHERE id=:delete_id"
        rs = my_conn.execute(text(query), {'delete_id': selected_item})
        if rs.rowcount == 1:  # confirm that row is deleted
            my_conn.commit()  # Commit the delete operation to the database
            trv.delete(selected_item)  # delete from Treeview
            l1.config(fg='green')  # message font colour
            my_str.set("Record deleted")  # Success message
    except SQLAlchemyError as e:  # error in deleting 
        error = str(e.__dict__.get('orig', e))
        l1.config(fg='red')
        my_str.set(error)  # show error message
    l1.after(3000, lambda: my_str.set(''))  # remove the message

l1.after(6000, lambda: my_str.set(''))
my_w.mainloop()

Prompting User Confirmation Before Deleting a Record

Code Explanation: Breaking Down the Activities

1. Importing Required Modules

from tkinter import ttk
import tkinter as tk
from tkinter import messagebox
from sqlalchemy import create_engine, text
from sqlalchemy.exc import SQLAlchemyError

• ttk: Provides advanced widgets like Treeview.
• messagebox: Used to display dialogs for confirmation or warnings.
• create_engine: Establishes a connection with the database.
• SQLAlchemyError: Captures database-related errors for handling exceptions.

2. Adding a Delete Button

b1 = tk.Button(my_w, text='delete row', width=20, bg='yellow',
               command=lambda: confirm_delete())
b1.grid(row=2, column=1)

• The button triggers the confirm_delete() function, which handles confirmation before deletion.

3. Adding Confirmation Before Deletion

def confirm_delete():
    selected_item = trv.selection()
    if not selected_item:  # Ensure a row is selected
        messagebox.showwarning('Warning', 'Please select a row to delete.')
        return
    response = messagebox.askyesno('Confirm Delete', 'Are you sure you want to delete this record?')
    if response:  # User confirms deletion
        my_delete(selected_item[0])

• The confirm_delete function ensures no accidental deletions occur by prompting the user for confirmation.
• If no row is selected, a warning message is shown.

4. Deleting a Row

def my_delete(selected_item):
    try:
        query = "DELETE FROM student WHERE id=:delete_id"
        rs = my_conn.execute(text(query), {'delete_id': selected_item})
        if rs.rowcount == 1:  # Confirm row deletion
            my_conn.commit()  # Commit changes
            trv.delete(selected_item)  # Remove from Treeview
            l1.config(fg='green')
            my_str.set('Record deleted')
    except SQLAlchemyError as e:
        error = str(e.__dict__.get('orig', e))
        l1.config(fg='red')
        my_str.set(error)
    l1.after(3000, lambda: my_str.set(''))

• The my_delete function deletes the selected row from both the database and the Treeview widget.
• my_conn.commit() ensures that the changes are saved to the database.
• Error handling displays appropriate error messages if the operation fails.

from tkinter import ttk
import tkinter as tk
from tkinter import messagebox
from sqlalchemy import create_engine, text
from sqlalchemy.exc import SQLAlchemyError

my_conn = create_engine("mysql+mysqldb://id:pw@localhost/my_db")
my_conn = my_conn.connect()  # connection string or connection object
r_set = my_conn.execute(text('SELECT * FROM student LIMIT 0,10')) 

my_w = tk.Tk()
my_w.geometry('400x350')  # width and height of the window
my_w.title("www.plus2net.com")  # adding title to window
trv = ttk.Treeview(my_w, selectmode='browse')  # treeview object
trv.grid(row=1, column=1, padx=20, pady=20)  # placed on Grid layout
trv["columns"] = ("1", "2", "3", "4", "5")
trv['show'] = 'headings'
trv.column("1", width=30, anchor='c')
trv.column("2", width=80, anchor='c')
trv.column("3", width=80, anchor='c')
trv.column("4", width=80, anchor='c')
trv.column("5", width=80, anchor='c')
trv.heading("1", text="id")
trv.heading("2", text="Name")
trv.heading("3", text="Class")
trv.heading("4", text="Mark")
trv.heading("5", text="Gender")

for dt in r_set:  # adding rows of MySQL data to treeview 
    trv.insert("", 'end', iid=dt[0],
               values=(dt[0], dt[1], dt[2], dt[3], dt[4]))

b1 = tk.Button(my_w, text='delete row', width=20, bg='yellow',
               command=lambda: confirm_delete())
b1.grid(row=2, column=1)

my_font = ('times', 10, '')
my_str = tk.StringVar()
l1 = tk.Label(my_w, textvariable=my_str, font=my_font)
l1.config(fg='blue')
l1.grid(row=3, column=1)
my_str.set("Message here")

def confirm_delete():
    """Show a confirmation dialog before deleting."""
    selected_item = trv.selection()
    if not selected_item:  # Check if a row is selected
        messagebox.showwarning("Warning", "Please select a row to delete.")
        return
    response = messagebox.askyesno("Confirm Delete", "Are you sure you want to delete this record?")
    if response:  # User clicked Yes
        my_delete(selected_item[0])

def my_delete(selected_item):
    """Delete the selected item from the database and Treeview."""
    try:
        query = "DELETE FROM student WHERE id=:delete_id"
        rs = my_conn.execute(text(query), {'delete_id': selected_item})
        if rs.rowcount == 1:  # Confirm that row is deleted
            my_conn.commit()  # Commit the transaction
            trv.delete(selected_item)  # Delete from Treeview
            l1.config(fg='green')  # Success message color
            my_str.set("Record deleted")
    except SQLAlchemyError as e:  # Handle any SQL errors
        error = str(e.__dict__.get('orig', e))
        l1.config(fg='red')
        my_str.set(error)
    l1.after(3000, lambda: my_str.set(''))  # Clear the message after 3 seconds

my_w.mainloop()

Subhendu Mohapatra

Author

🎥 Join me live on YouTube

Passionate about coding and teaching, I publish practical tutorials on PHP, Python, JavaScript, SQL, and web development. My goal is to make learning simple, engaging, and project‑oriented with real examples and source code.

← Subscribe to our YouTube Channel here