Posts: 1
Threads: 1
Joined: Jul 2022
Jul-15-2022, 12:20 PM
(This post was last modified: Jul-15-2022, 06:42 PM by Yoriz.
Edit Reason: Added code tags
)
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()
Posts: 453
Threads: 16
Joined: Jun 2022
Jul-17-2022, 04:56 AM
(This post was last modified: Jul-17-2022, 05:02 AM by rob101.)
(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/
Sig:
>>> import this
The UNIX philosophy: "Do one thing, and do it well."
"The danger of computers becoming like humans is not as great as the danger of humans becoming like computers." :~ Konrad Zuse
"Everything should be made as simple as possible, but not simpler." :~ Albert Einstein
Posts: 1,144
Threads: 114
Joined: Sep 2019
Jul-18-2022, 06:06 AM
(This post was last modified: Jul-20-2022, 06:03 PM by menator01.
Edit Reason: Moved the create table before the getall function
)
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()
Posts: 536
Threads: 0
Joined: Feb 2018
Jul-18-2022, 07:55 PM
(This post was last modified: Jul-18-2022, 07:55 PM by woooee.)
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.
Posts: 6,780
Threads: 20
Joined: Feb 2020
Jul-18-2022, 11:53 PM
(This post was last modified: Jul-18-2022, 11:53 PM by deanhystad.)
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));
Posts: 1,144
Threads: 114
Joined: Sep 2019
Jul-21-2022, 07:46 PM
(This post was last modified: Jul-21-2022, 07:46 PM by menator01.)
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()
|
