def create_dataframe_with_stats():
logger.info("begin - create_dataframe_with_stats")
TMatch = aliased(Match, name=f.MATCH)
TChamp = aliased(Championship, name=f.CHAMPIONSHIP)
THome = aliased(Table, name=f.STATS_HOME)
TAway = aliased(Table, name=f.STATS_AWAY)
results = session.query(TChamp, TMatch, THome, TAway).filter(
TChamp.id == TMatch.championship_id,
TMatch.home_team_id == THome.team_id,
TMatch.away_team_id == TAway.team_id, TMatch.id == THome.next_match_id,
TMatch.id == TAway.next_match_id,
THome.matches_played == THome.last_matches_num,
TAway.matches_played == TAway.last_matches_num).order_by(
TMatch.championship_id, TMatch.match_date)
# with_labels() beacause exists ambiguous columns name.
df = pd.read_sql(results.with_labels().statement, session.bind)
add_stats_mean(df)
add_results(df)
df.to_csv(PATH_DATAFRAMES + 'bts_stats.csv', index=False)
logger.info("end - create_dataframe_with_stats")
def create_dataframe_with_odds():
logger.info("create_dataframe_with_odds: begin")
TMatch = aliased(Match, name=f.MATCH)
TChamp = aliased(Championship, name=f.CHAMPIONSHIP)
TOddsML = aliased(ResumeOddsMLI, name=f.ODDS_MLI)
TOddsBTS = aliased(ResumeOddsBTS, name=f.ODDS_BTS)
THome = aliased(Table, name=f.STATS_HOME)
TAway = aliased(Table, name=f.STATS_AWAY)
results = session.query(
TChamp, TMatch, TOddsML, TOddsBTS, THome, TAway).filter(
TChamp.id == TMatch.championship_id,
TMatch.id == TOddsBTS.match_id,
TMatch.home_team_id == THome.team_id,
TMatch.away_team_id == TAway.team_id,
TMatch.id == THome.next_match_id, TMatch.id == TAway.next_match_id,
or_(THome.last_matches_num == THome.matches_played,
THome.last_matches_num == None),
or_(TAway.last_matches_num == TAway.matches_played,
TAway.last_matches_num == None),
TMatch.id == TOddsML.match_id).order_by(TMatch.championship_id,
TMatch.match_date)
# with_labels() beacause exists ambiguous columns name.
df = pd.read_sql(results.with_labels().statement, session.bind)
add_stats_mean(df)
add_mli_fmu(df)
add_mli_prob(df)
add_bts_fu(df)
add_bts_prob(df)
add_results(df)
df.to_csv(PATH_DATAFRAMES + 'bts_total.csv', index=False)
# print(*df.columns,sep='\n')
logger.info("create_dataframe_with_odds: end")
def list_tables(self):
return session.query(Table).filter(
Table.championship_id == self.id,
Table.last_matches_num == Table.matches_played,
Table.next_match_id == Match.id).order_by(
Match.matchGroupNum).all()
def list_teams(self):
return session.query(Team).filter(or_(Match.away_team_id == Team.id, Match.homeTeamId == Team.id), \
Match.championship_id == self.id).distinct().all()
def list_matches(self):
return session.query(Match).filter(
Match.championshipId == self.id).order_by(Match.matchDate).all()
def list_by_name(championship_name):
return session.query(Championship).filter(
Championship.name == championship_name).all()
def get_by_id(champId):
return session.query(Championship).filter(
Championship.id == champId).one()
def get_by_id(self, teamId):
return session.query(Team).filter(Team.id == teamId).one()
def get(self, match_id):
return session.query(ResumeOddsBTS).filter(
ResumeOddsBTS.match_id == match_id).one()
def list():
return session.query(ResumeOddsMLI).filter().all()
def get(match_id):
return session.query(ResumeOddsMLI).filter(
ResumeOddsMLI.match_id == match_id).one()
def get_by_match(match_id):
return session.query(Table).filter(
Table.next_match_id == match_id).all()
def list(cls):
return session.query(cls).filter().all()
def list():
return session.query(Match).filter().all()
def get(match_id):
return session.query(Match).filter(Match.id == match_id).one()
def list_names():
return session.query(distinct(Championship.name)).all()
def get(self, teamName):
return session.query(Team).filter(Team.name == teamName).one()
def list(self):
return session.query(ResumeOddsBTS).filter().all()
def list_matches(self, championship_id):
return session.query(Match).filter(or_(Match.away_team_id == self.id, Match.home_team_id == self.id), \
Match.championship_id == championship_id).order_by(Match.match_date).all()
def get(championship_name, year):
return session.query(Championship).filter(
Championship.name == championship_name,
Championship.year == year).one()