Python Tkinter GUI DB Code


# To delete a selected row from a Tkinter Treeview and the corresponding record from a MySQL database on a button click, you'll need to follow these steps:

Requirements:

tkinter for GUI
>> pip install tkinter
mysql.connector to interact with MySQL
>> pip install mysql-connector-python

Example Code

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

# Connect to MySQL
db = mysql.connector.connect(
    host="localhost",
    user="root",
    password="Admin@2025",
    database="testdb"
)
cursor = db.cursor()

# Sample GUI
root = tk.Tk()
root.title("Delete Row Example")

tree = ttk.Treeview(root, columns=("id", "name"), show="headings")
tree.heading("id", text="ID")
tree.heading("name", text="Name")
tree.pack()

# Sample function to load data into Treeview :

def load_data():
    cursor.execute("SELECT id, name FROM your_table")
    rows = cursor.fetchall()
    for row in rows:
        tree.insert("", "end", values=row)

# Function to delete selected row

def delete_selected():
    selected_item = tree.selection()
    if not selected_item:
        messagebox.showwarning("No selection", "Please select a row to delete.")
        return

    item = tree.item(selected_item)
    record_id = item["values"][0]  # Assuming the first column is the ID

    # Confirm deletion
    if not messagebox.askyesno("Confirm", f"Delete record ID {record_id}?"):
        return

    try:
    
        # Delete from database
        cursor.execute("DELETE FROM your_table WHERE id = %s", (record_id,))
        db.commit()

        # Delete from Treeview
        tree.delete(selected_item)
        messagebox.showinfo("Success", f"Record ID {record_id} deleted.")
    except Exception as e:
        messagebox.showerror("Error", f"Failed to delete record: {e}")
        db.rollback()

# Delete button
delete_btn = tk.Button(root, text="Delete Selected", command=delete_selected)
delete_btn.pack(pady=10)

load_data()
root.mainloop()


