def get_season_results(self):
"""
Get all fixtures for a given team
:returns: ( (date, opposition, opposition_id, venue, formation), ... )
"""
sql = """SELECT DISTINCT date, team_id, team, opposition_id, opposition, venue, team_formation
FROM players WHERE team_id=%s ORDER BY DATE;"""
matches = run_multirow_query(sql, self.team_id)
matches_with_results = []
for m in matches:
m_with_result = {
'date': m[0],
'opposition_id': m[3],
'opposition': m[4],
'venue': m[5],
'formation': m[6],
'result': self.get_match_result(m[3], m[0])
}
matches_with_results.append(m_with_result)
return matches_with_results
def get_players_on_team(self):
"""
Get all players on a team given a team ID
:returns: [ (player_id, player_surname, player_forname), ... ]
"""
sql = """SELECT player_id, player_surname, player_forename, team
FROM players WHERE team_id=%s GROUP BY player_id, team_id;"""
return run_multirow_query(sql, self.team_id)
def get_games_played(self):
"""
Get the number of games a team played
:returns: (team_id, team_name, games played)
"""
sql = """SELECT player_id, player_surname, player_forename, team
FROM players WHERE team_id=%s GROUP BY player_id, team_ix """
return run_multirow_query(sql, self.team_id)
def get_teams(self):
"""
Fetch all the teams and their corresponding IDs
:rtype: a list of tuples
:returns: [ (team_id, team_name), (team_id, team_name), ... ]
"""
sql = "SELECT DISTINCT team_id, team FROM players ORDER BY team;"
return run_multirow_query(sql)
def get_minutes_played(self):
"""
Get the number of successful, unsuccessful, and pass percentage for a given player over an entire season
:returns: ( player_id, player_surname, player_forename, minutes_played)
"""
sql = """SELECT player_id, player_surname, player_forename, SUM(time_played)
FROM players WHERE player_id=%s GROUP BY player_id;"""
return run_multirow_query(sql, self.player_id)
def get_players(self):
"""
Fetch all the players in the league and their corresponding IDs
:rtype: a list of tuples
:returns: [ (player_id, player, team) ]
"""
sql = """SELECT distinct player_id, player_surname, player_forename, team_id, team
FROM players
ORDER BY team_id, player_surname;"""
return run_multirow_query(sql)