supercontest.dbsession.queries

Common wrappers around select statements. This is the only module that calls selects, the module that makes the actual queries.

For the most part, these wrappers extract the relevant information from the db rows. In some cases (rarer), the wrappers simply pass through the sqla objects.

This module contains the lazy-loading functionality of the cache, for the function level (memoization). View caching is handled in the views. A read will try the cache first. If hit, it will return. If miss, it will query the db, then update the cache, then return. Note that redis caches serialized values well - but NOT nested sqla objects. The parent won’t be bound to a session, so all relationships are unloadable. For this reason, caching the functions in THIS module is preferred over caching the functions in the selects module.

Module Attributes

MEMOIZE_TTL

How long to keep cache entries alive (in seconds), by default.

Functions

`are_games_in_db`	Checks if there are any rows in the Game table for a given season and week.
`are_picks_open`	The primary determinant of whether or not picks can be submitted.
`get_admin_emails`	Gives all the email addresses for users with the 'Admin' role.
`get_all_team_names`	Provides a simple list of all team names.
`get_all_user_emails`	Simply gives the full list of user emails (unique).
`get_contestant_from_team`	For a given week, simply provide the team name and this will give the contestant obj.
`get_current_season`	Simple return of current season year.
`get_current_season_and_week`	Simple return of the current season year and week number.
`get_current_week`	Simple return of current week number.
`get_current_week_start`	Simple return of the start date of the current week.
`get_distinct_lines`	Fetches lines from the DB.
`get_distinct_margins`	Fetches performance margins from the DB.
`get_finished_games_in_season`	Returns all games that have finished in a given season.
`get_finished_picks`	Returns all picks that have finished.
`get_games`	Returns all games in the database.
`get_games_in_week`	Returns all games in a given week.
`get_id_email_map`	Grabs the email for every row in the user table.
`get_id_name_map`	Grabs the display name for every row in the user table, returning the first+last name if populated.
`get_league_names`	Get leagues associated with a season.
`get_location_id`	Provide both teams in a matchup, and the team to check, and the home team.
`get_map_espn_abbv_status_id`	Provides a lookup for common scorefetch/dbstatus refs.
`get_map_espn_city_team`	Provides a lookup for common team-city pairs.
`get_max_season`	Simple return of max season year.
`get_max_week`	Used for "how many weeks are in a season?" Basically "17 if season <= 2020 else 18" but hits the database to be sure.
`get_ordered_games_in_week`	Returns all games in a given week.
`get_paid_league_for_season`	Returns the paid league for a specific season.
`get_paid_league_id_for_season`	Returns the ID of the official paid league for a specific season.
`get_paid_users_for_season`	Given a year, returns the IDs of the paid users in that season.
`get_picks_in_season_for_league`	Returns all picks for a given league in a given season.
`get_picks_in_week`	Returns all picks in a given week.
`get_picks_in_week_for_league`	Returns all picks for a given league in a given week.
`get_picks_in_week_for_user`	Returns all picks for a given user in a given week.
`get_prediction_id`	Provide both teams in a matchup, and the team to check, and the line (for push conditions).
`get_role_from_name`	Returns the id for a Role row, given its name.
`get_sorted_seasons`	Numerically sorted season years, ascending.
`get_status_id_from_name`	For a given name, get the SBSC status ID.
`get_status_names`	Provides a simple list of all status names.
`get_team_id`	Returns the id for a team, given its name.
`get_user_from_email`	Takes an email, returns a user object.
`get_users_in_league`	Gets the users in a league.
`get_users_in_season`	Uses picks to determine who is in a season.
`get_users_with_email_pref`	Gives the list of user email addresses for all users that have a given subscription preference.
`get_week_id`	Returns the id for a week, given its number and the season year.
`is_league_in_season`	Checks if a league is valid for a season.
`is_offseason`	If you're in the offseason.
`is_paid_user`	Checks if a given user has paid for a given season.
`is_postseason`	If you're in the time after the last week_end (for week 18) but before `season_end`.
`is_preseason`	If you're in the time after `season_start` but also before `week_start` for week 1.
`is_regseason`	If you're in an active regular season week.
`is_user_in_league`	Checks if a user is in a league.
`print_paid_player_emails`	Just prints out an alphabetically sorted list of official players for a season, for easy copy-paste to email the league.