Notes: Replace your_username, your_password, your_database, and your_table with your actual MySQL credentials and table name. Ensure the id column is the primary key or a unique identifier for deletion. @ If record_id is integer ? Yes, it is perfectly fine to use %s as a placeholder in MySQL queries — even for integers. In mysql.connector (and most other Python DB-API 2.0-compliant libraries), %s is the correct placeholder regardless of the data type (string, integer, float, etc.). The library safely handles the parameter substitution. Example — both valid: # If record_id is an integer: record_id = 5 cursor.execute("DELETE FROM your_table WHERE id = %s", (record_id,)) Avoid using string formatting like below: # This is unsafe and should be avoided (prone to SQL injection) cursor.execute(f"DELETE FROM your_table WHERE id = {record_id}") So yes, using %s with integers is correct and safe. **************************************** # To display a specific record from a MySQL table in a Tkinter Treeview based on an Entry input and button click, here's a full working example: --- Example: Search by ID and Show in Treeview import tkinter as tk from tkinter import ttk, messagebox import mysql.connector # --- MySQL Connection --- db = mysql.connector.connect( host="localhost", user="root", password="Admin@2025", database="testdb" ) cursor = db.cursor() # --- GUI Setup --- root = tk.Tk() root.title("Search Record") # --- Entry and Button --- search_frame = tk.Frame(root) search_frame.pack(pady=10) tk.Label(search_frame, text="Enter ID:").pack(side=tk.LEFT) id_entry = tk.Entry(search_frame) id_entry.pack(side=tk.LEFT, padx=5) # Fetch One Record def search_record(): # Clear the tree first for item in tree.get_children(): tree.delete(item) input_id = id_entry.get() if not input_id.isdigit(): messagebox.showwarning("Invalid Input", "Please enter a valid numeric ID.") return try: cursor.execute("SELECT id, name FROM tblusers WHERE id = %s", (input_id,)) row = cursor.fetchone() if row: tree.insert("", "end", values=row) else: messagebox.showinfo("Not Found", f"No record found with ID {input_id}") except Exception as e: messagebox.showerror("Error", str(e)) tk.Button(search_frame, text="Search", command=search_record).pack(side=tk.LEFT, padx=5) # --- Treeview Setup --- tree = ttk.Treeview(root, columns=("id", "name"), show="headings") tree.heading("id", text="ID") tree.heading("name", text="Name") tree.pack() root.mainloop() ---
What to Change Replace your_username, your_password, your_database, and your_table with your actual database info. Adjust the columns (id, name) if your table has different fields. **************************************** # Create a login form using Tkinter in Python, connected to a MySQL database. 1. Requirements Make sure you have these installed: >> pip install mysql-connector-python 2. MySQL Database Setup Create a MySQL database and user table. Example SQL: CREATE DATABASE user_db; USE user_db; CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL, password VARCHAR(50) NOT NULL ); # Insert a test user: INSERT INTO users (username, password) VALUES ('admin', 'admin123'); --- 3. Python Tkinter Login Form Code import tkinter as tk from tkinter import messagebox import mysql.connector # Function to check login credentials def login(): username = entry_user.get() password = entry_pass.get() if username == "" or password == "": messagebox.showwarning("Input error", "Please fill all fields") return try: # Connect to MySQL conn = mysql.connector.connect( host="localhost", user="your_mysql_user", password="your_mysql_password", database="user_db" ) cursor = conn.cursor() query = "SELECT * FROM users WHERE username=%s AND password=%s" cursor.execute(query, (username, password)) result = cursor.fetchone() if result: messagebox.showinfo("Login success", f"Welcome, {username}!") else: messagebox.showerror("Login failed", "Invalid credentials") cursor.close() conn.close() except mysql.connector.Error as err: messagebox.showerror("Database error", f"Error: {err}") # Create the GUI window root = tk.Tk() root.title("Login Form") root.geometry("300x200") # Username tk.Label(root, text="Username").pack(pady=5) entry_user = tk.Entry(root) entry_user.pack() # Password tk.Label(root, text="Password").pack(pady=5) entry_pass = tk.Entry(root, show='*') entry_pass.pack() # Login button tk.Button(root, text="Login", command=login).pack(pady=20) root.mainloop() ---
4. Notes Replace "your_mysql_user" and "your_mysql_password" with your actual MySQL credentials. Never store plaintext passwords in a real-world application — use password hashing like bcrypt. Would you like an enhanced version with password hashing and registration as well? **************************************** @ IF login successful then hide Login form and show other form To hide the login form and show another form after successful login in Tkinter, you can destroy the login window and open a new one. Here's how to modify the code: ---
Full Example with Second Form import tkinter as tk from tkinter import messagebox import mysql.connector def open_main_form(username): main_form = tk.Tk() main_form.title("Main App") main_form.geometry("300x200") tk.Label(main_form, text=f"Welcome, {username}!", font=("Arial", 14)).pack(pady=50) tk.Button(main_form, text="Exit", command=main_form.destroy).pack() main_form.mainloop() def login(): username = entry_user.get() password = entry_pass.get() if username == "" or password == "": messagebox.showwarning("Input error", "Please fill all fields") return try: # Connect to MySQL conn = mysql.connector.connect( host="localhost", user="root", password="Admin@2025", database="user_db" ) cursor = conn.cursor() query = "SELECT * FROM users WHERE username=%s AND password=%s" cursor.execute(query, (username, password)) result = cursor.fetchone() if result: messagebox.showinfo("Login success", f"Welcome, {username}!") login_window.destroy() # Close login window open_main_form(username) # Open main app window else: messagebox.showerror("Login failed", "Invalid credentials") cursor.close() conn.close() except mysql.connector.Error as err: messagebox.showerror("Database error", f"Error: {err}") # Create the login form login_window = tk.Tk() login_window.title("Login Form") login_window.geometry("300x200") tk.Label(login_window, text="Username").pack(pady=5) entry_user = tk.Entry(login_window) entry_user.pack() tk.Label(login_window, text="Password").pack(pady=5) entry_pass = tk.Entry(login_window, show='*') entry_pass.pack() tk.Button(login_window, text="Login", command=login).pack(pady=20) login_window.mainloop() --- What this does: If login is successful: The login window closes. A new main application window opens with a welcome message.


# How to use Toplevel() to show the second form within the same main application after successful login.
This is cleaner and more appropriate than opening another Tk() window.


Tkinter Login with Toplevel() Main Form :


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

def open_main_form(username):
    main_form = tk.Toplevel(login_window)
    main_form.title("Main Application")
    main_form.geometry("300x200")

    tk.Label(main_form, text=f"Welcome, {username}!", font=("Arial", 14)).pack(pady=50)
    tk.Button(main_form, text="Close App", command=login_window.destroy).pack()

