def main():
files = [
f for f in os.listdir(constants.LOGDIR_EXTRACT) if 'pbp_espn' in f
]
for f in files:
print f
game_id = db.nba_query("SELECT id FROM game WHERE abbrev = '%s'" %
f.replace('_pbp_espn', ''))[0][0]
newfiledata = []
newfiledata = [[game_id] + line.rstrip().split(',')
for line in open(constants.LOGDIR_EXTRACT + f, 'r')]
writer = csv.writer(open(constants.LOGDIR_EXTRACT + 'tmpextract_ref',
'wb'),
delimiter=',',
lineterminator='\n')
writer.writerows(newfiledata)
sql = """
LOAD DATA LOCAL INFILE '%s' REPLACE
INTO TABLE extract_ref
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(game_id,period_name, play_num, time_left,away_score,home_score,away_play,home_play)
""" % (constants.LOGDIR_EXTRACT + 'tmpextract_ref')
db.nba_query(sql)
def _getNumberOfPeriods(self):
return db.nba_query(
"""
SELECT MAX(period) FROM playbyplay_espn
WHERE game_id = %s and period <= 4
"""
% self.game_id
)[0][0]
def _getCurrentPlayers(self, team_id):
sql = """
SELECT p.id
FROM player_nbacom_by_game nbacom
INNER JOIN player p ON p.nbacom_player_id = nbacom.nbacom_player_id
WHERE nbacom.team = %s AND nbacom.game_id = %s
""" % (team_id, self.game_id)
return [itm[0] for itm in db.nba_query(sql)]
def _saveToDatabase(self, away_fiveman, home_fiveman):
# Away fiveman unit
for quarter_row in away_fiveman:
for row in quarter_row:
db.nba_query("""
UPDATE pbp2 SET away_fiveman = '%s' WHERE game_id = %s AND play_num = %s AND period = %s
""" % (','.join(map(str, row['unit'])), self.game_id,
row['play_number'], row['period']))
# home fiveman unit
for quarter_row in home_fiveman:
for row in quarter_row:
db.nba_query("""
UPDATE pbp2 SET home_fiveman = '%s' WHERE game_id = %s AND play_num = %s AND period = %s
""" % (','.join(map(str, row['unit'])), self.game_id,
row['play_number'], row['period']))
def _getCurrentPlayers(self, team_id):
sql = """
SELECT p.id
FROM player_nbacom_by_game nbacom
INNER JOIN player p ON p.nbacom_player_id = nbacom.nbacom_player_id
WHERE nbacom.team = %s AND nbacom.game_id = %s
""" % (
team_id,
self.game_id,
)
return [itm[0] for itm in db.nba_query(sql)]
def main():
files = [f for f in os.listdir(constants.LOGDIR_EXTRACT) if 'pbp_espn' in f]
for f in files:
print f
game_id = db.nba_query("SELECT id FROM game WHERE abbrev = '%s'" % f.replace('_pbp_espn',''))[0][0]
newfiledata = []
newfiledata = [[game_id] + line.rstrip().split(',') for line in open(constants.LOGDIR_EXTRACT + f,'r')]
writer = csv.writer(open(constants.LOGDIR_EXTRACT + 'tmpextract_ref','wb'),delimiter=',',lineterminator='\n')
writer.writerows(newfiledata)
sql = """
LOAD DATA LOCAL INFILE '%s' REPLACE
INTO TABLE extract_ref
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(game_id,period_name, play_num, time_left,away_score,home_score,away_play,home_play)
""" % (constants.LOGDIR_EXTRACT + 'tmpextract_ref')
db.nba_query(sql)
def _saveToDatabase(self, away_fiveman, home_fiveman):
# Away fiveman unit
for quarter_row in away_fiveman:
for row in quarter_row:
db.nba_query(
"""
UPDATE pbp2 SET away_fiveman = '%s' WHERE game_id = %s AND play_num = %s AND period = %s
"""
% (",".join(map(str, row["unit"])), self.game_id, row["play_number"], row["period"])
)
# home fiveman unit
for quarter_row in home_fiveman:
for row in quarter_row:
db.nba_query(
"""
UPDATE pbp2 SET home_fiveman = '%s' WHERE game_id = %s AND play_num = %s AND period = %s
"""
% (",".join(map(str, row["unit"])), self.game_id, row["play_number"], row["period"])
)
def testShotDefinitions(self):
shots = db.nba_query("SELECT * FROM cbsshot")
for line in self.obj.assertShotDefinitions():
converted = (int(line[0]),line[1])
self.assertTrue(converted in shots, "%s not found in known shots" % str(line))
def _getNumberOfPeriods(self):
return db.nba_query("""
SELECT MAX(period) FROM playbyplay_espn
WHERE game_id = %s and period <= 4
""" % self.game_id)[0][0]
