Python TKInter Forms Data Saving DATABASE version

ExamStore Python Example 2

import tkinter as tk

# Define the UserExamStore class
class UserExamStore:
    def __init__(self, id, name, date, exams=None, languages=None, formats=None):
        self.id = id
        self.name = name
        self.date = date
        self.exams = exams if exams is not None else set()
        self.languages = languages if languages is not None else set()
        self.formats = formats if formats is not None else set()

    def __repr__(self):
        return f"UserExamStore(id={self.id}, name={self.name}, date={self.date}, exams={self.exams}, languages={self.languages}, formats={self.formats})"


import sqlite3

def create_database():
    connection = sqlite3.connect("exams.db")
#Call sqlite3.connect () to create a connection to the database tutorial.db in the current working directory, implicitly creating it if it does not exist
    cursor = connection.cursor()

    cursor.execute("""
        CREATE TABLE IF NOT EXISTS ExamHeader (
            ID INTEGER PRIMARY KEY,
            Name TEXT,
            Date TEXT
        )
    """)

    cursor.execute("""
        CREATE TABLE IF NOT EXISTS ExamLines (
            ID INTEGER PRIMARY KEY,
            ExamID INTEGER,
            Exam TEXT,
            Language TEXT,
            Format TEXT,
            FOREIGN KEY (ExamID) REFERENCES ExamHeader (ID)
        )
    """)

    connection.commit()
    connection.close()

# Call the create_database function to create the database and tables
create_database()

def load_user_exam_stores():
    connection = sqlite3.connect("exams.db")
    cursor = connection.cursor()

    cursor.execute("SELECT * FROM ExamHeader")
    exam_headers = cursor.fetchall()
    user_exam_stores = []

    for exam_header in exam_headers:
        exam_id, name, date = exam_header

        cursor.execute("SELECT Exam, Language, Format FROM ExamLines WHERE ExamID = ?", (exam_id,))
        exam_lines = cursor.fetchall()

        exams = set()
        languages = set()
        formats = set()

        for exam_line in exam_lines:
            exam, language, format = exam_line
            exams.add(exam)
            languages.add(language)
            formats.add(format)

        user_exam_store = UserExamStore(exam_id, name, date, exams, languages, formats)
        user_exam_stores.append(user_exam_store)

    connection.close()
    return user_exam_stores



# Define the MainWindow class, inheriting from tk.Tk
class MainWindow(tk.Tk):
    def __init__(self):
        super().__init__()

        self.title("Main Window")
        self.geometry("450x400")

        tk.Label(self, text="Name:").grid(row=0, column=0, padx=10, pady=(10, 5), sticky='w')
        self.name_entry = tk.Entry(self)
        self.name_entry.grid(row=0, column=1, padx=10, pady=(10, 5), sticky='ew')

        open_button = tk.Button(self, text="Open Second Window", command=self.open_second_window)
        open_button.grid(row=1, column=0, padx=10, pady=5, sticky='ew')

        save_button = tk.Button(self, text="Save All Exams", command=self.save_all_exams)
        save_button.grid(row=1, column=1, padx=10, pady=5, sticky='ew')

        self.user_exam_stores_listbox = tk.Listbox(self)
        self.user_exam_stores_listbox.grid(row=2, column=0, columnspan=2, padx=10, pady=5, sticky='ew')
        self.user_exam_stores_listbox.bind("<<ListboxSelect>>", self.on_listbox_select)

        self.result_text = tk.Text(self, wrap=tk.WORD)
        self.result_text.grid(row=3, column=0, columnspan=2, padx=10, pady=5, sticky='nsew')

        # Configure the grid layout to adjust weights for row and columns
        self.grid_columnconfigure(0, weight=1)
        self.grid_columnconfigure(1, weight=1)
        self.grid_rowconfigure(2, weight=1)
        self.grid_rowconfigure(3, weight=3)

        self.user_exam_stores = load_user_exam_stores()
        print(f"Loading user_exam_stores: {self.user_exam_stores}")

        self.load_user_exam_stores_to_listbox()


    def load_user_exam_stores_to_listbox(self):
        for user_exam_store in self.user_exam_stores:
            self.update_listbox(user_exam_store)

    def open_second_window(self):
        user_name = self.name_entry.get()
        user_exam_store = UserExamStore(self.get_next_id(), user_name, "2023-05-04")
        second_window = SecondWindow(self, user_exam_store)
        self.wait_window(second_window)

    # Method to handle the "Save All Exams" button click event
    def save_all_exams(self):
        connection = sqlite3.connect("exams.db")
        cursor = connection.cursor()

        for user_exam_store in self.user_exam_stores:
            cursor.execute("""
                INSERT OR REPLACE INTO ExamHeader (ID, Name, Date)
                VALUES (?, ?, ?)
            """, (user_exam_store.id, user_exam_store.name, user_exam_store.date))

            for exam in user_exam_store.exams:
                for language in user_exam_store.languages:
                    for format in user_exam_store.formats:
                        cursor.execute("""
                            INSERT INTO ExamLines (ExamID, Exam, Language, Format)
                            VALUES (?, ?, ?, ?)
                        """, (user_exam_store.id, exam, language, format))

        connection.commit()
        connection.close()
        print("All exams saved to the database.")

    # Method to add a new UserExamStore instance to the list and update the listbox
    def update_listbox(self, user_exam_store):
        for index, existing_exam_store in enumerate(self.user_exam_stores):
            if existing_exam_store.id == user_exam_store.id:
                self.user_exam_stores[index] = user_exam_store
                self.user_exam_stores_listbox.delete(index)
                self.user_exam_stores_listbox.insert(index, f"{user_exam_store.id}: {user_exam_store.name}")
                break
        else:
            self.user_exam_stores.append(user_exam_store)
            self.user_exam_stores_listbox.insert(tk.END, f"{user_exam_store.id}: {user_exam_store.name}")

    # Method to handle the listbox's selection event
    def on_listbox_select(self, event):
        selection = self.user_exam_stores_listbox.curselection()
        if selection:
            index = selection[0]
            user_exam_store = self.user_exam_stores[index]
            self.update_text_widget(user_exam_store)

    # Method to update the text widget with information about a UserExamStore instance
    def update_text_widget(self, user_exam_store):
        self.result_text.delete(1.0, tk.END)
        self.result_text.insert(tk.END, str(user_exam_store))

    # Method to calculate the next unique ID for a new UserExamStore instance
    def get_next_id(self):
        if not self.user_exam_stores:
            return 1
        else:
            highest_id = max(user_exam_store.id for user_exam_store in self.user_exam_stores)
            return highest_id + 1

