Python Forum

Yoriz Wrote:Split from https://python-forum.io/thread-27861-post-118832.html into its own thread

Hello. Can anyone help me too? .delete and .insert is not working on my program. here's my code:

# import libraries
import dataclasses
from tkinter import *
import tkinter.messagebox as tkMessageBox
import sqlite3
from tkinter import ttk


# function to define database
def Database():
    global conn, cursor
    # creating student database
    conn = sqlite3.connect("qsuclinicrecord.db")
    cursor = conn.cursor()
    # creating STUD_REGISTRATION table
    cursor.execute(
        "CREATE TABLE IF NOT EXISTS STUDENT_REGISTRATION (STU_ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "
        "ID TEXT, STU_FIRSTNAME TEXT, STU_MIDDLENAME TEXT, STU_LASTNAME TEXT, STU_BIRTHDAY TEXT, STU_AGE INTEGER, "
        "STU_GENDER TEXT, STU_ADDRESS TEXT, STU_WEIGHT INTEGER, STU_HEIGHT NUMBER, STU_BLOODTYPE TEXT, "
        "STU_DISABILITIES TEXT)")


# defining function for creating GUI Layout
def DisplayForm():
    # creating window
    display_screen = Tk()
    # setting width and height for window
    display_screen.geometry("1024x700")
    # setting title for window
    display_screen.title("QSU-Diffun Clinic Record System")
    global tree
    global SEARCH
    global student_id, fname, mname, lname, bday, ag_e, gen_der, add_ress, wg, hg, btype, dis
    SEARCH = StringVar()
    student_id = StringVar()
    fname = StringVar()
    mname = StringVar()
    lname = StringVar()
    bday = StringVar()
    ag_e = StringVar()
    gen_der = StringVar()
    add_ress = StringVar()
    wg = StringVar()
    hg = StringVar()
    btype = StringVar()
    dis = StringVar()

    # creating frames for layout
    # topview frame for heading
    TopViewForm = Frame(display_screen, width=600, bd=1, relief=SOLID)
    TopViewForm.pack(side=TOP, fill=X)
    # first left frame for registration from
    LFrom = Frame(display_screen, width="350")
    LFrom.pack(side=LEFT, fill=Y)
    # seconf left frame for search form
    LeftViewForm = Frame(display_screen, width=500, bg="gray")
    LeftViewForm.pack(side=LEFT, fill=Y)
    # mid frame for displaying students record
    MidViewForm = Frame(display_screen, width=600)
    MidViewForm.pack(side=RIGHT)
    # label for heading
    lbl_text = Label(TopViewForm, text="QSU-Diffun Clinic Record System", font=('verdana', 20, 'bold'), width=600,
                     bg="green",
                     fg="white", height="3")
    lbl_text.pack(fill=X)
    # creating registration form in first left frame
    Label(LFrom, text="Student ID No.  ", font=("Arial", 12)).pack(side=TOP)
    Entry(LFrom, font=("Arial", 10, "bold"), textvariable=student_id).pack(side=TOP, padx=10, fill=X)
    Label(LFrom, text="First Name ", font=("Arial", 12)).pack(side=TOP)
    Entry(LFrom, font=("Arial", 10, "bold"), textvariable=fname).pack(side=TOP, padx=10, fill=X)
    Label(LFrom, text="Middle Name ", font=("Arial", 12)).pack(side=TOP)
    Entry(LFrom, font=("Arial", 10, "bold"), textvariable=mname).pack(side=TOP, padx=10, fill=X)
    Label(LFrom, text="Last Name ", font=("Arial", 12)).pack(side=TOP)
    Entry(LFrom, font=("Arial", 10, "bold"), textvariable=lname).pack(side=TOP, padx=10, fill=X)
    Label(LFrom, text="Birthday ", font=("Arial", 12)).pack(side=TOP)
    Entry(LFrom, font=("Arial", 10, "bold"), textvariable=bday).pack(side=TOP, padx=10, fill=X)
    Label(LFrom, text="Age ", font=("Arial", 12)).pack(side=TOP)
    Entry(LFrom, font=("Arial", 10, "bold"), textvariable=ag_e).pack(side=TOP, padx=10, fill=X)
    Label(LFrom, text="Gender ", font=("Arial", 12)).pack(side=TOP)
    Entry(LFrom, font=("Arial", 10, "bold"), textvariable=gen_der).pack(side=TOP, padx=10, fill=X)
    Label(LFrom, text="Address ", font=("Arial", 12)).pack(side=TOP)
    Entry(LFrom, font=("Arial", 10, "bold"), textvariable=add_ress).pack(side=TOP, padx=10, fill=X)
    Label(LFrom, text="Weight ", font=("Arial", 12)).pack(side=TOP)
    Entry(LFrom, font=("Arial", 10, "bold"), textvariable=wg).pack(side=TOP, padx=10, fill=X)
    Label(LFrom, text="Height ", font=("Arial", 12)).pack(side=TOP)
    Entry(LFrom, font=("Arial", 10, "bold"), textvariable=hg).pack(side=TOP, padx=10, fill=X)
    Label(LFrom, text="Bloodtype ", font=("Arial", 12)).pack(side=TOP)
    Entry(LFrom, font=("Arial", 10, "bold"), textvariable=btype).pack(side=TOP, padx=10, fill=X)
    Label(LFrom, text="Disabilities or other Health Issues ", font=("Arial", 12)).pack(side=TOP)
    Entry(LFrom, font=("Arial", 10, "bold"), textvariable=dis).pack(side=TOP, padx=10, fill=X)
    Button(LFrom, text="Add New Record", font=("Arial", 10, "bold"), command=register).pack(side=TOP, padx=10, pady=5,
                                                                                            fill=X)
    # creating search label and entry in second frame
    lbl_txtsearch = Label(LeftViewForm, text="Student ID No.", font=('verdana', 10), bg="gray")
    lbl_txtsearch.pack()
    # creating search entry
    search = Entry(LeftViewForm, textvariable=SEARCH, font=('verdana', 15), width=10)
    search.pack(side=TOP, padx=10, fill=X)
    # creating search button
    btn_search = Button(LeftViewForm, text="Search", command=SearchRecord)
    btn_search.pack(side=TOP, padx=10, pady=10, fill=X)
    # creating view button
    btn_view = Button(LeftViewForm, text="View All Records", command=DisplayData)
    btn_view.pack(side=TOP, padx=10, pady=10, fill=X)
    # creating reset button
    btn_reset = Button(LeftViewForm, text="Reset", command=Reset)
    btn_reset.pack(side=TOP, padx=10, pady=10, fill=X)
    # creating reset button
    btn_update = Button(LeftViewForm, text="Update", command=Reset)
    btn_update.pack(side=TOP, padx=10, pady=10, fill=X)
    # creating delete button
    btn_delete = Button(LeftViewForm, text="Delete", command=Delete)
    btn_delete.pack(side=TOP, padx=10, pady=10, fill=X)
    # setting scrollbar
    scrollbarx = Scrollbar(MidViewForm, orient=HORIZONTAL)
    scrollbary = Scrollbar(MidViewForm, orient=VERTICAL)
    tree = ttk.Treeview(MidViewForm, columns=(
        "No", "Student ID No", "First Name", "Middle Name", "Last Name", "Birthday", "Age", "Gender", "Address",
        "Weight", "Height", "Bloodtype", "Disabilities or other Health Issues"),
                        selectmode="extended", height=100, yscrollcommand=scrollbary.set, xscrollcommand=scrollbarx.set)
    scrollbary.config(command=tree.yview)
    scrollbary.pack(side=RIGHT, fill=Y)
    scrollbarx.config(command=tree.xview)
    scrollbarx.pack(side=BOTTOM, fill=X)
    # setting headings for the columns
    tree.heading('No', text="No.", anchor=W)
    tree.heading('Student ID No', text="Student ID No.", anchor=W)
    tree.heading('First Name', text="First Name", anchor=W)
    tree.heading('Middle Name', text="Middle Name", anchor=W)
    tree.heading('Last Name', text="Last Name", anchor=W)
    tree.heading('Birthday', text="Birthday", anchor=W)
    tree.heading('Age', text="Age", anchor=W)
    tree.heading('Gender', text="Gender", anchor=W)
    tree.heading('Address', text="Address", anchor=W)
    tree.heading('Weight', text="Weight", anchor=W)
    tree.heading('Height', text="Height", anchor=W)
    tree.heading('Bloodtype', text="Bloodtype", anchor=W)
    tree.heading('Disabilities or other Health Issues', text="Disabilities or other Health Issues", anchor=W)
    # setting width of the columns
    tree.column('#0', stretch=NO, minwidth=0, width=0)
    tree.column('#1', stretch=NO, minwidth=0, width=80)
    tree.column('#2', stretch=NO, minwidth=0, width=150)
    tree.column('#3', stretch=NO, minwidth=0, width=150)
    tree.column('#4', stretch=NO, minwidth=0, width=120)
    tree.column('#5', stretch=NO, minwidth=0, width=120)
    tree.column('#6', stretch=NO, minwidth=0, width=120)
    tree.column('#7', stretch=NO, minwidth=0, width=120)
    tree.column('#8', stretch=NO, minwidth=0, width=120)
    tree.column('#9', stretch=NO, minwidth=0, width=120)
    tree.column('#10', stretch=NO, minwidth=0, width=120)
    tree.column('#11', stretch=NO, minwidth=0, width=120)
    tree.column('#12', stretch=NO, minwidth=0, width=120)
    tree.pack()
    DisplayData()


# function to insert data into database
def register():
    Database()
    # getting form data
    stuid = student_id.get()
    firstname = fname.get()
    middlename = mname.get()
    lastname = lname.get()
    birthday = bday.get()
    age = ag_e.get()
    gender = gen_der.get()
    address = add_ress.get()
    weight = wg.get()
    height = hg.get()
    bloodtype = btype.get()
    disabilities = dis.get()

    # applying empty validation
    if stuid == '' or firstname == '' or middlename == '' or lastname == '' or birthday == '' or age == '' or gender == '' or address == '' or weight == '' or height == '' or bloodtype == '':
        tkMessageBox.showinfo("Warning", "fill the empty field!!!")
    else:
        # execute query
        conn.execute('INSERT INTO STUDENT_REGISTRATION (ID, STU_FIRSTNAME, STU_MIDDLENAME, STU_LASTNAME, STU_BIRTHDAY, STU_AGE, STU_GENDER, STU_ADDRESS, STU_WEIGHT, STU_HEIGHT, STU_BLOODTYPE, STU_DISABILITIES) \
              VALUES (?,?,?,?,?,?,?,?,?,?,?,?)', (
            stuid, firstname, middlename, lastname, birthday, age, gender, address, weight, height, bloodtype,
            disabilities));
        conn.commit()
        tkMessageBox.showinfo("Message", "Recorded successfully")
        # refresh table data
        DisplayData()
        conn.close()


def Reset():
    # clear current data from table
    tree.delete(*tree.get_children())
    # refresh table data
    DisplayData()
    # clear search text
    SEARCH.set("")
    student_id.set("")
    fname.set("")
    mname.set("")
    lname.set("")
    bday.set("")
    ag_e.set("")
    gen_der.set("")
    add_ress.set("")
    wg.set("")
    hg.set("")
    btype.set("")
    dis.set("")


# Select Record
def select_record():
    student_id.delete(0, END)
    fname.delete(0, END)
    mname.delete(0, END)
    lname.delete(0, END)
    bday.delete(0, END)
    ag_e.delete(0, END)
    gen_der.delete(0, END)
    add_ress.delete(0, END)
    wg.delete(0, END)
    hg.delete(0, END)
    btype.delete(0, END)
    dis.delete(0, END)

    selected = tree.focus()
    values = tree.item(selected, 'values')

    student_id.insert(0, values[1])
    fname.insert(0, values[2])
    mname.insert(0, values[3])
    lname.insert(0, values[4])
    bday.insert(0, values[5])
    ag_e.insert(0, values[6])
    gen_der.insert(0, values[7])
    add_ress.insert(0, values[8])
    wg.insert(0, values[9])
    hg.insert(0, values[10])
    btype.insert(0, values[11])
    dis.insert(0, values[12])


