def newGame(): cnx = cs411_db.getConnection() cursor = cnx.cursor() cursor.callproc('SP_Generate_Categories_PROD', []) for result in cursor.stored_results(): for r in result: resultId = r[0] cursor.close() cnx.commit() cnx.close() # Randomly select 3 contestants to play in the game # In the future, front-end should provide this cnx = cs411_db.getConnection() #cursor = cnx.cursor() #query = """INSERT INTO Game_Contestants (Games_Game_ID, Contestants_Contestant_ID) # SELECT {0} AS Games_Game_ID, Contestant_ID AS Contestants_Contestant_ID # FROM Contestants # WHERE Contestant_ID = 1000""".format(resultId) #cursor.execute(query) #cursor.close() #cnx.commit() cursor = cnx.cursor() query = """INSERT INTO Game_Contestants (Games_Game_ID, Contestants_Contestant_ID) SELECT {0} AS Games_Game_ID, Contestant_ID AS Contestants_Contestant_ID FROM Contestants ORDER BY RAND() LIMIT 1""".format(resultId) cursor.execute(query) cursor.close() cnx.commit() cursor = cnx.cursor() query = """SELECT Contestants_Contestant_ID FROM Game_Contestants WHERE Games_Game_ID = {0}""".format(resultId) cursor.execute(query) results = [] for row in cursor: results.append(row[0]) cnx.close() gQ = getQuestions(resultId) gQ["Contestants"] = results return gQ
def validateLogin(username, password): """Returns None on failure, otherwise a dictionary of an authenticated user.""" conn = cs411_db.getConnection() curs = conn.cursor() # This syntax prevents SQL injection attacks on username and password # TODO: Matt B change trigger to return these attributes instead of success/fail query = """SELECT Create_Date, Account_Admin, UFirst_Name, ULast_Name, Email, User_ID FROM Users WHERE Account_Active = 'Y' AND User_ID = %(username)s AND Password = %(password)s""" curs.execute(query, {"username": username, "password": password}) result = curs.fetchone() curs.close() conn.close() if result is None: return None else: print(result) return { "Create_Date": result[0], "Account_Admin": result[1].decode(), "UFirst_Name": result[2].decode(), "ULast_Name": result[3].decode(), "Email": result[4].decode(), "User_ID": result[5].decode() }
def executeDeletions(): cnx = cs411_db.getConnection() cursor = cnx.cursor() cursor.callproc('SP_Delete_All_Game_Records', []) for rows in cursor.stored_results(): for r in rows: result = r[0] print(result) cursor.close() cnx.commit() cnx.close() return { "message": "success", "gamesDeleted": None }
def getProposedDeletes(): cnx = cs411_db.getConnection() cursor = cnx.cursor() cursor.callproc('SP_Game_DELETE_Candidates_Prod', []) results = [] for rows in cursor.stored_results(): for r in rows: result = {"Game_ID": r[0], "Game_Date": r[1]} results.append(result) cursor.close() cnx.commit() cnx.close() return results
def randomAnswer(): """Returns a questionID, Category and Answer at random for testing.""" conn = cs411_db.getConnection() curs = conn.cursor() # Check that the user name is not already taken. query = """SELECT Question_ID, Category, Question_Text FROM Questions ORDER BY RAND() LIMIT 1""" curs.execute(query) result = curs.fetchone() curs.close() conn.close() (id, category, answer) = (result[0], result[1].decode(), result[2].decode()) return {"Question_ID": id, "Category": category, "Answer": answer }
def richContestants(): cnx = cs411_db.getConnection() cursor = cnx.cursor() query = """SELECT CONCAT(CFirst_Name, " ", CLast_Name) AS Name, Winnings AS "Total Amount of Winnings" FROM Contestants_RPT WHERE Contestant_ID <> 1000 ORDER BY 2 DESC LIMIT 10""" cursor.execute(query) results = {} for row in cursor: results[row[0].decode()] = row[1] cnx.close() return results
def gameEnd(gameID, contestantID): # Randomly select winning contestant and update Game table to reflect cnx = cs411_db.getConnection() cursor = cnx.cursor() query = """UPDATE Games SET Contestants_Contestant_ID_Winner = {0}, Game_End_Date = Now() WHERE Game_ID = {1}""".format(contestantID, gameID) print(query) cursor.execute(query) r = cursor.rowcount cnx.commit() cursor.close() cnx.close() return r
def dumbContestants(): cnx = cs411_db.getConnection() cursor = cnx.cursor() query = """SELECT CONCAT(CFirst_Name, " ", CLast_Name) AS Name, COUNT(*) AS "Number of Incorrect Guesses" FROM Contestants_RPT,`Game_Answers_RPT` WHERE Contestants_RPT.Contestant_ID = Game_Answers_RPT.Contestant_Contestant_ID AND Game_Answers_RPT.ContestantAnswer = "WRONG" AND Contestant_Contestant_ID <> 1000 GROUP BY `Contestant_Contestant_ID` LIMIT 10""" cursor.execute(query) results = {} for row in cursor: results[row[0].decode()] = row[1] cnx.close() return results
def easyCategory(): cnx = cs411_db.getConnection() cursor = cnx.cursor() query = """SELECT Game_Questions_RPT.Category, COUNT(Category) AS "Correct Answers" FROM Game_Questions_RPT, Game_Answers_RPT WHERE Game_Questions_RPT.Game_Questions_ID = Game_Answers_RPT.GameQuestions_ID AND ContestantAnswer = "CORRECT" GROUP BY Category ORDER BY 2 DESC LIMIT 10""" cursor.execute(query) results = {} for row in cursor: results[row[0].decode()] = row[1] cnx.close() return results
def submitAnswer(Games_Game_ID, GameQuestions_ID, Contestant_Coontestant_ID, questionGuess): """Submits the guessed answer for a given ID. Return a dictionary containing: status: 'Y' for successful writing to database, 0 otherwise message: The reason the update to database failed if applicapable Games_Game_ID: The ID of the Game. (You passed this in) GameQuestions_ID: The ID of the Question asked. (You passed this in) Contestant_Contestant_ID: The ID of the Contestant. (You passed this in) CorrecAnswer: The actual correct question according to the archives. ConsideredCorrect: a Y or N that if the question is considered correct. """ check = checkAnswer2(GameQuestions_ID, questionGuess) conn = cs411_db.getConnection() curs = conn.cursor() test = curs.callproc("SP_Insert_Answer", (GameQuestions_ID, Contestant_Coontestant_ID, questionGuess, check["CorrectQuestion"], Games_Game_ID)) success = 0 message = "This code has not executed" for result in curs.stored_results(): for r in result: message = r[0] curs.close() conn.commit() if r[0] == 1: success = 1 message = "" else: success = 0 result = { "status": success, "message": message, "Games_Game_ID": Games_Game_ID, "GameQuestions_ID": GameQuestions_ID, "Contestant_Coontestant_ID": Contestant_Coontestant_ID, "CorrectAnswer": check["CorrectQuestion"], "ConsideredCorrect": check["ConsideredCorrect"] } return result
def interactiveDebug(): conn = cs411_db.getConnection() curs = conn.cursor() # Check that the user name is not already taken. query = """SELECT Question_ID, Category, Question_Text, Question_Answer FROM Questions ORDER BY RAND() LIMIT 1""" curs.execute(query) result = curs.fetchone() (id, category, answer, question) = (result[0], result[1].decode(), result[2].decode(), result[3].decode()) curs.close() conn.close() print("CATEGORY {0} ... The answer is: {1}".format(category, answer)) yourGuess = input("What is the Question? ") question = textCleaning(question) yourGuess = textCleaning(yourGuess) print("Your guess scored: {0}\n".format(answerCorrect(question, yourGuess))) print("The right answer was {0}, but you answered {1}.\n".format(str(question),str(yourGuess)))
def fakeUpdate(gameID): # Randomly select winning contestant and update Game table to reflect cnx = cs411_db.getConnection() cursor = cnx.cursor() query = """UPDATE Games SET Contestants_Contestant_ID_Winner = ( SELECT Contestants_Contestant_ID FROM Game_Contestants WHERE Games_Game_ID = {0} ORDER BY RAND() LIMIT 1), Game_End_Date = Now() WHERE Game_ID = {0}""".format(gameID) print(query) cursor.execute(query) r = cursor.rowcount cnx.commit() cursor.close() cnx.close() return r
def changePassword(username, password, newpassword): """Returns 0 on failure or a 1 on a success.""" conn = cs411_db.getConnection() curs = conn.cursor() query = """UPDATE Users SET Password = %(newpassword)s WHERE Account_Active = 'Y' AND User_ID = %(username)s AND Password = %(password)s""" curs.execute(query, { "username": username, "password": password, "newpassword": newpassword }) conn.commit() affected_rows = curs.rowcount curs.close() conn.close() return affected_rows
def registerUser(username, password, email, first, last): """Returns a tuple (SuccessfulRegisteration, Dictionary). The first element of the tuple is False if registration failed for any reason. The reason why is in the dictionary under key "message". The first element of the tuple is True if registration was successful. The dictionary contains the authenticated user information. """ conn = cs411_db.getConnection() curs = conn.cursor() # Check that the user name is not already taken. query = """SELECT EXISTS( SELECT * FROM Users WHERE LCASE(User_ID) = %(username)s ) AS usercheck""" curs.execute(query, {"username": username.lower()}) result = curs.fetchone() if result[0] == 1: return (False, { "message": "User ID ``{0}'' not available".format(username) }) # Check that the email is not alreay in use. query = """SELECT EXISTS( SELECT * FROM Users WHERE LCASE(Email) = %(email)s ) AS usercheck""" curs.execute(query, {"email": email.lower()}) result = curs.fetchone() if result[0] == 1: return (False, { "message": "Email ``{0}'' already used by another user.".format(email) }) # Create the user curs.callproc("SP_Insert_Users", (username, password, first, last, email)) curs.close() conn.commit() conn.close() return (True, validateLogin(username, password))
def getGames(): conn = cs411_db.getConnection() cursor = conn.cursor() query = ("""SELECT Game_ID, Gamescol, Game_Date, Game_End_Date, Contestants_Contestant_ID_Winner, GameCreation_Type, GameCreation_Options FROM Games""") cursor.execute(query) results = [] for game in cursor: gameDict = { "Game_ID": game[0], "Gamescol": game[1], "Game_Date": game[2], "Game_End_Date": game[3], "Contestants_Contestant_ID_Winner": game[4], "GameCreation_Type": game[5], "GameCreation_Options": game[6] } results.append(gameDict) cursor.close() conn.close() return results
def checkAnswer2(questionID, questionGuess): """Checks the guessed answer for a given ID. Return a dictionary containing: Question_ID: The ID of the Question asked. (You passed this in) CorrectQuestion: The actual correct question according to the archives. ParsedQuestion: The actual correct question ran through text cleaning. YourGuess: The text of the user guess (You passed this in) ParsedGuess: The text of the user guess ran through the text cleaning. CorrectGuess: A score between 0 and 1 that indicates how correct your guess is. ConsideredCorrect: a Y or N that removes any doubt about whether the question should be considered correct. """ conn = cs411_db.getConnection() curs = conn.cursor() query = """SELECT Answer_Text FROM Game_Questions WHERE Game_Questions_ID = {0}""" .format(questionID) curs.execute(query) result = curs.fetchone() print(result) if result != None: question = result[0].decode() else: question = "Question with ID {0} does not exist in the database.".format(questionID) curs.close() conn.close() questionParsed = textCleaning(question) questionGuessParsed = textCleaning(questionGuess) score = answerCorrect(questionParsed, questionGuessParsed) correct = (score >= 0.5) result = { "Question_ID": questionID, "CorrectQuestion": question, "ParsedQuestion": str(questionParsed), "YourGuess": questionGuess, "ParsedGuess": questionGuessParsed, "Correctness": score, "ConsideredCorrect": 'Y' if correct else 'N' } return result
def getQuestions(questionID): conn = cs411_db.getConnection() cursor = conn.cursor() query = ("""SELECT Question_Text, Category, Value, Round, Game_Questions_ID FROM Game_Questions WHERE Games_Game_ID={}""".format(questionID)) cursor.execute(query) answers = {} # { "GAMES": [ {question: "This game has a queen and king", answer: "Chess"}, .... ] for tup in cursor: print(tup) (qid, category, question, value, rnd) = (tup[4], tup[1].decode(), tup[0].decode(), tup[2], tup[3].decode()) t = {"question_id": qid, "question": question, "round": rnd, "value": value} if category in answers: answers[category].append(t) else: answers[category] = [t] result = { "Games_Game_ID": questionID, "categories": [], "round1": [], "round2": [], "final": [], "questions": answers } print(answers) for c in answers.keys(): print(c) result["categories"].append(c) if (answers[c][0]["round"] == '1'): result["round1"].append(c) elif (answers[c][0]["round"] == '2'): result["round2"].append(c) else: result["final"] = c new_round_1 = [] for cat in result["round1"]: r = { "category": cat, "questions": [] } for q in result["questions"][cat]: r["questions"].append(q) new_round_1.append(r) new_round_2 = [] for cat in result["round2"]: r = { "category": cat, "questions": [] } for q in result["questions"][cat]: r["questions"].append(q) new_round_2.append(r) print(result["final"]) print(result["questions"]) new_final = { "category": result["final"], "questions": [ result["questions"][result["final"]] ] } new_result = { "Games_Game_ID": questionID, "round1": new_round_1, "round2": new_round_2, "final": new_final } cursor.close() conn.close() print(new_result) return new_result