def games_scheduled(cursor): """ Returns a list of GSIS identifiers corresponding to games that have schedule data in the database but don't have any drives or plays in the database. In the typical case, this corresponds to games that haven't started yet. The list is sorted in the order in which the games will be played. """ scheduled = [] cursor.execute(''' SELECT DISTINCT game.gsis_id, game.start_time FROM game LEFT JOIN drive ON game.gsis_id = drive.gsis_id WHERE drive.drive_id IS NULL ''') for row in cursor.fetchall(): # This condition guards against unnecessarily processing games # that have only schedule data but aren't even close to starting yet. # Namely, if a game doesn't have any drives, then there's nothing to # bulk insert. # # We start looking at games when it's 15 minutes before game time. # Eventually, the game will start, and the first bits of drive/play # data will be bulk inserted. On the next database update, the game # will move to the `games_in_progress` list and updated incrementally. # # So what's the point of bulk inserting? It's useful when updates are # done infrequently (like the initial load of the database or say, # once a week). if seconds_delta(row['start_time'] - nfldb.now()) < 900: scheduled.append(row['gsis_id']) return sorted(scheduled, key=int)
def game_from_id(cursor, gsis_id): """ Returns an `nfldb.Game` object given its GSIS identifier. Namely, it looks for a completed or in progress game in nflgame's schedule, otherwise it creates a dummy `nfldb.Game` object with data from the schedule. """ schedule = nflgame.sched.games[gsis_id] start_time = nfldb.types._nflgame_start_time(schedule) if seconds_delta(start_time - nfldb.now()) >= 900: # Bail quickly if the game isn't close to starting yet. return game_from_schedule(cursor, gsis_id) g = nflgame.game.Game(gsis_id) if g is None: # Whoops. I guess the pregame hasn't started yet? return game_from_schedule(cursor, gsis_id) return nfldb.Game._from_nflgame(cursor.connection, g)
def update_players(cursor, interval): db = cursor.connection cursor.execute('SELECT last_roster_download FROM meta') last = cursor.fetchone()['last_roster_download'] update_due = seconds_delta(nfldb.now() - last) >= interval num_existing = nfldb.db._num_rows(cursor, 'player') # The interval only applies if the player table has data in it. # If it's empty, we always want to try and update regardless of interval. if not update_due and num_existing > 0: return log('Updating player JSON database... (last update was %s)' % last) run_cmd(sys.executable, '-m', 'nflgame.update_players', '--no-block') log('done.') # Reset the player JSON database. nflgame.players = nflgame.player._create_players() log('Locking player table...') cursor.execute(''' LOCK TABLE player IN SHARE ROW EXCLUSIVE MODE ''') log('Updating %d players... ' % len(nflgame.players), end='') for p in nflgame.players.values(): dbp = nfldb.Player._from_nflgame_player(db, p) for table, prim, vals in dbp._rows: nfldb.db._upsert(cursor, table, vals, prim) log('done.') # If the player table is empty at this point, then something is very # wrong. The user MUST fix things before going forward. if nfldb.db._num_rows(cursor, 'player') == 0: log('Something is very wrong. The player table is empty even after\n' 'trying to update it. Please seek help. Include the output of\n' 'this program when asking for help.') log('The likely cause here is that the `nflgame-update-players`\n' 'program is failing somehow. Try running it separately to see\n' 'if it succeeds on its own.') sys.exit(1) # Finally, report that we've just update the rosters. cursor.execute('UPDATE meta SET last_roster_download = NOW()')
import nfldb from nflgame import teams import math import operator db = nfldb.connect() FIRSTYEAR = 2009 #Database starts at the year 2009 CURRENTYEAR = nfldb.now().year #get Current Year ELORATINGS = {} HOMEFIELDADV = 63 #determined Home Field is worth 2.5 points or about 63 elo rating points def calcK(mov = 1, eloDiff = 1): #mov = Margin of Victory movMultiplier = math.log(1+mov) * (2.2/(eloDiff*.001+2.2)) return 20 * movMultiplier def updateElo(currentGame): #Calculate Elo Differential between Home and Away Teams eloDiffHome = ((ELORATINGS[currentGame.home_team] + HOMEFIELDADV) - ELORATINGS[currentGame.away_team]) #Calculate Probability of Win for Home and Away Teams pOfWinHome= 1/(1+(math.pow(10, (-1 * eloDiffHome/400)))) pOfWinAway = 1-pOfWinHome #Calculate Margin of Victory and K Value margin = abs(currentGame.home_score-currentGame.away_score) #Determine if home or away team won if g.loser == currentGame.away_team: loserAway = True kEloDiff = (ELORATINGS[currentGame.home_team] + HOMEFIELDADV) - ELORATINGS[currentGame.away_team] else: