def insert_player_role(self, player_name, role_name): try: self.db_cursor.execute(""" insert into player_role ( player_id, role_id ) values ( ( select player_id from player where name = '""" + player_name + """' ), ( select role_id from role where role_name = '""" + role_name + """' ) ) """) self.db_connection.commit() except Exception: error_reporting.report_error(get_error()) return # Perform callbacks upon successful insertion of role for callback in self.insertion_callbacks_dict[Table.PLAYER_ROLE]: callback()
def get_next_unplayed_game_details(self): try: self.db_cursor.execute(""" select g.game_id, g.season, g.game_number, p_home.name as 'home_player_name', p_away.name as 'away_player_name' from game g inner join player p_home on (g.home_player_id = p_home.player_id) inner join player p_away on (g.away_player_id = p_away.player_id) where g.game_id not in ( select game_id from player_game ) order by g.season, g.game_number limit 1 """) raw_result = self.db_cursor.fetchall() if len(raw_result) == 0: return [] return raw_result[0] except Exception: error_reporting.report_error(get_error())
def get_footwear_names(self): try: self.db_cursor.execute(""" select footwear_name from footwear """) raw_result = self.db_cursor.fetchall() if len(raw_result) == 0: return [] result = [row[0] for row in raw_result] return result except Exception: error_reporting.report_error(get_error())
def get_season_start_dates(self): try: self.db_cursor.execute(""" select start_date from season """) raw_result = self.db_cursor.fetchall() if len(raw_result) == 0: return [] result = [row[0] for row in raw_result] return result except Exception: error_reporting.report_error(get_error())
def get_role_names(self): try: self.db_cursor.execute(""" select role_name from role """) raw_result = self.db_cursor.fetchall() if len(raw_result) == 0: return [] result = [row[0].lower() for row in raw_result] return result except Exception: error_reporting.report_error(get_error())
def is_player_in_db(self, player_name): try: self.db_cursor.execute(""" select name from player """) raw_result = self.db_cursor.fetchall() if len(raw_result) == 0: return False result = [row[0].lower() for row in raw_result] return player_name.lower() in result except Exception: error_reporting.report_error(get_error())
def insert_role(self, role_name): try: self.db_cursor.execute( "insert into role (role_name) values ('" + role_name + "')") self.db_connection.commit() except mysql.connector.IntegrityError: pass # Ignore if role already exists except Exception: error_reporting.report_error(get_error()) return # Perform callbacks upon successful insertion of role for callback in self.insertion_callbacks_dict[Table.ROLE]: callback()
def get_player_id(self, player_name): try: self.db_cursor.execute(""" select player_id from player where name = '""" + player_name + """' limit 1 """) raw_result = self.db_cursor.fetchall() if len(raw_result) == 0: return -1 return raw_result[0][0] except Exception: error_reporting.report_error(get_error())
def insert_footwear(self, footwear_name): try: self.db_cursor.execute(""" insert into footwear (footwear_name) values ('""" + footwear_name + """') """) self.db_connection.commit() except mysql.connector.IntegrityError: pass # Ignore if trying to insert an existing footwear except Exception: error_reporting.report_error(get_error()) # Perform callbacks upon successful insertion of footwear for callback in self.insertion_callbacks_dict[Table.FOOTWEAR]: callback()
def insert_game(self, season, game_number, home_player_id, away_player_id): try: self.db_cursor.execute(""" insert into game (season, game_number, home_player_id, away_player_id) values ( '""" + season.strftime('%Y-%m-%d') + "'," + str(game_number) + ',' + str(home_player_id) + ',' + str(away_player_id) + """ ) """) self.db_connection.commit() except Exception: error_reporting.report_error(get_error()) return # Perform callbacks upon successful insertion of player game for callback in self.insertion_callbacks_dict[Table.GAME]: callback()
def insert_season(self, start_date, postseason_start_game, end_date=None): try: self.db_cursor.execute(""" insert into season (start_date, postseason_start_game""" + (""", end_date""" if end_date is not None else '') + """) values ( '""" + start_date.strftime('%Y-%m-%d') + "'," + str(postseason_start_game) + (", '" + end_date.strftime('%Y-%m-%d') + "'" if end_date is not None else '') + """) """) self.db_connection.commit() except Exception: error_reporting.report_error(get_error()) return # Perform callbacks upon successful insertion of player game for callback in self.insertion_callbacks_dict[Table.SEASON]: callback()
def insert_player(self, name, logo=None, headshot=None): field_str = 'name' values_str = '%s' values = [name] if logo is not None: field_str += ', logo' values_str += ',%s' values.append(logo) if headshot is not None: field_str += ', headshot' values_str += ',%s' values.append(headshot) try: self.db_cursor.execute( 'insert into player (' + field_str + ') values (' + values_str + ')', tuple(values)) self.db_connection.commit() except Exception: error_reporting.report_error(get_error()) return # Perform callbacks upon successful insertion of player for callback in self.insertion_callbacks_dict[Table.PLAYER]: callback()
def insert_player_game(self, player_id, game_id, footwear_id, differential, knockovers, own_cups, aces, serve_breaks, penalty_shots_made, penalty_shots_attempted, penalties_committed): try: self.db_cursor.execute(""" insert into player_game ( player_id, game_id, footwear_id, differential, knockovers, own_cups, aces, serve_breaks, penalty_shots_made, penalty_shots_attempted, penalties_committed ) values (""" + str(player_id) + ',' + str(game_id) + ',' + str(footwear_id) + ',' + str(differential) + ',' + str(knockovers) + ',' + str(own_cups) + ',' + str(aces) + ',' + str(serve_breaks) + ',' + str(penalty_shots_made) + ',' + str(penalty_shots_attempted) + ',' + str(penalties_committed) + """ )""" ) self.db_connection.commit() except Exception: error_reporting.report_error(get_error()) return # Perform callbacks upon successful insertion of player game for callback in self.insertion_callbacks_dict[Table.PLAYER_GAME]: callback()
def submit_new_season(self): # Check all user-entered values and players are valid if eval(self.spinbox_num_games.get()) <= 0: error_reporting.report_warning( 'Invalid number of games per player') return if len(self.listbox_players.curselection()) <= 0: error_reporting.report_warning( 'No players selected for the new season') return if self.date_entry_start_date.get_date() in self.controller.get_db( ).get_season_start_dates(): error_reporting.report_warning( 'Existing season already started on date entered') return players = [ self.listbox_players.get(player) for player in self.listbox_players.curselection() ] player_ids = [ self.controller.get_db().get_player_id(name) for name in players ] if -1 in player_ids: error_reporting.report_error( 'Database error. A player name did not match any known entries.' ) return # Enter new season into database schedule = analysis.generate_new_schedule( eval(self.spinbox_num_games.get()), player_ids) self.controller.get_db().insert_season( self.date_entry_start_date.get_date(), len(schedule) + 1) for game in range(len(schedule)): self.controller.get_db().insert_game( self.date_entry_start_date.get_date(), game + 1, schedule[game][0], schedule[game][1]) self.reset_entries()
def connect_server(self, host, user, password, b_reset_schema=False): """ Connects to MySQL server and keeps connection open until this is called again or the connection is closed :param host: Host name/IP to connect to :param user: User on host to log in with :param password: User password :param b_reset_schema: Whether schema should be reset upon connection """ # If trying to connect to something else, make sure to close previous database first self.disconnect_server() try: self.db_connection = mysql.connector.connect(host=host, user=user, password=password) self.db_cursor = self.db_connection.cursor() self.db_cursor.execute('use pppl') # Reset schema if told to if b_reset_schema: self.reset_database_schema() # Notify via callbacks that server is now connected and database is available for table in Table: for callback in self.insertion_callbacks_dict[table]: callback() except Exception: error_reporting.report_error(get_error()) return
def reset_database_schema(self): try: self.db_cursor.execute('create database if not exists pppl') self.db_cursor.execute('drop table if exists player_game') self.db_cursor.execute('drop table if exists footwear') self.db_cursor.execute('drop table if exists game') self.db_cursor.execute('drop table if exists season') self.db_cursor.execute('drop table if exists player_role') self.db_cursor.execute('drop table if exists player') self.db_cursor.execute('drop table if exists role') self.db_cursor.execute(""" create table role ( role_id int not null unique auto_increment, role_name varchar(50) not null unique, primary key (role_id) ) """) self.db_cursor.execute(""" create table player ( player_id int not null unique auto_increment, name varchar(50) not null unique, logo longblob, headshot longblob, primary key (player_id) ) """) self.db_cursor.execute(""" create table player_role ( player_id int not null, role_id int not null, foreign key (player_id) references player(player_id), foreign key (role_id) references role(role_id) ) """) self.db_cursor.execute(""" create table season ( start_date date not null unique, end_date date unique, postseason_start_game int unsigned not null, primary key (start_date) ) """) self.db_cursor.execute(""" create table game ( game_id int not null unique auto_increment, season date not null, game_number int not null, home_player_id int not null, away_player_id int not null, primary key (game_id), foreign key (season) references season(start_date), foreign key (home_player_id) references player(player_id), foreign key (away_player_id) references player(player_id) ) """) self.db_cursor.execute(""" create table footwear ( footwear_id int not null unique auto_increment, footwear_name varchar(50) not null unique ) """) self.db_cursor.execute(""" create table player_game ( player_id int not null, game_id int not null, footwear_id int not null, differential int not null, knockovers int not null, penalty_shots_made int not null, penalty_shots_attempted int not null, penalties_committed int not null, own_cups int not null, aces int not null, serve_breaks int not null, foreign key (player_id) references player(player_id), foreign key (game_id) references game(game_id), foreign key (footwear_id) references footwear(footwear_id) ) """) except Exception: error_reporting.report_error(get_error())