

In [16]:

    
from datetime import datetime
import json
import logging
import os
import sys

from configparser import ConfigParser

from nba.db.nbapg import NBAPostgres
from nba.dates import convert_format

Setting Up the Database Connection



In [2]:

    
logging.basicConfig(stream=sys.stdout, level=logging.INFO)
config = ConfigParser()
configfn = os.path.join(os.path.expanduser('~'), '.pgcred')
config.read(configfn)









    Out[2]:





['/home/sansbacon/.nbadb']



In [3]:

    
nbapg = NBAPostgres(username=config['nbadb']['username'],
                password=config['nbadb']['password'],
                database=config['nbadb']['database'])

Rotogrinders



In [4]:

    
game_date = '20170321'
q = """SELECT data FROM rotogrinders WHERE game_date = '{}'"""



In [23]:

    
# data column is stored as jsonb; psycopg2 will convert it back to dict
data = nbapg.select_scalar(q.format(convert_format(game_date, 'nba')))

The dictionary has a series of keys which look like gameids. Each gameid has a data key which has what we want.

{
37302
    data
        away_id : "130"
        name : null
        team_home
            data
                city : "Toronto"
                direction : "N"
                lineups
                    paging
                    collection
                name : "Toronto Raptors"
                time_zone : null
                stadium : "Air Canada Centre"
                hashtag : "TOR"
                zip_code : null
                id : 129
                sport_id : "3"
                mascot : "Raptors"
        home_locked : "0"
        url : null
        home_notes : null
        updated_at : "2017-03-21 11:25:03"
        forecast : null
        home_id : "129"
        team_away
            same structure as team_home
        vegas
            timestamp
                spread
                    spread_visiting : "6.5"
                    spread_adjust_visiting : "-110"
                    spread_home : "-6.5"
                    spread_adjust_home : "-110"
                total
                    total_points : "201.5"
                    under_adjust : "-110"
                    over_adjust : "-110"
                moneyline
                    moneyline_visiting : "240"
                    moneyline_home : "-285"
                team_total
                    delta_visiting : 0.25
                    team_total_home : 104
                    delta_home : -0.25
                    team_total_visiting : 97.5
            timestamp
                same structure
        home_score : "0"
        weather : null
        away_notes : null
        time : "7:00 PM ET"
        date : "2017-03-21 23:00:00"
        away_score : "0"
        id : 37302
        sport_id : "3"
        away_locked : "0"
}

Now we can loop through each gameid to get the spreads and totals



In [35]:

    
for gid, gdata in data.items():
    hid = gdata['data']['home_id']
    vid = gdata['data']['away_id']
    hcode = gdata['data']['team_home']['data']['hashtag']
    vcode = gdata['data']['team_away']['data']['hashtag']
    vegas = gdata['data']['vegas']
    
    # they have odds with various timestamps during the day
    if vegas:
        stamps = [datetime.strptime(k, '%Y-%m-%d %H:%M:%S') for k in vegas]
        if stamps:
            fmt = '%Y-%m-%d %H:%M:%S'
            maxts = datetime.strftime(max(stamps), fmt)
            newest = vegas[maxts]
            print (gid, maxts, vid, vcode, float(newest['spread']['spread_visiting']),
                   hid, hcode, float(newest['spread']['spread_home']))









    



(u'37304', '2017-03-21 11:25:04', u'132', u'DET', -5.0, u'126', u'BKN', 5.0)
(u'37305', '2017-03-21 11:25:04', u'142', u'MEM', 2.5, u'143', u'NOP', -2.5)
(u'37306', '2017-03-21 11:25:04', u'150', u'GSW', -4.0, u'140', u'DAL', 4.0)
(u'37307', '2017-03-21 11:25:04', u'144', u'SAS', -5.0, u'146', u'MIN', 5.0)
(u'37302', '2017-03-21 11:25:02', u'130', u'CHI', 6.5, u'129', u'TOR', -6.5)
(u'37309', '2017-03-21 11:25:04', u'151', u'LAC', -8.0, u'152', u'LAL', 8.0)



In [37]:

    
results = []
for gid, gdata in data.items():
    hid = gdata['data']['home_id']
    vid = gdata['data']['away_id']
    hcode = gdata['data']['team_home']['data']['hashtag']
    vcode = gdata['data']['team_away']['data']['hashtag']
    vegas = gdata['data']['vegas']

    # they have odds with various timestamps during the day
    if vegas:
        stamps = [datetime.strptime(k, '%Y-%m-%d %H:%M:%S') for k in vegas]
        if stamps:
            fmt = '%Y-%m-%d %H:%M:%S'
            maxts = datetime.strftime(max(stamps), fmt)
            newest = vegas[maxts]
            results.append({'game_id': gid,
                'ts': maxts,
                'visitor_team_id': vid,
                'visitor_team_code': vcode.upper(),
                'visitor_team_spread': float(newest['spread']['spread_visiting']),
                'home_team_id': hid,
                'home_team_code': hcode.upper(),
                'home_team_spread': float(newest['spread']['spread_home']),
                'game_ou': float(newest['total']['total_points']),
                'delta_visiting': float(newest['team_total']['delta_visiting']),
                'delta_home': float(newest['team_total']['delta_home']),
                'team_total_home': float(newest['team_total']['team_total_home']),
                'team_total_visiting': float(newest['team_total']['team_total_visiting']) 
            })



In [ ]: