def is_valid(ballot_id): with get_cnx() as db: cursor = db.cursor() cursor.execute( f""" SELECT COUNT(*) FROM {ballot_table} B INNER JOIN {scores_table} S ON B.id = S.ballot_id WHERE B.id = %s """, (ballot_id,) ) (num,) = cursor.fetchone() assert(num == 28) cursor.execute( f""" SELECT COUNT(*) FROM {ballot_table} B INNER JOIN {ranks_table} R ON B.id = R.ballot_id WHERE B.id = %s """, (ballot_id,) ) (ranks_cnt,) = cursor.fetchone()
def _get_info_for_exam(matchup_id: int, side: str, order: int): with get_cnx() as db: cursor = db.cursor() cursor.execute( f""" SELECT witness_id, attorney_id, crossing_id, witness_name FROM {examination_table} WHERE matchup_id = %s AND side = %s AND `order` = %s """, (matchup_id, side, order), ) try: (witness, attorney, crosser, name) = cursor.fetchone() return { "witness": witness, "attorney": attorney, "crosser": crosser, "name": name, } except: return { "witness": None, "attorney": None, "crosser": None, "name": None, }
def get_exam_section(ballot_id, side, exam_num, role, exam_type): with get_cnx() as db: cursor = db.cursor() sql_attorney = BallotSections._sql_attorney(role) sql_cross = BallotSections._sql_cross(exam_type) sql_pl = BallotSections._sql_pl(side) cursor.execute( f""" SELECT id, score, note FROM {exam_data_table} WHERE ballot_id = %s AND exam_num = %s AND attorney = %s AND `cross` = %s AND pl = %s """, (ballot_id, exam_num, sql_attorney, sql_cross, sql_pl), ) results = cursor.fetchall() if len(results) == 0: return None for (score_id, score, note) in results: pass return {"id": score_id, "score": score, "notes": note}
def get_speech_section(ballot_id: int, side: Literal["pl", "def"], speech: Literal["open", "close"]): with get_cnx() as db: cursor = db.cursor() sql_pl = BallotSections._sql_pl(side) sql_open = BallotSections._sql_opening(speech) cursor.execute( f""" SELECT score, note, id FROM {speech_data_table} WHERE ballot_id = %s AND pl = %s AND opening = %s """, ( ballot_id, sql_pl, sql_open, ), ) result = cursor.fetchone() if result is None: return None score, note, score_id = result return {"score": score, "notes": note, "id": score_id}
def get_student(student_id): with get_cnx() as db: cursor = db.cursor() cursor.execute( f"SELECT student_name FROM {student_table} WHERE id = %s", (student_id, )) return {"name": cursor.fetchone()[0]}
def add_school(tournament_id: int, name: str): with get_cnx() as db: cursor = db.cursor() cursor.execute( f"INSERT INTO {school_table} (tournament_id, name) VALUES (%s, %s)", (tournament_id, name), ) db.commit()
def get_is_complete(ballot_id): with get_cnx() as db: cursor = db.cursor() cursor.execute( f"SELECT complete FROM {ballot_table} WHERE id = %s", (ballot_id,) ) (complete,) = cursor.fetchone() return complete == 1
def get_conflicts(tournament_id: int, id: int): with get_cnx() as db: cursor = db.cursor() cursor.execute( f"SELECT school_name FROM {conflict_table} WHERE judge_id = %s", (id, )) conflicts = [name for (name, ) in cursor.fetchall()] return conflicts
def get_ballots(tournament_id: int, judge_id: int): with get_cnx() as db: cursor = db.cursor() cursor.execute( f"SELECT id FROM {ballots_table} WHERE judge_id = %s", (judge_id, )) ballot_ids = [b_id for (b_id, ) in cursor.fetchall()] return ballot_ids
def get_ballots(matchup_id: int): with get_cnx() as db: cursor = db.cursor() cursor.execute( f"SELECT id FROM {ballots_table} WHERE matchup_id = %s", (matchup_id, )) ballot_ids = [bid for (bid, ) in cursor.fetchall()] return ballot_ids
def create_tournament(name): with get_cnx() as db: cursor = db.cursor() cursor.execute( f"INSERT INTO {tournament_table} (name) VALUES (%s)", (name, )) db.commit() return cursor.lastrowid
def get_all_tournaments(): with get_cnx() as db: cursor = db.cursor() cursor.execute(f"SELECT * FROM {tournament_table}") tournaments = [] for (id, name) in cursor.fetchall(): tournaments.append({"id": id, "name": name}) return tournaments
def add_student(tournament_id, team_num, name): with get_cnx() as db: cursor = db.cursor() cursor.execute( f"INSERT INTO {student_table} (tournament_id, team_num, student_name) VALUES (%s, %s, %s)", (tournament_id, team_num, name), ) db.commit() return cursor.lastrowid
def delete_tournament(id): with get_cnx() as db: cursor = db.cursor() cursor.execute( f"DELETE FROM {tournament_table} WHERE tournament_id = %s", (id, )) db.commit() return True
def get_judge(tournament_id: int, id: int): with get_cnx() as db: cursor = db.cursor() cursor.execute( f"SELECT tournament_id, id, name FROM {judge_table} WHERE id = %s", (id, ), ) tourn_id, judge_id, name = cursor.fetchone() return {"id": judge_id, "name": name, "tournament_id": tourn_id}
def add_judge(tournament_id: int, name: str): with get_cnx() as db: cursor = db.cursor() cursor.execute( f"INSERT INTO {judge_table} (tournament_id, name) VALUES (%s, %s)", (tournament_id, name), ) db.commit() return cursor.lastrowid
def assign_role(tournament_id, matchup_id, team_num, student_id, role): with get_cnx() as db: cursor = db.cursor() cursor.execute( f"INSERT INTO {role_table} (tournament_id, matchup_id, team_num, student_id, role) VALUES (%s, %s, %s, %s, %s)", (tournament_id, matchup_id, team_num, student_id, role), ) db.commit() return cursor.lastrowid
def add_conflict(tournament_id: int, id: int, school: str): with get_cnx() as db: cursor = db.cursor() cursor.execute( f"INSERT INTO {conflict_table} (tournament_id, judge_id, school_name) VALUES (%s, %s, %s)", (tournament_id, id, school), ) db.commit() return cursor.lastrowid
def get_all_rounds(tournament_id: int): with get_cnx() as db: cursor = db.cursor() cursor.execute( f"SELECT DISTINCT round_num FROM {matchup_table} WHERE tournament_id = %s", (tournament_id, ), ) rounds = [num for (num, ) in cursor.fetchall()] return rounds
def get_all_info_for_tournament(id): with get_cnx() as db: cursor = db.cursor() cursor.execute( f"SELECT tournament_id, name FROM {tournament_table} WHERE tournament_id = %s", (id, ), ) (id, name) = cursor.fetchone() return {"id": id, "name": name}
def set_email(judge_id: int, email: str): with get_cnx() as db: cursor = db.cursor() cursor.execute( f""" UPDATE {judge_table} SET email = %s WHERE id = %s """, (email, judge_id), ) db.commit()
def set_is_complete(ballot_id, complete): with get_cnx() as db: cursor = db.cursor() cursor.execute( f""" UPDATE {ballot_table} SET complete = {1 if complete else 0} WHERE id = %s """, (ballot_id,), ) db.commit()
def set_score_only(ballot_id, score_only): with get_cnx() as db: cursor = db.cursor() cursor.execute( f""" UPDATE {ballot_table} SET note_only = %s WHERE id = %s """, (Ballot._bool_to_SQL(score_only), ballot_id), ) db.commit()
def get_teams_for_school(tournament_id: int, name: str): with get_cnx() as db: cursor = db.cursor() cursor.execute( f"SELECT team_num, name FROM {team_table} WHERE tournament_id = %s AND school_name = %s", (tournament_id, name), ) teams = [] for team_num, name in cursor.fetchall(): teams.append({"num": team_num, "name": name}) return teams
def _set_section_note(section_id, note, db_cnx=None): with get_cnx() as db: cursor = db.cursor() cursor.execute( f""" UPDATE {score_table} SET note = %s WHERE id = %s """, (note, section_id), ) db.commit()
def _set_section_score(section_id: int, new_score: int): with get_cnx() as db: cursor = db.cursor() cursor.execute( f""" UPDATE {score_table} SET score = %s WHERE id = %s """, (new_score, section_id), ) db.commit()
def _add_single_section(ballot_id, side): with get_cnx() as db: cursor = db.cursor() cursor.execute( f""" INSERT INTO {score_table} (ballot_id, pl) VALUES (%s, %s) """, (ballot_id, BallotSections._sql_pl(side)), ) db.commit() return cursor.lastrowid
def set_rank_for_ballot(ballot_id, witness: bool, rank, student): with get_cnx() as db: cursor = db.cursor() cursor.execute( f""" INSERT INTO {ranks_table} (ballot_id, rank, witness, student) VALUES (%s, %s, %s, %s) ON DUPLICATE KEY UPDATE student = %s """, (ballot_id, rank, Ballot._bool_to_SQL(witness), student, student), ) db.commit()
def assign_witness_name(matchup_id: int, side: str, order: int, witness_name: str): with get_cnx() as db: cursor = db.cursor() cursor.execute( f""" INSERT INTO {examination_table} (matchup_id, side, `order`, witness_name) VALUES (%s, %s, %s, %s) ON DUPLICATE KEY UPDATE witness_name = %s """, (matchup_id, side, order, witness_name, witness_name), ) db.commit()
def assign_attorney_to_cross(matchup_id: int, side: str, order: int, student_id: int): with get_cnx() as db: cursor = db.cursor() cursor.execute( f""" INSERT INTO {examination_table} (matchup_id, side, `order`, crossing_id) VALUES (%s, %s, %s, %s) ON DUPLICATE KEY UPDATE crossing_id = %s """, (matchup_id, side, order, student_id, student_id), ) db.commit()