# Update record
def update_record():
    # Grab the record number
    selected = tree.focus()
    # Update record
    tree.item(selected, text="",
              values=(student_id.get(), fname.get(), mname.get(), lname.get(), bday.get(), ag_e.get(),
                      gen_der.get(), add_ress.get(), wg.get(), hg.get(), btype.get(), dis.get(),))

    # Update the database
    # Create a database or connect to one that exists
    conn = sqlite3.connect('qsuclinicrecord.db')

    # Create a cursor instance
    c = conn.cursor()

    c.execute('UPDATE STUDENT_REGISTRATION (ID, STU_FIRSTNAME, STU_MIDDLENAME, STU_LASTNAME, STU_BIRTHDAY, STU_AGE, STU_GENDER, STU_ADDRESS, STU_WEIGHT, STU_HEIGHT, STU_BLOODTYPE, STU_DISABILITIES) \
              VALUES (?,?,?,?,?,?,?,?,?,?,?,?)', (student_id, fname, mname, lname, bday, ag_e, gen_der, add_ress, wg, hg, btype, dis))

    # Commit changes
    conn.commit()

    # Close our connection
    conn.close()


def Delete():
    # open database
    Database()
    if not tree.selection():
        tkMessageBox.showwarning("Warning", "Select data to delete.")
    else:
        result = tkMessageBox.askquestion('Confirm', 'Are you sure you want to delete this record?',
                                          icon="warning")
        if result == 'yes':
            curItem = tree.focus()
            contents = (tree.item(curItem))
            selecteditem = contents['values']
            tree.delete(curItem)
            cursor = conn.execute("DELETE FROM STUDENT_REGISTRATION WHERE STU_ID  = %d" % selecteditem[0])
            conn.commit()
            cursor.close()
            conn.close()


# function to search data
def SearchRecord():
    # open database
    Database()
    # checking search text is empty or not
    if SEARCH.get() != "":
        # clearing current display data
        tree.delete(*tree.get_children())
        # select query with where clause
        cursor = conn.execute("SELECT * FROM STUDENT_REGISTRATION WHERE ID LIKE ?", ('%' + str(SEARCH.get()) + '%',))
        # fetch all matching records
        fetch = cursor.fetchall()
        # loop for displaying all records into GUI
        for data in fetch:
            tree.insert('', 'end', values=(data))
        cursor.close()
        conn.close()


# defining function to access data from SQLite database
def DisplayData():
    # open database
    Database()
    # clear current data
    tree.delete(*tree.get_children())
    # select query
    cursor = conn.execute("SELECT * FROM STUDENT_REGISTRATION")
    # fetch all data from database
    fetch = cursor.fetchall()
    # loop for displaying all data in GUI
    for data in fetch:
        tree.insert('', 'end', values=(data))
    cursor.close()
    conn.close()


# calling function
DisplayForm()
if __name__ == '__main__':
    # Running Application
    mainloop()

(Jul-15-2022, 12:20 PM)Yoriz Wrote: [ -> ]Please post all code, output and errors (in their entirety) between their respective tags.

With all due respect, I don't think that this is good advice as it's very likely that a major part of the 300+ lines of code in this thread, are absolutely fine, and it's the way in which the Database access has been coded that's the issue here.

Would it not be better to ask that the SSCCE guidelines be used? That way, the author of the thread could put together a simple working example of what the issue is, which would (as likely as not) be a tenth of the 300+ lines of code, or even less.

see: http://sscce.org/

I got interested in your project and took a shot at it.
Only the add record button and view all button work and search student no work.
I have not worked with any others.
All records will be shown (if any are in database) when the window opens or when a new record is added.
There is not any validation other than checking for empty fields.

---

Updated the code a little. Can search by id, firstname, or lastname. Kind of went green crazy too.

import tkinter as tk
from tkinter import messagebox, ttk
import sqlite3 as sq
from functools import partial

class Database:
    def __init__(self):
        self.connect = sq.connect('qsuclinicrecord.db')
        self.cursor = self.connect.cursor()

        self.connect.execute('''
                                create table if not exists registration (
                                id integer primary key autoincrement,
                                firstname varchar(100), middlename varchar(100), lastname varchar(100),
                                birthdate text, age integer, gender text, address text, weight integer,
                                height text, bloodtype varchar(25), disabilities text
                                );
                             ''')

    def getall(self):
        self.cursor.execute('''select * from registration''')
        result = self.cursor.fetchall()
        if result:
            return result
        return False

    def search(self, term):
        self.cursor.execute('''
                                select * from registration where id=? or firstname=? or lastname=?;
                            ''', (term, term.lower(), term.lower()))
        result = self.cursor.fetchall()
        if result:
            return result
        return False

    def insert(self, data):
        self.connect.execute('''
            insert into registration (firstname, middlename,
            lastname, birthdate, age, gender, address, weight, height, bloodtype, disabilities)
            values (?,?,?,?,?,?,?,?,?,?,?)
        ''', data)
        self.connect.commit()

    def update(self, data):
        pass

    def delete(self, id):
        pass


