def load_predictions():
qs = '''
SELECT
*
FROM
predictions_odds
ORDER BY
game_date,
game_id
'''
return get_as_df('betting', qs)
def load_game_predictions(tournament, sport_name, game_id, conn=None):
qs = '''
SELECT
*
FROM
newest_predictions
WHERE
tournament='{}' AND
sport_name='{}' AND
game_id='{}'
'''.format(tournament, sport_name, game_id)
return get_as_df('betting', qs, conn)
def load_bettable_predictions(conn=None):
qs = '''
SELECT
*
FROM
predictions_odds
WHERE
game_id not in (select distinct game_id from bets) AND
kelly_bet > 0 AND
now() > open_time AND
now() < close_time
'''
return get_as_df('betting', qs)
def _load_vl_player_stats():
qs = '''
SELECT
sum(shots) as shots,
sum(goals) as goals,
sum(games) as games,
nimi as player_name,
player_id
FROM veikkausliiga_player_stats
WHERE
season > '2015'
GROUP BY
player_name,
player_id
'''
return get_as_df('betting', qs)
def _load_liiga_player_stats():
qs = '''
SELECT
sum(shots) as shots,
sum(goals) as goals,
sum(games) as games,
player_id
FROM
liiga_player_stats
WHERE
season > '2015'
GROUP BY
player_id
'''
res = get_as_df('betting', qs)
return res
def load_games(sport_name, tournament, cutoff_date=datetime.now().date()):
qs = '''
SELECT
*
FROM
games
WHERE
sport_name='{}' AND
tournament='{}'
ORDER BY game_date
'''.format(sport_name, tournament)
res = get_as_df('betting', qs)
res['home_team'] = res['home_team'].apply(lambda s: s.strip())
res['away_team'] = res['away_team'].apply(lambda s: s.strip())
games = res[res['game_date'].apply(lambda a: a < cutoff_date)]
oos = res[res['game_date'].apply(lambda a: a >= cutoff_date)]
return games, oos
def load_lineups(tournament):
qs = '''
SELECT
team,
game_id,
player_id,
season,
team_type,
player_position,
game_set
FROM
lineups
WHERE
league='{}'
'''.format(tournament)
res = get_as_df('betting', qs)
return res
def load_betting_results():
qs = 'SELECT * FROM betting_results WHERE money_won IS NOT NULL order by game_date'
return get_as_df('betting', qs)