def hitter_trades_post(self): """POST request to update the database with the trade information to create the player split.""" vm = TradesViewModel() vm.from_dict(self.request.POST) 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") TradeService.get_hitter_trade( vm.player_id, vm.team_id, vm.hr, vm.ba, vm.ab, vm.hits, vm.pa, vm.games, vm.rbi, ) session.close() # redirect self.redirect("/admin")
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_tiebreaker(): session = DbSessionFactory.create_session() season_row = session.query(SeasonInfo).filter( SeasonInfo.id == "1").first() season = season_row.current_season update_date = get_update_date() response = requests.get( "https://api.mysportsfeeds.com/v2.0/pull/mlb/" + str(season) + "-regular/standings.json?team=120", auth=HTTPBasicAuth(config.msf_api, config.msf_v2pw), ) team_json = response.json() team_data = team_json["teams"] for teams in team_data: twins_wins = teams["stats"]["standings"]["wins"] session.query(WeeklyTeamStats).filter( WeeklyTeamStats.team_id == 120).filter( update_date == update_date).filter( season == season).update( {"tiebreaker_twin_wins": twins_wins}) session.commit() session.close()
def find_changes(user_id): session = DbSessionFactory.create_session() season_row = (session.query( SeasonInfo.current_season).filter(SeasonInfo.id == 1).first()) season = season_row.current_season try: for pick in (session.query(PlayerPicks.changed).filter( PlayerPicks.user_id == user_id).filter( PlayerPicks.season == season).filter( PlayerPicks.changed).all()): print(pick) if pick[0] == 1: pick = True else: pick = False print(pick) return pick except TypeError: pick = 0 return pick session.close()
def display_player_standings(player_id, season=None): if season is None: season = get_seasons() sqlstr = "SELECT DISTINCT(w.pick_id), coalesce(w.points_earned,0) as points, a.first_name, a.last_name, p.pick_type, p.rank, p.multiplier, t.name, p.changed, " sqlstr += "c.league, 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.update_date = (SELECT MAX(update_date) from WeeklyPlayerResults WHERE season=" + str(season) + ") ") sqlstr += "LEFT JOIN DivisionInfo d on p.division_id=d.division_id " sqlstr += "LEFT JOIN LeagueInfo c ON p.league_id= c.league_id " sqlstr += "LEFT JOIN TeamInfo t ON p.team_id = t.team_id " sqlstr += "LEFT JOIN ActiveMLBPlayers 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 + "'" # print(sqlstr) 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=w.season " sqlstr += ( "AND w.update_date = (SELECT MAX(update_date) from WeeklyPlayerResults WHERE season = " + str(season) + ") ") sqlstr += "GROUP BY p.user_id " sqlstr += "ORDER BY total_points DESC" #print(sqlstr) session = DbSessionFactory.create_session() standings = session.execute(sqlstr) dict_standings = [dict(row) for row in standings] session.close() return dict_standings
def display_picks(user_id, season): session = DbSessionFactory.create_session() picks_query = (session.query( PlayerPicks.pick_type, LeagueInfo.league, DivisionInfo.division, TeamInfo.name, TeamInfo.team_id, PlayerPicks.rank, DivisionInfo.division_id, LeagueInfo.league_id, ActiveMLBPlayers.firstname, ActiveMLBPlayers.lastname, PlayerPicks.multiplier, PlayerPicks.twins_wins, PlayerPicks.changed, PlayerPicks.player_id, ).outerjoin(LeagueInfo).outerjoin(TeamInfo).outerjoin( DivisionInfo, and_(PlayerPicks.division_id == DivisionInfo.division_id)).outerjoin( ActiveMLBPlayers, and_( PlayerPicks.player_id == ActiveMLBPlayers.player_id, PlayerPicks.season == ActiveMLBPlayers.season, ), ).filter(PlayerPicks.user_id == user_id, PlayerPicks.season == season)) session.close() return picks_query
def get_all_accounts(): session = DbSessionFactory.create_session() account_list = session.query(Account).all() session.close() return account_list
def unique_player_picks(cls, pick_type, league): session = DbSessionFactory.create_session() season_row = session.query(SeasonInfo).filter( SeasonInfo.id == "1").first() current_season = season_row.current_season all_star_game_query = session.query( SeasonInfo.all_star_game_date).first() all_star_game_date_string = str(all_star_game_query[0]) all_star_game_datetime = pendulum.parse(all_star_game_date_string, tz="America/New_York") converted_date = pendulum.instance(all_star_game_datetime) all_star_game = converted_date.at(19) now_time = TimeService.get_time() # Calculate Unique Picks at season start if now_time < all_star_game: # Unique picks are 2 players - trying ct = 2 (instead of ct=1 txtstr = "UPDATE PlayerPicks SET multiplier=2 WHERE player_id IN " txtstr += "(SELECT player_id FROM (select DISTINCT(player_id), COUNT(player_id) AS ct FROM PlayerPicks WHERE " midstr = " GROUP BY player_id)PlayerPicks WHERE ct<3) " condstr = (" pick_type=" + str(pick_type) + " AND season=" + str(current_season)) condstr += " AND league_id=" + str(league) txtstr += condstr + midstr + "AND " + condstr # print(txtstr) session.execute(txtstr) session.commit() else: txtstr = ( "UPDATE PlayerPicks SET multiplier=2 WHERE changed=1 and player_id IN " ) txtstr += "(SELECT player_id FROM (select DISTINCT(player_id), COUNT(player_id) AS ct FROM PlayerPicks WHERE " midstr = " GROUP BY player_id)PlayerPicks WHERE ct<3) " condstr = (" pick_type=" + str(pick_type) + " AND season=" + str(current_season)) condstr += " AND league_id=" + str(league) txtstr += condstr + midstr + "AND " + condstr # print(txtstr) session.execute(txtstr) session.commit() session.close()
def admin_check(): session = DbSessionFactory.create_session() season_start_query = session.query(SeasonInfo.season_start_date).first() session.close() return season_start_query
def get_update_players_date(season): session = DbSessionFactory.create_session() qry = session.query( func.max(WeeklyMLBPlayerStats.update_date).label("max")) res = qry.one() latest_date = res.max session.close() return latest_date
def get_seasons(): session = DbSessionFactory.create_session() season_row = session.query(SeasonInfo).filter(SeasonInfo.id == "1").first() current_season = season_row.current_season session.close() return current_season
def trade_adjustments(): season = get_seasons() session = DbSessionFactory.create_session() # ASSUMES players are either pitcher or not # update all except batting average sqlstr = "UPDATE WeeklyMLBPlayerStats w " sqlstr += "INNER JOIN InterleagueTrades i " sqlstr += "ON i.player_id = w.player_id AND i.season=w.season " sqlstr += "SET w.home_runs = (w.home_runs - i.home_runs), " sqlstr += "w.at_bats = (w.at_bats - i.at_bats), " sqlstr += "w.hits = (w.hits - i.hits), " sqlstr += "w.plate_appearances = (w.RBI - i.RBI) " sqlstr += "WHERE w.batting_average IS NOT NULL " sqlstr += "AND w.season=" + str(season) session.execute(sqlstr) session.commit() # print(sqlstr) # update batting average sqlstr = "UPDATE WeeklyMLBPlayerStats w " sqlstr += "INNER JOIN InterleagueTrades i " sqlstr += "ON i.player_id = w.player_id AND i.season=w.season " sqlstr += "SET w.batting_average = (w.hits / w.at_bats) " sqlstr += "WHERE w.batting_average IS NOT NULL " sqlstr += "AND w.season=" + str(season) session.execute(sqlstr) session.commit() # print(sqlstr) # pitchers sqlstr = "UPDATE WeeklyMLBPlayerStats w " sqlstr += "INNER JOIN InterleagueTrades i " sqlstr += "ON i.player_id = w.player_id AND i.season=w.season " sqlstr += "SET w.pitcher_wins = (w.pitcher_wins-i.pitcher_wins), " sqlstr += "w.earned_runs = (w.earned_runs-i.earned_runs), " sqlstr += "w.innings_pitched=(w.innings_pitched-i.innings_pitched) " sqlstr += "WHERE w.ERA IS NOT NULL " sqlstr += "AND w.season=" + str(season) session.execute(sqlstr) session.commit() # print(sqlstr) # pitchers update ERA sqlstr = "UPDATE WeeklyMLBPlayerStats w " sqlstr += "INNER JOIN InterleagueTrades i " sqlstr += "ON i.player_id = w.player_id AND i.season=w.season " sqlstr += "SET " sqlstr += "w.ERA = ROUND(((w.earned_runs/w.innings_pitched)*9),2) " sqlstr += "WHERE w.ERA IS NOT NULL " sqlstr += "AND w.season=" + str(season) # print(sqlstr) session.execute(sqlstr) session.commit() session.close()
def seasons_played(cls, user_id): session = DbSessionFactory.create_session() seasons_played = (session.query(PlayerPicks.season).distinct( PlayerPicks.season).filter(Account.id == user_id)) session.close() return seasons_played
def get_seasons(): """Get the current active season from the database""" session = DbSessionFactory.create_session() season_row = session.query(SeasonInfo).filter(SeasonInfo.id == "1").first() current_season = season_row.current_season session.close() return current_season
def league_list(): session = DbSessionFactory.create_session() leagues = session.query(LeagueInfo).all() session.close() return leagues
def split_player_stats(): session = DbSessionFactory.create_session() season_row = session.query(SeasonInfo).filter( SeasonInfo.id == "1").first() season = season_row.current_season session.close()
def division_list(): session = DbSessionFactory.create_session() divisions = session.query(DivisionInfo).all() session.close() return divisions
def all_star_game_date(): """Get the time of the season opener's game""" session = DbSessionFactory.create_session() all_star_game_date = session.query(SeasonInfo.all_star_game_date).first() session.close() return all_star_game_date
def reset_paid(): session = DbSessionFactory.create_session() for player in session.query(Account): session.query(Account.paid).update({"paid": 0}) session.commit() session.close()
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 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 team_list(): session = DbSessionFactory.create_session() teams = (session.query(TeamInfo).filter(TeamInfo.team_id).order_by( TeamInfo.name).all()) session.close() return teams
def update_mlbplayers(): 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/mlb/" "players.json?season=" + str(season) + "&rosterstatus=assigned-to-roster", auth=HTTPBasicAuth(config.msf_api, config.msf_v2pw), ) player_info = response.json() player_list = player_info["players"] player_tuple = (session.query(ActiveMLBPlayers.player_id).filter( ActiveMLBPlayers.season == season).all()) current_players = [sql_players for sql_players, in player_tuple] print(current_players) for players in player_list: try: firstname = players["player"]["firstName"] lastname = players["player"]["lastName"] player_id = players["player"]["id"] team_id = players["player"]["currentTeam"]["id"] position = players["player"]["primaryPosition"] except KeyError: continue if int(player_id) not in current_players: updated_players = ActiveMLBPlayers( firstname=firstname, lastname=lastname, player_id=player_id, team_id=team_id, position=position, season=season, ) session.add(updated_players) session.commit() else: pass session.close()
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 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] session.close() return account_date
def last_game_date(): session = DbSessionFactory.create_session() last_game_date = session.query(SeasonInfo.season_end_date).first() last_game_info = str(last_game_date[0]) last_game = pendulum.parse(last_game_info, tz=timezone) final_date = last_game.add(days=3) session.close() return final_date
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 get_hitter_stats(): """Get stats for hitters (home runs, batting average and ERA) from MySportsFeeds and insert into the database""" 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/mlb/" + str(season) + "-regular/player_stats_totals.json?position=C,1B,2B,SS,3B,OF,RF,CF,LF,DH", auth=HTTPBasicAuth(config.msf_api, config.msf_v2pw), ) player_json = response.json() player_data = player_json["playerStatsTotals"] for players in player_data: try: player_id = players["player"]["id"] home_runs = players["stats"]["batting"]["homeruns"] RBI = players["stats"]["batting"]["runsBattedIn"] batting_average = players["stats"]["batting"]["battingAvg"] at_bats = players["stats"]["batting"]["atBats"] hits = players["stats"]["batting"]["hits"] plate_appearances = players["stats"]["batting"][ "plateAppearances"] player_games_played = players["stats"]["gamesPlayed"] except KeyError: continue update_date = get_update_date() weekly_player_stats = WeeklyMLBPlayerStats( player_id=player_id, season=season, home_runs=home_runs, RBI=RBI, batting_average=batting_average, at_bats=at_bats, hits=hits, plate_appearances=plate_appearances, player_games_played=player_games_played, update_date=update_date, ) session.add(weekly_player_stats) session.commit() session.close()
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