class Window:
    def __init__(self, parent):

        '''
            Various colors for styling
        '''
        search_color = 'mediumseagreen'
        button_highlight_color = '#50c878'
        button_active_color = 'mediumseagreen'
        search_label_color = '#e9ffdb'
        frame_color = '#ace1af'
        frame_label_color = '#ace1af'
        window_background_color = '#355e3b'
        main_container_color = '#ace1af'
        treefield_color = 'beige'
        field_row_color_even = 'beige'
        field_row_color_odd = '#ace1af'

        ''' Set some class variables
            self.parent.columnconfigure and self.parent.rowconfigure help when the window is expanded
            self.parent update is needed to get the screen size
        '''
        self.parent = parent
        self.parent['bg'] = window_background_color
        self.parent.columnconfigure(0, weight=1)
        self.parent.rowconfigure(0, weight=1)
        self.parent.update()

        '''
            Get the screen width and height. The window will be sized based on user screen.
            Placement of the window will be screen size / 2 - window size / 2.
            This should place the window in the middle of the screen
        '''
        screen_size = (self.parent.winfo_screenwidth(), self.parent.winfo_screenheight())
        window_size = (int(screen_size[0]/2), int(screen_size[1]/2))
        window_placement = (int((screen_size[0]/2)-(window_size[0]/2)), int((screen_size[1]/2)-(window_size[1]/2)))

        '''
            Styling the window. Set the geometry of the window from the above variables
        '''
        self.parent.title('QSU-Diffun Clinic Record System')
        self.parent.geometry(f'{window_size[0]}x550+{window_placement[0]}+{window_placement[1]}')

        '''
            Create four containers for the widgets.
            First will be the main container which will hold all other containers
            Second will left container for holding the form
            Third will be for the search field and various buttons
            Fourth will be for the treeview widget
        '''

        container = tk.Frame(self.parent)
        container['padx'] = 8
        container['pady'] = 8
        container['bg'] = main_container_color
        container['borderwidth'] = 1
        container['highlightthickness'] = 1
        container['highlightcolor'] = 'black'
        container['highlightbackground'] = 'black'
        container.grid(column=0, row=0, sticky='news', padx=8, pady=8)

        '''
            The widget container will be various sizes, set with columnconfigure.
            The left container will hold the for fields, setting it to a weight of 2
            The middle container will hold the search field which is searching by id,
            will not require alot of space so setting it to a weight of 1
            The right container will hold the treeview widget. Although there will be
            scrollbars, trying to have as much of the widget in view as possible.
            Giving it a weight of  3
        '''

        container.grid_columnconfigure(0, weight=0)
        container.grid_columnconfigure(1, weight=0)
        container.grid_columnconfigure(2, weight=3)

        '''
            Create and style the three sub containers. Using self on the left container
            as it will help in clearing the form fields on submission
        '''

        self.left_container = tk.Frame(container, bg=frame_color)
        self.left_container['borderwidth'] = 1
        self.left_container['highlightthickness'] = 1
        self.left_container['highlightcolor'] = 'black'
        self.left_container['highlightbackground'] = 'black'
        self.left_container.grid(column=0, row=1, sticky='news', padx=(0, 8), pady=8)
        for i in range(12):
            self.left_container.grid_columnconfigure(0, weight=1)
            self.left_container.grid_columnconfigure(1, weight=2)

        mid_container = tk.Frame(container, bg=frame_color)
        mid_container['borderwidth'] = 1
        mid_container['highlightthickness'] = 1
        mid_container['highlightcolor'] = 'black'
        mid_container['highlightbackground'] = 'black'
        mid_container.grid(column=1, row=1, sticky='news', padx=8, pady=8)
        mid_container.grid_columnconfigure(0, weight=3)

        search_container = tk.Frame(container)
        search_container['borderwidth'] = 1
        search_container['highlightthickness'] = 1
        search_container['highlightcolor'] = 'lightgray'
        search_container['highlightbackground'] = 'lightgray'
        search_container['bg'] = search_color

        search_container.grid(column=0, columnspan=3, row=3, sticky='news')
        search_container.grid_columnconfigure(0, weight=0)
        search_container.grid_columnconfigure(1, weight=3)
        search_container.grid_columnconfigure(2, weight=1)

        right_container = tk.Frame(container, width=600)
        right_container.grid(column=2, row=1, sticky='news', padx=(8, 0), pady=8)
        right_container.grid_columnconfigure(0, weight=3)
        right_container.grid_rowconfigure(0, weight=3)

        '''
            Create and style the window heading
        '''

        header = tk.Label(container, pady=8)
        header['text'] = 'QSU-Diffun Clinic Record System'
        header['font'] = ('arial', 20, 'bold')
        header['bg'] = 'mediumseagreen'
        header['fg'] = 'white'
        header['relief'] = 'raised'
        header.grid(column=0, columnspan=3, row=0, sticky='news', pady=(0,8))

        '''
            Create the labels and fields for the form.
            Create three list to save some typing
            One list to hold the label names and two empty list to hold
            field names and stringvariables
        '''
        labels = ['First Name', 'Middle Name', 'Last Name', 'Birthdate', 'Age', 'Gender',
                  'Address', 'Weight', 'Height', 'Bloodtype', 'Disabilities']
        label_headings = labels.copy()
        label_headings.insert(0, 'Student ID')

        fields = []
        self.vars = []

        '''
            Loop through the label list appending fields and stringvariables
            Style the labels and fields
        '''
        for i, label in enumerate(labels):
            fields.append(tk.Entry(self.left_container))
            self.vars.append(tk.StringVar())
            labels[i] = tk.Label(self.left_container, text=labels[i].title(), anchor='w')
            labels[i]['bg'] = frame_label_color
            labels[i]['fg'] = 'forestgreen'
            labels[i]['relief'] = 'ridge'
            labels[i]['font'] = ('tahoma', 12, 'bold')
            labels[i].grid(column=0, row=i, sticky='new', padx=(8, 3), pady=2)

            fields[i]['font'] = ('tahoma', 12, 'normal')
            fields[i]['textvariable'] = self.vars[i]
            fields[i]['bg'] = 'beige'
            fields[i].grid(column=1, row=i, sticky='new', padx=(3, 8), pady=2)

        '''
            Create a button for submitting entries.
            The command for the button will be set in the controller class.
            The button needs to be created in the format self.button
        '''

        self.button = tk.Button(self.left_container, text='Add New Record')
        self.button['cursor'] = 'hand2'
        self.button['bg'] = 'lightseagreen'
        self.button['fg'] = 'navy'
        self.button['highlightbackground'] = button_highlight_color
        self.button['highlightcolor'] = button_highlight_color
        self.button['activebackground'] = button_active_color
        self.button['activeforeground'] = 'white'
        self.button.grid(column=0, columnspan=2, row=len(labels)+1, sticky='news', padx=4, pady=8)

        '''
            Buttons for various functions
        '''

        self.buttons = ['View All Records', 'Reset', 'Update', 'Delete']
        for i, button in enumerate(self.buttons):
            self.buttons[i] = tk.Button(mid_container, text=button)
            self.buttons[i]['cursor'] = 'hand2'
            self.buttons[i]['font'] = ('arial', 11, 'normal')
            self.buttons[i]['bg'] = 'lightseagreen'
            self.buttons[i]['fg'] = 'navy'
            self.buttons[i]['highlightbackground'] = button_highlight_color
            self.buttons[i]['highlightcolor'] = button_highlight_color
            self.buttons[i]['activebackground'] = button_active_color
            self.buttons[i]['activeforeground'] = 'white'
            self.buttons[i].grid(column=0, row=i, sticky='news', padx=4, pady=8)

        '''
            Create and style the treeview and scrollbars
        '''
        style = ttk.Style()
        style.theme_use('alt')
        style.configure('Treeview', fieldbackground=treefield_color, padding=3)
        style.configure('Treeview.Heading', background='mediumseagreen', foreground='white',
                        font=('arial', 11, 'bold'))
        style.configure('TScrollbar', troughcolor='mediumseagreen', arrowcolor='lightseagreen',
                        background='forestgreen', arrowsize=20)
        style.map('TScrollbar',
                  background=[
                      ('disabled', 'seagreen'),
                      ('pressed', '#ace1af'),
                      ('active', '#ace1af')
                      ],
                  arrowcolor=[('active', 'green'), ('disabled', 'green')]
                  )

        self.tree = ttk.Treeview(right_container)
        self.tree['columns'] = label_headings
        self.tree['show'] = 'headings'
        [self.tree.heading(i, text=label) for i,label in enumerate(label_headings)]
        self.tree.grid(column=0, row=0, sticky='news')
        self.tree.tag_configure('oddrow', background=field_row_color_odd)
        self.tree.tag_configure('evenrow', background=field_row_color_even)

        # Create and configure scrollbars
        yscrollbar = ttk.Scrollbar(right_container, orient='vertical')
        yscrollbar.grid(column=1, row=0, sticky='ns')

        xscrollbar = ttk.Scrollbar(right_container, orient='horizontal')
        xscrollbar.grid(column=0, row=1, sticky='we')

        self.tree.configure(yscrollcommand=yscrollbar.set, xscrollcommand=xscrollbar.set)
        yscrollbar.configure(command=self.tree.yview, cursor='hand2')
        xscrollbar.configure(command=self.tree.xview, cursor='hand2')

        '''
            Create a label search field and button
        '''
        label = tk.Label(search_container)
        label['font'] = ('arial', 11, 'italic normal')
        label['text'] = 'Search by id, first name, or last name'
        label['fg'] = 'black'
        label['bg'] = search_label_color
        label.grid(column=0, columnspan=3, row=0, sticky='new', padx=4, pady=2)

        label = tk.Label(search_container, text='Search:', bg=search_color, fg='white')
        label['font'] = ('arial', 12, 'bold')
        label.grid(column=0, row=1, sticky='new', padx=(4, 2), pady=8)

        self.search_var = tk.StringVar()
        self.entry = tk.Entry(search_container)
        self.entry['font'] = ('arial', 12, 'normal')
        self.entry['bg'] = 'beige'
        self.entry['textvariable'] = self.search_var
        self.entry.grid(column=1, row=1, sticky='new', pady=8)

        self.search_button = tk.Button(search_container, text='Search', pady=2)
        self.search_button['cursor'] = 'hand2'
        self.search_button['bg'] = 'lightseagreen'
        self.search_button['fg'] = 'navy'
        self.search_button['font'] = ('sans serif', 10, 'normal')
        self.search_button['highlightbackground'] = button_highlight_color
        self.search_button['highlightcolor'] = button_highlight_color
        self.search_button['activebackground'] = button_active_color
        self.search_button['activeforeground'] = 'white'
        self.search_button.grid(column=2, row=1, sticky='new', padx=(10, 4), pady=8)


