Untitled
unknown
python
2 years ago
11 kB
7
Indexable
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)Editor is loading...