[Tkinter] data enterred through gui is not storing in sqlite3 database

[menator01]

Have everything working except for the edit buttons. May finish later. For now I think I will try to do a pyqt5 version and clean the code up.
I have some repetitive code that should be eliminated.

#! /usr/bin/env python3

import tkinter as tk
from tkinter import ttk
from tkinter import messagebox
import sqlite3
from datetime import datetime
from functools import partial
from os import sys

# Class for all database functions - add, delete, and edit records
class Database:
    # Define a class global
    DBFILE = 'question_bank'
    def __init__(self):
        '''
        Create and connect to the database file
        Get the cursor object
        Check the database for the tables
        If the tables do not exists, create them
        '''
        conn = sqlite3.connect(Database.DBFILE)
        cursor = conn.cursor()
        cursor.execute(''' select count(name) from sqlite_master where type='table' \
        and name='question_bank' ''')

        if cursor.fetchone()[0] == 1:
            conn.close()
            pass
        else:
            cursor.execute('''create table if not exists questions
            (id integer primary key autoincrement,
            module_id integer not null,
            question varchar(255) not null)''')
            conn.commit()

            cursor.execute('''create table if not exists modules
            (id integer primary key autoincrement,
            name varchar(100) not null unique,
            date_created timestamp)''')
            conn.commit()
            conn.close()

    # Method for adding entries to the tables
    def add(self, table=None, id=None, arg=None):
        # Get the connection
        conn = sqlite3.connect(Database.DBFILE)
        # Check which table we want to enter data
        if table == 'modules':
            try:
                conn.execute('insert into modules (name, date_created) values (?, ?)', (arg, datetime.now()))
                conn.commit()
                conn.close()
            except sqlite3.IntegrityError as error:
                messagebox.showwarning('Error', f'Error: {arg.title()} already exists in the database.')
                pass
        else:
            try:
                conn.execute('insert into questions (module_id, question) values (?, ?)', (id, arg))
                conn.commit()
                conn.close()
            except sqlite3.IntegrityError as error:
                return f'Error: That question already exists.'

    # Method for deleting records
    def delete(self, table=None, module=None, question=None):
        conn = sqlite3.connect(Database.DBFILE)
        if table == 'modules':
            cursor = conn.cursor()
            cursor.execute('select id from modules where name = ?', (module,))
            module_id = cursor.fetchone()[0]
            conn.execute('delete from questions where module_id = ?', (module_id,))
            conn.execute('delete from modules where name = ?', (module,))
            conn.commit()
            conn.close()
        else:
            conn.execute('delete from questions where question = ?', (question,))
            conn.commit()
            conn.close()

    # Method for editing records
    def edit(self):
        messagebox.showinfo('Not Done', 'This method has not been programmed yet.')
        pass

    def get_id(self, module):
        conn = sqlite3.connect(Database.DBFILE)
        cursor = conn.cursor()
        cursor.execute('select id from modules where name = ?', (module,))
        return cursor.fetchone()

    # Method for getting data from tables to view
    def view(self, table, *args, **kwargs):
        conn = sqlite3.connect(Database.DBFILE)
        cursor = conn.cursor()
        if table == 'modules':
            cursor.execute(f'select * from {table}')
        else:
            try:
                id = kwargs['id']
                cursor.execute(f'select * from {table} where module_id = ?', (id,))
            except ValueError as error:
                print(error)
        rows = cursor.fetchall()
        return rows