class Controller:
    def __init__(self, window, database):

        '''
            Define class variables
        '''
        self.window = window
        self.database = database

        '''
            Setup button commands
        '''
        self.window.button['command'] = partial(self.insert, self.window.vars)
        self.window.search_button['command'] = partial(self.search, self.window.search_var)

        ''' Populate the treeview'''
        self.getall()

    def search(self, search_term):
        term = search_term.get()
        if term:
            self.window.entry.delete(0, tk.END)
            data = self.database.search(term.lower())

            # Clear the treeview. Probably should make this a function as it is called several times.
            for item in self.window.tree.get_children():
                self.window.tree.delete(item)

            # Populate the treeview with retuned search results
            if data:
                for i, items in enumerate(data):
                    if i % 2 == 0:
                        self.window.tree.insert('', 'end', values=[item.title() if isinstance(item, str) \
                                                                   else item for item in items], tags=('evenrow',))
                    else:
                        self.window.tree.insert('', 'end', values=[item.title() if isinstance(item, str) \
                                                                   else item for item in items], tags=('oddrow',))
            else:
                messagebox.showinfo('No Results', f'Did not find any results for {term}')
        else:
            messagebox.showerror('Search Error!', 'You must enter a search term to search the database.', icon='error')

    '''
        Method for inserting data into the database.
        The method checks for empty fields and add 1 to the errors variable.
        If errors is greater than 0 a messagebox pops up.
        If no errors, then data is inserted into database and the treeview
        is cleared and repopulated. The entry fields in the left entry fields
        are cleared
    '''
    def insert(self, data):

        # Set some variables
        errors = 0
        vars = []

        # Loop through the data and convert to human readable format
        [vars.append(val.get().lower()) for val in data]

        # Check for empty fields
        for item in vars:
            if item == '':
                errors += 1

        # Check if errors is greater than 0. If not insert data
        if errors > 0:
            messagebox.showwarning('Error!', 'All fields are required.', icon='warning')
        else:

            # Insert data into database
            self.database.insert(vars)

            # Clear entry fields
            for item in self.window.left_container.winfo_children():
                if 'entry' in str(item):
                    item.delete(0, tk.END)

            # Repopulate the treeview
            self.getall()

    def getall(self):

        # Clear treeview and repopulate
        for item in self.window.tree.get_children():
            self.window.tree.delete(item)

        # Grab the data
        data = self.database.getall()

        # If there is data insert into the treeview with alternation background colors
        if data:
            for i, items in enumerate(data):
                if i % 2 == 0:
                    self.window.tree.insert('', 'end', values=[item.title() if isinstance(item, str) \
                                                               else item for item in items], tags=('evenrow',))
                else:
                    self.window.tree.insert('', 'end', values=[item.title() if isinstance(item, str) \
                                                               else item for item in items], tags=('oddrow',))


if __name__ == '__main__':
    root = tk.Tk()
    controller = Controller(Window(root), Database())
    root.mainloop()

You have to declare a textvariable as a StringVar, IntVar , etc. https://dafarry.github.io/tkinterbook/variable.htm Why do you have 400+ lines of code that has obviously not bee tested. Statements like "age = ag_e.get()" do not work because it is in a function and "age" is garbage collected when the function exits.

It works for me. I used the interface to make a small database with 4 students. I successfully searched by student ID. I viewed all the records. I deleted a student. All that worked. I even used DB Browser for sqlite and saw everything in the database is correct.

What makes you think it doesn't work?

---

If you spent a moment to look at the code you would see that "age = ag_e.get()" does define a variable that is local to the function, but it is used a few lines later in the same function.

        conn.execute('INSERT INTO STUDENT_REGISTRATION (ID, STU_FIRSTNAME, STU_MIDDLENAME, STU_LASTNAME, STU_BIRTHDAY, STU_AGE, STU_GENDER, STU_ADDRESS, STU_WEIGHT, STU_HEIGHT, STU_BLOODTYPE, STU_DISABILITIES) \
              VALUES (?,?,?,?,?,?,?,?,?,?,?,?)', (
            stuid, firstname, middlename, lastname, birthday, [color=#E74C3C]age[/color], gender, address, weight, height, bloodtype,
            disabilities));

Here is my finished attempt at your project. Modified the code a little. Took out the reset button as the treeview is updated anytime an action is performed.
Took out the update button and changed the text and command when record is being updated. To update a record double click a row in the treeview. It will populate the form.
Again the only validation that is done is checking for empty fields.
There are a lot of comments in the code. Did my best at trying to explain what is taking place throughout the code. Again I kinda went green crazy. :)

import tkinter as tk
from tkinter import messagebox, ttk
import sqlite3 as sq
from functools import partial

'''
 This class handles all database manipulations. eg. search, insert, edit, delete, and read
'''
class Database:
    def __init__(self):

        # Create and/or connect to database
        self.connect = sq.connect('qsuclinicrecord.db')
        self.cursor = self.connect.cursor()

        # Create the table if it does not exist
        self.connect.execute('''
                                create table if not exists registration (
                                id integer primary key autoincrement,
                                firstname varchar(100), middlename varchar(100), lastname varchar(100),
                                birthdate text, age integer, gender text, address text, weight integer,
                                height text, bloodtype varchar(25), disabilities text
                                );
                             ''')

    # Method for getting all records
    def getall(self):
        self.cursor.execute('''select * from registration''')
        result = self.cursor.fetchall()
        if result:
            return result
        return False

    # Method for searching the database
    def search(self, term):
        self.cursor.execute('''
                                select * from registration where id=? or firstname=? or lastname=?;
                            ''', (term, term.lower(), term.lower()))
        result = self.cursor.fetchall()
        if result:
            return result
        return False

    # Method for inserting new records
    def insert(self, data):
        self.connect.execute('''
            insert into registration (firstname, middlename,
            lastname, birthdate, age, gender, address, weight, height, bloodtype, disabilities)
            values (?,?,?,?,?,?,?,?,?,?,?)
        ''', data)
        self.connect.commit()

    # Method for updating records
    def update(self, data, id):
        self.connect.execute('''
            update registration set firstname=?, middlename=?, lastname=?, birthdate=?, age=?,
            gender=?, address=?, weight=?, height=?, bloodtype=?, disabilities=? where id=?
                             ''', (*data, id,))
        self.connect.commit()

    # Method for deleting a record
    def delete(self, id):
        self.connect.execute('''delete from registration where id=?''', (id,))
        self.connect.commit()


