def get_total_teams(self): """Method to get team count from database Args: None Return: count (int): total number of teams Raises: data_manager_exceptions.DBConnectionError data_manager_exceptions.DBSyntaxError """ try: LOGGER.info("Getting total team count") self.check_if_db_connected() cursor = self.db_conn.cursor() cursor.execute("SELECT COUNT(team_id) FROM team") count = cursor.fetchone()[0] except MySQLdb.OperationalError: LOGGER.error("MySQL operational error occured") traceback.print_exc() raise data_manager_exceptions.DBConnectionError("Cannot connect \ to MySQL server") except MySQLdb.ProgrammingError: LOGGER.error("MySQL programming error") traceback.print_exc() raise data_manager_exceptions.DBSyntaxError("MySQL syntax error") else: return count
def get_all_teams(self): """Method to get all teams from database Args: None Returns: teams (tup): tuple of team tuples Raises: data_manager_exceptions.DBConnectionError data_manager_exceptions.DBSyntaxError """ all_teams = () try: LOGGER.info("Getting team list") self.check_if_db_connected() cursor = self.db_conn.cursor() cursor.execute("SELECT team_id, team_name FROM team ORDER BY \ time DESC") teams = cursor.fetchall() for team_id, name in teams: intermediate_teams = () intermediate_teams = intermediate_teams + (name,) cursor.execute("SELECT player FROM player_team_xref WHERE \ team = {0}".format(team_id)) players = cursor.fetchall() if len(players) != 2: raise data_manager_exceptions.DBValueError("Found more \ than two players per team") for player in players: cursor.execute("SELECT first_name, last_name, nickname \ FROM player WHERE player_id = {0}".format(player[0])) first_name, last_name, nickname = cursor.fetchall()[0] intermediate_teams = intermediate_teams + (first_name, last_name, nickname) all_teams = all_teams + (intermediate_teams,) del intermediate_teams except MySQLdb.OperationalError: LOGGER.error("MySQL operational error occured") traceback.print_exc() raise data_manager_exceptions.DBConnectionError("Cannot connect \ to MySQL server") except MySQLdb.ProgrammingError: LOGGER.error("MySQL programming error") traceback.print_exc() raise data_manager_exceptions.DBSyntaxError("MySQL syntax error") else: return all_teams
def check_if_players_on_team(self, member_one, member_two): """Method to check if two players are already on a team Args: player_one (tup): player one player_two (tup): player two Returns: (bool): True/False Raises: data_manager_exceptions.DBConnectionError data_manager_exceptions.DBSyntaxError """ try: LOGGER.info("Checking if players are already on team") self.check_if_db_connected() cursor = self.db_conn.cursor() cursor.execute("SELECT player_id FROM player WHERE \ first_name = '{0}' AND last_name = '{1}' AND nickname = '{2}'".format( member_one[0], member_one[1], member_one[2])) player_one_id = cursor.fetchone()[0] cursor.execute("SELECT player_id FROM player WHERE \ first_name = '{0}' AND last_name = '{1}' AND nickname = '{2}'".format( member_two[0], member_two[1], member_two[2])) player_two_id = cursor.fetchone()[0] cursor.execute("SELECT team FROM player_team_xref WHERE \ player = {0}".format(player_one_id)) teams = cursor.fetchall() for team in teams: cursor.execute("SELECT player FROM player_team_xref WHERE \ team = {0}".format(team[0])) players = cursor.fetchall() for player in players: if player[0] == player_two_id: return team[0] except MySQLdb.OperationalError: LOGGER.error("MySQL operational error occured") traceback.print_exc() raise data_manager_exceptions.DBConnectionError("Cannot connect \ to MySQL server") except MySQLdb.ProgrammingError: LOGGER.error("MySQL programming error") traceback.print_exc() raise data_manager_exceptions.DBSyntaxError("MySQL syntax error") else: return False
def add_player(self, first_name, last_name, nickname): """Method to add a player to the database Args: first_name (str): player first name last_name(str): player last name nickname (str): player nickname Raises: data_manager_exceptions.DBValueError data_manager_exceptions.DBExistError data_manager_exceptions.DBConnectionError data_manager_exceptions.DBSyntaxError """ if len(first_name) is 0: raise data_manager_exceptions.DBValueError("First name must be at \ least one character") if len(last_name) is 0: raise data_manager_exceptions.DBValueError("Last name must be at \ least one character") try: if not self.check_if_player_exists(first_name=first_name, last_name=last_name, nickname=nickname): raise data_manager_exceptions.DBExistError("Name already \ exists in database") offense_rating_id = self.add_rating() defense_rating_id = self.add_rating() self.check_if_db_connected() cursor = self.db_conn.cursor() LOGGER.info("Adding player to database") cursor.execute("INSERT INTO player (first_name, last_name, \ nickname, offense_rating, defense_rating) VALUES ('{0}', '{1}', '{2}', \ {3}, {4})".format(first_name, last_name, nickname, offense_rating_id, defense_rating_id)) except MySQLdb.OperationalError: LOGGER.error("MySQL operational error occured") traceback.print_exc() raise data_manager_exceptions.DBConnectionError("Cannot connect \ to MySQL server") except MySQLdb.ProgrammingError: LOGGER.error("MySQL programming error") traceback.print_exc() raise data_manager_exceptions.DBSyntaxError("MySQL syntax error") else: pass
def check_if_player_exists(self, first_name, last_name, nickname): """Method to check if player currently exists in database Args: first_name (str): player first name last_name(str): player last name nickname (str): player nickname Returns: (bool): True/False if player exists Raises: data_manager_exceptions.DBConnectionError data_manager_exceptions.DBSyntaxError """ try: LOGGER.info("Checking if player already exists") LOGGER.debug("Player parameters:\n\ first name: {0}\n\ last name: {1}\n\ nickname: {2}".format(first_name, last_name, nickname)) self.check_if_db_connected() cursor = self.db_conn.cursor() cursor.execute("SELECT first_name, last_name, nickname FROM player") players = cursor.fetchall() for existing_first_name, existing_last_name, existing_nickname in \ players: if (first_name == existing_first_name) and (last_name == existing_last_name) and (nickname == existing_nickname): return False except MySQLdb.OperationalError: LOGGER.error("MySQL operational error occured") traceback.print_exc() raise data_manager_exceptions.DBConnectionError("Cannot connect \ to MySQL server") except MySQLdb.ProgrammingError: LOGGER.error("MySQL programming error") traceback.print_exc() raise data_manager_exceptions.DBSyntaxError("MySQL syntax error") else: return True
def edit_player(self, previous_player, new): """TODO""" if len(new['first_name']) is 0: raise data_manager_exceptions.DBValueError("First name must be at \ least one character") if len(new['last_name']) is 0: raise data_manager_exceptions.DBValueError("Last name must be at \ least one character") if not self.check_if_player_exists(**new): raise data_manager_exceptions.DBExistError("Name you are trying to change to already \ exists in database") self.check_if_db_connected() cursor = self.db_conn.cursor() LOGGER.info('Editing player') sql_params = dict(previous_player.items() + new.items()) sql = """UPDATE player SET first_name='{first_name}', last_name='{last_name}', nickname='{nickname}' WHERE first_name='{previous_first_name}' AND last_name='{previous_last_name}' AND nickname='{previous_nickname}';""".format(**sql_params) LOGGER.info(sql) try: cursor.execute(sql) except MySQLdb.OperationalError: LOGGER.error("MySQL operational error occured") traceback.print_exc() raise data_manager_exceptions.DBConnectionError("Cannot connect \ to MySQL server") except MySQLdb.ProgrammingError: LOGGER.error("MySQL programming error") traceback.print_exc() raise data_manager_exceptions.DBSyntaxError("MySQL syntax error") else: pass
def check_if_team_exists(self, team_name): """Method to check if team currently exists in database Args: team_name (str): team name Returns: (bool): True/False if team exists Raises: data_manager_exceptions.DBConnectionError data_manager_exceptions.DBSyntaxError """ try: LOGGER.info("Checking if team already exists") LOGGER.debug("Team parameters:\nTeam name: {0}".format(team_name)) self.check_if_db_connected() cursor = self.db_conn.cursor() cursor.execute("SELECT team_name FROM team") teams = cursor.fetchall() for team in teams: if team == team_name: return False except MySQLdb.OperationalError: LOGGER.error("MySQL operational error occured") traceback.print_exc() raise data_manager_exceptions.DBConnectionError("Cannot connect \ to MySQL server") except MySQLdb.ProgrammingError: LOGGER.error("MySQL programming error") traceback.print_exc() raise data_manager_exceptions.DBSyntaxError("MySQL syntax error") else: return True
def add_rating(self): """Method to add a new rating to the database Args: None Return: rating_id (int): rating_id for rating just created Raises: data_manager_exceptions.DBConnectionError data_manager_exceptions.DBSyntaxError """ try: LOGGER.info("Creating new rating") new_rating = trueskill.Rating() self.check_if_db_connected() cursor = self.db_conn.cursor() cursor.execute("INSERT INTO rating (mu, sigma) VALUES ({0}, {1}\ )".format(new_rating.mu, new_rating.sigma)) rating_id = cursor.lastrowid except MySQLdb.OperationalError: LOGGER.error("MySQL operational error occured") traceback.print_exc() raise data_manager_exceptions.DBConnectionError("Cannot connect \ to MySQL server") except MySQLdb.ProgrammingError: LOGGER.error("MySQL programming error") traceback.print_exc() raise data_manager_exceptions.DBSyntaxError("MySQL syntax error") else: return rating_id
def get_all_players(self): """Method to get all players from database Args: None Returns: players (tup): tuple of player tuples Raises: data_manager_exceptions.DBConnectionError data_manager_exceptions.DBSyntaxError """ try: LOGGER.info("Getting player list") self.check_if_db_connected() cursor = self.db_conn.cursor() cursor.execute("SELECT first_name, last_name, nickname FROM player \ ORDER BY time DESC") players = cursor.fetchall() except MySQLdb.OperationalError: LOGGER.error("MySQL operational error occured") traceback.print_exc() raise data_manager_exceptions.DBConnectionError("Cannot connect \ to MySQL server") except MySQLdb.ProgrammingError: LOGGER.error("MySQL programming error") traceback.print_exc() raise data_manager_exceptions.DBSyntaxError("MySQL syntax error") else: return players
def add_result(self, offense_winner, defense_winner, offense_loser, defense_loser): """Method to add a result to database Args: offense_winner(tup): offense_winner defense_winner (tup): defense_winner offense_loser (tup): offense_loser defense_loser (tup): defense_loser Raises: data_manager_exceptions.DBValueError data_manager_exceptions.DBExistError data_manager_exceptions.DBConnectionError data_manager_exceptions.DBSyntaxError """ if len(offense_winner) != 3: raise data_manager_exceptions.DBValueError("Offense winner must\ be complete") if len(defense_winner) != 3: raise data_manager_exceptions.DBValueError("Defense winner must\ be complete") if len(offense_loser) != 3: raise data_manager_exceptions.DBValueError("Offense loser must\ be complete") if len(defense_loser) != 3: raise data_manager_exceptions.DBValueError("Defense loser must\ be complete") try: LOGGER.info("Adding result to database") self.check_if_db_connected() cursor = self.db_conn.cursor() cursor.execute("INSERT INTO result (offense_winner, \ defense_winner, offense_loser, defense_loser) VALUES ((SELECT \ player_id FROM player WHERE first_name = '{0}' AND last_name \ = '{1}' AND nickname = '{2}'), (SELECT player_id FROM player WHERE first_name \ = '{3}' AND last_name = '{4}' AND nickname = '{5}'), (SELECT player_id FROM \ player WHERE first_name = '{6}' AND last_name = '{7}' AND nickname = '{8}'), \ (SELECT player_id FROM player WHERE first_name = '{9}' AND last_name = '{10}' \ AND nickname = '{11}'))".format(offense_winner[0], offense_winner[1], offense_winner[2], defense_winner[0], defense_winner[1], defense_winner[2], offense_loser[0], offense_loser[1], offense_loser[2], defense_loser[0], defense_loser[1], defense_loser[2])) LOGGER.info("Updating individual ratings") cursor.execute("SELECT player_id, offense_rating FROM player \ WHERE first_name = '{0}' AND last_name = '{1}' AND nickname = '{2}'".format( offense_winner[0], offense_winner[1], offense_winner[2])) offense_winner_player_id, rating = cursor.fetchall()[0] cursor.execute("SELECT mu, sigma FROM rating WHERE rating_id \ = {0}".format(rating)) mu, sigma = cursor.fetchall()[0] offense_winner_rating = trueskill.Rating(mu=float(mu), sigma=float(sigma)) cursor.execute("SELECT player_id, defense_rating FROM player \ WHERE first_name = '{0}' AND last_name = '{1}' AND nickname = '{2}'".format( defense_winner[0], defense_winner[1], defense_winner[2])) defense_winner_player_id, rating = cursor.fetchall()[0] cursor.execute("SELECT mu, sigma FROM rating WHERE rating_id \ = {0}".format(rating)) mu, sigma = cursor.fetchall()[0] defense_winner_rating = trueskill.Rating(mu=float(mu), sigma=float(sigma)) cursor.execute("SELECT player_id, offense_rating FROM player \ WHERE first_name = '{0}' AND last_name = '{1}' AND nickname = '{2}'".format( offense_loser[0], offense_loser[1], offense_loser[2])) offense_loser_player_id, rating = cursor.fetchall()[0] cursor.execute("SELECT mu, sigma FROM rating WHERE rating_id \ = {0}".format(rating)) mu, sigma = cursor.fetchall()[0] offense_loser_rating = trueskill.Rating(mu=float(mu), sigma=float(sigma)) cursor.execute("SELECT player_id, defense_rating FROM player \ WHERE first_name = '{0}' AND last_name = '{1}' AND nickname = '{2}'".format( defense_loser[0], defense_loser[1], defense_loser[2])) defense_loser_player_id, rating = cursor.fetchall()[0] cursor.execute("SELECT mu, sigma FROM rating WHERE rating_id \ = {0}".format(rating)) mu, sigma = cursor.fetchall()[0] defense_loser_rating = trueskill.Rating(mu=float(mu), sigma=float(sigma)) (new_offense_winner_rating, new_defense_winner_rating), \ (new_offense_loser_rating, new_defense_loser_rating) = \ trueskill.rate([(offense_winner_rating, defense_winner_rating), (offense_loser_rating, defense_loser_rating)], ranks=[0, 1]) cursor.execute("INSERT INTO rating (mu, sigma) VALUES ({0}, {1}\ )".format(new_offense_winner_rating.mu, new_offense_winner_rating.sigma)) new_rating_id = cursor.lastrowid cursor.execute("UPDATE player set offense_rating = {0} where \ player_id = {1}".format(new_rating_id, offense_winner_player_id)) cursor.execute("INSERT INTO rating (mu, sigma) VALUES ({0}, {1}\ )".format(new_defense_winner_rating.mu, new_defense_winner_rating.sigma)) new_rating_id = cursor.lastrowid cursor.execute("UPDATE player set defense_rating = {0} where \ player_id = {1}".format(new_rating_id, defense_winner_player_id)) cursor.execute("INSERT INTO rating (mu, sigma) VALUES ({0}, {1}\ )".format(new_offense_loser_rating.mu, new_offense_loser_rating.sigma)) new_rating_id = cursor.lastrowid cursor.execute("UPDATE player set offense_rating = {0} where \ player_id = {1}".format(new_rating_id, offense_loser_player_id)) cursor.execute("INSERT INTO rating (mu, sigma) VALUES ({0}, {1}\ )".format(new_defense_loser_rating.mu, new_defense_loser_rating.sigma)) new_rating_id = cursor.lastrowid cursor.execute("UPDATE player set defense_rating = {0} where \ player_id = {1}".format(new_rating_id, defense_loser_player_id)) # team ratings LOGGER.info("Updating team ratings") # check if winners are on a team together winning_team = self.check_if_players_on_team( member_one=offense_winner, member_two=defense_winner) if not winning_team: # create a new team winning_team = self.add_team(team_name="{0} & {1}\ ".format(offense_winner[0], defense_winner[0]), member_one=offense_winner, member_two=defense_winner) # check if losers are on a team together losing_team = self.check_if_players_on_team( member_one=offense_loser, member_two=defense_loser) if not losing_team: # create a new team losing_team = self.add_team(team_name="{0} & {1}\ ".format(offense_loser[0], defense_loser[0]), member_one=offense_loser, member_two=defense_loser) # get ratings cursor.execute("SELECT rating from team WHERE team_id \ = {0}".format(winning_team)) winning_team_rating_id = cursor.fetchone()[0] cursor.execute("SELECT rating from team WHERE team_id \ = {0}".format(losing_team)) losing_team_rating_id = cursor.fetchone()[0] cursor.execute("SELECT mu, sigma FROM rating WHERE rating_id \ = {0}".format(winning_team_rating_id)) mu, sigma = cursor.fetchall()[0] winning_team_rating = trueskill.Rating(mu=float(mu), sigma=float(sigma)) cursor.execute("SELECT mu, sigma FROM rating WHERE rating_id \ = {0}".format(losing_team_rating_id)) mu, sigma = cursor.fetchall()[0] losing_team_rating = trueskill.Rating(mu=float(mu), sigma=float(sigma)) new_winning_team_rating, new_losing_team_rating = \ trueskill.rate_1vs1(winning_team_rating, losing_team_rating) cursor.execute("INSERT INTO rating (mu, sigma) VALUES ({0}, {1}\ )".format(new_winning_team_rating.mu, new_winning_team_rating.sigma)) new_rating_id = cursor.lastrowid cursor.execute("UPDATE team SET rating = {0} where \ team_id = {1}".format(new_rating_id, winning_team)) cursor.execute("INSERT INTO rating (mu, sigma) VALUES ({0}, {1}\ )".format(new_losing_team_rating.mu, new_losing_team_rating.sigma)) new_rating_id = cursor.lastrowid cursor.execute("UPDATE team SET rating = {0} where \ team_id = {1}".format(new_rating_id, losing_team)) except MySQLdb.OperationalError: LOGGER.error("MySQL operational error occured") traceback.print_exc() raise data_manager_exceptions.DBConnectionError("Cannot connect \ to MySQL server") except MySQLdb.ProgrammingError: LOGGER.error("MySQL programming error") traceback.print_exc() raise data_manager_exceptions.DBSyntaxError("MySQL syntax error") else: pass
def add_team(self, team_name, member_one, member_two): """Method to add a team to database Args: team_name (str): team name member_one (tup): first member names member_two (tup): second member names Raises: data_manager_exceptions.DBValueError data_manager_exceptions.DBExistError data_manager_exceptions.DBConnectionError data_manager_exceptions.DBSyntaxError """ if len(team_name) is 0: raise data_manager_exceptions.DBValueError("Team name must be at \ least one character") if len(member_one) != 3: raise data_manager_exceptions.DBValueError("First team member must\ be complete") if len(member_two) != 3: raise data_manager_exceptions.DBValueError("Second team member must\ be complete") try: if not self.check_if_team_exists(team_name=team_name): raise data_manager_exceptions.DBExistError("Team already \ exists") if self.check_if_players_on_team(member_one, member_two): raise data_manager_exceptions.DBExistError("Players already \ on team together") rating_id = self.add_rating() LOGGER.info("Adding team to database") self.check_if_db_connected() cursor = self.db_conn.cursor() cursor.execute("INSERT INTO team (team_name, rating) VALUES \ ('{0}', {1})".format(team_name, rating_id)) team_id = cursor.lastrowid cursor.execute("INSERT INTO player_team_xref (player, team) \ VALUES ((SELECT player_id FROM player WHERE first_name = '{0}' AND last_name \ = '{1}' AND nickname = '{2}'), {3})".format(member_one[0], member_one[1], member_one[2], team_id)) cursor.execute("INSERT INTO player_team_xref (player, team) \ VALUES ((SELECT player_id FROM player WHERE first_name = '{0}' AND last_name \ = '{1}' AND nickname = '{2}'), {3})".format(member_two[0], member_two[1], member_two[2], team_id)) except MySQLdb.OperationalError: LOGGER.error("MySQL operational error occured") traceback.print_exc() raise data_manager_exceptions.DBConnectionError("Cannot connect \ to MySQL server") except MySQLdb.ProgrammingError: LOGGER.error("MySQL programming error") traceback.print_exc() raise data_manager_exceptions.DBSyntaxError("MySQL syntax error") except MySQLdb.IntegrityError: LOGGER.error("MySQL programming error") traceback.print_exc() raise data_manager_exceptions.DBValueError("MySQL integrity error") else: return team_id
def __init__(self, db_user, db_pass, db_host, db_name): try: LOGGER.info("Connecting to MySQL database") LOGGER.debug("Connection parameters:\n\ username: {0}\n\ password: {1}\n\ hostname: {2}\n\ database: {3}".format(db_user, db_pass, db_host, db_name)) self.db_conn = MySQLdb.connect(user=db_user, passwd=db_pass, host=db_host, db=db_name) self.db_user = db_user self.db_pass = db_pass self.db_host = db_host self.db_name = db_name cursor = self.db_conn.cursor() LOGGER.info("Creating MySQL tables") cursor.execute("CREATE TABLE IF NOT EXISTS rating (\ rating_id INT NOT NULL AUTO_INCREMENT,\ mu DECIMAL(6,4) NOT NULL,\ sigma DECIMAL(6,4) NOT NULL,\ time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\ PRIMARY KEY (rating_id),\ UNIQUE INDEX rating_id_UNIQUE (rating_id ASC))") cursor.execute("CREATE TABLE IF NOT EXISTS player (\ player_id INT NOT NULL AUTO_INCREMENT,\ first_name VARCHAR(45) NOT NULL,\ last_name VARCHAR(45) NOT NULL,\ nickname VARCHAR(45) NULL,\ time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\ offense_rating INT NOT NULL,\ defense_rating INT NOT NULL,\ PRIMARY KEY (player_id),\ UNIQUE INDEX player_id_UNIQUE (player_id ASC),\ INDEX offense_rating_idx (offense_rating ASC),\ INDEX defense_rating_idx (defense_rating ASC),\ CONSTRAINT offense_rating \ FOREIGN KEY (offense_rating) \ REFERENCES rating (rating_id) \ ON DELETE NO ACTION \ ON UPDATE NO ACTION,\ CONSTRAINT defense_rating \ FOREIGN KEY (defense_rating) \ REFERENCES rating (rating_id) \ ON DELETE NO ACTION \ ON UPDATE NO ACTION)") cursor.execute("CREATE TABLE IF NOT EXISTS team (\ team_id INT NOT NULL AUTO_INCREMENT,\ team_name VARCHAR(75) NOT NULL,\ time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\ rating INT NOT NULL,\ PRIMARY KEY (team_id),\ UNIQUE INDEX team_id_UNIQUE (team_id ASC),\ UNIQUE INDEX team_name_UNIQUE (team_name ASC),\ INDEX rating_idx (rating ASC),\ CONSTRAINT rating_1 \ FOREIGN KEY (rating) \ REFERENCES rating (rating_id) \ ON DELETE NO ACTION \ ON UPDATE NO ACTION)") cursor.execute("CREATE TABLE IF NOT EXISTS player_team_xref (\ player INT NOT NULL,\ team INT NOT NULL,\ INDEX player_idx (player ASC),\ INDEX team_idx (team ASC),\ CONSTRAINT player \ FOREIGN KEY (player) \ REFERENCES player (player_id) \ ON DELETE NO ACTION \ ON UPDATE NO ACTION,\ CONSTRAINT team \ FOREIGN KEY (team) \ REFERENCES team (team_id) \ ON DELETE NO ACTION \ ON UPDATE NO ACTION)") cursor.execute("CREATE TABLE IF NOT EXISTS result (\ result_id INT NOT NULL AUTO_INCREMENT,\ offense_winner INT NOT NULL,\ defense_winner INT NOT NULL,\ offense_loser INT NOT NULL,\ defense_loser INT NOT NULL,\ time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\ PRIMARY KEY (result_id),\ UNIQUE INDEX result_id_UNIQUE (result_id ASC),\ INDEX offense_winner_idx (offense_winner ASC),\ INDEX defense_winner_idx (defense_winner ASC),\ INDEX offense_loser_idx (offense_loser ASC),\ INDEX defense_loser_idx (defense_loser ASC),\ CONSTRAINT offense_winner \ FOREIGN KEY (offense_winner) \ REFERENCES player (player_id) \ ON DELETE NO ACTION \ ON UPDATE NO ACTION,\ CONSTRAINT defense_winner \ FOREIGN KEY (defense_winner) \ REFERENCES player (player_id) \ ON DELETE NO ACTION \ ON UPDATE NO ACTION,\ CONSTRAINT offense_loser \ FOREIGN KEY (offense_loser) \ REFERENCES player (player_id) \ ON DELETE NO ACTION \ ON UPDATE NO ACTION,\ CONSTRAINT defense_loser \ FOREIGN KEY (defense_loser) \ REFERENCES player (player_id) \ ON DELETE NO ACTION \ ON UPDATE NO ACTION)") except MySQLdb.OperationalError: LOGGER.error("MySQL operational error occured") traceback.print_exc() raise data_manager_exceptions.DBConnectionError("Cannot connect \ to MySQL server") except MySQLdb.ProgrammingError: LOGGER.error("MySQL programming error") traceback.print_exc() raise data_manager_exceptions.DBSyntaxError("MySQL syntax error") else: pass
def delete_player(self, first_name, last_name, nickname): """TODO""" """Method to delete a player from the database Args: first_name (str): player first name last_name(str): player last name nickname (str): player nickname Raises: data_manager_exceptions.DBValueError data_manager_exceptions.DBExistError data_manager_exceptions.DBConnectionError data_manager_exceptions.DBSyntaxError """ #TODO check if data exists and don't delete if it does #TODO deleting player should also delete a team if len(first_name) is 0: raise data_manager_exceptions.DBValueError("First name must be at \ least one character") if len(last_name) is 0: raise data_manager_exceptions.DBValueError("Last name must be at \ least one character") try: LOGGER.info("Checking that player already exists") LOGGER.debug("Player parameters:\n\ first name: {0}\n\ last name: {1}\n\ nickname: {2}".format(first_name, last_name, nickname)) self.check_if_db_connected() cursor = self.db_conn.cursor() cursor.execute("SELECT player_id, first_name, last_name, \ nickname FROM player") players = cursor.fetchall() if len(players) is 0: raise data_manager_exceptions.DBExistError("No players \ in database to delete") for player in players: player_id, existing_first_name, existing_last_name, \ existing_nickname = player if (first_name == existing_first_name) and (last_name == existing_last_name) and (nickname == existing_nickname): LOGGER.info("Deleting player from database") cursor.execute("DELETE FROM player WHERE player_id \ = {0}".format(player_id)) return else: continue raise data_manager_exceptions.DBExistError("Player \ doesn't exist") except MySQLdb.OperationalError: LOGGER.error("Cannot connect to MySQL server") raise data_manager_exceptions.DBConnectionError("Cannot connect \ to MySQL server") except MySQLdb.ProgrammingError: LOGGER.error("MySQL syntax error") raise data_manager_exceptions.DBSyntaxError("MySQL syntax error") else: pass
def get_individual_rankings(self): """Method to get individual rankings from database Args: None Returns: ranks (list): individual rank list Raises: data_manager_exceptions.DBConnectionError data_manager_exceptions.DBSyntaxError """ ranks = [] try: LOGGER.info("Getting individual rankings") self.check_if_db_connected() cursor = self.db_conn.cursor() cursor.execute("SELECT player_id, first_name, last_name, \ nickname FROM player") players = cursor.fetchall() for player_id, first_name, last_name, nickname in players: cursor.execute("SELECT offense_rating, defense_rating FROM \ player WHERE player_id = {0}".format(player_id)) offense_rating, defense_rating = cursor.fetchall()[0] cursor.execute("SELECT mu, sigma FROM rating WHERE rating_id \ = {0}".format(offense_rating)) offense_mu, offense_sigma = cursor.fetchall()[0] cursor.execute("SELECT mu, sigma FROM rating WHERE rating_id \ = {0}".format(defense_rating)) defense_mu, defense_sigma = cursor.fetchall()[0] offense_rank = float(offense_mu) - (3 * float(offense_sigma)) defense_rank = float(defense_mu) - (3 * float(defense_sigma)) cursor.execute("SELECT COUNT(result_id) FROM result WHERE \ offense_winner = {0}".format(player_id)) offense_win_count = cursor.fetchone()[0] cursor.execute("SELECT COUNT(result_id) FROM result WHERE \ defense_winner = {0}".format(player_id)) defense_win_count = cursor.fetchone()[0] cursor.execute("SELECT COUNT(result_id) FROM result WHERE \ offense_loser = {0}".format(player_id)) offense_lose_count = cursor.fetchone()[0] cursor.execute("SELECT COUNT(result_id) FROM result WHERE \ defense_loser = {0}".format(player_id)) defense_lose_count = cursor.fetchone()[0] intermediate_rank = (first_name, last_name, nickname, 'Offense', round(offense_rank, 4), offense_win_count, offense_lose_count) ranks.append(intermediate_rank) del intermediate_rank intermediate_rank = (first_name, last_name, nickname, 'Defense', round(defense_rank, 4), defense_win_count, defense_lose_count) ranks.append(intermediate_rank) del intermediate_rank except MySQLdb.OperationalError: LOGGER.error("MySQL operational error occured") traceback.print_exc() raise data_manager_exceptions.DBConnectionError("Cannot connect \ to MySQL server") except MySQLdb.ProgrammingError: LOGGER.error("MySQL programming error") traceback.print_exc() raise data_manager_exceptions.DBSyntaxError("MySQL syntax error") else: return ranks
def get_team_rankings(self): """Method to get team rankings from database Args: None Returns: ranks (list): team rank list Raises: data_manager_exceptions.DBConnectionError data_manager_exceptions.DBSyntaxError """ ranks = [] try: LOGGER.info("Getting team rankings") self.check_if_db_connected() cursor = self.db_conn.cursor() cursor.execute("SELECT team_id, team_name FROM team") teams = cursor.fetchall() for team_id, team_name in teams: cursor.execute("SELECT rating FROM \ team WHERE team_id = {0}".format(team_id)) team_rating = cursor.fetchall()[0] cursor.execute("SELECT mu, sigma FROM rating WHERE rating_id \ = {0}".format(team_rating[0])) offense_mu, offense_sigma = cursor.fetchall()[0] team_rank = float(offense_mu) - (3 * float(offense_sigma)) # get player_ids cursor.execute("SELECT player from player_team_xref \ WHERE team = {0}".format(team_id)) players = cursor.fetchall() player_one = players[0] player_two = players[1] cursor.execute("SELECT first_name FROM player WHERE \ player_id = {0}".format(player_one[0])) player_one_name = cursor.fetchone()[0] cursor.execute("SELECT first_name FROM player WHERE \ player_id = {0}".format(player_two[0])) player_two_name = cursor.fetchone()[0] cursor.execute("SELECT COUNT(result_id) FROM result WHERE \ (offense_winner = {0} AND defense_winner = {1}) OR (offense_winner = {1} \ AND defense_winner = {0})".format(player_one[0], player_two[0])) team_win_count = cursor.fetchone()[0] cursor.execute("SELECT COUNT(result_id) FROM result WHERE \ (offense_loser = {0} AND defense_loser = {1}) OR (offense_loser = {1} \ AND defense_loser = {0})".format(player_one[0], player_two[0])) team_loss_count = cursor.fetchone()[0] intermediate_rank = (team_name, round(team_rank, 4), team_win_count, team_loss_count, player_one_name, player_two_name) ranks.append(intermediate_rank) del intermediate_rank except MySQLdb.OperationalError: LOGGER.error("MySQL operational error occured") traceback.print_exc() raise data_manager_exceptions.DBConnectionError("Cannot connect \ to MySQL server") except MySQLdb.ProgrammingError: LOGGER.error("MySQL programming error") traceback.print_exc() raise data_manager_exceptions.DBSyntaxError("MySQL syntax error") else: return ranks
def get_all_results(self): """Method to get all results from database Args: None Raises: data_manager_exceptions.DBConnectionError data_manager_exceptions.DBSyntaxError """ all_results = () try: LOGGER.info("Getting result list") self.check_if_db_connected() cursor = self.db_conn.cursor() cursor.execute("SELECT offense_winner, defense_winner, \ offense_loser, defense_loser, time FROM result ORDER BY time DESC") results = cursor.fetchall() for offense_winner_id, defense_winner_id, offense_loser_id, \ defense_loser_id, time in results: intermediate_results = () cursor.execute("SELECT first_name, last_name, nickname FROM \ player WHERE player_id = {0}".format(offense_winner_id)) offense_winner = cursor.fetchall() first_name_offense_winner, last_name_offense_winner, \ nickname_offense_winner = offense_winner[0] cursor.execute("SELECT first_name, last_name, nickname FROM \ player WHERE player_id = {0}".format(defense_winner_id)) defense_winner = cursor.fetchall() first_name_defense_winner, last_name_defense_winner, \ nickname_defense_winner = defense_winner[0] cursor.execute("SELECT first_name, last_name, nickname FROM \ player WHERE player_id = {0}".format(offense_loser_id)) offense_loser = cursor.fetchall() first_name_offense_loser, last_name_offense_loser, \ nickname_offense_loser = offense_loser[0] cursor.execute("SELECT first_name, last_name, nickname FROM \ player WHERE player_id = {0}".format(defense_loser_id)) defense_loser = cursor.fetchall() first_name_defense_loser, last_name_defense_loser, \ nickname_defense_loser = defense_loser[0] intermediate_results = intermediate_results + \ (first_name_offense_winner, last_name_offense_winner, nickname_offense_winner, first_name_defense_winner, last_name_defense_winner, nickname_defense_winner, first_name_offense_loser, last_name_offense_loser, nickname_offense_loser, first_name_defense_loser, last_name_defense_loser, nickname_defense_loser, time.strftime('%Y-%m-%d')) all_results = all_results + (intermediate_results,) del intermediate_results except MySQLdb.OperationalError: LOGGER.error("MySQL operational error occured") traceback.print_exc() raise data_manager_exceptions.DBConnectionError("Cannot connect \ to MySQL server") except MySQLdb.ProgrammingError: LOGGER.error("MySQL programming error") traceback.print_exc() raise data_manager_exceptions.DBSyntaxError("MySQL syntax error") else: return all_results