# Class for adding questions
class AddQuestion:
    def __init__(self, window, task, get_list):
        self.db = Database()
        self.window = window
        self.get_list = get_list
        self.task = task
        window.iconify()

        # Create toplevel window
        self.top = tk.Toplevel(None)
        self.top.title('Add, Edit, and Delete Questions')
        self.top.geometry('600x350+250+250')
        self.top.resizable(False, False)
        self.top['padx'] = 8
        self.top['pady'] = 5
        self.top.columnconfigure(0, weight=1)
        self.top.rowconfigure(0, weight=1)


        container = tk.Frame(self.top)
        container['borderwidth'] = 1
        container['highlightthickness'] = 1
        container['highlightcolor'] = 'black'
        container['highlightbackground'] = 'black'
        container.grid(column=0, row=0, sticky='news')
        container.grid_columnconfigure(0, weight=0)
        container.grid_columnconfigure(1, weight=3)

        label = tk.Label(container, anchor='w', padx=3)
        label['font'] = ('sans', 10, 'bold underline')
        label['text'] = 'Choose Module'
        label.grid(column=0, row=0, sticky='new', pady=8, padx=3)

        label = tk.Label(container, anchor='w', padx=3)
        label['font'] = ('sans', 10, 'bold underline')
        label['text'] = 'Type in your question'
        label.grid(column=1, row=0, sticky='new', pady=8, padx=3)

        values = []
        for item in self.db.view('modules'):
            values.append(item[1].title())

        string = tk.StringVar()

        self.combobox = ttk.Combobox(container)
        self.combobox['values'] = values
        self.combobox['state'] = 'readonly'
        self.combobox['cursor'] = 'hand2'
        self.combobox.grid(column=0, row=1, sticky='new', padx=3)
        self.combobox.bind('<<ComboboxSelected>>', partial(self.get_index, self.combobox))
        self.combobox.current(0)

        self.entry = ttk.Entry(container)
        self.entry['textvariable'] = string
        self.entry.grid(column=1, row=1, sticky='new', padx=3)

        label = tk.Label(container)
        label['text'] = 'Question List'
        label['font'] = ('sans', 12, 'bold')
        label.grid(column=0, row=2, columnspan=2, sticky='new', padx=3, pady=5)

        frame = tk.Frame(container)
        frame['bg'] = 'white'
        frame['borderwidth'] = 1
        frame['highlightcolor'] = 'black'
        frame['highlightbackground'] = 'black'
        frame['highlightthickness'] = 1
        frame.grid(column=0, row=3, columnspan=2, sticky='news', padx=3)
        frame.grid_columnconfigure(0, weight=3)

        self.listbox = tk.Listbox(frame)
        self.listbox['borderwidth'] = 0
        self.listbox['highlightcolor'] = 'white'
        self.listbox['highlightbackground'] = 'white'
        self.listbox['highlightthickness'] = 1
        self.listbox.grid(column=0, columnspan=2, row=2, sticky='news', padx=5, pady=5)


        label = tk.Label(container)
        label['text'] = 'Tip: Use mouse wheel to scroll list'
        label['bg'] = 'lightyellow'
        label['font'] = ('sans', 8, 'italic')
        label['relief'] = 'groove'
        label.grid(column=0, row=4, columnspan=2, sticky='new', padx=3, pady=5)

        btn_container = tk.Frame(container)
        btn_container.grid(column=0, row=5, columnspan=2, sticky='new', ipadx=3, pady=5)
        for i in range(4):
            btn_container.grid_columnconfigure(i, weight=3, uniform='buttons')

        buttons = {
        'Add': partial(self.add, string),
        'Edit': self.db.edit,
        'Delete': partial(self.delete),
        'Done': partial(self.done),
        }

        i = 0
        bgcolor = 'lightgray'
        fgcolor = 'black'
        for btn, command in buttons.items():
            if btn == 'Done':
                bgcolor = 'burlywood'
                fgcolor = 'brown'
            self.button = tk.Button(btn_container, text=btn, bg=bgcolor, fg=fgcolor)
            self.button['command'] = command
            self.button['cursor'] = 'hand2'
            self.button['font'] = ('sans', 10, 'normal')
            self.button.grid(column=i, row=0, sticky='new', padx=2)
            i += 1

            self.button.bind('<Enter>', partial(self.enter, self.button))
            self.button.bind('<Leave>', partial(self.leave, self.button))
        self.get_questions()

    def get_index(self, string, event):
        string = string.get().lower()
        id = self.db.get_id(string)
        id = id[0]
        self.get_questions(id=id)


    def delete(self):
        try:
            index = self.listbox.curselection()[0]
            question = self.listbox.get(index)
            self.db.delete(table='questions', question=question.lower())
            module = self.combobox.get()
            id = self.db.get_id(module.lower())
            self.get_questions(id=id[0])
        except:
            pass

    def get_questions(self, *args, id=1, **kwargs):
        self.listbox.delete(0, tk.END)
        for question in self.db.view('questions', id=id):
            self.listbox.insert(question[0], question[2])
        self.listbox.select_set(0)

    def enter(self, btn, event):
        if btn['text'].lower() == 'done':
            btn['bg'] = 'sandybrown'
            btn['fg'] = 'white'
            btn['font'] = ('sans', 10, 'bold')
        else:
            btn['bg'] = 'lightblue'
            btn['fg'] = 'mediumblue'
            btn['font'] = ('sans', 10, 'bold')

    def leave(self, btn, event):
        if btn['text'].lower() == 'done':
            btn['bg'] = 'burlywood'
            btn['fg'] = 'brown'
            btn['font'] = ('sans', 10, 'normal')
        else:
            btn['bg'] = 'lightgray'
            btn['fg'] = 'black'
            btn['font'] = ('sans', 10, 'normal')

    def add(self, string, *args, **kwargs):
        string = string.get().lower()
        module = self.combobox.get()
        id = self.db.get_id(module.lower())
        self.db.add(table=None, id=id[0], arg=string)
        self.entry.delete(0, tk.END)
        self.get_questions(id=id[0])

    def done(self):
        self.get_list(self.task)
        self.task.set('option1')
        self.window.deiconify()
        self.top.destroy()