def login():
    username = entry_user.get()
    password = entry_pass.get()

    if username == "" or password == "":
        messagebox.showwarning("Input error", "Please fill all fields")
        return

    try:
        # Connect to MySQL
        conn = mysql.connector.connect(
            host="localhost",
            user="root",
            password="Admin@2025",
            database="user_db"
        )
        cursor = conn.cursor()

        query = "SELECT * FROM users WHERE username=%s AND password=%s"
        cursor.execute(query, (username, password))

        result = cursor.fetchone()

        if result:
            messagebox.showinfo("Login success", f"Welcome, {username}!")

# Hide login widgets
            login_frame.pack_forget()  

# Open new form using Toplevel
            open_main_form(username)  

        else:
            messagebox.showerror("Login failed", "Invalid credentials")

        cursor.close()
        conn.close()

    except mysql.connector.Error as err:
        messagebox.showerror("Database error", f"Error: {err}")

# Main window
login_window = tk.Tk()
login_window.title("Login Form")
login_window.geometry("300x200")

# Frame for login widgets (makes hiding easier)
login_frame = tk.Frame(login_window)
login_frame.pack(pady=20)

tk.Label(login_frame, text="Username").pack(pady=5)
entry_user = tk.Entry(login_frame)
entry_user.pack()

tk.Label(login_frame, text="Password").pack(pady=5)
entry_pass = tk.Entry(login_frame, show='*')
entry_pass.pack()

tk.Button(login_frame, text="Login", command=login).pack(pady=20)

login_window.mainloop()


---

Key Points:

Toplevel() creates a new window attached to the root (login_window).

Instead of destroying the login window, we hide the login frame using pack_forget().

This way, the app runs as a single process with multiple windows.



# Python Tkinter GUI Database App using Class

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

# MySQL connection
def connect_db():
    try:
        return mysql.connector.connect(
            host="localhost",
            user="your_username",
            password="your_password",
            database="testdb"
        )
    except mysql.connector.Error as err:
        messagebox.showerror("Database Error", f"Error: {err}")
        return None

# Main App Class
class App(tk.Tk):
    def __init__(self):
        super().__init__()
        self.title("Multi-Page Tkinter + MySQL App")
        self.geometry("600x450")
        self.resizable(False, False)

        # Create container and all frames
        container = tk.Frame(self)
        container.pack(fill="both", expand=True)

        self.frames = {}
        for F in (LoginPage, DashboardPage, UserFormPage):
            frame = F(container, self)
            self.frames[F] = frame
            frame.grid(row=0, column=0, sticky="nsew")

        self.show_frame(LoginPage)

    def show_frame(self, page):
        frame = self.frames[page]
        frame.tkraise()

# Login Page
class LoginPage(tk.Frame):
    def __init__(self, parent, controller):
        super().__init__(parent)
        self.controller = controller

        tk.Label(self, text="Login Page", font=("Arial", 18)).pack(pady=20)
        tk.Label(self, text="Username").pack()
        self.username_entry = tk.Entry(self)
        self.username_entry.pack()
        tk.Label(self, text="Password").pack()
        self.password_entry = tk.Entry(self, show="*")
        self.password_entry.pack()

        tk.Button(self, text="Login", command=self.login).pack(pady=10)

    def login(self):
        # Just a mock login (replace with real user table if needed)
        if self.username_entry.get() == "admin" and self.password_entry.get() == "admin":
            self.controller.show_frame(DashboardPage)
        else:
            messagebox.showerror("Login Failed", "Invalid credentials.")

# Dashboard Page
class DashboardPage(tk.Frame):
    def __init__(self, parent, controller):
        super().__init__(parent)
        self.controller = controller
        tk.Label(self, text="Dashboard", font=("Arial", 18)).pack(pady=20)

        tk.Button(self, text="Manage Users", command=lambda: controller.show_frame(UserFormPage)).pack(pady=10)
        tk.Button(self, text="Logout", command=lambda: controller.show_frame(LoginPage)).pack()

