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