# Class for setting up the add module window
class AddModule:
    def __init__(self, window, listbox, get_list, task):
        # Initiate the Database class, set some varibles from the main window
        # iconify the main window
        self.db = Database()
        self.window = window
        self.listbox = listbox
        self.get_list = get_list
        self.task = task
        window.iconify()

        # Create a toplevel window and form
        self.top = tk.Toplevel()
        self.top.title('Add a module')
        self.top.columnconfigure(0, weight=1)
        self.top.rowconfigure(0, weight=1)
        self.top['padx'] = 5
        self.top['pady'] = 5
        self.top.geometry('400x100+250+250')
        self.top.resizable(False, False)

        self.container = tk.LabelFrame(self.top)
        self.container['text'] = 'Add a new module to the database'
        self.container.grid(column=0, row=0, sticky='news')
        self.container.grid_columnconfigure(1, weight=3)

        label = tk.Label(self.container)
        label['text'] = 'Module Name: '
        label.grid(column=0, row=0, sticky='new', padx=5, pady=8)

        module = tk.StringVar()
        style = ttk.Style()
        style.configure('TEntry', padding='5 1 5 1')
        self.entry = ttk.Entry(self.container, style='TEntry')
        self.entry['textvariable'] = module
        self.entry.focus_force()
        self.entry.grid(column=1, row=0, sticky='new', padx=5, pady=8)

        self.button = tk.Button(self.container, text='Submit')
        self.button['command'] = partial(self.add_module, self.entry)
        self.button['cursor'] = 'hand2'
        self.button['bg'] = 'lightgray'
        self.button.grid(column=1, row=1, columnspan=2, sticky='e', padx=5)
        self.entry.bind('<Return>', partial(self.add_module, self.entry))
        self.button.bind('<Enter>', self.enter)
        self.button.bind('<Leave>', self.leave)

    def enter(self, event):
        self.button['bg'] = 'lightblue'

    def leave(self, event):
        self.button['bg'] = 'lightgray'

    # Method for adding modules to the database, deiconify the main window and
    # destroy the top window
    def add_module(self, module, event=None):
        module = module.get()
        if module:
            self.db.add(table='modules', arg=module.lower())
            self.entry.delete(0, tk.END)
            self.listbox.delete(0, tk.END)
            self.get_list(self.task)
            self.window.deiconify()
            self.top.destroy()

        else:
            messagebox.showwarning('No Module Entered', 'Please type in a module name to add')