# Define the SecondWindow class, inheriting from tk.Toplevel
class SecondWindow(tk.Toplevel):
    def __init__(self, parent, user_exam_store):
        super().__init__(parent)

        self.title("Second Window")
        self.geometry("450x300")

    # Store the reference to the UserExamStore instance passed to this window
        self.user_exam_store = user_exam_store

        # Define the data for the listboxes
        exams = ["Exam A", "Exam B", "Exam C"]
        languages = ["English", "French", "Spanish"]
        formats = ["Compact", "Normal", "Extended"]

        # Create and populate the listboxes for exams, languages, and formats
        self.exams_listbox = tk.Listbox(self, selectmode=tk.MULTIPLE, exportselection=False)
        self.languages_listbox = tk.Listbox(self, selectmode=tk.MULTIPLE, exportselection=False)
        self.formats_listbox = tk.Listbox(self, selectmode=tk.MULTIPLE, exportselection=False)

        for exam in exams:
            self.exams_listbox.insert(tk.END, exam)
        for language in languages:
            self.languages_listbox.insert(tk.END, language)
        for format in formats:
            self.formats_listbox.insert(tk.END, format)

        # Create the submit button and set its command to the on_submit method
        submit_button = tk.Button(self, text="Submit", command=self.on_submit)

        # Place the listboxes and submit button using grid layout
        self.exams_listbox.grid(row=0, column=0, padx=10, pady=10)
        self.languages_listbox.grid(row=0, column=1, padx=10, pady=10)
        self.formats_listbox.grid(row=0, column=2, padx=10, pady=10)
        submit_button.grid(row=1, column=1, pady=10)

# Method to handle the submit button click event
    def on_submit(self):
        # Retrieve the selected options from each listbox and store them in the UserExamStore instance
        self.user_exam_store.exams = [self.exams_listbox.get(i) for i in self.exams_listbox.curselection()]
        self.user_exam_store.languages = [self.languages_listbox.get(i) for i in self.languages_listbox.curselection()]
        self.user_exam_store.formats = [self.formats_listbox.get(i) for i in self.formats_listbox.curselection()]

        # Update the listbox in the main window with the new UserExamStore instance
        if self.user_exam_store not in self.master.user_exam_stores:
            self.master.update_listbox(self.user_exam_store)

        # Close the second window
        self.destroy()

main_window = MainWindow()
main_window.mainloop()

This version opens a main form where you can enter a name, open a second form where you select from 3 list boxes then submit.
On saving these are added to a list in the main form.
Then you can hit save all to save to a sqllite database.
If you have python 3.8+ (my guess on the version), sqlite is built in!
So this code will make your database and tables for you if they don't exist already.

On running again you will find your old data exists and can be viewed by selecting the appropriate item.

Note you can use DB Browser for sql lite in windows to look at your database too.