def correct_graphs(user_id): dates_pure = [] correct_pure = [] sql_pure = """SELECT time_stamp,SUM(Correct) FROM pure_results WHERE user_id = ? GROUP BY time_stamp""" cursor.execute(sql_pure, [(user_id)]) for a in cursor.fetchall(): dates_pure.append(a[0]) correct_pure.append(a[1]) dates_applied = [] correct_applied = [] sql_applied = """SELECT time_stamp, SUM(Correct) FROM applied_results WHERE user_id = ? GROUP BY time_stamp""" cursor.execute(sql_applied, [(user_id)]) for b in cursor.fetchall(): dates_applied.append(b[0]) correct_applied.append(b[1]) plt.figure(1) plt.subplot(211) plt.plot(dates_pure, correct_pure) plt.ylabel("Questions Correct") plt.xlabel("Dates of Maths Question Attempted") plt.title("Pure Maths Correct Progress") plt.subplot(212) plt.plot(dates_applied, correct_applied, "-g") plt.xlabel("Dates of Maths Question Attempted") plt.ylabel("Questions Correct") plt.title("Applied Maths Correct Progress") plt.tight_layout() plt.show()
def username_check(username): # function for username vaildation # Checking the length of username is between 6 and 30 chracters if (len(username) >= 6) and (len(username) <= 30): # sql statement for checking existing users # Checks student database for username fetchstudents = ( "SELECT DISTINCT Students.Username from Students WHERE Username = ?" ) # Checkes teacher databaase for username fetchteachers = ( "SELECT DISTINCT Teachers.Username from Teachers WHERE Username = ?" ) cursor.execute( fetchstudents, [(username)]) # executes the above query on the student table cursor1.execute( fetchteachers, [(username)]) # execute the above query on the teacher table checking = cursor.fetchall( ) # stores the result of sql search done on the Student table checking1 = cursor1.fetchall( ) # stores the result of sql search done on the Teacher table if checking or checking1: # if checking or checking1 has values then return tkinter error messagebox.showerror( "Username", "That username has been taken please try another one") else: # if checking and checking 1 is none then return true return True else: #if username isn't in the range then return tkinter error message messagebox.showerror("Username", "Username has to be between 6 and 30 characters")
def username_check(username): # function for username vaildation # Checking the length of username is more than 6 charcters if len(username) >= 6: # sql statement for checking existing users # Checks student database for username fetchstudents = ( "SELECT DISTINCT Students.Username from Students WHERE Username = ?" ) # Checkes teacher databaase for username fetchteachers = ( "SELECT DISTINCT Teachers.Username from Teachers WHERE Username = ?" ) cursor.execute( fetchstudents, [(username)]) # executes the above query on the student table cursor1.execute( fetchteachers, [(username)]) # execute the above query on the teacher table checking = cursor.fetchall() # stores the result of sql search checking1 = cursor1.fetchall() if checking or checking1: messagebox.showerror( "Username", "That username has been taken please try another one") else: return True else: messagebox.showerror("Username", "Username has to be 6 or more characters")
def correct_graphs(user_id): dates_pure = [] # lists that stores dates and correct from pure results correct_pure = [] # sql for getting the dates and correct from table pure results in the db file # using the user_id sql_pure = """SELECT time_stamp,SUM(Correct) FROM pure_results WHERE user_id = ? GROUP BY time_stamp""" cursor.execute(sql_pure, [(user_id)]) # executing the sql for a in cursor.fetchall(): # storing the result in the pure lists dates_pure.append(a[0]) correct_pure.append(a[1]) dates_applied = [] # list that stores dates and correct from applied result correct_applied = [] # sql for getting the dates and correct from table applied results # in the db file using the user_id sql_applied = """SELECT time_stamp, SUM(Correct) FROM applied_results WHERE user_id = ? GROUP BY time_stamp""" cursor.execute(sql_applied, [(user_id)]) # executing the sql for b in cursor.fetchall(): dates_applied.append(b[0]) correct_applied.append(b[1]) # storing the result the applied lists plt.figure(1) # creates the figure for the graph to be plot plt.subplot(211) # creates a subplot for the pure graph plt.plot(dates_pure,correct_pure) # plots the pure values on one subplot plt.ylabel("Questions Correct") # labels the y axis plt.xlabel("Dates of Maths Question Attempted") # labels the x axis plt.title("Pure Maths Correct Progress") # puts a title on the graph plt.subplot(212) # creates another subplot for applied graph plt.plot(dates_applied, correct_applied, "-g") #plots the applied values on the other subplot and sets the line colour green plt.xlabel("Dates of Maths Question Attempted") # labels the x axis plt.ylabel("Questions Correct") # labels the y axis plt.title("Applied Maths Correct Progress") # puts a title on the graph plt.tight_layout() # changes the layout to be tight plt.show() # shows the graphs made
def incorrect_graphs(user_id): dates_pure = [] # four lists 2 for pure and 2 for applied incorrect_pure = [] dates_applied = [] incorrect_applied = [] # Sql that perform the same task but on different tables pure_results and applied_results # using the user_id getting the incorrect values along with there respective date sql_pure = """SELECT time_stamp,SUM(Incorrect) FROM pure_results WHERE user_id = ? GROUP BY time_stamp""" sql_applied = """SELECT time_stamp, SUM(Incorrect) FROM applied_results WHERE user_id = ? GROUP BY time_stamp""" cursor.execute(sql_pure, [(user_id)]) #executes the pure statement for a in cursor.fetchall(): # appends all the values to the two pure lists dates_pure.append(a[0]) incorrect_pure.append(a[1]) cursor.execute(sql_applied, [(user_id)]) # executes the applied statement for b in cursor.fetchall(): # appends all the value to the two applied lists dates_applied.append(b[0]) incorrect_applied.append(b[1]) plt.figure(1) # creates the figure for the graph to be plot plt.subplot(211) # creates a subplot for the pure graph plt.plot(dates_pure,incorrect_pure) # plots the pure values on one subplot plt.ylabel("Questions Incorrect") # labels the y axis plt.xlabel("Dates of Maths Question Attempted") # labels the x axis plt.title("Pure Maths Incorrect Progress") # puts a title on the graph plt.subplot(212) # creates another subplot for applied graph plt.plot(dates_applied, incorrect_applied, "-g") #plots the applied values on the other subplot and sets the line colour green plt.xlabel("Dates of Maths Question Attempted") # labels the x axis plt.ylabel("Questions Incorrect") # labels the y axis plt.title("Applied Maths Incorrect Progress") # puts a title on the graph plt.tight_layout()# changes the layout to be tight plt.show() # shows the graphs made
def score_graph(user_id): dates = [] total_score = [] sql = """SELECT time_stamp,SUM(score) total FROM (SELECT time_stamp,score FROM pure_results WHERE user_id = ? UNION ALL SELECT time_stamp,score FROM applied_results WHERE user_id = ?) t GROUP BY time_stamp""" cursor.execute(sql, [(user_id), (user_id)]) for a in cursor.fetchall(): dates.append(a[0]) total_score.append(a[1]) plt.plot(dates, total_score) plt.xlabel("Date") plt.ylabel("Current Score") plt.title("A level Score Progess") plt.show()
def total_graph_incorrect(user_id): dates = [] incorrect = [] sql = """SELECT time_stamp,SUM(Incorrect) total FROM (SELECT time_stamp,Incorrect FROM pure_results WHERE user_id = ? UNION ALL SELECT time_stamp, Incorrect FROM applied_results WHERE user_id = ?) t GROUP BY time_stamp""" cursor.execute(sql, [(user_id), (user_id)]) for a in cursor.fetchall(): dates.append(a[0]) incorrect.append(a[1]) plt.plot(dates, incorrect, label="Incorrect Progess") plt.xlabel("Dates of Maths Question Attempted") plt.ylabel("Incorrect Questions") plt.title("A Level Maths Total Incorrect Progress") plt.show()
def graph_total_questions(user_id): dates = [] total_questions = [] sql = """SELECT time_stamp,SUM(total_questions) total FROM (SELECT time_stamp,total_questions FROM pure_results WHERE user_id = ? UNION ALL SELECT time_stamp,total_questions FROM applied_results WHERE user_id = ?) t GROUP BY time_stamp""" cursor.execute(sql, [(user_id), (user_id)]) for a in cursor.fetchall(): dates.append(a[0]) total_questions.append(a[1]) plt.plot(dates, total_questions) plt.xlabel("Dates of Questions Attempted") plt.ylabel("Total Questions Attempted") plt.title("A Level Maths Total Questions Attempted") plt.show()
def score_graph(user_id): dates = [] # two lists one for dates the other for total questions total_score = [] # sql that combines score (along with the date connected to it) # from both tables into one value and groups the dates together # hence returning it back in order using the user_id sql = """SELECT time_stamp,SUM(score) total FROM (SELECT time_stamp,score FROM pure_results WHERE user_id = ? UNION ALL SELECT time_stamp,score FROM applied_results WHERE user_id = ?) t GROUP BY time_stamp""" cursor.execute(sql, [(user_id), (user_id)]) # executes sql statement for a in cursor.fetchall(): # appends the data to the two lists dates.append(a[0]) total_score.append(a[1]) plt.figure(1) # creates the figure for the graph to be plot plt.plot(dates, total_score) # plots the data on the figure plt.xlabel("Date") # labels the x axis plt.ylabel("Current Score") # labels the y axis plt.title("A level Score Progess") # gives the graph a title plt.figure(1).autofmt_xdate() # formats the x axis to prevent overlap plt.show() # shows the graph
def total_graph_correct(user_id): dates = [] # Two lists one for dates the other for correct correct = [] # Sql that combines correct (along with the date connected to it) from both tables into # one value and groups the dates together hence returning it back in order # using the user_id sql = """SELECT time_stamp,SUM(Correct) total FROM (SELECT time_stamp,Correct FROM pure_results WHERE user_id = ? UNION ALL SELECT time_stamp, Correct FROM applied_results WHERE user_id = ?) t GROUP BY time_stamp""" cursor.execute(sql, [(user_id), (user_id)]) # executes th sql statement for a in cursor.fetchall(): # appends the results found to the two lists dates.append(a[0]) correct.append(a[1]) plt.plot(dates, correct, label="Total Correct Progress") # plots the data # and sets a label on it plt.xlabel("Dates of Maths Question Attempted") # labels the x axis plt.ylabel("Questions Correct") # labels the y axis plt.title("A Level Maths Total Correct Progress") # gives the graph a title plt.figure(1).autofmt_xdate() # formats the x axis to prevent overlap on the figure plt.show() # shows the graph
def graph_total_questions(user_id): dates = [] # two lists one for dates the other for total questions total_questions = [] # sql that combines total_question (along with the date connected to it) # from both tables into one value and groups the dates together # hence returning it back in order using the user_id sql = """SELECT time_stamp,SUM(total_questions) total FROM (SELECT time_stamp,total_questions FROM pure_results WHERE user_id = ? UNION ALL SELECT time_stamp,total_questions FROM applied_results WHERE user_id = ?) t GROUP BY time_stamp""" cursor.execute(sql, [(user_id), (user_id)]) # executes the sql for a in cursor.fetchall(): # appends the data found to the two lists dates.append(a[0]) total_questions.append(a[1]) plt.figure(1) # creates the figure for the graph to be plot plt.plot(dates, total_questions) # plots the data on the figure plt.xlabel("Dates of Questions Attempted") # labels the x axis plt.ylabel("Total Questions Attempted") # labels the y axis plt.title("A Level Maths Total Questions Attempted") # gives the graph a title plt.figure(1).autofmt_xdate()# formats the x axis to prevent overlap plt.show() # shows the graph
def sort_gender(): sql = """ SELECT ID, Forename, Surname, Age, class, gender FROM students ORDER BY Gender""" cursor.execute(sql) result = cursor.fetchall() return result
def get_students(): sql = """ SELECT ID, Forename, Surname, Age, class, gender FROM students """ cursor.execute(sql) result = cursor.fetchall() return result