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