'''
    This class is for viewing records. Contains all the widgets for the form, treeview, and buttons
'''
class Window:
    def __init__(self, parent):

        '''
            Various colors for styling
        '''
        search_color = 'mediumseagreen'
        button_highlight_color = '#50c878'
        button_active_color = 'mediumseagreen'
        search_label_color = '#e9ffdb'
        frame_color = '#ace1af'
        frame_label_color = '#ace1af'
        window_background_color = '#355e3b'
        main_container_color = '#ace1af'
        treefield_color = 'beige'
        field_row_color_even = 'beige'
        field_row_color_odd = '#ace1af'

        ''' Set some class variables
            self.parent.columnconfigure and self.parent.rowconfigure help when the window is expanded
            self.parent update is needed to get the screen size
        '''
        self.parent = parent
        self.parent['bg'] = window_background_color
        self.parent.columnconfigure(0, weight=1)
        self.parent.rowconfigure(0, weight=1)
        self.parent.update()

        '''
            Get the screen width and height. The window will be sized based on user screen.
            Placement of the window will be screen size / 2 - window size / 2.
            This should place the window in the middle of the screen
        '''
        screen_size = (self.parent.winfo_screenwidth(), self.parent.winfo_screenheight())
        window_size = (int(screen_size[0]/2), int(screen_size[1]/2))
        window_placement = (int((screen_size[0]/2)-(window_size[0]/2)), int((screen_size[1]/2)-(window_size[1]/2)))

        '''
            Styling the window. Set the geometry of the window from the above variables
        '''
        self.parent.title('QSU-Diffun Clinic Record System')
        self.parent.geometry(f'{window_size[0]}x500+{window_placement[0]}+{window_placement[1]}')
        self.parent.resizable(False, False)

        '''
            Create four containers for the widgets.
            First will be the main container which will hold all other containers
            Second will left container for holding the form
            Third will be for the search field and various buttons
            Fourth will be for the treeview widget
        '''

        container = tk.Frame(self.parent)
        container['padx'] = 8
        container['pady'] = 8
        container['bg'] = main_container_color
        container['borderwidth'] = 1
        container['highlightthickness'] = 1
        container['highlightcolor'] = 'black'
        container['highlightbackground'] = 'black'
        container.grid(column=0, row=0, sticky='news', padx=8, pady=8)

        '''
            The widget container will be various sizes, set with columnconfigure.
            The left container will hold the for self.fields, setting it to a weight of 2
            The middle container will hold the search field which is searching by id,
            will not require alot of space so setting it to a weight of 1
            The right container will hold the treeview widget. Although there will be
            scrollbars, trying to have as much of the widget in view as possible.
            Giving it a weight of  3
        '''

        container.grid_columnconfigure(0, weight=0)
        container.grid_columnconfigure(1, weight=0)
        container.grid_columnconfigure(2, weight=3)

        '''
            Create and style the three sub containers. Using self on the left container
            as it will help in clearing the form self.fields on submission
        '''

        self.left_container = tk.Frame(container, bg=frame_color)
        self.left_container['borderwidth'] = 1
        self.left_container['highlightthickness'] = 1
        self.left_container['highlightcolor'] = 'black'
        self.left_container['highlightbackground'] = 'black'
        self.left_container.grid(column=0, row=1, sticky='news', padx=(0, 8), pady=8)
        for i in range(12):
            self.left_container.grid_columnconfigure(0, weight=1)
            self.left_container.grid_columnconfigure(1, weight=2)

        mid_container = tk.Frame(container, bg=frame_color)
        mid_container['borderwidth'] = 1
        mid_container['highlightthickness'] = 1
        mid_container['highlightcolor'] = 'black'
        mid_container['highlightbackground'] = 'black'
        mid_container.grid(column=1, row=1, sticky='news', padx=8, pady=8)
        mid_container.grid_columnconfigure(0, weight=3)

        search_container = tk.Frame(container)
        search_container['borderwidth'] = 1
        search_container['highlightthickness'] = 1
        search_container['highlightcolor'] = 'lightgray'
        search_container['highlightbackground'] = 'lightgray'
        search_container['bg'] = search_color

        search_container.grid(column=0, columnspan=3, row=3, sticky='news')
        search_container.grid_columnconfigure(0, weight=0)
        search_container.grid_columnconfigure(1, weight=3)
        search_container.grid_columnconfigure(2, weight=1)

        right_container = tk.Frame(container, width=600)
        right_container.grid(column=2, row=1, sticky='news', padx=(8, 0), pady=8)
        right_container.grid_columnconfigure(0, weight=3)
        right_container.grid_rowconfigure(0, weight=3)

        '''
            Create and style the window heading
        '''

        header = tk.Label(container, pady=8)
        header['text'] = 'QSU-Diffun Clinic Record System'
        header['font'] = ('arial', 20, 'bold')
        header['bg'] = 'mediumseagreen'
        header['fg'] = 'white'
        header['relief'] = 'raised'
        header.grid(column=0, columnspan=3, row=0, sticky='news', pady=(0,8))

        '''
            Create the labels and self.fields for the form.
            Create three list to save some typing
            One list to hold the label names and two empty list to hold
            field names and stringvariables
        '''
        labels = ['First Name', 'Middle Name', 'Last Name', 'Birthdate', 'Age', 'Gender',
                  'Address', 'Weight', 'Height', 'Bloodtype', 'Disabilities']
        label_headings = labels.copy()
        label_headings.insert(0, 'Student ID')

        self.fields = []
        self.vars = []

        '''
            Loop through the label list appending self.fields and stringvariables
            Style the labels and self.fields
        '''
        for i, label in enumerate(labels):
            self.fields.append(tk.Entry(self.left_container))
            self.vars.append(tk.StringVar())
            labels[i] = tk.Label(self.left_container, text=labels[i].title(), anchor='w')
            labels[i]['bg'] = frame_label_color
            labels[i]['fg'] = 'forestgreen'
            labels[i]['relief'] = 'ridge'
            labels[i]['font'] = ('tahoma', 12, 'bold')
            labels[i].grid(column=0, row=i, sticky='new', padx=(8, 3), pady=2)

            self.fields[i]['font'] = ('tahoma', 12, 'normal')
            self.fields[i]['textvariable'] = self.vars[i]
            self.fields[i]['bg'] = 'beige'
            self.fields[i].grid(column=1, row=i, sticky='new', padx=(3, 8), pady=2)

        '''
            Buttons for various functions
        '''

        self.buttons = ['View All Records', 'Add New Record', 'Delete']
        for i, button in enumerate(self.buttons):
            self.buttons[i] = tk.Button(mid_container, text=button)
            self.buttons[i]['cursor'] = 'hand2'
            self.buttons[i]['font'] = ('arial', 11, 'normal')
            self.buttons[i]['bg'] = 'lightseagreen'
            self.buttons[i]['fg'] = 'navy'
            self.buttons[i]['highlightbackground'] = button_highlight_color
            self.buttons[i]['highlightcolor'] = button_highlight_color
            self.buttons[i]['activebackground'] = button_active_color
            self.buttons[i]['activeforeground'] = 'white'
            self.buttons[i].grid(column=0, row=i, sticky='news', padx=4, pady=8)

        '''
            Create and style the treeview and scrollbars
        '''
        style = ttk.Style()
        style.theme_use('alt')
        style.configure('Treeview', fieldbackground=treefield_color, padding=3)
        style.configure('Treeview.Heading', background='mediumseagreen', foreground='white',
                        font=('arial', 11, 'bold'))
        style.configure('TScrollbar', troughcolor='mediumseagreen', arrowcolor='lightseagreen',
                        background='forestgreen', arrowsize=20)
        style.map('TScrollbar',
                  background=[
                      ('disabled', 'seagreen'),
                      ('pressed', '#ace1af'),
                      ('active', '#ace1af')
                      ],
                  arrowcolor=[('active', 'green'), ('disabled', 'green')]
                  )

        self.tree = ttk.Treeview(right_container)
        self.tree['columns'] = label_headings
        self.tree['show'] = 'headings'
        self.tree.column(0, minwidth=100, width=100)
        self.tree.column(4, minwidth=150, width=150)
        self.tree.column(5, minwidth=80, width=80)
        self.tree.column(6, minwidth=120, width=120)
        self.tree.column(7, minwidth=300, width=300)
        self.tree.column(8, minwidth=100, width=100)
        self.tree.column(9, minwidth=100, width=100)
        [self.tree.heading(i, text=label) for i,label in enumerate(label_headings)]
        self.tree.grid(column=0, row=0, sticky='news')
        self.tree.tag_configure('oddrow', background=field_row_color_odd)
        self.tree.tag_configure('evenrow', background=field_row_color_even)

        # Create and configure scrollbars
        yscrollbar = ttk.Scrollbar(right_container, orient='vertical')
        yscrollbar.grid(column=1, row=0, sticky='ns')

        xscrollbar = ttk.Scrollbar(right_container, orient='horizontal')
        xscrollbar.grid(column=0, row=1, sticky='we')

        self.tree.configure(yscrollcommand=yscrollbar.set, xscrollcommand=xscrollbar.set)
        yscrollbar.configure(command=self.tree.yview, cursor='hand2')
        xscrollbar.configure(command=self.tree.xview, cursor='hand2')

        '''
            Create a label search field and button
        '''
        label = tk.Label(search_container)
        label['font'] = ('arial', 11, 'italic normal')
        label['text'] = 'Search by id, first name, or last name'
        label['fg'] = 'black'
        label['bg'] = search_label_color
        label.grid(column=0, columnspan=3, row=0, sticky='new', padx=4, pady=2)

        label = tk.Label(search_container, text='Search:', bg=search_color, fg='white')
        label['font'] = ('arial', 12, 'bold')
        label.grid(column=0, row=1, sticky='new', padx=(4, 2), pady=8)

        self.search_var = tk.StringVar()
        self.entry = tk.Entry(search_container)
        self.entry['font'] = ('arial', 12, 'normal')
        self.entry['bg'] = 'beige'
        self.entry['textvariable'] = self.search_var
        self.entry.grid(column=1, row=1, sticky='new', pady=8)

        self.search_button = tk.Button(search_container, text='Search', pady=2)
        self.search_button['cursor'] = 'hand2'
        self.search_button['bg'] = 'lightseagreen'
        self.search_button['fg'] = 'navy'
        self.search_button['font'] = ('sans serif', 10, 'normal')
        self.search_button['highlightbackground'] = button_highlight_color
        self.search_button['highlightcolor'] = button_highlight_color
        self.search_button['activebackground'] = button_active_color
        self.search_button['activeforeground'] = 'white'
        self.search_button.grid(column=2, row=1, sticky='new', padx=(10, 4), pady=8)


