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()
