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