class Window:
    def __init__(self, parent):
        self.parent = parent
        self.parent.columnconfigure(0, weight=1)
        self.parent.rowconfigure(0, weight=1)

        # Initiate the Database class
        self.db = Database()

        self.task = tk.StringVar()
        self.task.set('option1')

        # Create a container for widgets
        self.container = tk.Frame(self.parent, padx=4, pady=2)
        self.container['borderwidth'] = 1
        self.container['highlightcolor'] = 'black'
        self.container['highlightbackground'] = 'black'
        self.container['highlightthickness'] = 1
        self.container.grid(column=0, row=0, sticky='new', pady=8, padx=8)
        self.container.grid_columnconfigure(0, weight=3)

        # Create the header
        header = tk.Label(self.container, text='Administration Tools')
        header['font'] = ('verdana', 16, 'bold')
        header['bg'] = 'lightgray'
        header.grid(column=0, row=0, sticky='new')

        # What operations do we want
        self.option = tk.LabelFrame(self.container, text='Choose a task')
        self.option.grid(column=0, row=1, sticky='new')
        self.option.grid_columnconfigure(0, weight=3, uniform='options')
        self.option.grid_columnconfigure(1, weight=3, uniform='options')

        self.option1 = tk.Radiobutton(self.option, text='Modules')
        self.option1['variable'] = self.task
        self.option1['value'] = 'option1'
        self.option1['command'] = partial(self.get_list, self.task)
        self.option1.grid(column=0, row=0, sticky='new')

        self.option2 = tk.Radiobutton(self.option, text='Questions')
        self.option2['variable'] = self.task
        self.option2['value'] = 'option2'
        self.option2['command'] = partial(self.add_window)
        self.option2.grid(column=1, row=0, sticky='new')

        # Create a listbox
        frame = tk.Frame(self.container, bg='white')
        frame['borderwidth'] = 1
        frame['highlightcolor'] = 'black'
        frame['highlightbackground'] = 'black'
        frame['highlightthickness'] = 1
        frame.grid(column=0, row=2, sticky='new', ipadx=5, ipady=5, pady=8)
        frame.grid_columnconfigure(0, weight=3)

        self.listbox = tk.Listbox(frame)
        self.listbox['borderwidth'] = 0
        self.listbox['highlightbackground'] = 'white'
        self.listbox['highlightcolor'] = 'white'
        self.listbox['selectmode'] = tk.SINGLE
        self.listbox.grid(column=0, row=0, sticky='news', padx=5, pady=5)

        # Populate the listbox on opening the window
        self.get_list(self.task)

    # Method to check if any modules exist. If so go to the questions window.
    # Otherwise stays on the modules window
    def add_window(self):
        if self.listbox.curselection():
            AddQuestion(self.parent, self.task, self.get_list)
        else:
            messagebox.showwarning('No Modules', 'You have to add at least one module to add questions.')
            self.task.set('option1')

    # Method for editing entries
    def edit(self, table, task):
        messagebox.showinfo('Edit', 'This method has not been programmed yet.')

    def delete(self, table, task):
        try:
            index = self.listbox.curselection()[0]
            confirm = messagebox.askokcancel('Delete Module', f'You are about to delete this module ( {self.listbox.get(index)} ).')
            if confirm:
                self.db.delete('modules', self.listbox.get(index).lower())
            else:
                pass
        except:
            pass

        # Updates the listbox
        self.get_list(task)

    # Method for changing button color on entring
    def enter(self, btn, event):
        if btn['text'].lower() == 'exit':
            btn['bg'] = 'red'
            btn['fg'] = 'white'
            btn['font'] = ('sans', 10, 'bold')
        else:
            btn['bg'] = 'lightblue'
            btn['fg'] = 'mediumblue'
            btn['font'] = ('sans', 10, 'bold')

    # Method for restoring button colors when exiting
    def leave(self, btn, event):
        if btn['text'].lower() == 'exit':
            btn['bg'] = 'tomato'
            btn['fg'] = 'black'
            btn['font'] = ('sans', 10, 'normal')
        else:
            btn['bg'] = 'lightgray'
            btn['fg'] = 'black'
            btn['font'] = ('sans', 10, 'normal')

    # Method for populating the listboxes
    # If option1 we want the modules else the questions
    def get_list(self, task):
        self.listbox.delete(0, tk.END)

        for item in self.db.view('modules'):
            self.listbox.insert(item[0], item[1].title())
        self.listbox.select_set(0)

        buttons = {
        'Add':partial(AddModule, self.parent, self.listbox, self.get_list, self.task),
        'Edit':partial(self.edit, 'modules', self.task),
        'Delete':partial(self.delete, 'modules', self.task),
        'Exit':sys.exit
        }

        # Create a container for the buttons
        btn_frame = tk.Frame(self.container)
        btn_frame.grid(column=0, row=3, sticky='new', pady=10)
        for btn in range(4):
            btn_frame.columnconfigure(btn, weight=3, uniform='buttons')

        # Control buttons and commands
        i = 0
        color = 'lightgray'
        for btn, command in buttons.items():
            if btn == 'Exit':
                color = 'tomato'
            self.button = tk.Button(btn_frame, bg=color)
            self.button['cursor'] = 'hand2'
            self.button['text'] = btn
            self.button['font'] = ('sans', 10, 'normal')
            self.button['command'] = command
            self.button.grid(column=i, row=0, sticky='new', padx=1)
            i += 1

            self.button.bind('<Enter>', partial(self.enter, self.button))
            self.button.bind('<Leave>', partial(self.leave, self.button))

def main():
    root = tk.Tk()
    root.title('Administration Tools')
    root.geometry('800x350+250+250')
    root['padx'] = 3
    root['pady'] = 5
    root.resizable(False, False)
    Window(root)
    root.mainloop()

main()