def team_game_log():
with sql_query.SqlQuery() as query:
for season in range(1995, 2021):
season_games = endpoints.LeagueGameLog(
season=season, headers=constant.headers).get_normalized_dict(
)['LeagueGameLog']
game_data = [] # array of tuples to hold teams season games
for game in season_games: # iterate over teams games played in a season
home = False
win = False
opponent_id = constant.team_abbr[re.split(
r'\W\s', game['MATCHUP'])[1]]
if not re.findall('@', game['MATCHUP']
): # if not away team set home team to true
home = True
if game['WL'] is 'W': # if team won set win to true
win = True
game_data.append(
[game['GAME_ID'], game['TEAM_ID'], home, opponent_id, win])
query.insert_many(
game_data,
'team_event',
)
time.sleep(random.randint(4, 7))
def nba_seasons():
season = 1970 # start season
tuple_list = [] # list of tuples holds season data
while season < 2021:
year = str(season) + "-" + str(season + 1)
tuple_list.append((season + 210000, year))
season += 1
with sql_query.SqlQuery() as query: # insert into sql table5
query.insert_many(tuple_list, table='season')
def league_game_logs():
with sql_query.SqlQuery() as query:
for season in range(1995, 2021): # loop through seasons we want
season_games = endpoints.LeagueGameLog(
season=season, headers=constant.headers).get_normalized_dict(
)['LeagueGameLog']
season_game_data = [
] # list of tuples representing a season, each tuple object representing a game in the season
for x in range(0, len(season_games), 2):
game = season_games[x]
season_id = int(game['SEASON_ID']) + 190000
season_game_data.append(
(game['GAME_ID'], game['GAME_DATE'], season_id, 21))
print(season_game_data)
if len(season_game_data) > 0:
query.insert_many(season_game_data, table='event')
def active_player_seasons():
upper = UPPER_RANGE
lower = LOWER_RANGE
with sql_query.SqlQuery() as query:
active_players = query.get_table('player',
'*',
'WHERE is_active = 1 ',
dataframe=False)
a = 0
for x in range(0, len(active_players)):
a = a + 1
if a > 513:
player_id = active_players[x][0] # current player_id
season_logs = endpoints.CommonPlayerInfo(
player_id, headers=constant.headers)
team_id = season_logs.get_normalized_dict(
)['CommonPlayerInfo'][0]['TEAM_ID']
seasons = season_logs.get_data_frames()[
2] # available seasons for player
seasons = seasons[(seasons['SEASON_ID'] > lower) &
(seasons['SEASON_ID'] < upper
)] # filtering out preseason and playoffs
seasons = seasons.astype(int)
seasons['player_id'] = player_id
seasons = seasons[['player_id', 'SEASON_ID']]
seasons.columns = ['player_id', 'season_id']
seasons['season_id'] += 190000
arg = f"WHERE id={player_id}"
query.insert_many(seasons, 'player_season')
print(team_id)
if team_id != 0:
query.update_row(team_id, 'player', 'team_id', arg)
print(a)
time.sleep(random.randint(2, 4))
def nba_players():
with sql_query.SqlQuery() as query:
df = pd.DataFrame(players.get_players())
query.insert_many(df, table='player')
def nba_teams():
teams = pd.DataFrame(teams_nba.get_teams())
teams = teams.drop(columns='year_founded', axis=0)
teams = teams.drop(columns='nickname', axis=0)
with sql_query.SqlQuery() as query:
query.insert_many(teams, 'team', True)
def team_game_event():
with sql_query.SqlQuery() as query:
team_seasons = query.get_table(
'team', dataframe=False
) # gets avaliable seasons for every active player from sql database
len_team_seasons = len(team_seasons)
for x in range(
0, len_team_seasons
): # for each team season in seasons starting with first active
time.sleep(2)
team_season_games = endpoints.TeamGameLog(
team_id=team_seasons[x][0],
season=2019, #just did it season by season
headers=constant.headers).get_normalized_dict()['TeamGameLog']
# player game event data
game_frame = pd.DataFrame(
team_season_games) # holds season of games for player
game_frame = game_frame.rename(
columns={
'Team_ID': 'team_id',
'Game_ID': 'event_id',
'GAME_DATE': 'game_date',
'MIN': 'min',
'FGM': 'fgm',
'FGA': 'fga',
'FG_PCT': 'fg_pct',
'FG3M': 'fg3m',
'FG3A': 'fg3a',
'FG3_PCT': 'fg3_pct',
'FTM': 'ftm',
'FTA': 'fta',
'FT_PCT': 'ft_pct',
'OREB': 'oreb',
'DREB': 'dreb',
'REB': 'reb',
'AST': 'ast',
'STL': 'stl',
'BLK': 'blk',
'TOV': 'tov',
'PF': 'pf',
'PTS': 'pts'
})
if len(game_frame) > 0:
game_frame['team_id'] = game_frame['MATCHUP'].str.extract(
r'(^\w{3})') #get first team initiasls
game_frame['opponent_id'] = game_frame['MATCHUP'].str.extract(
r'\W\s(\w{3})') #get second team initials
game_frame['team_id'] = game_frame['team_id'].map(
constant.team_id) #map first team to our team id
game_frame['opponent_id'] = game_frame['opponent_id'].map(
constant.team_id) #map second team to team id
team_basic_stats = game_frame.iloc[:, [1, 0, 27] +
list(range(8, 27))]
#print(player_basic_stats)
query.insert_many(team_basic_stats,
"basketball_team_game_basic")
print(x)
def player_game_log():
with sql_query.SqlQuery() as query:
players_seasons = query.get_table(
'player_season', dataframe=False
) # gets avaliable seasons for every active player from sql database
len_players_seasons = len(players_seasons)
a = 2559
for x in range(
2560, len_players_seasons
): # for each player season in seasons starting with first active
time.sleep(2)
player_season_games = endpoints.PlayerGameLog(
player_id=players_seasons[x][0],
season=players_seasons[x][1] - 210000,
headers=constant.headers).get_normalized_dict(
)['PlayerGameLog']
# player game event data
game_frame = pd.DataFrame(
player_season_games) # holds season of games for player
game_frame = game_frame.rename(
columns={
'SEASON_ID': 'season_id',
'Player_ID': 'player_id',
'Game_ID': 'event_id',
'GAME_DATE': 'game_date',
'MIN': 'min',
'FGM': 'fgm',
'FGA': 'fga',
'FG_PCT': 'fg_pct',
'FG3M': 'fg3m',
'FG3A': 'fg3a',
'FG3_PCT': 'fg3_pct',
'FTM': 'ftm',
'FTA': 'fta',
'FT_PCT': 'ft_pct',
'OREB': 'oreb',
'DREB': 'dreb',
'REB': 'reb',
'AST': 'ast',
'STL': 'stl',
'BLK': 'blk',
'TOV': 'tov',
'PF': 'pf',
'PTS': 'pts'
})
game_frame['team_id'] = game_frame['MATCHUP'].str.extract(
r'(^\w{3})') #get first team initiasls
game_frame['opponent_id'] = game_frame['MATCHUP'].str.extract(
r'\W\s(\w{3})') #get second team initials
game_frame['team_id'] = game_frame['team_id'].map(
constant.team_id) #map first team to our team id
game_frame['opponent_id'] = game_frame['opponent_id'].map(
constant.team_id) #map second team to team id
game_frame['season_id'] = game_frame['season_id'].astype(
int) + 190000 #set our identifier before it
player_event = game_frame.iloc[:, [2, 1, 27, 28, 0]]
player_basic_stats = game_frame.iloc[:,
[2, 1] + list(range(6, 25))]
query.insert_many(player_event, "player_event")
query.insert_many(player_basic_stats,
"basketball_player_game_basic")
a = a + 1
print(a)