Python Tkinter CRUD application with SQLite

Python Tkinter File Operations and Integration: Exercise-12 with Solution

Write a Python program that implements CRUD (Create, Read, Update, Delete) operations using Tkinter and a database.

Sample Solution:

Python Code:

import tkinter as tk
import sqlite3
from tkinter import messagebox

class CRUDApp:
    def __init__(self, root):
        self.root = root
        self.root.title("CRUD Application")

        # Create a database or connect to an existing one
        self.conn = sqlite3.connect("school.db")
        self.cursor = self.conn.cursor()

        # Create a table if it doesn't exist
        self.cursor.execute('''CREATE TABLE IF NOT EXISTS students (
            id INTEGER PRIMARY KEY,
            name TEXT,
            stclass TEXT,
            marks REAL
        )''')
        self.conn.commit()

        # Create GUI elements
        self.name_label = tk.Label(root, text="Name:")
        self.name_label.pack()

        self.name_entry = tk.Entry(root)
        self.name_entry.pack()

        self.position_label = tk.Label(root, text="Class:")
        self.position_label.pack()

        self.stclass_entry = tk.Entry(root)
        self.stclass_entry.pack()

        self.salary_label = tk.Label(root, text="Marks:")
        self.salary_label.pack()

        self.marks_entry = tk.Entry(root)
        self.marks_entry.pack()

        self.add_button = tk.Button(root, text="Add Student", command=self.add_student)
        self.add_button.pack()

        self.student_listbox = tk.Listbox(root)
        self.student_listbox.pack()

        self.load_students()

        self.update_button = tk.Button(root, text="Update Student", command=self.update_student)
        self.update_button.pack()

        self.delete_button = tk.Button(root, text="Delete Student", command=self.delete_student)
        self.delete_button.pack()

    def add_student(self):
        name = self.name_entry.get()
        stclass = self.stclass_entry.get()
        marks = self.marks_entry.get()
        if name and stclass and marks:
            #self.cursor.execute("INSERT INTO students (name, class, marks) VALUES (?, ?, ?)", (name, class, marks))
            self.cursor.execute("INSERT INTO students (name, stclass, marks) VALUES (?, ?, ?)", (name, stclass, marks))
            self.conn.commit()
            self.load_students()
            self.clear_entries()
        else:
            messagebox.showwarning("Warning", "Please fill in all fields.")

    def load_students(self):
        self.student_listbox.delete(0, tk.END)
        self.cursor.execute("SELECT * FROM students")
        students = self.cursor.fetchall()
        for row in students:
            self.student_listbox.insert(tk.END, f"{row[0]}. {row[1]}, {row[2]}, {'%.2f' % float(row[3])}")


    def clear_entries(self):
        self.name_entry.delete(0, tk.END)
        self.stclass_entry.delete(0, tk.END)
        self.marks_entry.delete(0, tk.END)

    def update_student(self):
        selected_student = self.student_listbox.get(tk.ACTIVE)
        if selected_student:
            student_id = int(selected_student.split(".")[0])
            name = self.name_entry.get()
            stclass = self.stclass_entry.get()
            marks = self.marks_entry.get()
            if name and stclass and marks:
                self.cursor.execute("UPDATE students SET name=?, stclass=?, marks=? WHERE id=?", (name, stclass, marks, student_id))
                self.conn.commit()
                self.load_students()
                self.clear_entries()
            else:
                messagebox.showwarning("Warning", "Please fill in all fields.")
        else:
            messagebox.showwarning("Warning", "Please select an student to update.")

    def delete_student(self):
        selected_student = self.student_listbox.get(tk.ACTIVE)
        if selected_student:
            student_id = int(selected_student.split(".")[0])
            self.cursor.execute("DELETE FROM students WHERE id=?", (student_id,))
            self.conn.commit()
            self.load_students()
            self.clear_entries()
        else:
            messagebox.showwarning("Warning", "Please select an student to delete.")

    def __del__(self):
        self.conn.close()

if __name__ == "__main__":
    root = tk.Tk()
    app = CRUDApp(root)
    root.mainloop()

Explanation:

In the exercise above -

• Import the necessary modules.
• Initialize the Tkinter app: The CRUDApp class is defined, which represents the main application. The constructor (__init__) sets up the main application window, initializes the database connection, and creates GUI elements.
• Create a SQLite database "school.db" (or create it if it doesn't exist) and define a table named "students" with columns for student information, including name, class, and marks.
• Create GUI elements include labels, entry fields, buttons, and a listbox to display student records.
• Implement CRUD operations:
• add_student: Adds a new student record to the database when the "Add Student" button is clicked. It checks if all required fields (name, class, and marks) are filled before inserting data.
• load_students: Loads and displays student records from the database in the listbox.
• clear_entries: Clears the input fields.
• update_student: Updates an existing student record when the "Update Student" button is clicked. It also validates the input fields.
• delete_student: Deletes a selected student record when the "Delete Student" button is clicked.
• Database interaction: The program uses SQLite database queries to "insert", "update", and "delete" student records and fetches records to display in the listbox.
• Cleanup: The "del()" method ensures that the database connection is closed when the program exits.
• Main loop: Finally, the program checks if it's being run as the main module and starts the Tkinter main loop.

Output:

Flowchart:

Python Code Editor:

Previous: Python SQLite database with Tkinter.