Fist i would create the database and have it look like the following (note viewed this using the db browser for sqlite)
Note ignore the top table the db browser auto makes that the ones you are interested in are the other 2. The way you would use this is by using the two student ids to link the tables together, each time a student is added the student id will auto increment this means that it wil be made for you and go up by 1 for each new record.
With this there would be serverall sql you want be wanting, the one shown below is the sql needed to create your tables/ clear them:
sql = """CREATE TABLE `tbl_Students` (
`StudentID` INTEGER PRIMARY KEY AUTOINCREMENT,
`FirstName` TEXT,
`LastName` TEXT
)"""
#this is the sql that will be run
with sqlite3.connect("studentscores.db") as db:
cursor = db.cursor()
cursor.execute("select name from sqlite_master where name=?",("tbl_Students",))
result = cursor.fetchall()
cursor.execute("drop table if exists {0}".format("tbl_Students")) #if table exist delete it
cursor.execute(sql)
db.commit()
#this is the sql that will be run
sql = """CREATE TABLE `tbl_scores` (
`StudentID` INTEGER,
`DateOfScore` INTEGER,
`Score` INTEGER
)"""
with sqlite3.connect("studentscores.db") as db:
cursor = db.cursor()
cursor.execute("select name from sqlite_master where name=?",("tbl_scores",))
result = cursor.fetchall()
cursor.execute("drop table if exists {0}".format("tbl_scores"))#if table exist delete it
cursor.execute(sql)
db.commit()
self.cleardb.set("database cleared")
next is inserting values into the database
with sqlite3.connect("studentscores.db") as db:
cursor=db.cursor()
sql = "insert into tbl_Students (FirstName, Lastname) values (?,?)"
cursor.execute(sql,(firstname,lastname))
db.commit()
When inserting values for scores i would have the user select a student first so that you can then link the two tables (i often use a tree for this but a drop down could also be used which would be populated from using data from the database)
with sqlite3.connect("studentscores.db") as db:
cursor=db.cursor()
sql = "insert into tbl_scores (StudentID, DateOfScore, Score) values (?,?, ?)"
cursor.execute(sql,(StudentID,nDateOfScore,nScore))
db.commit()
thirdly is updating values
with sqlite3.connect("studentscores.db") as db:
cursor = db.cursor()
sql = "update tblStudents set FirstName =?,LastName=? where StudentID=?"
cursor.execute(sql,(FirstName,LastName,StudentID))
db.commit()
lastly is deleting values
with sqlite3.connect("GuitarLessons.db") as db:
cursor = db.cursor()
sql = "delete from tblStudents where StudentID = ?"
cursor.execute(sql,(StudentID))
db.commit()
i have made a simple example where you can add new students and scores and clear the database note i put them all on one form but it would probly be best to put them on different from by having a menu system they have to go through (this would include splitting your code up into different classes and changing the toplevel if using object orientated programming)
note i have made it using object orientated programming
from tkinter import *
import sqlite3
from tkinter.ttk import Combobox,Treeview,Scrollbar
class test(Frame):
'this runs when prgram first runs'
def __init__(self, master):
self.modefunction = 1
""" Initialize the frame. """
super(test,self).__init__(master)
self.grid()
self.student_input_frm = LabelFrame(self, width=100, height=30, text = "entering stdent details")
self.student_input_frm.grid(row = 0, column = 0)
self.clear_databse_frm = LabelFrame(self, width=100, height=30, text = "clearing database")
self.clear_databse_frm.grid(row = 1, column = 0)
self.score_input_frm = LabelFrame(self, width=100, height=30, text = "adding scores")
self.score_input_frm.grid(row = 2, column = 0)
#these forms are used to group objects ogether
self.create_GUI()
def create_GUI(self):
self.create_clear_datbase()
self.create_student_input()
self.create_score_input()
def create_clear_datbase(self):
self.cleardb = StringVar()
self.cleardb.set("clearing datbase will cause all datba to be lost")
self.recreatedb_btn = Button(self.clear_databse_frm, text = "re create database", command = self.recreatedb)
self.recreatedb_btn.grid(row = 0,column = 0)
self.cleardb_message_lbl = Label(self.clear_databse_frm, textvariable = self.cleardb)
self.cleardb_message_lbl.grid(row = 0, column = 1)
def recreatedb(self):
sql = """CREATE TABLE `tbl_Students` (
`StudentID` INTEGER PRIMARY KEY AUTOINCREMENT,
`FirstName` TEXT,
`LastName` TEXT
)"""
#this is the sql that will be run
with sqlite3.connect("studentscores.db") as db:
cursor = db.cursor()
cursor.execute("select name from sqlite_master where name=?",("tbl_Students",))
result = cursor.fetchall()
cursor.execute("drop table if exists {0}".format("tbl_Students")) #if table exist delete it
cursor.execute(sql)
db.commit()
#this is the sql that will be run
sql = """CREATE TABLE `tbl_scores` (
`StudentID` INTEGER,
`DateOfScore` INTEGER,
`Score` INTEGER
)"""
with sqlite3.connect("studentscores.db") as db:
cursor = db.cursor()
cursor.execute("select name from sqlite_master where name=?",("tbl_scores",))
result = cursor.fetchall()
cursor.execute("drop table if exists {0}".format("tbl_scores"))#if table exist delete it
cursor.execute(sql)
db.commit()
self.cleardb.set("database cleared")
def create_student_input(self):
self.student_input = StringVar()
self.student_input.set("click add student button to add student")
self.nfirst_name_lbl = Label(self.student_input_frm, text = "students first name:")
self.nfirst_name_lbl.grid(row = 0,column = 0)
self.nfirst_name_txt = Entry(self.student_input_frm)
self.nfirst_name_txt.grid(row = 0,column = 1)
self.nlast_name_lbl = Label(self.student_input_frm, text = "students last name:")
self.nlast_name_lbl.grid(row = 1,column = 0)
self.nlast_name_txt = Entry(self.student_input_frm)
self.nlast_name_txt.grid(row = 1,column = 1)
self.nstudent_btn = Button(self.student_input_frm, text = "enter new student", command = self.nstudent)
self.nstudent_btn.grid(row = 0,column = 3)
self.nstudent_message_lbl = Label(self.student_input_frm, textvariable = self.student_input)
self.nstudent_message_lbl.grid(row = 1, column = 3)
def nstudent(self): #adds new record to studdent table
firstname = self.nfirst_name_txt.get()
lastname = self.nlast_name_txt.get()
with sqlite3.connect("studentscores.db") as db:
cursor=db.cursor()
sql = "insert into tbl_Students (FirstName, Lastname) values (?,?)"
cursor.execute(sql,(firstname,lastname))
db.commit()
self.student_input.set("new student added")
def create_score_input(self):
self.first_name_lbl = Label(self.score_input_frm, text = "students first name:")
self.first_name_lbl.grid(row = 0,column = 0)
self.first_name_txt = Entry(self.score_input_frm)
self.first_name_txt.grid(row = 0,column = 1)
self.last_name_lbl = Label(self.score_input_frm, text = "students last name:")
self.last_name_lbl.grid(row = 1,column = 0)
self.last_name_txt = Entry(self.score_input_frm)
self.last_name_txt.grid(row = 1,column = 1)
self.search_students_adding_score_btn = Button(self.score_input_frm, command = lambda : self.search_students_adding_score(),text = "search students")
self.search_students_adding_score_btn.grid(row = 2, column = 0)
self.tree = Treeview(self.score_input_frm) #tree is good way of presenting reults froim sql
self.tree["columns"] = ("StudentID","First Name","Last Name")
self.tree.column("StudentID",width = 100)
self.tree.column("First Name",width = 100)
self.tree.column("Last Name", width = 100)
self.tree.heading("StudentID",text="StudentID")
self.tree.heading("First Name",text="First Name")
self.tree.heading("Last Name",text="Last Name")
self.tree["show"] = "headings"
yscrollbar = Scrollbar(self.score_input_frm, orient='vertical', command=self.tree.yview)
xscrollbar = Scrollbar(self.score_input_frm, orient='horizontal', command=self.tree.xview)
self.tree.configure(yscroll=yscrollbar.set, xscroll=xscrollbar.set)
yscrollbar.grid(row=3, column=4,sticky=NS)
self.tree.grid(row=3,column=0, columnspan = 3)
self.tree.bind("<Double-1>",lambda event :self.OnDoubleClick_input_scores(event))
def search_students_adding_score(self):
FirstName = self.first_name_txt.get()
LastName = self.last_name_txt.get()
with sqlite3.connect("studentscores.db") as db:
cursor = db.cursor()
cursor.row_factory = sqlite3.Row
sql = "select StudentID,FirstName,LastName"\
" from tbl_Students"\
" where FirstName like ?"\
" and LastName like ?"
cursor.execute(sql,("%"+FirstName+"%","%"+LastName+"%",))
StudentList = cursor.fetchall() #results of sql put into this array
self.loadStudents_inputing_score(StudentList)
def loadStudents_inputing_score(self,StudentList):
for i in self.tree.get_children():
self.tree.delete(i)
for student in StudentList:
self.tree.insert("" , 0,values=(student[0],student[1],student[2]))
def OnDoubleClick_input_scores(self,event):
curItem = self.tree.focus()
contents =(self.tree.item(curItem))
StudentDetails = contents['values']
self.nStudentID = int(StudentDetails[0])
self.score_input_message = StringVar()
self.score_input_message.set(" ")
self.date_score_lbl = Label(self.score_input_frm, text = "date")
self.date_score_lbl.grid(row = 4,column = 0)
self.date_score_txt = Entry(self.score_input_frm)
self.date_score_txt.grid(row = 4,column = 1)
self.score_lbl = Label(self.score_input_frm, text = "score")
self.score_lbl.grid(row = 5,column = 0)
self.score_txt = Entry(self.score_input_frm)
self.score_txt.grid(row = 5,column = 1)
self.nstudent_btn = Button(self.score_input_frm, text = "enter new score for the selected student", command = self.nscore)
self.nstudent_btn.grid(row = 6,column = 0)
self.date_score_lbl = Label(self.score_input_frm, textvariable = self.score_input_message)
self.date_score_lbl.grid(row = 6,column = 1)
def nscore(self): #adds new record intop score table the student id indicates which student that score is for
self.nDateOfScore = self.date_score_txt.get()
self.nScore = self.score_txt.get()
print(self.nStudentID)
print(self.nDateOfScore)
with sqlite3.connect("studentscores.db") as db:
cursor=db.cursor()
sql = "insert into tbl_scores (StudentID, DateOfScore, Score) values (?,?, ?)"
cursor.execute(sql,(self.nStudentID,self.nDateOfScore,self.nScore))
db.commit()
self.score_input_message.set("new student added")
root = Tk()
root.title("booking system")
root.geometry("500x500")
root.configure(bg="white")
app = test(root)
root.mainloop()
Then to do what you wanted in your question i would have a tree view where the user enters a student name and that used in sql to get all that students scores then sort these into order and take the top 3 and average them.
To sort these dates first convert them to date times - enter link description here
Then to sort them - enter link description here
