def num_games(team, year=None, season_id=None):
if (year and season_id) or (not (year or season_id)):
raise ValueError("Must pass year or season_id")
q = session().query(Game).join(Season)\
.filter(or_(Game.team_one == team.id,
Game.team_two == team.id))
if year:
q = q.filter(Season.year == year)
if season_id:
q = q.filter(Season.id == season_id)
return q.count()
def get_and_insert_data(year):
ts = TournamentScraper()
gs = GameScraper()
team_s = TeamScraper()
games, teams, final_four, champion = ts.get_tournament_info(year)
season = Season(year)
session().add(season)
session().flush()
tournament = Tournament(season.id)
for team in teams.items():
print("Team: " + str(team))
wins, losses, stats_table = team_s.get_team_info(team[1][0])
print("Team Stats: " + str(stats_table))
t = Team(team[0], season.id, made_tournament=True, bracket=team[1][2],
seed=team[1][1], wins=wins, losses=losses)
session().add(t)
session().flush()
with open(t.stats_path, 'w') as f:
csv.writer(f).writerows(stats_table)
if t.name == champion:
print("Champion: " + str(champion))
tournament.champion_id = t.id
session().add(tournament)
session().flush()
player_info = team_s.get_player_info(team[1][0])
print("Player Info: " + str(player_info[0]))
for player in player_info[1].items():
print("Player: " + str(player))
p = Player(player[0], t.id)
session().add(p)
session().flush()
with open(p.stats_path, 'w') as f:
csv.writer(f).writerow(player[1])
for idx in range(len(games)):
g = gs.get_game_info(games[idx][-1], games[idx][:-1])
print("Game: " + str(g))
team_one = session().query(Team).filter(Team.name == g[0]).first().id
team_two = session().query(Team).filter(Team.name == g[1]).first().id
game = Game(team_one=team_one, team_two=team_two, season=season.id,
date_played=g[4], team_one_score=g[2],
team_two_score=g[3], tournament_id=tournament.id)
session().add(game)
session().flush()
g_stats = gs.get_game_stats(games[idx][-1])
with open(game.stats_path, 'w') as f:
csv.writer(f).writerows(g_stats[0])
csv.writer(f).writerows(g_stats[1])
print("Game Stats: " + str(g_stats))
session().commit()
def get_and_insert_data(year, offset=0):
ss = ScheduleScraper()
gs = GameScraper()
ts = TeamScraper()
print("Initiated Scrapers")
season = session().query(Season).filter(Season.year == year).first()
urls = ss.get_team_urls(year)[offset:]
print("Got urls")
exisiting_teams = set([
t.name for t in session().query(Team).join(Season).filter(
Season.year == year).all()
]) # noqa
already_seen = set()
for url in urls:
print(url)
schedule = ss.get_schedule(url) #only_season=True)
print("Got Schedule")
for endpoint in schedule:
if endpoint in already_seen:
continue
g = gs.get_game_info(endpoint, team_urls=True)
print('{} v. {}'.format(g[0], g[1]))
new_teams = []
if g[0] not in exisiting_teams:
new_teams.append((g[0], g[2]))
if g[1] not in exisiting_teams:
new_teams.append((g[1], g[3]))
for new_team in new_teams:
print("Creating Team: {}".format(new_team[0]))
wins, losses, team_opp = ts.get_team_info(new_team[1])
t = Team(new_team[0], season.id, wins=wins, losses=losses)
session().add(t)
session().flush()
exisiting_teams.add(new_team[0])
with open(t.stats_path, 'w') as f:
csv.writer(f).writerows(team_opp)
player_info = ts.get_player_info(new_team[1])
for player in player_info[1].items():
print("Player: {}".format(player[0]))
p = Player(player[0], t.id)
session().add(p)
session().flush()
with open(p.stats_path, 'w') as f:
csv.writer(f).writerow(player[1])
cond = and_(Team.name == g[0], Season.year == year)
t_one = session().query(Team).join(Season).filter(cond).first()
cond = and_(Team.name == g[1], Season.year == year)
t_two = session().query(Team).join(Season).filter(cond).first()
cond = and_(
Game.date_played == g[6],
or_(and_(Game.team_one == t_one.id, Game.team_two == t_two.id),
and_(Game.team_one == t_two.id,
Game.team_two == t_one.id)))
game = session().query(Game).filter(cond).first()
if not game or game.tournament_id:
if not game:
print("Creating Game")
g[4], g[5] = (g[4], g[5]) if t_one.name == g[0] else (g[5],
g[4])
game = Game(t_one.id,
t_two.id,
season.id,
g[6],
team_one_score=g[4],
team_two_score=g[5])
session().add(game)
session().flush()
else:
print("Tournament Game")
g_stats = gs.get_game_stats(endpoint)
with open(game.stats_path, 'w') as f:
csv.writer(f).writerows(g_stats[0])
csv.writer(f).writerows(g_stats[1])
already_seen.add(endpoint)
session().commit()
print("Committed")
offset += 1
with open('offset.txt', 'w') as f:
f.write(str(offset))
def team_games_before(t, g):
return session().query(Game)\
.filter(Game.date_played < g.date_played,
or_(Game.team_one == t.id,
Game.team_two == t.id))
def get_teams(game):
t1 = session().query(Team).filter(Team.id == game.team_one).first()
t2 = session().query(Team).filter(Team.id == game.team_two).first()
return t1, t2
def all_games(year=None):
q = session().query(Game)
if year:
q = q.join(Season).filter(Season.year == year)
return q.all()
def last_year(t):
t_season = session().query(Season).filter(Season.id == t.season_id).first()
last_year_t = session().query(Team).join(Season)\
.filter(Team.name == t.name,
Season.year == t_season.year-1).first()
return last_year_t
from mlhoops.db import session
from mlhoops.models import Team, Season, Game
from mlhoops.ml.normalize import feature_scaling
from sqlalchemy import and_, or_
from tensorflow.python.keras.models import Sequential
from tensorflow.python.keras.layers import Dense
teams = session().query(Team).filter(Team.season_id == 15,
Team.made_tournament == True).all()
print("Number of tournament teams: {}".format(len(teams)))
team_ids = []
team_data = []
for team in teams:
team_ids.append(team.id)
team_data.append(team.get_data()[1])
team_data = {
team_ids[i]: row
for i, row in enumerate(feature_scaling(team_data))
}
tour_cond = and_(Game.tournament_id != None, Game.season_id == 15)
season_cond = and_(
Game.tournament_id == None, Game.season_id == 15,
or_(Game.team_one.in_(team_ids), Game.team_two.in_(team_ids)))
tour_games = session().query(Game).filter(tour_cond).all()
season_games = session().query(Game).filter(season_cond).all()
print("Number of tournament games: {}".format(len(tour_games)))
print("Number of season games: {}".format(len(season_games)))
def init_db_data(engine):
season_data = [{'year': 2016}]
team_data = [
{'name': 'Oregon'},
{'name': 'Oregon State'}
]
game_data = [
{'date_played': datetime.utcnow(),
'team_one_score': 100, 'team_two_score': 52,
'team_one': team_data[0], 'team_two': team_data[1],
'season': season_data[0]}
]
player_data = [
{'name': 'Jordan Bell', 'team_name': 'Oregon'},
{'name': 'Ronnie Stacy', 'team_name': 'Oregon State'}
]
season = Season(season_data[0]['year'])
session().add(season)
session().flush()
for idx, team in enumerate(team_data):
team = Team(team['name'], season.id, made_tournament=True,
bracket='midwest', seed=idx+1)
session().add(team)
session().flush()
for player in player_data:
if player['team_name'] == team.name:
player = Player(player['name'], team.id)
session().add(player)
session().flush()
tournament = Tournament(season.id, 1)
session().add(tournament)
session().flush()
# need to commit so we can query team and season tables
session().commit()
for game in game_data:
game['team_one'] = session().query(Team).\
filter(Team.name == game['team_one']['name']).first().id
game['team_two'] = session().query(Team).\
filter(Team.name == game['team_two']['name']).first().id
season = session().query(Season).\
filter(Season.year == game['season']['year']).first()
game['season'] = season.id
game['tournament_id'] = tournament.id
game = Game(**game)
session().add(game)
session().flush()
session().commit()
tournament = Tournament(season.id, 1)
session().add(tournament)
session().flush()
# need to commit so we can query team and season tables
session().commit()
for game in game_data:
game['team_one'] = session().query(Team).\
filter(Team.name == game['team_one']['name']).first().id
game['team_two'] = session().query(Team).\
filter(Team.name == game['team_two']['name']).first().id
season = session().query(Season).\
filter(Season.year == game['season']['year']).first()
game['season'] = season.id
game['tournament_id'] = tournament.id
game = Game(**game)
session().add(game)
session().flush()
session().commit()
if __name__ == '__main__':
from mlhoops.db import engine
Base.metadata.reflect(bind=engine)
drop_db(engine)
init_db()
init_db_data()
session().close()