supercontest.dbsession.queries.MEMOIZE_TTL = 30: How long to keep cache entries alive (in seconds), by default. This is mostly to optimize multiple function calls WITHIN the same request session, so that I don’t have to be careful about reentrance in app logic (eg checking membership, recreating maps, get_current_week, stuff like that).

supercontest.dbsession.queries.are_games_in_db(season: int, week: int) → bool

Checks if there are any rows in the Game table for a given season and week. Usually called to determine if you’re in limbo after week_start (5pm PT) but before Westgate has posted.

Parameters:

season – Season year.
week – Week number.

Returns:

If the games for that week are already in the database.

supercontest.dbsession.queries.are_picks_open() → bool

The primary determinant of whether or not picks can be submitted. The condition is that the current timestamp is within 79 hours of the current week’s start (5pm Wed -> Midnight Saturday). This is used by views to configure many things, functional and visual. If you’re not in the current week, this obviously returns False.

Returns:: Whether or not picks are open for the current week.

supercontest.dbsession.queries.get_admin_emails() → list[str]

Gives all the email addresses for users with the ‘Admin’ role.

Returns:: Admin email addresses.

supercontest.dbsession.queries.get_all_team_names() → list[str]

Provides a simple list of all team names.

Returns:: Team names (first letter capitalized).

supercontest.dbsession.queries.get_all_user_emails() → list[str]

Simply gives the full list of user emails (unique).

Returns:: All user email addresses in the database.

supercontest.dbsession.queries.get_contestant_from_team(season: int, week: int, team: str) → Contestant

For a given week, simply provide the team name and this will give the contestant obj.

Parameters:

season – Season year.
week – Week number.
team – Team name.

Returns:

The Contestant row corresponding to the inputs.

supercontest.dbsession.queries.get_current_season() → int | None

Simple return of current season year.

Returns:: Current season year or None if not in an active season.

supercontest.dbsession.queries.get_current_season_and_week() → tuple[int | None, int | None]

Simple return of the current season year and week number.

Returns:: Current season year or None if not in an active season.
Returns:: Current week number or None if not in an active week.

supercontest.dbsession.queries.get_current_week() → int | None

Simple return of current week number.

Returns:: Current week number or None if not in an active week.

supercontest.dbsession.queries.get_current_week_start() → datetime | None

Simple return of the start date of the current week.

Returns:: The start date of the current week or None if not in an active week.

supercontest.dbsession.queries.get_distinct_lines() → list[float]

Fetches lines from the DB. Adds negative lines.

Returns:: An unordered list of all distinct lines.

supercontest.dbsession.queries.get_distinct_margins() → list[float]

Fetches performance margins from the DB.

Returns:: An unordered list of all distinct margins.

supercontest.dbsession.queries.get_finished_games_in_season(season: int) → list[Game]

Returns all games that have finished in a given season.

Parameters:: season – Season year.
Returns:: All games that have finished.

supercontest.dbsession.queries.get_finished_picks() → list[Pick]

Returns all picks that have finished.

Returns:: All picks that have finished.

supercontest.dbsession.queries.get_games() → list[Game]

Returns all games in the database.

Returns:: All games in the database.

supercontest.dbsession.queries.get_games_in_week(season: int, week: int) → list[Game]

Returns all games in a given week.

Parameters:

season – Season year.
week – Week number.

Returns:

All games in that week.

supercontest.dbsession.queries.get_id_email_map() → dict[int, str]

Grabs the email for every row in the user table.

Returns:: Keys are user IDs and values are emails.

supercontest.dbsession.queries.get_id_name_map() → dict[int, str]

Grabs the display name for every row in the user table, returning the first+last name if populated. If not, returns the email.

Returns:: Keys are user IDs and values are names (or emails if no name).

supercontest.dbsession.queries.get_league_names(season: int) → list[tuple[int, str]]

Get leagues associated with a season.

Parameters:: season – Season year.
Returns:: Pairs of league ID and league name.

supercontest.dbsession.queries.get_location_id(team: str, favored_team: str, underdog_team: str, home_team: str | None) → int

Provide both teams in a matchup, and the team to check, and the home team.

Parameters:

team – The team to get the prediction for.
favored_team – The favored team in the matchup.
underdog_team – The underdog team in the matchup.
home_team – The specified home team.

Returns:

