def sql_table(cls) -> Table:
if cls.table is None:
competition_column = Column(ColumnNames.Competition_ID.name,
Affinity.INTEGER)
home_team_column = Column(ColumnNames.Home_ID.name,
Affinity.INTEGER)
away_team_column = Column(ColumnNames.Away_ID.name,
Affinity.INTEGER)
cls.table = Table(cls.__name__, id_column(), [
id_column(),
Column(ColumnNames.Season_ID.name,
Affinity.INTEGER), competition_column,
Column(ColumnNames.Match_Date.name,
Affinity.TEXT), home_team_column,
Column(ColumnNames.Home_Score_HT.name, Affinity.INTEGER),
Column(ColumnNames.Home_Score_FT.name, Affinity.INTEGER),
Column(ColumnNames.Home_Lineup.name, Affinity.BLOB),
Column(ColumnNames.Home_Bench.name, Affinity.BLOB),
Column(ColumnNames.Home_Substitutions.name, Affinity.BLOB),
away_team_column,
Column(ColumnNames.Away_Score_HT.name, Affinity.INTEGER),
Column(ColumnNames.Away_Score_FT.name, Affinity.INTEGER),
Column(ColumnNames.Away_Lineup.name, Affinity.BLOB),
Column(ColumnNames.Away_Bench.name, Affinity.BLOB),
Column(ColumnNames.Away_Substitutions.name, Affinity.BLOB)
])
cls.table.add_foreign_key(competition_column,
Competition.sql_table())
cls.table.add_foreign_key(home_team_column, Team.sql_table())
cls.table.add_foreign_key(away_team_column, Team.sql_table())
return cls.table
def extract_picked_team(database_name: str,
team_name: str,
league: League = None,
error: bool = True) -> List[str]:
team_name = team_name.replace('*', '%')
team_name = team_name.replace("'", "''")
team_rows = []
constraints = []
team_constraint = "{} {} '{}' {} {}".format(ColumnNames.Name.name,
Keywords.LIKE.name, team_name,
Keywords.COLLATE.name,
Keywords.NOCASE.name)
constraints.append(team_constraint)
with Database(database_name) as db:
team_rows.extend(db.fetch_all_rows(Team.sql_table(), constraints))
if (not team_rows or len(team_rows) > 1) and league:
team_rows = []
country_constaint = "{}='{}' {} {}".format(ColumnNames.Country.name,
league.country,
Keywords.COLLATE.name,
Keywords.NOCASE.name)
constraints.append(country_constaint)
with Database(database_name) as db:
team_rows.extend(db.fetch_all_rows(Team.sql_table(), constraints))
if not team_rows:
if error:
messages.error_message(
"No team '{}' found in the database.".format(team_name))
else:
all_rows = []
with Database(database_name) as db:
all_rows.extend(db.fetch_all_rows(Team.sql_table(), []))
expr = compile(r'.*{}.*'.format(team_name.replace('%', '.*')))
return [row for row in all_rows if expr.match(row[1])]
elif len(team_rows) > 1:
if error:
options = ', '.join(row[1] for row in team_rows)
messages.error_message(
"Too many teams match the name '{}' in the database: {}.".
format(team_name, options))
else:
return team_rows
else:
return team_rows
def process_teams(arguments: Namespace):
print('{:<25} {:<7} {:<7} {:<7} {:<7} {:<7}'.format(
'TEAM', 'MEDIAN', 'MEAN', 'STD', 'MIN', 'MAX'))
with Database(arguments.database) as db:
for team_name in arguments.team:
lexemes = team_name.split('_')
canonical_team_name = ' '.join(lexemes)
(team_row, ) = db.fetch_rows_with_likeness(Team.sql_table(),
canonical_team_name)
team = Team.inventory[team_row[0]]
match_rows = db.fetch_match_rows(team)
matches = []
for match_row in match_rows:
match = create_match_from_row(match_row)
matches.append(match)
matches.sort(key=lambda m: m.match_date)
under_analysis = []
for match in matches:
event_rows = db.fetch_event_rows(match)
match_events = []
for event_row in event_rows:
event = create_event_from_row(event_row)
match_events.append(event)
assert match_events
match_events.sort(key=lambda e: (e.period, e.timestamp))
under_analysis.append(match_events)
analyse(team, under_analysis)
def update_leagues(database: str, leagues: List[str], past: bool, force: bool):
with Database(database) as db:
team_rows = db.fetch_all_rows(Team.sql_table())
for row in team_rows:
create_team_from_row(row)
for league_code in leagues:
messages.vanilla_message('Updating {}...'.format(league_code))
league = league_register[league_code]
name_constraint = "{}='{}' {} {}".format(ColumnNames.Code.name,
league.name,
Keywords.COLLATE.name,
Keywords.NOCASE.name)
country_constraint = "{}='{}' {} {}".format(
ColumnNames.Country.name, league.country,
Keywords.COLLATE.name, Keywords.NOCASE.name)
current_constraint = "{}={}".format(
ColumnNames.Current.name,
Characters.FALSE.value if past else Characters.TRUE.value)
constraints = [
name_constraint, country_constraint, current_constraint
]
season_rows = db.fetch_all_rows(Season.sql_table(), constraints)
for row in season_rows:
season = create_season_from_row(row)
create_fixtures_json(season.id, force)
load_fixture_data(league, season)
db.create_table(Fixture)
db.create_rows(Fixture)
db.create_table(Team)
db.create_rows(Team)
def read(self, dataBaseInst):
teamsPlayers = {}
with open(self._filePath, newline='') as f:
reader = csv.reader(f)
for row in reader:
playerName, pos, idTeam, eval, gaolNumber, prize, percentTit = row
if playerName != "":
playersList = teamsPlayers.get(idTeam, [])
if len(playersList) == 0:
teamsPlayers[idTeam] = playersList
idPlayer = "{}_{}".format(idTeam, playerName)
eval = float(eval.replace(",", "."))
percentTit = float(
re.sub(r"\s*%", "", percentTit).replace(",", "."))
playersList.append(
Player(idPlayer, playerName,
pos, eval, int(gaolNumber), int(prize),
int(prize), percentTit))
for idTeam, playersList in teamsPlayers.items():
newTeam = Team(idTeam)
dataBaseInst.addTeam(newTeam)
for playerInst in playersList:
playerInst.setTeam(newTeam)
dataBaseInst.addPlayer(playerInst)
def create_event_from_row(row: List, fixture: Fixture):
time = int(row[1])
extra_time = int(row[2])
team_id = int(row[3])
team_name = row[4]
team = Team.find_team(team_id, team_name)
left_id = int(row[5])
right_id = int(row[6])
detail = EventDetail[row[7]]
event = Event(fixture, time, extra_time, team, left_id, right_id, detail)
return event
def load_fixture_data(league: League, season: Season):
fixtures_json = get_fixtures_json(season.id)
if not fixtures_json.exists():
messages.warning_message("No fixtures available for season {}".format(
season.id))
else:
messages.verbose_message('Season {}'.format(season.year))
with fixtures_json.open() as in_file:
json_text = load(in_file)
for data in json_text['api']['fixtures']:
home_id, home_name = get_home_team_data(data)
if not Team.has_team(home_id):
team_row = create_team_row(league, home_id, home_name)
create_team_from_row(team_row)
away_id, away_name = get_away_team_data(data)
if not Team.has_team(away_id):
team_row = create_team_row(league, away_id, away_name)
create_team_from_row(team_row)
create_fixture_from_json(data)
def main(args: Namespace):
with Database(args.database) as db:
team_rows = db.fetch_all_rows(Team.sql_table())
for row in team_rows:
create_team_from_row(row)
for league_code in args.league:
league = league_register[league_code]
update_leagues(args.database, league, args.past)
with Database(args.database) as db:
db.create_table(Event)
db.create_rows(Event)
def main(args: Namespace):
leagues = []
if args.country:
for country in args.country:
leagues.extend([code for code, league in league_register.items() if league.country == country.capitalize()])
if args.league:
leagues.extend(list(args.league))
if not args.country and not args.league:
leagues.extend(list(league_register.keys()))
left_datetime = datetime(args.year, args.month, args.day, args.lower)
right_datetime = datetime(args.year, args.month, args.day, args.upper)
print(left_datetime, right_datetime)
with Database(args.database) as db:
team_rows = db.fetch_all_rows(Team.sql_table())
for row in team_rows:
create_team_from_row(row)
for league_code in leagues:
league = league_register[league_code]
season = get_current_season(db, league)
if season is not None:
season_id = season[0]
season_constraint = "{}={}".format(ColumnNames.Season_ID.name, season_id)
finished_constraint = "{}={}".format(ColumnNames.Finished.name, Characters.FALSE.value)
constraints = [season_constraint, finished_constraint]
fixtures = get_fixtures(db, constraints)
fixtures = filter_fixtures(fixtures, left_datetime, right_datetime)
if fixtures:
if args.event:
teams = []
for fixture in fixtures:
teams.append(Team.inventory[fixture.home_team.id])
teams.append(Team.inventory[fixture.away_team.id])
analyse_sequences(db,
league_code,
teams,
args.event,
args.negate,
args.venue,
args.half,
args.minimum)
else:
output_fixtures(league, fixtures)
def create_event_from_json(data: Dict, fixture: Fixture):
time = int(data['elapsed'])
if data['elapsed_plus']:
extra_time = int(data['elapsed_plus'])
else:
extra_time = 0
team_id = int(data['team_id'])
team_name = data['teamName']
team = Team.find_team(team_id, team_name)
if data['assist_id']:
left_id = int(data['assist_id'])
else:
left_id = 0
if data['player_id']:
right_id = int(data['player_id'])
else:
right_id = 0
if data['type'] == 'Goal':
if data['detail'] == 'Normal Goal':
detail = EventDetail.normal_goal
elif data['detail'] == 'Own Goal':
detail = EventDetail.own_goal
elif data['detail'] == 'Penalty':
detail = EventDetail.penalty
elif data['detail'] == 'Missed Penalty':
detail = EventDetail.missed_penalty
else:
assert False
elif data['type'] == 'subst':
detail = EventDetail.substitution
elif data['type'] == 'Var':
detail = EventDetail.var
elif data['type'] == 'Card':
if data['detail'] == 'Yellow Card':
detail = EventDetail.yellow_card
elif data['detail'] == 'Red Card':
detail = EventDetail.red_card
else:
assert False
else:
assert False
Event.inventory[(fixture.id, time,
extra_time)] = Event(fixture, time, extra_time, team,
left_id, right_id, detail)
def create_fixture_from_json(data: Dict):
if is_regular_fixture(data['round']):
id_ = int(data['fixture_id'])
date = datetime.datetime.fromisoformat(data['event_date'])
season_id = int(data['league_id'])
home_id = int(data['homeTeam']['team_id'])
home_name = data['homeTeam']['team_name']
home_team = Team.find_team(home_id, home_name)
away_id = int(data['awayTeam']['team_id'])
away_name = data['awayTeam']['team_name']
away_team = Team.find_team(away_id, away_name)
half_time = data['score']['halftime']
full_time = data['score']['fulltime']
finished = True if data['status'] == 'Match Finished' else False
if home_team and away_team:
fixture = Fixture(id_, date, season_id, home_team, away_team,
half_time, full_time, finished,
datetime.datetime.now())
Fixture.inventory[fixture.id] = fixture
else:
messages.warning_message('Ignoring fixture. Round is {}.'.format(
data['round']))
def load_players_and_coaches_and_teams_and_competitions(arguments: Namespace):
with Database(arguments.database) as db:
player_rows = db.fetch_all_rows(Player.sql_table())
for row in player_rows:
create_player_from_row(row)
team_rows = db.fetch_all_rows(Team.sql_table())
for row in team_rows:
create_team_from_row(row)
competition_rows = db.fetch_all_rows(Competition.sql_table())
for row in competition_rows:
create_competition_from_row(row)
coach_rows = db.fetch_all_rows(Coach.sql_table())
for row in coach_rows:
create_coach_from_row(row)
def create_rows(self):
table = Competition.sql_table()
table.rows.clear()
for competition in Competition.inventory.values():
table.add_row(competition.sql_values())
table.insert_rows(self._cursor)
self._connection.commit()
table = Coach.sql_table()
table.rows.clear()
for coach in Coach.inventory.values():
table.add_row(coach.sql_values())
table.insert_rows(self._cursor)
self._connection.commit()
table = Player.sql_table()
table.rows.clear()
for player in Player.inventory.values():
table.add_row(player.sql_values())
table.insert_rows(self._cursor)
self._connection.commit()
table = Team.sql_table()
table.rows.clear()
for team in Team.inventory.values():
table.add_row(team.sql_values())
table.insert_rows(self._cursor)
self._connection.commit()
table = Event.sql_table()
table.rows.clear()
for event in Event.inventory.values():
table.add_row(event.sql_values())
table.insert_rows(self._cursor)
self._connection.commit()
table = Match.sql_table()
table.rows.clear()
for match in Match.inventory.values():
table.add_row(match.sql_values())
table.insert_rows(self._cursor)
self._connection.commit()
def update_all(database: str, past: bool, force: bool):
codes = []
with Database(database) as db:
team_rows = db.fetch_all_rows(Team.sql_table())
for row in team_rows:
create_team_from_row(row)
for code, league in league_register.items():
constraints = [
"{}='{}'".format(ColumnNames.Country.name, league.country),
"{}='{}'".format(ColumnNames.Code.name, league.name)
]
season_rows = db.fetch_all_rows(Season.sql_table(), constraints)
for season_row in season_rows:
season = create_season_from_row(season_row)
if season.current:
if force or fixtures_played(database, season):
codes.append(code)
update_leagues(database, codes, past, force or codes)
def read(self, dataBaseInst):
with open(self._filePath) as f:
tree = xmlElt.parse(self._filePath)
root = tree.getroot()
for teamElt in root.iter(XML.ATTR_TEAM):
newTeam = Team(teamElt.get(XML.TAG_NAME))
dataBaseInst.addTeam(newTeam)
for playerElt in teamElt.iter(XML.ATTR_PLAYER):
playerId = playerElt.get(XML.TAG_ID)
playerName = playerElt.get(XML.TAG_NAME)
pos = playerElt.get(XML.TAG_POSITION)
evalMoy = float(playerElt.get(XML.TAG_EVAL))
goalNumber = int(playerElt.get(XML.TAG_GOAL_NUMBER))
offPrize = int(playerElt.get(XML.TAG_PRIZE))
buyPrize = int(playerElt.get(XML.TAG_BUY_PRIZE, offPrize))
percentTit = float(playerElt.get(XML.TAG_PERCENT_TIT))
dataBaseInst.addPlayer(
Player(playerId, playerName, pos, evalMoy, goalNumber,
offPrize, buyPrize, percentTit, newTeam))
def sql_table(cls) -> sql_tables.Table:
if cls.table is None:
match_column = Column(ColumnNames.Match_ID.name, Affinity.INTEGER)
player_column = Column(ColumnNames.Player_ID.name,
Affinity.INTEGER)
team_column = Column(ColumnNames.Team_ID.name, Affinity.INTEGER)
cls.table = sql_tables.Table(
cls.__name__, sql_columns.id_column(), [
sql_columns.id_column(), match_column, player_column,
team_column,
Column(ColumnNames.Period.name, Affinity.INTEGER),
Column(ColumnNames.Timestamp.name, Affinity.REAL),
Column(ColumnNames.Event_ID.name, Affinity.INTEGER),
Column(ColumnNames.Sub_Event_ID.name, Affinity.INTEGER),
Column(ColumnNames.Tags.name, Affinity.BLOB),
Column(ColumnNames.From_Position.name, Affinity.BLOB),
Column(ColumnNames.To_Position.name, Affinity.BLOB)
])
cls.table.add_foreign_key(match_column, Match.sql_table())
cls.table.add_foreign_key(player_column, Player.sql_table())
cls.table.add_foreign_key(team_column, Team.sql_table())
return cls.table
def load_teams(filename: str):
check_database_exists(filename)
with Database(filename) as db:
team_rows = db.fetch_all_rows(Team.sql_table())
for row in team_rows:
create_team_from_row(row)