def create_games(path_to_games_list): #for now we just do sugar bowl osu = session.query(Team).filter_by(name='Ohio State').one() bama = session.query(Team).filter_by(name='Alabama').one() game = Game() game.home_team_id = osu.id game.away_team_id = bama.id session.add(game) session.commit()
def create_teams(path_to_teams): file = open(path_to_teams, 'r') teams = [line.split(',') for line in file.readlines()] file.close() for team in teams[1:]:#first line is column names #make and commit team team_record = Team() team_record.name = team[1] session.add(team_record) file.close() session.commit()
def create_qb_actions(path_to_game_file): #get quarterback actions file = open(path_to_game_file,'r') lines = file.readlines() for line in lines[2:]:#first two lines are meta cols = line.split(',') if cols[0].strip() != '': play_index = cols[0].strip() game_id = 1 play_id = session.query(Play)\ .join(Game)\ .filter(Play.play_index == play_index)\ .filter(Game.id == game_id)\ .one().id offense_code = cols[1].strip() offense = decode_def_off[offense_code] quarterback = cols[9].strip() if quarterback != '': first_name, last_name, number = quarterback.split(' ') first_name = first_name.strip() last_name = last_name.strip() sguc = cols[10].strip() alt = cols[11].strip() comp = cols[12].strip() direction = cols[13].strip() air_yards = cols[14].strip() total_yards = cols[15].strip() press = cols[16].strip() pock = cols[17].strip() scram = cols[18].strip() qb = session.query(Athlete).filter(Athlete.position == 'QB') qb = qb.filter(Athlete.first_name == first_name) qb = qb.filter(Athlete.last_name == last_name) try: qb = qb.one() except: print('no matching qb for ' + first_name + ' ' + last_name) else: qb_action = Action() qb_action.play_id = play_id qb_action.athlete_id = qb.id qb_action.action_type = 'QB' session.add(qb_action) session.commit() file.close()
def create_plays(path_to_game_file): osu = session.query(Team).filter(Team.name == 'Ohio State').one() bama = session.query(Team).filter(Team.name == 'Alabama').one() decode_def_off = { 'OSU': osu, 'BAMA': bama } game = session.query(Game).one() file = open(path_to_game_file,'r') lines = file.readlines() for line in lines[2:]:#first two lines are meta cols = line.split(',') if cols[0].strip() != '': play_index = cols[0].strip() offense_code = cols[1].strip() print(offense_code) defense_code = cols[2].strip() offense = decode_def_off[offense_code] defense = decode_def_off[defense_code] quarter = int(cols[3].strip()) down = int(cols[4].strip()) print(offense.id) #yards_to_go = int(cols[5].strip()) half = cols[6].strip() yards = cols[7].strip() result = cols[8].strip() play = Play() play.game_id = game.id play.play_index = int(play_index) play.quarter_index = int(quarter) play.offense_id = offense.id play.defense_id = defense.id play.down = down #play.yards_to_go = yards_to_go play.half = bool(half == 'Own') play.yards = int(yards) play.result = result session.add(play) session.commit() file.close()
def create_athletes(path_to_teams): teams = session.query(Team).all() for team in teams: team_name = '_'.join(team.name.split(' ')) file = open(path_to_data_dir + '/rosters/' + team_name + '.txt', 'r') for line in file.readlines(): first_name, last_name, suffix, position, height, weight, year = line.split(',') #make and commit athlete athlete = Athlete() athlete.first_name = first_name athlete.last_name = last_name athlete.suffix = suffix athlete.position = position athlete.height = height athlete.weight = weight athlete.year = year athlete.team_id = team.id session.add(athlete) session.commit()