Integrating Database with ttkbootstrap Checkbuttons

In this tutorial, we will explore how to integrate a database with ttkbootstrap checkbuttons to manage dynamic data efficiently. We will start with a simple dictionary as a data source and gradually transition to a fully functional database-driven application.

Tutorial Breakdown:

• Part I: Using a dictionary as a data source to create the checkbutton interface.
• Part II: Integrating an SQLite database to store and retrieve checkbutton states.
• Part III: Adding advanced features like Check All and Uncheck All to update all checkbuttons at once.
• Part IV: Using MySQL Instead of SQLite

Part I : Using dictionary as data source for Student Payment Status Program 🔝

• Imports Required Modules: The script imports ttkbootstrap and ttkbootstrap.constants to use Bootstrap-themed widgets in Tkinter.
• Dictionary for Student Data: A dictionary named students stores student names along with their payment status (True for Paid, False for Unpaid). Check this for loop which uses the enumerate object from the dictionary and creates the checkbuttons dynamically.

  for row, (student_id, (name, status)) in enumerate(students.items()):

Setting Up the Main Window

• Creating the Tkinter Window: A ttk.Window object is created with the superhero theme applied.
• Window Properties: The title is set to www.plus2net.com Student Payment Status, and the window size is set to 400x300 pixels.

Function to Toggle Payment Status

• toggle_status(student_id): This function updates the payment status of a student when a checkbutton is clicked.
• Boolean Toggle: The function switches the stored True or False value in the dictionary.
• Console Output: It prints whether the student's payment status is Paid or Unpaid.

Creating Labels and Checkbuttons

• Looping Through the Students Dictionary: A for loop iterates through the dictionary, creating a Label and a checkbutton for each student.
• Label Placement: Each student's name is displayed using a ttk.Label with a left-aligned placement.
• Checkbutton for Payment Status: A ttk.Checkbutton is created with square-toggle styling.
• BooleanVar for Checkbutton: The checkbutton state is managed using BooleanVar to reflect the student's payment status.
• Grid Layout: Labels are placed in column 0 (left) and checkbuttons in column 1 (right), with padding applied.

Running the Application

• Main Event Loop: The root.mainloop() function is called to keep the application running.

import ttkbootstrap as ttk
from ttkbootstrap.constants import *

# Dictionary storing student names and their payment status
students = {
    1: ["Alice", True],
    2: ["Bob", False],
    3: ["Charlie", True],
    4: ["David", False],
    5: ["Eve", True]
}

# Create the main window
root = ttk.Window(themename="superhero") # Update the theme
root.title("www.plus2net.com Student Payment Status") # Window title
root.geometry("400x300")  # Increased window size for better spacing

# Function to toggle payment status
def toggle_status(student_id):
    students[student_id][1] = not students[student_id][1]
    print(
        f"{students[student_id][0]}'s:\
        {'Paid' if students[student_id][1] else 'Unpaid'}"
    )

# Create labels and checkbuttons in a grid layout using students dictionary
for row, (student_id, (name, status)) in enumerate(students.items()):
    # Name label with left padding and larger font size
    lb = ttk.Label(root, text=name, padding=(20, 5), font=("Arial", 12))
    lb.grid(row=row, column=0, sticky="w") # Label is placed at left

    # Checkbutton aligned to the right
    chk_var = ttk.BooleanVar(value=status)  # Status True or False
    chk = ttk.Checkbutton(
        root,
        variable=chk_var,
        bootstyle="square-toggle",  # Update the style
        command=lambda s=student_id: toggle_status(s)
    )
    chk.grid(row=row, column=1, sticky="e", padx=15, pady=15) # Placed at right

# Run the application
root.mainloop()

Summary

• This program provides an interactive interface to track student payment status without any database integration.
• The checkbuttons allow toggling of payment status dynamically.
• The layout is neatly arranged using a grid layout with proper styling.

Part II : Adding SQLite database to read and update status 🔝

It performs the following tasks:

• ✅ Create an SQLite database and insert sample student data.
• ✅ Fetch data from the database to populate the checkbuttons.
• ✅ Update payment status in the database when a checkbutton is clicked.

