def interception_leaders(): session = DbSessionFactory.create_session() afc_interception_query = ( session.query( WeeklyNFLPlayerStats.interceptions, WeeklyNFLPlayerStats.player_id ) .join(ActiveNFLPlayers) .outerjoin(TeamInfo, ActiveNFLPlayers.team_id == TeamInfo.team_id) .filter(TeamInfo.conference_id == 0) .filter(WeeklyNFLPlayerStats.interceptions) .limit(20) .all() ) print(afc_interception_query) afc_interception_list = [ list(afc_interception_query) for afc_interception_query in afc_interception_query ] print(afc_interception_list) afc_sorted_interceptions = sorted(afc_interception_list, reverse=True) rank = 0 for _, grp in groupby(afc_sorted_interceptions, key=lambda xs: xs[0]): r = rank + 1 for x in grp: x.append(r) rank += 1 print(afc_sorted_interceptions)
def get_account_info(cls, user_id): session = DbSessionFactory.create_session() account_info = session.query(Account).filter( Account.id == user_id).all() return account_info
def get_rec_stats(): session = DbSessionFactory.create_session() season_row = session.query(SeasonInfo).filter( SeasonInfo.id == "1").first() season = season_row.current_season response = requests.get( "https://api.mysportsfeeds.com/v2.0/pull/nfl/" + str(season) + "-regular/player_stats_totals.json?position=WR,TE&stats=Yds", auth=HTTPBasicAuth(secret.msf_api, secret.msf_v2pw), ) player_json = response.json() player_data = player_json["playerStatsTotals"] for players in player_data: try: player_id = players["player"]["id"] recyds = players["stats"]["receiving"]["recYards"] except KeyError: continue week = TimeService.get_week() weekly_team_stats = WeeklyNFLPlayerStats(player_id=player_id, season=season, recyds=recyds, week=week) session.add(weekly_team_stats) session.commit()
def create_pick_types(cls): for x in range(1, 11): if x == 1: name = "team" elif x == 2: name = "player" elif x == 3: name = "points_for" elif x == 4: name = "passing" elif x == 5: name = "rushing" elif x == 6: name = "receiving" elif x == 7: name = "sacks" elif x == 8: name = "interceptions" elif x == 9: name = "wildcard" else: name = "tiebreaker" session = DbSessionFactory.create_session() pick_type_info = PickTypes(name=name) session.add(pick_type_info) session.commit()
def unique_team_picks(cls, pick_type, conf=None, div=None, rank=None): session = DbSessionFactory.create_session() season_row = session.query(SeasonInfo).filter(SeasonInfo.id == "1").first() current_season = season_row.current_season txtstr = "UPDATE PlayerPicks SET multiplier=2 WHERE team_id IN " txtstr += "(SELECT team_id FROM (select DISTINCT(team_id), COUNT(team_id) AS ct FROM PlayerPicks WHERE " midstr = " GROUP BY team_id) WHERE ct=1) " condstr = "pick_type=" + str(pick_type) + " AND season=" + str(current_season) if conf is not None: condstr += " AND conf_id=" + str(conf) if div is not None: condstr += " AND division_id=" + str(div) if rank is not None: condstr += " AND rank=" + str(rank) txtstr += condstr + midstr + "AND " + condstr # print(txtstr) session.execute(txtstr) session.commit() session.close()
def display_player_standings(player_id, season=None): if season is None: season = get_seasons() sqlstr = ( "SELECT coalesce(w.points_earned,0) as points, a.first_name, a.last_name, w.pick_id, p.pick_type, " "p.rank, p.multiplier, t.name, ") sqlstr += "c.conference, d.division, ap.firstname, ap.lastname " sqlstr += "FROM PlayerPicks p, Account a " sqlstr += "LEFT JOIN WeeklyPlayerResults w on p.pick_id = w.pick_id " sqlstr += ( "AND w.week = (SELECT MAX(week) from WeeklyPlayerResults WHERE season=" + str(season) + ") ") sqlstr += "LEFT JOIN DivisionInfo d on p.division_id=d.division_id " sqlstr += "LEFT JOIN ConferenceInfo c ON p.conf_id= c.conf_id " sqlstr += "LEFT JOIN TeamInfo t ON p.team_id = t.team_id " sqlstr += "LEFT JOIN ActiveNFLPlayers ap ON p.player_id = ap.player_id AND p.season = ap.season " sqlstr += "WHERE " sqlstr += "p.user_id = a.id " sqlstr += "AND p.season = " + str(season) + " " sqlstr += "AND p.user_id = '" + player_id + "'" session = DbSessionFactory.create_session() standings = session.execute(sqlstr) dict_standings = [dict(row) for row in standings] session.close() return dict_standings
def display_weekly_standings(season=None): # return list that contains player standings for most recent week in results table if season is None: season = get_seasons() sqlstr = ( "SELECT SUM(w.points_earned) as total_points, a.first_name, a.last_name, a.id " "from WeeklyPlayerResults w, PlayerPicks p, Account a ") sqlstr += "WHERE w.pick_id = p.pick_id AND p.user_id = a.id " sqlstr += "AND w.season = " + str(season) + " " sqlstr += "AND p.season = " + str(season) + " " sqlstr += ( "AND w.week = (SELECT MAX(week) from WeeklyPlayerResults WHERE season = " + str(season) + ") ") sqlstr += "GROUP BY p.user_id " sqlstr += "ORDER BY total_points DESC" session = DbSessionFactory.create_session() standings = session.execute(sqlstr) dict_standings = [dict(row) for row in standings] session.close() return dict_standings
def get_points_for(): session = DbSessionFactory.create_session() season_row = session.query(SeasonInfo).filter( SeasonInfo.id == "1").first() season = season_row.current_season response = requests.get( "https://api.mysportsfeeds.com/v2.0/pull/nfl/" + str(season) + "-regular/team_stats_totals.json?stats=pointsFor", auth=HTTPBasicAuth(secret.msf_api, secret.msf_v2pw), ) points_for_json = response.json() points_for_data = points_for_json["teamStatsTotals"] x = 0 for teams in points_for_data: team_id = int(points_for_data[x]["team"]["id"]) points_for = points_for_data[x]["stats"]["standings"]["pointsFor"] week = TimeService.get_week() x += 1 session.query(WeeklyTeamStats).filter( WeeklyTeamStats.team_id == team_id).filter( WeeklyTeamStats.season == season).filter( WeeklyTeamStats.week == week).update( {"points_for": points_for}) session.commit()
def change_picks(user_id, season): session = DbSessionFactory.create_session() picks_query = (session.query( PlayerPicks.pick_type, ConferenceInfo.conference, DivisionInfo.division, TeamInfo.name, PlayerPicks.rank, TeamInfo.team_id, PlayerPicks.rank, DivisionInfo.division_id, ConferenceInfo.conf_id, ActiveNFLPlayers.firstname, ActiveNFLPlayers.lastname, PlayerPicks.multiplier, PlayerPicks.player_id, ).outerjoin(ConferenceInfo).outerjoin(DivisionInfo).outerjoin( TeamInfo).outerjoin( ActiveNFLPlayers, and_( PlayerPicks.player_id == ActiveNFLPlayers.player_id, PlayerPicks.season == ActiveNFLPlayers.season, ), ).filter(PlayerPicks.user_id == user_id, PlayerPicks.season == season).all()) return picks_query
def seasons_played(cls, user_id): session = DbSessionFactory.create_session() seasons_played = (session.query(PlayerPicks.season).distinct( PlayerPicks.season).filter(Account.id == user_id)) return seasons_played
def create_season(cls, season): session = DbSessionFactory.create_session() season_row = session.query(SeasonInfo) if season_row.count() == 0: print("New install, adding a season") response = requests.get( "https://api.mysportsfeeds.com/v2.0/pull/nfl/" + str(season) + "-regular/games.json", auth=HTTPBasicAuth(secret.msf_api, secret.msf_v2pw), ) gameday_json = response.json() gameday_data = gameday_json["games"][0] first_game_date = gameday_data["schedule"]["startTime"] home_team = gameday_data["schedule"]["homeTeam"]["id"] away_team = gameday_data["schedule"]["awayTeam"]["id"] first_game_calc = pendulum.parse(first_game_date) new_season = SeasonInfo( season_start_date=first_game_date, home_team=home_team, away_team=away_team, current_season=season, ) session.add(new_season) session.commit() else: print("Existing season found, updating to new year") response = requests.get( "https://api.mysportsfeeds.com/v2.0/pull/nfl/" + str(season) + "-regular/games.json", auth=HTTPBasicAuth(secret.msf_api, secret.msf_v2pw), ) gameday_json = response.json() gameday_data = gameday_json["games"][0] first_game_date = gameday_data["startTime"] home_team = gameday_data["homeTeam"]["id"] away_team = gameday_data["awayTeam"]["id"] first_game_calc = pendulum.parse(first_game_date) update_row = session.query(SeasonInfo).filter(SeasonInfo.id == "1").first() update_row.current_season = season update_row.season_start_date = pendulum.instance(first_game_calc) update_row.away_team = away_team update_row.home_team = home_team session.commit()
def reset_paid(): session = DbSessionFactory.create_session() for player in session.query(Account): session.query(Account.paid).update({"paid": 0}) session.commit()
def get_account_date(cls, user_id): session = DbSessionFactory.create_session() account_created = session.query(Account.created).first() account_string = str(account_created[0]) account_date_split = account_string.split() account_date = account_date_split[0] return account_date
def get_team_info(): session = DbSessionFactory.create_session() season_query = session.query(SeasonInfo.current_season).first() season = season_query[0] x = 0 response = requests.get( "https://api.mysportsfeeds.com/v2.0/pull/nfl/" + str(season) + "-regular/standings.json", auth=HTTPBasicAuth(secret.msf_api, secret.msf_v2pw), ) data = response.json() teamlist = data["teams"] # Create a loop to extract all team info and insert into the database for team_list in teamlist: team_name = teamlist[x]["team"]["name"] team_city = teamlist[x]["team"]["city"] team_id = int(teamlist[x]["team"]["id"]) team_abbr = teamlist[x]["team"]["abbreviation"] conference_name = teamlist[x]["conferenceRank"]["conferenceName"] if team_id <= 55: division_id = 1 elif team_id <= 63: division_id = 2 elif team_id <= 71: division_id = 3 else: division_id = 4 if conference_name == "AFC": conference_id = 0 else: conference_id = 1 x += 1 team_info = TeamInfo( city=team_city, team_id=team_id, team_abbr=team_abbr, name=team_name, conference_id=conference_id, division_id=division_id, ) session.add(team_info) session.commit()
def admin_check(): session = DbSessionFactory.create_session() season_start_query = session.query( SeasonInfo.season_start_date).first() session.close() return season_start_query
def update_admin(cls, user_id: str): session = DbSessionFactory.create_session() for player in session.query(Account.id).filter(Account.id == user_id): session.query(Account.id).filter(Account.id == user_id).update( {"is_super_user": 1}) session.commit()
def update_nflschedule( cls, season: int, game_id: int, game_date: str, away_team: int, home_team: int, week: int, ): session = DbSessionFactory.create_session() season_row = session.query(SeasonInfo).filter( SeasonInfo.id == "1").first() season = season_row.current_season response = requests.get( "https://api.mysportsfeeds.com/v2.0/pull/nfl/" + str(season) + "-regular/games.json", auth=HTTPBasicAuth(secret.msf_api, secret.msf_v2pw), ) schedule_query = response.json() team_schedule = schedule_query["games"] print(type(team_schedule), team_schedule) x = 0 for schedule in team_schedule: game_id = team_schedule[x]["schedule"]["id"] week = team_schedule[x]["schedule"]["week"] game_time = team_schedule[x]["schedule"]["startTime"] away_team = team_schedule[x]["schedule"]["awayTeam"]["id"] home_team = team_schedule[x]["schedule"]["homeTeam"]["id"] game_date = pendulum.parse(game_time) x = x + 1 season_row = session.query(SeasonInfo).filter( SeasonInfo.id == "1").first() season = season_row.current_season add_schedule = NFLSchedule( game_id=game_id, game_date=game_date, away_team=away_team, home_team=home_team, week=week, season=season, ) session.add(add_schedule) session.commit()
def admin_check(): session = DbSessionFactory.create_session() su__query = (session.query( Account.id).filter(Account.is_super_user == 1).filter( Account.id == self.logged_in_user_id).first()) print(su__query) if not su__query[0] == self.logged_in_user_id: print("You must be an administrator to view this page") self.redirect("/home")
def find_reset_code(cls, code): if not code or not code.strip(): return None session = DbSessionFactory.create_session() reset = session.query(PasswordReset).filter( PasswordReset.id == code).first() return reset
def division_winners(): session = DbSessionFactory.create_session() season_row = session.query(SeasonInfo).filter(SeasonInfo.id == "1").first() season = season_row.current_season season_start = season_row.season_start_date today = datetime.date.today() days = abs(today - season_start) week = int((days / 7) + 1)
def update_paid(cls, user_id: str): session = DbSessionFactory.create_session() for player in session.query(Account.id).filter(Account.id == user_id): session.query(Account.id).filter(Account.id == user_id).update( {"paid": 1}) session.commit() session.close()
def update_weekly_stats_post(self): vm = UpdateWeeklyStats() vm.from_dict(self.request.POST) session = DbSessionFactory.create_session() week = TimeService.get_week() season_row = session.query(SeasonInfo).filter( SeasonInfo.id == "1").first() season = season_row.current_season row = (session.query(WeeklyTeamStats.week).filter( WeeklyTeamStats.season == season).order_by( WeeklyTeamStats.week.desc()).first()) # Check if the stats have already been updated for the week and, if so, redirect # Try / Except to determine if it's Week 1 (Week would be empty resulting in a TypeError NoneType) try: if row[0] == week or week >= 18: self.redirect("/admin/stats_already_ran") else: # Insert weekly team and player stats WeeklyStatsService.get_qb_stats() WeeklyStatsService.get_rb_stats() WeeklyStatsService.get_rec_stats() WeeklyStatsService.get_sack_stats() WeeklyStatsService.get_interception_stats() WeeklyStatsService.get_rankings() WeeklyStatsService.get_points_for() WeeklyStatsService.get_tiebreaker() StandingsService.update_player_pick_points() StandingsService.update_team_pick_points() # redirect on finish self.redirect("/admin") except TypeError: # Insert weekly team and player stats WeeklyStatsService.get_qb_stats() WeeklyStatsService.get_rb_stats() WeeklyStatsService.get_rec_stats() WeeklyStatsService.get_sack_stats() WeeklyStatsService.get_interception_stats() WeeklyStatsService.get_rankings() WeeklyStatsService.get_points_for() WeeklyStatsService.get_tiebreaker() StandingsService.update_player_pick_points() StandingsService.update_team_pick_points() # redirect on finish self.redirect("/admin")
def get_week(): session = DbSessionFactory.create_session() season_row = session.query(SeasonInfo).filter( SeasonInfo.id == "1").first() season_start = pendulum.parse(season_row.season_start_date) diff = TimeService.get_time() - season_start week = int((diff.days / 7) + 1) return week
def find_account_by_id(cls, user_id): if not user_id: return None user_id = user_id.strip() session = DbSessionFactory.create_session() account = session.query(Account).filter(Account.id == user_id).first() return account
def stats_already_ran(self): session = DbSessionFactory.create_session() su__query = (session.query( Account.id).filter(Account.is_super_user == 1).filter( Account.id == self.logged_in_user_id).first()) if su__query is None: print("You must be an administrator to view this page") self.redirect("/home") return {}
def all_seasons_played(): """This method is used to get a list of all seasons played and display on the Standings index page for players to click through to see the season standings / points scored by player""" session = DbSessionFactory.create_session() seasons_played = (session.query(PlayerPicks.season).distinct( PlayerPicks.season).order_by(PlayerPicks.season.desc())) session.close() return seasons_played
def update_weekly_stats(self): session = DbSessionFactory.create_session() su__query = (session.query( Account.id).filter(Account.is_super_user == 1).filter( Account.id == self.logged_in_user_id).first()) if su__query is None: print("You must be an administrator to view this page") self.redirect("/home") vm = UpdateWeeklyStats() return vm.to_dict()
def new_season_get(self): session = DbSessionFactory.create_session() su__query = (session.query( Account.id).filter(Account.is_super_user == 1).filter( Account.id == self.logged_in_user_id).first()) if su__query is None: print("You must be an administrator to view this page") self.redirect("/home") vm = NewSeasonViewModel() return vm.to_dict()
def find_account_by_email(cls, email): if not email or not email.strip(): return None email = email.lower().strip() session = DbSessionFactory.create_session() account = session.query(Account).filter(Account.email == email).first() return account
def set_password(cls, plain_text_password, account_id): print("Resetting password for user {}".format(account_id)) session = DbSessionFactory.create_session() account = session.query(Account).filter( Account.id == account_id).first() if not account: print("Warning: Cannot reset password, no account found.") return print("New password set.") account.password_hash = AccountService.hash_text(plain_text_password) session.commit()