def main():
conn, db = apiUtils.getDbConnection('player', False)
query = "SELECT distinct id from player"
playerRankings = []
db.execute(query)
result = db.fetchall()
for playerTuple in result:
playerId = playerTuple[0]
query = "SELECT (pts+0.5*fg3PtMade+offReb*1.25-0.5*tov) as offScore, (defReb*1.25+stl*2+blk*2) as defScore from daily_player_box_stats d inner join game g on g.id = d.gameId where d.playerId=" + str(
playerId
) + " and playedStatus='COMPLETED' order by g.startTime desc limit 25"
db.execute(query)
bs = db.fetchall()
offTotal = 0
defTotal = 0
count = 0
for row in bs:
# pts+ast*0.5+(offReb+defReb)*1.25+ast*1.5+stl*2+blk*2+to*-0.5
count += 1
offTotal += row[0]
defTotal += row[1]
if count > 0:
playerRankings.append({
'id': playerId,
'offScore': round(offTotal / count),
'defScore': round(defTotal / count)
})
playerRankings.sort(key=lambda x: x['offScore'], reverse=True)
maxOff = playerRankings[0]['offScore']
for obj in playerRankings:
obj['offRating'] = round((obj['offScore'] * 50) / maxOff) + 49
playerRankings.sort(key=lambda x: x['defScore'], reverse=True)
maxDef = playerRankings[0]['defScore']
for obj in playerRankings:
obj['defRating'] = round(obj['defScore'] * 50 / maxDef) + 49
playerRankings.sort(key=lambda x: x['offScore'] + x['defScore'],
reverse=True)
maxOverall = playerRankings[0]['offScore'] + playerRankings[0]['defScore']
for obj in playerRankings:
obj['overallRating'] = round(
(obj['offScore'] + obj['defScore']) * 50 / maxOverall) + 49
for obj in playerRankings:
query = "INSERT INTO player_ranking (rankingDate, playerId, overallRating, offRating, defRating) VALUES (DATE(DATE_SUB(now(), INTERVAL 6 HOUR)), " + str(
obj['id']) + ", " + str(obj['overallRating']) + ", " + str(
obj['offRating']) + ", " + str(obj['defRating']) + ")"
db.execute(query)
conn.commit()
#!/usr/bin/python3
import requests
import json
# This is because MySQLdb only works for python2
import pymysql
pymysql.install_as_MySQLdb()
import MySQLdb
import api_utils as apiUtils
from datetime import date
conn, db = apiUtils.getDbConnection('injuries')
response = apiUtils.getSportsFeed('injury')
injuriesArr = json.loads(response.text)['players']
statusDate = str(date.today())
print(len(injuriesArr))
for p in injuriesArr:
playerId = p["id"]
injuryDescription = p["currentInjury"]["description"]
playingProbability = p["currentInjury"]["playingProbability"]
insertStatement = """INSERT INTO injuries (playerId, statusDate, injuryDescription, playingProbability) VALUES ({playerId}, '{statusDate}', '{injuryDescription}', '{playingProbability}');""".format(
playerId=playerId,
statusDate=statusDate,
injuryDescription=injuryDescription,
playingProbability=playingProbability)
try:
db.execute(insertStatement)
conn.commit()
def download(season, gameDate):
conn, db = apiUtils.getDbConnection('daily_player_box_stats', False)
response = apiUtils.getDailyBoxScore(season, gameDate)
if response.status_code != 200:
print('request status code ' + str(response.status_code))
return
resp = json.loads(response.text)
if "gamelogs" not in resp:
return
playerArr = resp['gamelogs']
print(season, gameDate, len(playerArr))
deleteStatement = "DELETE FROM daily_player_box_stats WHERE DATE(DATE_SUB(startTime, INTERVAL 6 HOUR)) = '" + gameDate + "'"
try:
db.execute(deleteStatement)
conn.commit()
except:
print(deleteStatement)
conn.rollback()
for pData in playerArr:
playerId = pData["player"]["id"]
g = pData["game"]
gameId = g["id"]
startTimeUnformatted = g["startTime"]
startTime = startTimeUnformatted[:-5].replace('T', ' ')
# "2018-05-16T00:30:00.000Z"" to 2018-05-16 00:30:00
awayTeamAbbreviation = g["awayTeamAbbreviation"]
homeTeamAbbreviation = g["homeTeamAbbreviation"]
playerTeamId = pData["team"]["id"]
position = pData["player"]["position"]
s = pData["stats"]
fg = s["fieldGoals"]
fg2PtAtt = fg["fg2PtAtt"]
fg2PtMade = fg["fg2PtMade"]
fg3PtAtt = fg["fg3PtAtt"]
fg3PtMade = fg["fg3PtMade"]
ft = s["freeThrows"]
ftAtt = ft["ftAtt"]
ftMade = ft["ftMade"]
reb = s["rebounds"]
offReb = reb["offReb"]
defReb = reb["defReb"]
ast = s["offense"]["ast"]
pts = s["offense"]["pts"]
de = s["defense"]
tov = de["tov"]
stl = de["stl"]
blk = de["blk"]
blkAgainst = de["blkAgainst"]
msc = s["miscellaneous"]
fouls = msc["fouls"] if "fouls" in msc else 0
foulsDrawn = msc["foulsDrawn"] if "foulsDrawn" in msc else 0
foulPers = msc["foulPers"] if "foulPers" in msc else 0
foulPersDrawn = msc["foulPersDrawn"] if "foulPersDrawn" in msc else 0
foulTech = msc["foulTech"] if "foulTech" in msc else 0
foulTechDrawn = msc["foulTechDrawn"] if "foulTechDrawn" in msc else 0
foulFlag1 = msc["foulFlag1"] if "foulFlag1" in msc else 0
foulFlag1Drawn = msc["foulFlag1Drawn"] if "foulFlag1Drawn" in msc else 0
foulFlag2 = msc["foulFlag2"] if "foulFlag2" in msc else 0
foulFlag2Drawn = msc["foulFlag2Drawn"] if "foulFlag2Drawn" in msc else 0
ejections = msc["ejections"] if "ejections" in msc else 0
plusMinus = msc["plusMinus"]
minSeconds = msc["minSeconds"]
startedGame = "true" if "startedGame" in msc and msc[
"startedGame"] != 0 else "false"
insertStatement = """INSERT INTO daily_player_box_stats (playerId, gameId, startTime, awayTeamAbbreviation, homeTeamAbbreviation, playerTeamId, position, fg2PtAtt, fg2PtMade, fg3PtAtt, fg3PtMade, ftAtt, ftMade, offReb, defReb, ast, pts, tov, stl, blk, blkAgainst, fouls, foulsDrawn, foulPers, foulPersDrawn, foulTech, foulTechDrawn, foulFlag1, foulFlag1Drawn, foulFlag2, foulFlag2Drawn, ejections, plusMinus, minSeconds, startedGame) VALUES ({playerId}, {gameId}, '{startTime}', '{awayTeamAbbreviation}', '{homeTeamAbbreviation}', {playerTeamId}, '{position}', {fg2PtAtt}, {fg2PtMade}, {fg3PtAtt}, {fg3PtMade}, {ftAtt}, {ftMade}, {offReb}, {defReb}, {ast}, {pts}, {tov}, {stl}, {blk}, {blkAgainst}, {fouls}, {foulsDrawn}, {foulPers}, {foulPersDrawn}, {foulTech}, {foulTechDrawn}, {foulFlag1}, {foulFlag1Drawn}, {foulFlag2}, {foulFlag2Drawn}, {ejections}, {plusMinus}, {minSeconds}, {startedGame});""".format(
playerId=playerId,
gameId=gameId,
startTime=startTime,
awayTeamAbbreviation=awayTeamAbbreviation,
homeTeamAbbreviation=homeTeamAbbreviation,
playerTeamId=playerTeamId,
position=position,
fg2PtAtt=fg2PtAtt,
fg2PtMade=fg2PtMade,
fg3PtAtt=fg3PtAtt,
fg3PtMade=fg3PtMade,
ftAtt=ftAtt,
ftMade=ftMade,
offReb=offReb,
defReb=defReb,
ast=ast,
pts=pts,
tov=tov,
stl=stl,
blk=blk,
blkAgainst=blkAgainst,
fouls=fouls,
foulsDrawn=foulsDrawn,
foulPers=foulPers,
foulPersDrawn=foulPersDrawn,
foulTech=foulTech,
foulTechDrawn=foulTechDrawn,
foulFlag1=foulFlag1,
foulFlag1Drawn=foulFlag1Drawn,
foulFlag2=foulFlag2,
foulFlag2Drawn=foulFlag2Drawn,
ejections=ejections,
plusMinus=plusMinus,
minSeconds=minSeconds,
startedGame=startedGame)
try:
db.execute(insertStatement)
conn.commit()
# print('inserted '+firstName+' '+lastName)
except:
print('failed inserting ' + playerId + ' for date ' + gameDate)
print(insertStatement)
conn.rollback()
conn.close()
#if len(playerArr) > 0:
time.sleep(10)
def main():
# connect to the db
conn, db = apiUtils.getDbConnection('player_season_totals', False)
# columns for all the rankable stats
columns = [
'gameCount', 'fg2PtAtt', 'fg2PtMade', 'fg3PtAtt', 'fg3PtMade', 'ftAtt',
'ftMade', 'offReb', 'defReb', 'ast', 'pts', 'tov', 'stl', 'blk',
'blkAgainst', 'fouls', 'foulsDrawn', 'foulPers', 'foulPersDrawn',
'foulTech', 'foulTechDrawn', 'foulFlag1', 'foulFlag1Drawn',
'foulFlag2', 'foulFlag2Drawn', 'ejections', 'plusMinus', 'minSeconds',
'fpts'
]
# build query using columns
query = """select
p.firstName,
p.lastName,
pst.playerId,
pst.uploadDate,
"""
for i in range(len(columns)):
query += "pst." + columns[i] + ", "
query += "(offReb+defReb) as reb "
query += """
from player_season_totals pst
inner join player p
on p.id = pst.playerId;"""
# store the query result in a 2d list
db.execute(query)
result = list(db)
# rankings is a dictionary
# {playerId: {stat1: rank, stat2: rank ... }}
rankings = {}
# rankable columns indexed starting at 4
for colIndex in range(4, len(result[0])):
columnName = 'reb' if colIndex == len(
result[0]) - 1 else columns[colIndex - 4]
# all columns are per game except for gameCount
if colIndex == 4:
result.sort(key=lambda x: int(x[colIndex]), reverse=True)
else:
result.sort(key=lambda x: float(x[colIndex]) / float(x[4]),
reverse=True)
rankIndex = 1 # i.e. rankIndex of 4 means player was the 4th highest
# set the rankings in the rankings dictionary
for playerRow in result:
playerId = playerRow[2]
if playerId not in rankings:
rankings[playerId] = {}
rankings[playerId][columnName] = rankIndex
rankIndex += 1
# the first part of the insert
insert = "INSERT INTO player_ranking (playerId, uploadDate, "
for i in range(len(columns)):
insert += "rank_" + columns[i] + ", "
insert += "rank_reb) VALUES "
# add insert values for each player
count = 0
for playerId in rankings:
insert += "(" + str(playerId) + ", DATE(NOW()), "
for i in range(len(columns)):
insert += str(rankings[playerId][columns[i]]) + ", "
insert += str(rankings[playerId]['reb']) + ")"
# all except last should have a following comma
if count < len(rankings) - 1:
insert += ", "
count += 1
db.execute(insert)
deleteQuery = "delete from player_ranking where uploadDate < date_sub(now(), interval 24 HOUR)"
db.execute(deleteQuery)
conn.commit()
import requests
import json
# This is because MySQLdb only works for python2
import pymysql
pymysql.install_as_MySQLdb()
import MySQLdb
import api_utils as apiUtils
conn, db = apiUtils.getDbConnection('player')
response = apiUtils.getSportsFeed('player')
playerArr = json.loads(response.text)['players']
print(len(playerArr))
for pData in playerArr:
p = pData["player"]
playerId = p["id"]
firstName = p["firstName"].replace("'", "''")
lastName = p["lastName"].replace("'", "''")
primaryPosition = p["primaryPosition"]
jerseyNumber = p["jerseyNumber"]
currentTeamId = p["currentTeam"]["id"] if p["currentTeam"] is not None and p["currentTeam"]["id"] is not None else "NULL"
isOnRoster = p["currentRosterStatus"] == "ROSTER"
heightInches = apiUtils.stringHeightToInches(p["height"]) if p["height"] is not None else "NULL"
weightPounds = p["weight"] if p["weight"] is not None else "NULL"
birthDate = "'"+p["birthDate"]+"'" if p["birthDate"] is not None else "NULL"
birthCity = p["birthCity"].replace("'", "''") if p["birthCity"] is not None else "NULL"
birthCountry = p["birthCountry"].replace("'", "''") if p["birthCountry"] is not None else "NULL"
isRookie = p["rookie"] == True
highSchool = p["highSchool"].replace("'","''") if p["highSchool"] is not None else "NULL"
#!/usr/bin/python3
import requests
import json
# This is because MySQLdb only works for python2
import pymysql
pymysql.install_as_MySQLdb()
import MySQLdb
import api_utils as apiUtils
from datetime import date
conn, db = apiUtils.getDbConnection('team_standings')
response = apiUtils.getDailyStandings('2018-2019-regular')
standingsArr = json.loads(response.text)['teams']
statusDate = str(date.today())
print(len(standingsArr))
for s in standingsArr:
teamId = s["team"]["id"]
overallRank = s["overallRank"]["rank"]
overallGamesBack = s["overallRank"]["gamesBack"]
conferenceRank = s["conferenceRank"]["rank"]
conferenceGamesBack = s["conferenceRank"]["gamesBack"]
conferenceName = s["conferenceRank"]["conferenceName"]
divisionRank = s["divisionRank"]["rank"]
divisionGamesBack = s["divisionRank"]["gamesBack"]
divisionName = s["divisionRank"]["divisionName"]
wins = s["stats"]["standings"]["wins"]
losses = s["stats"]["standings"]["losses"]
winPct = s["stats"]["standings"]["winPct"]
gamesBack = s["stats"]["standings"]["gamesBack"]
def download(season, gameDate='', clearTable=False):
conn, db = apiUtils.getDbConnection('game', clearTable)
if gameDate == '':
response = apiUtils.getGames(season)
else:
response = apiUtils.getDailyGames(season, gameDate)
if response.status_code != 200:
print('request status code ' + str(response.status_code))
exit(1)
resp = json.loads(response.text)
if "games" not in resp:
return
gameArr = resp['games']
deleteStatement = "DELETE FROM game WHERE DATE(DATE_SUB(startTime, INTERVAL 6 HOUR)) = '" + gameDate + "'"
try:
db.execute(deleteStatement)
conn.commit()
except:
print(deleteStatement)
conn.rollback()
print(season, gameDate, len(gameArr))
for gData in gameArr:
s = gData["schedule"]
gameId = s["id"]
startTimeUnformatted = s["startTime"]
startTime = startTimeUnformatted[:-5].replace('T', ' ')
# "2018-05-16T00:30:00.000Z"" to 2018-05-16 00:30:00
awayTeamId = s["awayTeam"]["id"]
awayTeamAbbreviation = s["awayTeam"]["abbreviation"]
homeTeamId = s["homeTeam"]["id"]
homeTeamAbbreviation = s["homeTeam"]["abbreviation"]
venueId = s["venue"]["id"]
venueName = s["venue"]["name"]
scheduleStatus = s["scheduleStatus"]
delayedOrPostponedReason = s["delayedOrPostponedReason"]
playedStatus = s["playedStatus"]
sc = gData["score"]
currentQuarter = sc["currentQuarter"] if isinstance(
sc["currentQuarter"], (int)) else "NULL"
currentQuarterSecondsRemaining = sc[
"currentQuarterSecondsRemaining"] if isinstance(
sc["currentQuarterSecondsRemaining"], (int)) else "NULL"
currentIntermission = sc["currentIntermission"] if isinstance(
sc["currentIntermission"], (int)) else "NULL"
awayScoreTotal = sc["awayScoreTotal"] if isinstance(
sc["awayScoreTotal"], (int)) else "NULL"
homeScoreTotal = sc["homeScoreTotal"] if isinstance(
sc["homeScoreTotal"], (int)) else "NULL"
q = sc["quarters"] if "quarters" in sc else ""
quarter1awayScore = q[0]["awayScore"] if len(q) > 0 else 0
quarter1homeScore = q[0]["homeScore"] if len(q) > 0 else 0
quarter2awayScore = q[1]["awayScore"] if len(q) > 1 else 0
quarter2homeScore = q[1]["homeScore"] if len(q) > 1 else 0
quarter3awayScore = q[2]["awayScore"] if len(q) > 2 else 0
quarter3homeScore = q[2]["homeScore"] if len(q) > 2 else 0
quarter4awayScore = q[3]["awayScore"] if len(q) > 3 else 0
quarter4homeScore = q[3]["homeScore"] if len(q) > 3 else 0
overtime1homeScore = q[4]["awayScore"] if len(q) > 4 else 0
overtime1awayScore = q[4]["homeScore"] if len(q) > 4 else 0
overtime2homeScore = q[5]["awayScore"] if len(q) > 5 else 0
overtime2awayScore = q[5]["homeScore"] if len(q) > 5 else 0
overtime3homeScore = q[6]["awayScore"] if len(q) > 6 else 0
overtime3awayScore = q[6]["homeScore"] if len(q) > 6 else 0
overtime4homeScore = q[7]["awayScore"] if len(q) > 7 else 0
overtime4awayScore = q[7]["homeScore"] if len(q) > 7 else 0
overtime5homeScore = q[8]["awayScore"] if len(q) > 8 else 0
overtime5awayScore = q[8]["homeScore"] if len(q) > 8 else 0
overtime6homeScore = q[9]["awayScore"] if len(q) > 9 else 0
overtime6awayScore = q[9]["homeScore"] if len(q) > 9 else 0
insertStatement = """INSERT INTO game (id, startTime, awayTeamId, awayTeamAbbreviation, homeTeamId, homeTeamAbbreviation, venueId, venueName, scheduleStatus, delayedOrPostponedReason, playedStatus, currentQuarter, currentQuarterSecondsRemaining, currentIntermission, awayScoreTotal, homeScoreTotal, quarter1awayScore, quarter1homeScore, quarter2awayScore, quarter2homeScore, quarter3awayScore, quarter3homeScore, quarter4awayScore, quarter4homeScore, overtime1awayScore, overtime1homeScore, overtime2awayScore, overtime2homeScore, overtime3awayScore, overtime3homeScore, overtime4awayScore, overtime4homeScore, overtime5awayScore, overtime5homeScore, overtime6awayScore, overtime6homeScore) VALUES ({gameId}, '{startTime}', {awayTeamId}, '{awayTeamAbbreviation}', {homeTeamId}, '{homeTeamAbbreviation}', {venueId}, '{venueName}', '{scheduleStatus}', '{delayedOrPostponedReason}', '{playedStatus}', {currentQuarter}, {currentQuarterSecondsRemaining}, '{currentIntermission}', {awayScoreTotal}, {homeScoreTotal}, {quarter1awayScore}, {quarter1homeScore}, {quarter2awayScore}, {quarter2homeScore}, {quarter3awayScore}, {quarter3homeScore}, {quarter4awayScore}, {quarter4homeScore}, {overtime1awayScore}, {overtime1homeScore}, {overtime2awayScore}, {overtime2homeScore},{overtime3awayScore}, {overtime3homeScore},{overtime4awayScore}, {overtime4homeScore},{overtime5awayScore}, {overtime5homeScore},{overtime6awayScore}, {overtime6homeScore});""".format(
gameId=gameId,
startTime=startTime,
awayTeamId=awayTeamId,
awayTeamAbbreviation=awayTeamAbbreviation,
homeTeamId=homeTeamId,
homeTeamAbbreviation=homeTeamAbbreviation,
venueId=venueId,
venueName=venueName,
scheduleStatus=scheduleStatus,
delayedOrPostponedReason=delayedOrPostponedReason,
playedStatus=playedStatus,
currentQuarter=currentQuarter,
currentQuarterSecondsRemaining=currentQuarterSecondsRemaining,
currentIntermission=currentIntermission,
awayScoreTotal=awayScoreTotal,
homeScoreTotal=homeScoreTotal,
quarter1awayScore=quarter1awayScore,
quarter1homeScore=quarter1homeScore,
quarter2awayScore=quarter2awayScore,
quarter2homeScore=quarter2homeScore,
quarter3awayScore=quarter3awayScore,
quarter3homeScore=quarter3homeScore,
quarter4awayScore=quarter4awayScore,
quarter4homeScore=quarter4homeScore,
overtime1awayScore=overtime1awayScore,
overtime1homeScore=overtime1homeScore,
overtime2awayScore=overtime2awayScore,
overtime2homeScore=overtime2homeScore,
overtime3awayScore=overtime3awayScore,
overtime3homeScore=overtime3homeScore,
overtime4awayScore=overtime4awayScore,
overtime4homeScore=overtime4homeScore,
overtime5awayScore=overtime5awayScore,
overtime5homeScore=overtime5homeScore,
overtime6awayScore=overtime6awayScore,
overtime6homeScore=overtime6homeScore)
try:
db.execute(insertStatement)
conn.commit()
except:
print('failed inserting ' + str(gameId) + ' for date ' + gameDate)
print(insertStatement)
conn.rollback()
conn.close()
time.sleep(10)
import requests
import json
# This is because MySQLdb only works for python2
import pymysql
pymysql.install_as_MySQLdb()
import MySQLdb
import api_utils as apiUtils
conn, db = apiUtils.getDbConnection('team')
response = apiUtils.getSportsFeed('team')
teamArr = json.loads(response.text)['teams']
print(len(teamArr))
for tData in teamArr:
t = tData["team"]
teamId = t["id"]
city = t["city"]
name = t["name"]
abbreviation = t["abbreviation"]
homeVenueId = t["homeVenue"]["id"]
homeVenueName = t["homeVenue"]["name"]
insertStatement = """INSERT INTO team (id, city, teamName, abbreviation, venueId, venueName) VALUES ({teamId}, '{city}', '{name}', '{abbreviation}', {homeVenueId}, '{homeVenueName}');""".format(
teamId=teamId,
city=city,
name=name,
abbreviation=abbreviation,
homeVenueId=homeVenueId,