1️⃣ Creating the SQLite Database with SQLAlchemy

Run this script once to create the database and insert sample data. Ensure the database path is correctly set and used consistently in the main application.

We use SQLAlchemy to connect to the database and execute SQL commands.

from sqlalchemy import create_engine, text

# Define database path (ensure the same path is used in the main application)
DATABASE_URL = "sqlite:///F:\\testing\\ckb_students.db"

# Create database connection using SQLAlchemy
engine = create_engine(DATABASE_URL, echo=True)

# Create table using raw SQL
with engine.connect() as connection:
    connection.execute(text("""
        CREATE TABLE IF NOT EXISTS students (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            status BOOLEAN NOT NULL
        )
    """))

    # Check if data already exists
    result = connection.execute(text("SELECT COUNT(*) FROM students"))
    count = result.scalar()

    # Insert sample data if the table is empty
    if count == 0:
        students_data = [
            (1, "Alice", True),
            (2, "Bob", False),
            (3, "Charlie", True),
            (4, "David", False),
            (5, "Eve", True)
        ]
        connection.execute(text("INSERT INTO students (id, name, status) VALUES (:id, :name, :status)"), 
                           [{"id": s[0], "name": s[1], "status": s[2]} for s in students_data])
        connection.commit()

print("Database setup completed with sample data.")

Application to manage Student Payment Status with SQLite & Tkinter

1️⃣ Establishing a Single Database Connection

• We define the database URL: DATABASE_URL points to the SQLite database file.
• We create an SQLAlchemy engine: create_engine(DATABASE_URL, echo=False) sets up the connection.

2️⃣ Fetching Data from the Database

• The fetch_students() function retrieves student data: It runs SELECT id, name, status FROM students.
• Results are returned as a list: The function fetches all records and sends them back for use in the GUI.

3️⃣ Updating Payment Status

• The toggle_status() function updates the database: It runs UPDATE students SET status = :status WHERE id = :id.
• Uses bound parameters: Prevents SQL injection by passing values as { "status": new_status, "id": student_id }.
• Commits the changes: After executing the SQL, connection.commit() ensures data persistence.

4️⃣ Creating the Tkinter GUI

• We create the main Tkinter window: ttk.Window(themename="superhero") initializes the GUI.
• Window title and size are set: The title is www.plus2net.com Student Payment Status, and dimensions are 400x300.

5️⃣ Populating the GUI with Student Data

• Fetching students from the database: The fetch_students() function is called.
• A loop creates labels and checkbuttons: Each student’s name and payment status is displayed dynamically.
• Checkbuttons reflect database values: The status (Paid/Unpaid) is controlled using BooleanVar(value=status).
• Toggling a checkbutton updates the database: When clicked, the toggle_status() function is triggered.

6️⃣ Running and Closing the Application

• The Tkinter main event loop starts: root.mainloop() keeps the GUI active.
• Closing the database connection: connection.close() ensures resources are released when the program exits.

import ttkbootstrap as ttk
from ttkbootstrap.constants import *
from sqlalchemy import create_engine, text

# Define database connection using SQLAlchemy (Single Connection)
DATABASE_URL = "sqlite:///F:\\testing\\ckb_students.db"
engine = create_engine(DATABASE_URL, echo=False)
connection = engine.connect()  # Create a single persistent connection

# Function to fetch student data from SQLite using SQLAlchemy
def fetch_students():
    result = connection.execute(text("SELECT id, name, status FROM students"))
    return result.fetchall()

# Function to update payment status in SQLite when toggled
def toggle_status(student_id, chk_var):
    new_status = chk_var.get()
    connection.execute(text("UPDATE students SET status = :status WHERE id = :id"),
                           {"status": new_status, "id": student_id})
    connection.commit()
    print(f"Updated Student ID {student_id}: {'Paid' if new_status else 'Unpaid'}")

# Create main Tkinter window
root = ttk.Window(themename="superhero")
root.title("www.plus2net.com Student Payment Status")
root.geometry("400x300")

# Fetch students from database
students = fetch_students()

# Create labels and checkbuttons dynamically
for row, (student_id, name, status) in enumerate(students):
    # Label with student name
    lb = ttk.Label(root, text=name, padding=(20, 5), font=("Arial", 12))
    lb.grid(row=row, column=0, sticky="w")

    # Checkbutton with database status
    chk_var = ttk.BooleanVar(value=status)
    chk = ttk.Checkbutton(
        root,
        variable=chk_var,
        bootstyle="square-toggle", # Update the style 
        command=lambda s=student_id, v=chk_var: toggle_status(s, v)
    )
    chk.grid(row=row, column=1, sticky="e", padx=15, pady=15)

# Run Tkinter event loop
root.mainloop()

# Close the database connection when the application exits
connection.close()

Different Styles of ttkbootstrap Checkbuttons

We can modify the appearance of ttkbootstrap Checkbuttons using the bootstyle attribute. Below is an example showing different styles.

# Different styles for ttkbootstrap Checkbuttons

chk1 = ttk.Checkbutton(root, text="Square Toggle", bootstyle="square-toggle")
chk1.grid(row=0, column=0, padx=10, pady=5)

chk2 = ttk.Checkbutton(root, text="Round Toggle", bootstyle="round-toggle")
chk2.grid(row=1, column=0, padx=10, pady=5)

chk3 = ttk.Checkbutton(root, text="Outline", bootstyle="outline-toolbutton")
chk3.grid(row=2, column=0, padx=10, pady=5)

chk4 = ttk.Checkbutton(root, text="Secondary Style", bootstyle="secondary")
chk4.grid(row=3, column=0, padx=10, pady=5)

chk5 = ttk.Checkbutton(root, text="Success Style", bootstyle="success")
chk5.grid(row=4, column=0, padx=10, pady=5)

Explanation of Checkbutton Styles

• square-toggle: A square-shaped toggle button.
• round-toggle: A rounded toggle switch.
• outline-toolbutton: A bordered tool-style checkbutton.
• secondary: A Bootstrap secondary color-styled checkbutton.
• success: A Bootstrap success (green) checkbutton.

✅ Summary

• We used bootstyle to change the look of ttkbootstrap Checkbuttons.
• Different styles improve the UI and make checkbuttons visually distinct.
• This method enhances the usability of the application.

Part III: Bulk update for Check All / Uncheck All by using single button 🔝

Once the Check All / Uncheck All button is clicked, it sends the selected status to the toggle_all() function. This function then updates all records in the database with a single SQL query, ensuring efficient bulk updates. Simultaneously, all checkbuttons within the application are updated to reflect the new status instantly.

def toggle_all(status):
    # Update all records in one query
    connection.execute(text("UPDATE students SET status = :status"), {"status": status})
    connection.commit()
    print(f"All records updated: {'Paid' if status else 'Unpaid'}")

    # Update checkbuttons in the UI
    for chk_var, _ in checkboxes:
        chk_var.set(status)

✅ Improvements

• Reduced database calls: Instead of executing multiple queries, we now update all records with one SQL command.
• Faster execution: The UI updates immediately, and database operations are much quicker.
• Efficient resource usage: No need to iterate over each checkbutton for database updates.

import ttkbootstrap as ttk
from ttkbootstrap.constants import *
from sqlalchemy import create_engine, text

# Define database connection using SQLAlchemy (Single Connection)
DATABASE_URL = "sqlite:///F:\\testing\\ckb_students.db"
engine = create_engine(DATABASE_URL, echo=False)
connection = engine.connect()  # Create a single persistent connection

# Function to fetch student data from SQLite using SQLAlchemy
def fetch_students():
    result = connection.execute(text("SELECT id, name, status FROM students"))
    return result.fetchall()

# Function to update payment status in SQLite when toggled
def toggle_status(student_id, chk_var):
    new_status = chk_var.get()
    connection.execute(text("UPDATE students SET status = :status WHERE id = :id"),
                       {"status": new_status, "id": student_id})
    connection.commit()
    print(f"Updated Student ID {student_id}: {'Paid' if new_status else 'Unpaid'}")

# Optimized function to check/uncheck all records using a single SQL query
def toggle_all(status):
    # Update all records in one query
    connection.execute(text("UPDATE students SET status = :status"), {"status": status})
    connection.commit()
    print(f"All records updated: {'Paid' if status else 'Unpaid'}")

    # Update checkbuttons in the UI
    for chk_var, _ in checkboxes:
        chk_var.set(status)

# Create main Tkinter window
root = ttk.Window(themename="superhero")
root.title("www.plus2net.com Student Payment Status")
root.geometry("400x350")

# Fetch students from database
students = fetch_students()

# List to store checkbuttons and variables
checkboxes = []

# Create labels and checkbuttons dynamically
for row, (student_id, name, status) in enumerate(students):
    # Label with student name
    lb = ttk.Label(root, text=name, padding=(20, 5), font=("Arial", 12))
    lb.grid(row=row, column=0, sticky="w")

    # Checkbutton with database status
    chk_var = ttk.BooleanVar(value=status)
    chk = ttk.Checkbutton(
        root,
        variable=chk_var,
        bootstyle="square-toggle",
        command=lambda s=student_id, v=chk_var: toggle_status(s, v)
    )
    chk.grid(row=row, column=1, sticky="e", padx=15, pady=15)

    # Store checkbutton reference
    checkboxes.append((chk_var, student_id))

# Create "Check All / Uncheck All" button
check_all_var = ttk.BooleanVar()

check_all_btn = ttk.Checkbutton(
    root,
    text="Check All / Uncheck All",
    variable=check_all_var,
    bootstyle="outline-toolbutton",
    command=lambda: toggle_all(check_all_var.get())
)
check_all_btn.grid(row=len(students), column=0, columnspan=2, padx=20, pady=15)

# Run Tkinter event loop
root.mainloop()

# Close the database connection when the application exits
connection.close()

Part IV:Using MySQL Instead of SQLite 🔝

We can seamlessly switch to a MySQL database by modifying the database connection settings in SQLAlchemy. The rest of the code remains unchanged.

Below is the SQL dump to create the required table and insert sample data in MySQL.

MySQL Database Dump for Student Payment Status

Use the following SQL script to create the database, table, and insert sample records in MySQL.

-- Create the database
CREATE DATABASE student_db;
USE student_db;

-- Create the students table
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    status BOOLEAN NOT NULL DEFAULT 0
);

-- Insert sample student data
INSERT INTO students (name, status) VALUES
('Alice', 1),
('Bob', 0),
('Charlie', 1),
('David', 0),
('Eve', 1);

✅ Notes:

• BOOLEAN is used for the status column (0 = Unpaid, 1 = Paid).
• AUTO_INCREMENT ensures each student has a unique id.
• The database name is student_db, which you can modify as needed.

Updating the Connection for MySQL

To switch from SQLite to MySQL, update the database connection string in SQLAlchemy. Below is the required change:

# SQLite Connection (Commented Out)
# DATABASE_URL = "sqlite:///F:\\testing\\ckb_students.db"

# MySQL Connection using mysqldb or pymysql
DATABASE_URL = "mysql+mysqldb://username:password@localhost/student_db"

# Create the engine for MySQL
engine = create_engine(DATABASE_URL, echo=False)
connection = engine.connect()  # Establish the database connection

✅ Key Changes:

• SQLite connection is commented out to prevent conflicts.
• MySQL connection string follows the format: mysql+pymysql://username:password@localhost/student_db.
• Replace username and password with your actual MySQL credentials.
• Ensure you have the PyMySQL or mysqldb package installed using:

  pip install pymysql

  or

  pip install mysqlclient 

Conclusion

We have successfully integrated ttkbootstrap checkbuttons with a database using SQLAlchemy. Initially, we used a dictionary as a data source, then transitioned to an SQLite database, and finally adapted the code for MySQL.

By optimizing database operations, we improved performance and reduced resource usage. The addition of a Check All / Uncheck All button enhanced user experience by allowing bulk updates with a single click.

This approach ensures a scalable and efficient solution for managing dynamic checkbutton states in Tkinter applications.