# User Form Page
class UserFormPage(tk.Frame):
    def __init__(self, parent, controller):
        super().__init__(parent)
        self.controller = controller

        self.selected_id = None

        tk.Label(self, text="User Form", font=("Arial", 16)).pack(pady=10)

        # Entry fields
        self.name_entry = tk.Entry(self, width=30)
        self.email_entry = tk.Entry(self, width=30)

        tk.Label(self, text="Name").pack()
        self.name_entry.pack()
        tk.Label(self, text="Email").pack()
        self.email_entry.pack()

        btn_frame = tk.Frame(self)
        btn_frame.pack(pady=10)
        tk.Button(btn_frame, text="Add", command=self.insert_data).grid(row=0, column=0, padx=5)
        tk.Button(btn_frame, text="Update", command=self.update_data).grid(row=0, column=1, padx=5)
        tk.Button(btn_frame, text="Delete", command=self.delete_data).grid(row=0, column=2, padx=5)
        tk.Button(btn_frame, text="Back", command=lambda: controller.show_frame(DashboardPage)).grid(row=0, column=3, padx=5)

        # Search box
        self.search_entry = tk.Entry(self)
        self.search_entry.pack()
        tk.Button(self, text="Search", command=self.search_data).pack(pady=5)

        # Treeview table
        self.tree = ttk.Treeview(self, columns=("ID", "Name", "Email"), show='headings')
        self.tree.heading("ID", text="ID")
        self.tree.heading("Name", text="Name")
        self.tree.heading("Email", text="Email")
        self.tree.bind("<>", self.select_item)
        self.tree.pack(fill="both", expand=True, pady=10)

        self.fetch_data()

    def insert_data(self):
        name = self.name_entry.get()
        email = self.email_entry.get()

        if name and email:
            conn = connect_db()
            if conn:
                cursor = conn.cursor()
                cursor.execute("INSERT INTO users (name, email) VALUES (%s, %s)", (name, email))
                conn.commit()
                conn.close()
                messagebox.showinfo("Success", "User added.")
                self.clear()
                self.fetch_data()

    def fetch_data(self):
        for row in self.tree.get_children():
            self.tree.delete(row)

        conn = connect_db()
        if conn:
            cursor = conn.cursor()
            cursor.execute("SELECT id, name, email FROM users")
            for row in cursor.fetchall():
                self.tree.insert("", tk.END, values=row)
            conn.close()

    def select_item(self, event):
        selected = self.tree.focus()
        if selected:
            values = self.tree.item(selected, 'values')
            self.selected_id = values[0]
            self.name_entry.delete(0, tk.END)
            self.email_entry.delete(0, tk.END)
            self.name_entry.insert(0, values[1])
            self.email_entry.insert(0, values[2])

    def update_data(self):
        if not self.selected_id:
            messagebox.showwarning("Select a record", "Please select a user to update.")
            return
        conn = connect_db()
        if conn:
            cursor = conn.cursor()
            cursor.execute("UPDATE users SET name=%s, email=%s WHERE id=%s",
                           (self.name_entry.get(), self.email_entry.get(), self.selected_id))
            conn.commit()
            conn.close()
            messagebox.showinfo("Updated", "User updated.")
            self.clear()
            self.fetch_data()

    def delete_data(self):
        if not self.selected_id:
            messagebox.showwarning("Select a record", "Please select a user to delete.")
            return
        confirm = messagebox.askyesno("Confirm", "Are you sure?")
        if confirm:
            conn = connect_db()
            if conn:
                cursor = conn.cursor()
                cursor.execute("DELETE FROM users WHERE id=%s", (self.selected_id,))
                conn.commit()
                conn.close()
                messagebox.showinfo("Deleted", "User deleted.")
                self.clear()
                self.fetch_data()

    def search_data(self):
        keyword = self.search_entry.get()
        for row in self.tree.get_children():
            self.tree.delete(row)

        conn = connect_db()
        if conn:
            cursor = conn.cursor()
            cursor.execute("SELECT id, name, email FROM users WHERE name LIKE %s OR email LIKE %s",
                           ('%' + keyword + '%', '%' + keyword + '%'))
            for row in cursor.fetchall():
                self.tree.insert("", tk.END, values=row)
            conn.close()

    def clear(self):
        self.name_entry.delete(0, tk.END)
        self.email_entry.delete(0, tk.END)
        self.search_entry.delete(0, tk.END)
        self.selected_id = None

# Run the app
if __name__ == "__main__":
    app = App()
    app.mainloop()
    

Post a Comment