Untitled
unknown
python
7 months ago
11 kB
1
Indexable
Never
import pymysql as pm import re conn = pm.connect(host='localhost', user='root', port='', database='WSHS Swimming Carnival') cursor = conn.cursor() def login(): print("Please enter your Username and Password") while True: userName = input("Username:") userPassword = input("Password:") sql = "SELECT UserID FROM UserInformation WHERE UserName = '"+userName+"' AND UserPassword = '"+userPassword+"'" cursor.execute(sql) conn.commit() for row in cursor.fetchall(): if row[0] > 0: showInfo(int(row[0])) break else: print("Invalid Username or Password") def raceDataEntry(): cursor.execute("SELECT DISTINCT RaceID FROM StudentRaces WHERE TimeTaken IS NULL") undocumentedRaces = [row[0] for row in cursor.fetchall()] print(undocumentedRaces) print("You are about to input race times into the system") print("These are the races yet to be documented:") count = 0 for i in undocumentedRaces: cursor.execute(f"SELECT RaceName, RaceID FROM RaceInformation WHERE RaceID = '{i}'") for row in cursor.fetchall(): count += 1 print(str(count) + ") " + str(row[0])) print(f"Race id: {row[1]}") while True: try: userChoice = int(input("Which race do you want to input data for? Please input the race id.")) if userChoice in undocumentedRaces: break else: print("Invalid Answer") except: print("Invalid Answer") getStudentNames(userChoice) def getStudentNames(raceID): sql = "SELECT StudentID FROM StudentRaces WHERE RaceID = '"+str(raceID)+"'" cursor.execute(sql) conn.commit() studentNameList = cursor.fetchall() print("This is a list of all students who participated in this race.") for row in studentNameList: sql = "SELECT FirstName, LastName FROM StudentData WHERE StudentID = '"+str(row[0])+"'" cursor.execute(sql) conn.commit() for row in cursor.fetchall(): print(row) finalDataEntry(raceID) def finalDataEntry(raceID): cursor.execute("SELECT ui.UserID, sd.FirstName, sd.LastName FROM StudentData sd JOIN UserInformation ui ON sd.UserID = ui.UserID JOIN StudentRaces sr ON sd.StudentID = sr.StudentID WHERE sr.RaceID = %s", raceID) participants = cursor.fetchall() for participant in participants: userID, firstName, lastName = participant while True: race_time_str = input(f"Enter {firstName} {lastName}'s race time (mm:ss.msms): ") if re.match(r'^\d{2}:\d{2}\.\d{2}$', race_time_str): break else: print("Invalid format. Please use the format mm:ss.ll (e.g. 01:23.45).") raceTime = int(race_time_str[:2]) * 60 + int(race_time_str[3:5]) + float(race_time_str[6:]) / 100 cursor.execute("UPDATE StudentRaces SET TimeTaken = %s WHERE StudentID = (SELECT StudentID FROM StudentData WHERE UserID = %s) AND RaceID = %s", (raceTime, userID, raceID)) conn.commit() updateHousePoints(raceID, 1) def updateHousePoints(raceID, editTF): cursor.execute(f"SELECT CurrentRecord FROM RaceInformation WHERE RaceID = {raceID}") current_record = cursor.fetchone()[0] cursor.execute("SELECT StudentRaces.StudentID, StudentData.StudentHouse, StudentRaces.TimeTaken " f"FROM StudentRaces INNER JOIN StudentData ON StudentRaces.StudentID = StudentData.StudentID " f"WHERE StudentRaces.RaceID = {raceID}") results = cursor.fetchall() sorted_results = sorted(results, key=lambda x: x[2]) print(sorted_results) for i in range(len(sorted_results)): student_id, house, time_taken = sorted_results[i] if i == 0: cursor.execute(f"UPDATE EventData SET HousePoints = HousePoints + 20 WHERE HouseName = '{house}'") elif i == 1: cursor.execute(f"UPDATE EventData SET HousePoints = HousePoints + 15 WHERE HouseName = '{house}'") elif i == 2: cursor.execute(f"UPDATE EventData SET HousePoints = HousePoints + 10 WHERE HouseName = '{house}'") elif time_taken != '00:00.00': cursor.execute(f"UPDATE EventData SET HousePoints = HousePoints + 3 WHERE HouseName = '{house}'") best_time = sorted_results[0][2] if best_time < current_record: cursor.execute(f"UPDATE RaceInformation SET CurrentRecord = '{best_time}' WHERE RaceID = {raceID}") cursor.execute(f"SELECT StudentName FROM StudentData WHERE StudentID = '{sorted_results[0]}") print(f"'{cursor.fetchall()}' has set a new record!") conn.commit() if editTF != 0: createFinal(raceID) def createFinal(raceID): cursor.execute(f"SELECT FinalOrSemi FROM RaceInformation WHERE RaceID = '{raceID}'") for row in cursor.fetchall(): if row[0] == 0: print("A finals race will now be created.") else: print("This race has now concluded. You will now be redirected to the login page.") login() return cursor.execute("SELECT StudentRaces.StudentID, StudentRaces.TimeTaken " f"FROM StudentRaces INNER JOIN StudentData ON StudentRaces.StudentID = StudentData.StudentID " f"WHERE StudentRaces.RaceID = {raceID}") results = cursor.fetchall() sorted_results = sorted(results, key=lambda x: x[1]) print(sorted_results) maxEight = 0 finals = raceID + 1 finals = str(finals) for i in range(len(sorted_results)): if maxEight == 8: break maxEight += 1 student_id, time_taken = sorted_results[i] cursor.execute(f"INSERT INTO StudentRaces (StudentID, RaceID) VALUES ({student_id}, {finals})") conn.commit() def showInfo(currentUser): sql = "SELECT UserRole FROM UserInformation WHERE UserID = '"+str(currentUser)+"'" cursor.execute(sql) conn.commit() for row in cursor.fetchall(): if row[0] == "Teacher": teacherPage() else: studentPage(currentUser) def studentPage(studentUserID): print("You are logged in as a student.") sql = "SELECT FirstName FROM StudentData, UserInformation WHERE StudentData.UserID = '"+str(studentUserID)+"' AND UserInformation.UserID = StudentData.UserID" cursor.execute(sql) conn.commit() for row in cursor.fetchall(): print("Hello "+str(row[0])) showScore() def teacherPage(): print("You are logged in as a teacher.") showScore() while True: teacherInput = input("Do you want to input new race times or edit existing ones? Type nothing to be logged out.") print("1) Input New" "2) Edit Existing") if teacherInput == "1": raceDataEntry() elif teacherInput == "2": editTimes() elif teacherInput == "": print("You will now be logged out.") break else: print("Invalid input") print("You will now be logged out.") def editTimes(): cursor.execute("SELECT DISTINCT RaceID FROM StudentRaces WHERE TimeTaken IS NOT NULL") documentedRaces = [row[0] for row in cursor.fetchall()] print(documentedRaces) print("These are the races that have been documented:") count = 0 for i in documentedRaces: cursor.execute(f"SELECT RaceName, RaceID FROM RaceInformation WHERE RaceID = '{i}'") for row in cursor.fetchall(): count += 1 print(str(count) + ") " + str(row[0])) print(f"Race id: {row[1]}") while True: try: userChoice = int(input("Which race do you want to change data for? Please input the race id.")) if userChoice in documentedRaces: break else: print("Invalid Answer") except: print("Invalid Answer") newTimes(userChoice) def newTimes(raceID): cursor.execute("SELECT StudentData.FirstName, StudentData.LastName, StudentData.StudentHouse, StudentRaces.TimeTaken, StudentRaces.StudentID " f"FROM StudentRaces INNER JOIN StudentData ON StudentRaces.StudentID = StudentData.StudentID " f"WHERE StudentRaces.RaceID = {raceID}") studentIDs = [row[4] for row in cursor.fetchall()] print("These are the times the students got:") counter = 0 for row in cursor.fetchall(): counter += 1 print(str(counter) +f") {row[0]} {row[1]}: {row[3]}. Student ID = {row[4]}") while True: try: userChoice = int(input("Please select the ID of the student.")) if userChoice not in studentIDs: print("Invalid Input") else: break except: print("Invalid Input") cursor.execute("SELECT StudentRaces.StudentID, StudentData.StudentHouse, StudentRaces.TimeTaken " f"FROM StudentRaces INNER JOIN StudentData ON StudentRaces.StudentID = StudentData.StudentID " f"WHERE StudentRaces.RaceID = {raceID}") results = cursor.fetchall() sorted_results = sorted(results, key=lambda x: x[2]) print(sorted_results) for i in range(len(sorted_results)): student_id, house, time_taken = sorted_results[i] if i == 0: cursor.execute(f"UPDATE EventData SET HousePoints = HousePoints - 20 WHERE HouseName = '{house}'") elif i == 1: cursor.execute(f"UPDATE EventData SET HousePoints = HousePoints - 15 WHERE HouseName = '{house}'") elif i == 2: cursor.execute(f"UPDATE EventData SET HousePoints = HousePoints - 10 WHERE HouseName = '{house}'") elif time_taken != '00:00.00': cursor.execute(f"UPDATE EventData SET HousePoints = HousePoints - 3 WHERE HouseName = '{house}'") while True: race_time_str = input(f"Enter the student's new race time (mm:ss.msms): ") if re.match(r'^\d{2}:\d{2}\.\d{2}$', race_time_str): break else: print("Invalid format. Please use the format mm:ss.ll (e.g. 01:23.45).") raceTime = int(race_time_str[:2]) * 60 + int(race_time_str[3:5]) + float(race_time_str[6:]) / 100 cursor.execute(f"UPDATE StudentRaces SET TimeTaken = {raceTime} WHERE StudentID = '{userChoice}'") conn.commit() updateHousePoints(raceID, 0) def showScore(): print("Current Standings:") sql = "SELECT HouseName, HousePoints From EventData" cursor.execute(sql) conn.commit() for row in cursor.fetchall(): print(row)