The ID of the location object.

Raises:

ValueError – If the provided team is not in the matchup.

supercontest.dbsession.queries.get_map_espn_abbv_status_id() → dict[str, int]

Provides a lookup for common scorefetch/dbstatus refs.

Returns:: ESPN games statuses -> SBSC status IDs.

supercontest.dbsession.queries.get_map_espn_city_team() → dict[str, str]

Provides a lookup for common team-city pairs. Note that it’s the ESPN city (returned from score fetch), not the SBSC standard city.

Returns:: Keys are team cities, values are team names.

supercontest.dbsession.queries.get_max_season() → int

Simple return of max season year.

Returns:: The most recent season in the database.

supercontest.dbsession.queries.get_max_week(season: int) → int

Used for “how many weeks are in a season?” Basically “17 if season <= 2020 else 18” but hits the database to be sure.

Parameters:: season – Season year.
Returns:: The number of weeks in that season.

supercontest.dbsession.queries.get_ordered_games_in_week(season: int, week: int) → list[Game]

Returns all games in a given week. Sort is by datetime chronologically ascending, then favored_team alphabetically.

Parameters:

season – Season year.
week – Week number.

Returns:

All games in that week, ordered by datetime.

supercontest.dbsession.queries.get_paid_league_for_season(season: int | None) → League

Returns the paid league for a specific season. This is denoted by the league name being “Paid” - if there isn’t one with that name for that season, this errors. If multiple exist (should never happen), this takes the first one. Raises an error (via one()) if there are no paid leagues for that season.

Parameters:: season – Season year.
Returns:: The paid league object.

supercontest.dbsession.queries.get_paid_league_id_for_season(season: int | None) → int

Returns the ID of the official paid league for a specific season. This is denoted by the league name being “Paid” - if there isn’t one with that name for that season, this errors. If multiple exist (should never happen), this takes the first one. Raises an error (via one()) if there are no paid leagues for that season.

Parameters:: season – Season year.
Returns:: The paid league ID.

supercontest.dbsession.queries.get_paid_users_for_season(season: int, return_emails: Literal[False] = False) → list[int]

supercontest.dbsession.queries.get_paid_users_for_season(season: int, return_emails: Literal[True]) → list[str]

supercontest.dbsession.queries.get_paid_users_for_season(season: int, return_emails: bool) → list[int] | list[str]

Given a year, returns the IDs of the paid users in that season. Can obviously just len() this function’s return to determine league size, for calculations like total payout purse. Raises an error if there’s no paid league for that season.

Parameters:

season – Season year.
return_emails – Returns IDs by default. Pass True to return emails instead.

Returns:

User IDs or emails, depending on the input.

supercontest.dbsession.queries.get_picks_in_season_for_league(season: int, league_id: int) → list[Pick]

Returns all picks for a given league in a given season.

Parameters:

season – Season year.
league_id – League ID.

Returns:

All picks for that league in that season.

supercontest.dbsession.queries.get_picks_in_week(season: int, week: int) → list[Pick]

Returns all picks in a given week.

Parameters:

season – Season year.
week – Week number.

Returns:

All picks in that week.

supercontest.dbsession.queries.get_picks_in_week_for_league(season: int, week: int, league_id: int) → list[Pick]

Returns all picks for a given league in a given week.

Parameters:

season – Season year.
week – Week number.
league_id – League ID.

Returns:

All picks for that league in that week.

supercontest.dbsession.queries.get_picks_in_week_for_user(season: int, week: int, user_id: int) → list[Pick]

Returns all picks for a given user in a given week.

Parameters:

season – Season year.
week – Week number.
user_id – User ID.

Returns:

All picks for that user in that week.

supercontest.dbsession.queries.get_prediction_id(team: str, favored_team: str, underdog_team: str, line: float) → int

Provide both teams in a matchup, and the team to check, and the line (for push conditions).

Parameters:

team – The team to get the prediction for.
favored_team – The favored team in the matchup.
underdog_team – The underdog team in the matchup.
line – The value of the line in the matchup:

Returns:

The ID of the prediction object.

Raises:

ValueError – If this function doesn’t match the expected prediction (internal error).

supercontest.dbsession.queries.get_role_from_name(name: str) → Role

Returns the id for a Role row, given its name.

Parameters:: name – Name of the Role to lookup.
Returns:: The Role object.

