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()