'''
 This class controlls all the data flow between Window() and Database() classes
'''
class Controller:
    def __init__(self, window, database):

        '''
            Define class variables
        '''
        self.window = window
        self.database = database

        '''
            Setup button commands
        '''

        self.window.search_button['command'] = partial(self.search, self.window.search_var)
        self.window.entry.bind('<Return>', partial(self.search, self.window.search_var))
        self.window.entry.bind('<KP_Enter>', partial(self.search, self.window.search_var))
        self.window.buttons[1]['command'] = partial(self.insert, self.window.vars)
        self.window.buttons[2]['command'] = self.delete
        self.window.tree.bind('<Double-Button-1>', self.populate_form)

        ''' Populate the treeview '''
        self.getall()

    ''' Method for updating records '''
    def update(self, data, id):

        # Set some variables
        errors = 0
        vars = []

        # Loop through the data and convert to human readable format
        [vars.append(val.get().lower()) for val in data]

        # Check for empty self.fields
        for item in vars:
            if item == '':
                errors += 1

        # Check if errors is greater than 0. If not update data
        if errors > 0:
            messagebox.showwarning('Error!', 'All self.fields are required.', icon='warning')
        else:

            self.window.buttons[1]['text'] = 'Add New Record'
            self.window.buttons[1]['command'] = partial(self.insert, self.window.vars)

            self.database.update(vars, id)

            # Clear entry self.fields
            for item in self.window.left_container.winfo_children():
                if 'entry' in str(item):
                    item.delete(0, tk.END)

            # Populate the treeview
            self.getall()

    # Method for populating the form for editing
    def populate_form(self, event):
        item_id = event.widget.focus()
        item = event.widget.item(item_id)
        values = item['values']
        id = values.pop(0)

        for i, value in enumerate(values):
            self.window.fields[i].insert(0, value)

        # Change the add record button text and command
        self.window.buttons[1]['text'] = 'Update Record'
        self.window.buttons[1]['command'] = partial(self.update, self.window.vars, id)


    # Method for deleting a record
    def delete(self):
        row = self.window.tree.focus()
        id = self.window.tree.item(row)['values'][0]
        confirm = messagebox.askquestion('Confirm', 'Are you sure you wish to delete this record?')
        if confirm == 'yes':
            messagebox.showinfo('Record Deleted', 'The record has been deleted.')
            self.database.delete(id)
            self.getall()

    # Method for searching for a record by id, firstname or lastname
    def search(self, search_term, event=None):
        term = search_term.get()
        if term:
            self.window.entry.delete(0, tk.END)
            data = self.database.search(term.lower())

            # Clear the treeview. Probably should make this a function as it is called several times.
            for item in self.window.tree.get_children():
                self.window.tree.delete(item)

            # Populate the treeview with retuned search results
            if data:
                for i, items in enumerate(data):
                    if i % 2 == 0:
                        self.window.tree.insert('', 'end', values=[item.title() if isinstance(item, str) \
                                                                   else item for item in items], tags=('evenrow',))
                    else:
                        self.window.tree.insert('', 'end', values=[item.title() if isinstance(item, str) \
                                                                   else item for item in items], tags=('oddrow',))
            else:
                messagebox.showinfo('No Results', f'Did not find any results for {term}')
        else:
            messagebox.showerror('Search Error!', 'You must enter a search term to search the database.', icon='error')

    '''
        Method for inserting data into the database.
        The method checks for empty self.fields and add 1 to the errors variable.
        If errors is greater than 0 a messagebox pops up.
        If no errors, then data is inserted into database and the treeview
        is cleared and repopulated. The entry self.fields in the left entry self.fields
        are cleared
    '''
    def insert(self, data):

        # Set some variables
        errors = 0
        vars = []

        # Loop through the data and convert to human readable format
        [vars.append(val.get().lower()) for val in data]

        # Check for empty self.fields
        for item in vars:
            if item == '':
                errors += 1

        # Check if errors is greater than 0. If not insert data
        if errors > 0:
            messagebox.showwarning('Error!', 'All self.fields are required.', icon='warning')
        else:

            # Insert data into database
            self.database.insert(vars)

            # Clear entry self.fields
            for item in self.window.left_container.winfo_children():
                if 'entry' in str(item):
                    item.delete(0, tk.END)

            # Repopulate the treeview
            self.getall()

    # Method for populating the treeview
    def getall(self):

        # Clear treeview and repopulate
        for item in self.window.tree.get_children():
            self.window.tree.delete(item)

        # Grab the data
        data = self.database.getall()

        # If there is data insert into the treeview with alternation background colors
        if data:
            for i, items in enumerate(data):
                if i % 2 == 0:
                    self.window.tree.insert('', 'end', values=[item.title() if isinstance(item, str) \
                                                               else item for item in items], tags=('evenrow',))
                else:
                    self.window.tree.insert('', 'end', values=[item.title() if isinstance(item, str) \
                                                               else item for item in items], tags=('oddrow',))

if __name__ == '__main__':
    root = tk.Tk()
    controller = Controller(Window(root), Database())
    root.mainloop()