supercontest.dbsession.queries.get_sorted_seasons() → list[int]

Numerically sorted season years, ascending.

Returns:: All season years, from oldest to newest.

supercontest.dbsession.queries.get_status_id_from_name(name: str) → int

For a given name, get the SBSC status ID.

Parameters:: name – The name of the status.
Returns:: The ID of that status row.

supercontest.dbsession.queries.get_status_names() → list[str]

Provides a simple list of all status names.

Returns:: Status names (first letter capitalized).

supercontest.dbsession.queries.get_team_id(name: str) → int

Returns the id for a team, given its name. Compares with ilike, so the argument can be capitalized, lowered, uppered, etc.

Parameters:: name – The name of the team.
Returns:: Team ID.

supercontest.dbsession.queries.get_user_from_email(email: str) → User

Takes an email, returns a user object. This is case-insensitive. If the email address has been lowered, like the admin.late_picks form does, it will still find the right user.

Parameters:: email – Email.
Returns:: The user object.

supercontest.dbsession.queries.get_users_in_league(league_id: int, return_emails: Literal[False] = False) → list[int]

supercontest.dbsession.queries.get_users_in_league(league_id: int, return_emails: Literal[True]) → list[str]

supercontest.dbsession.queries.get_users_in_league(league_id: int, return_emails: bool) → list[int] | list[str]

Gets the users in a league. Handles the league_id=0 case (the free league, all users).

Parameters:

league_id – League ID.
return_emails – Returns IDs by default. Pass True to return emails instead.

Returns:

User IDs or emails, depending on the input.

supercontest.dbsession.queries.get_users_in_season(season: int, league_id: int = 0) → set[User]

Uses picks to determine who is in a season. Can filter down into any league as well. Most commonly used for the main paid league in each season.

Parameters:

season – Season year.
league_id – League ID. If None, returns all users. Effectively the Free league, since it does not filter the results on league.

Returns:

User objects.

supercontest.dbsession.queries.get_users_with_email_pref(pref: str) → list[str]

Gives the list of user email addresses for all users that have a given subscription preference.

Parameters:: pref – The name of the preference, eg email_when_picks_open.
Returns:: The email addresses of the users which have that preference enabled.

supercontest.dbsession.queries.get_week_id(season: int | None, week: int | None) → int

Returns the id for a week, given its number and the season year.

Parameters:

season – Season year.
week – Week number.

Returns:

Week ID.

supercontest.dbsession.queries.is_league_in_season(league_id: int, season: int) → bool

Checks if a league is valid for a season. Free league (id=0) is considered to be in all seasons.

Parameters:

league_id – League ID.
season – Season year.

Returns:

If the league is valid for that season.

supercontest.dbsession.queries.is_offseason() → bool

If you’re in the offseason.

Returns:: False if regseason, preseason, or postseason. True if offseason.

supercontest.dbsession.queries.is_paid_user(season: int | None, user_id: int) → bool

Checks if a given user has paid for a given season. If a user is requesting this info during an offseason, it defaults to the status of the most recent season (i.e. if you are a paid player during a season, it stays that way until the next season starts).

Parameters:

season – Season year.
user_id – User ID.

Returns:

If the user is in the paid league for that season.

supercontest.dbsession.queries.is_postseason() → bool

If you’re in the time after the last week_end (for week 18) but before season_end. Just take the complement of the others.

Returns:: False if regseason, offseason, or preseason. True if postseason.

supercontest.dbsession.queries.is_preseason() → bool

If you’re in the time after season_start but also before week_start for week 1.

Returns:: False if regseason, offseason, or postseason. True if preseason.

supercontest.dbsession.queries.is_regseason() → bool

If you’re in an active regular season week.

Returns:: False if offseason, preseason, or postseason. True if regseason.

supercontest.dbsession.queries.is_user_in_league(user_id: int, league_id: int) → bool

Checks if a user is in a league. If checking against the free league (id=0), then every user is considered active.

Parameters:

user_id – User ID.
league_id – League ID.

Returns:

If the user is in the league.

supercontest.dbsession.queries.print_paid_player_emails(season: int) → None

Just prints out an alphabetically sorted list of official players for a season, for easy copy-paste to email the league. Raises an error if there’s no paid league for that season.

Parameters:: season – Season year.