Beispiel #1
0
    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
Beispiel #2
0
    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
Beispiel #3
0
    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
Beispiel #4
0
    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
Beispiel #5
0
    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
Beispiel #6
0
    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
Beispiel #7
0
    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
Beispiel #8
0
    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
Beispiel #9
0
    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
Beispiel #10
0
    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
Beispiel #11
0
    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
Beispiel #12
0
    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
Beispiel #13
0
    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
Beispiel #14
0
    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
Beispiel #15
0
    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
Beispiel #16
0
    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