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.

The wrappers marked @memoize remember their answers for the rest of the request (supercontest.dbsession.memo), so a caller never has to be careful about asking the same question twice. Shared response caching is the API’s, in api/cache.py.

Module Attributes

Scope

Whose picks make someone part of the league: any season's, or this season's.

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.
`count_games_in_stats_scope`	How many games the statistics pages cover.
`get_admin_emails`	Gives all the email addresses of admins (`is_superuser`).
`get_all_team_names`	Provides a simple list of all team names.
`get_all_users`	Every user row.
`get_contestant_from_team`	For a given week, simply provide the team name and this will give the contestant obj.
`get_contestants_by_team`	A week's contestants, keyed by lowercased team name, in one read.
`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_finished_picks`	Returns all picks that have finished.
`get_game`	One game by id.
`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: the first and last name if both are set, else the masked address (`mask_email()`).
`get_league_ids_for_user`	Every league a user belongs to, in every season.
`get_league_names`	Get leagues associated with a season.
`get_leagues_by_season`	`get_league_names()` for every season at once.
`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_max_season`	Simple return of max season year.
`get_max_week`	Answers "how many weeks are in a season?".
`get_max_weeks`	`get_max_week()` for every season at once.
`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_user_ids_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_sorted_seasons`	Numerically sorted season years, ascending.
`get_stats_counts`	Contestant counts for every combination of values, over several groupings in one query.
`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_user_ids_in_league`	Gets the users in a league.
`get_user_rows`	Every user's light row, read once per request: every user lookup below derives from it.
`get_users_with_any_email_pref`	The verified addresses in the league, as scoped, with at least one preference on.
`get_users_with_email_pref`	Who a weekly broadcast goes to: the league, then those of it who want this email.
`get_week_id`	Returns the id for a week, given its number and the season year.
`get_week_totals`	Each user's weeks, totalled, as the database keeps them.
`has_contest_history`	Whether a user has picked, or joined a league.
`is_after_last_week`	If the season's last week has ended.
`is_league_in_season`	Checks if a league is valid for a season.
`is_paid_user`	Checks if a given user has paid for a given season.
`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.
`mask_email`	An address as another member may see it: the ends of its local part, and its domain.
`now`	The request's one "now": captured once per request, so every time-dependent answer within it -- season, week, phase, pick window -- agrees.

supercontest.dbsession.queries.Scope

Whose picks make someone part of the league: any season’s, or this season’s.

alias of Literal[‘any’, ‘season’]

async supercontest.dbsession.queries.are_games_in_db(session: AsyncSession, 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:

session – The session to read through.
season – Season year.
week – Week number.

Returns:

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

async supercontest.dbsession.queries.are_picks_open(session: AsyncSession) → 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.

Parameters:: session – The session to read through.
Returns:: Whether or not picks are open for the current week.

async supercontest.dbsession.queries.count_games_in_stats_scope(session: AsyncSession, season: int | None) → int

How many games the statistics pages cover.

Parameters:

session – The session to read through.
season – Season year, or None for all seasons.

Returns:

The number of finished games.

async supercontest.dbsession.queries.get_admin_emails(session: AsyncSession) → list[str]

Gives all the email addresses of admins (is_superuser).

Parameters:: session – The session to read through.
Returns:: Admin email addresses.

async supercontest.dbsession.queries.get_all_team_names(session: AsyncSession) → list[str]

Provides a simple list of all team names.

Parameters:: session – The session to read through.
Returns:: Team names (first letter capitalized).

async supercontest.dbsession.queries.get_all_users(session: AsyncSession) → list[User]

Every user row.

Parameters:: session – The session to read through.
Returns:: All users.

async supercontest.dbsession.queries.get_contestant_from_team(session: AsyncSession, season: int, week: int, team: str) → Contestant | None

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

Parameters:

session – The session to read through.
season – Season year.
week – Week number.
team – Team name.

Returns:

The Contestant row corresponding to the inputs, or None if that team has no game that week.

async supercontest.dbsession.queries.get_contestants_by_team(session: AsyncSession, season: int, week: int) → dict[str, Contestant]

A week’s contestants, keyed by lowercased team name, in one read.

For a caller resolving several teams at once: one query for the week rather than one per team.

Parameters:

session – The session to read through.
season – Season year.
week – Week number.

Returns:

Lowercased team name to its contestant; the first, if a team plays twice.

async supercontest.dbsession.queries.get_current_season(session: AsyncSession) → int | None

Simple return of current season year.

Parameters:: session – The session to read through.
Returns:: Current season year or None if not in an active season.

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

Simple return of the current season year and week number.

Parameters:: session – The session to read through.
Returns:: Current season year or None if not in an active season.
Returns:: Current week number or None if not in an active week.

async supercontest.dbsession.queries.get_current_week(session: AsyncSession) → int | None

Simple return of current week number.

Parameters:: session – The session to read through.
Returns:: Current week number or None if not in an active week.

async supercontest.dbsession.queries.get_current_week_start(session: AsyncSession) → datetime | None

Simple return of the start date of the current week.

Parameters:: session – The session to read through.
Returns:: The start date of the current week or None if not in an active week.

async supercontest.dbsession.queries.get_finished_picks(session: AsyncSession) → list[Pick]

Returns all picks that have finished.

Parameters:: session – The session to read through.
Returns:: All picks that have finished.

async supercontest.dbsession.queries.get_game(session: AsyncSession, game_id: int) → Game | None

One game by id.

Parameters:

session – The session to read through.
game_id – The game’s id.

Returns:

The game, or None if there is none.

async supercontest.dbsession.queries.get_games(session: AsyncSession) → list[Game]

Returns all games in the database.

Parameters:: session – The session to read through.
Returns:: All games in the database.

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

Returns all games in a given week.

Parameters:

session – The session to read through.
season – Season year.
week – Week number.

Returns:

All games in that week.

async supercontest.dbsession.queries.get_id_email_map(session: AsyncSession) → dict[int, str]

Grabs the email for every row in the user table.

Parameters:: session – The session to read through.
Returns:: Keys are user IDs and values are emails.

async supercontest.dbsession.queries.get_id_name_map(session: AsyncSession) → dict[int, str]

Grabs the display name for every row in the user table: the first and last name if both are set, else the masked address (mask_email()).

These names go to every member – the leaderboards, all-picks and the emailed image – so the full address never does.

Parameters:: session – The session to read through.
Returns:: Keys are user IDs and values are names (or masked emails if no name).

async supercontest.dbsession.queries.get_league_ids_for_user(session: AsyncSession, user_id: int) → set[int]

Every league a user belongs to, in every season.

Parameters:

session – The session to read through.
user_id – User ID.

Returns:

League ids.

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

Get leagues associated with a season.

Parameters:

session – The session to read through.
season – Season year.

Returns:

Pairs of league ID and league name.

async supercontest.dbsession.queries.get_leagues_by_season(session: AsyncSession) → dict[int, list[tuple[int, str]]]

get_league_names() for every season at once.

Parameters:: session – The session to read through.
Returns:: Season year to pairs of league ID and name, in id order.

async supercontest.dbsession.queries.get_location_id(session: AsyncSession, 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:

session – The session to read through.
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.

async supercontest.dbsession.queries.get_map_espn_abbv_status_id(session: AsyncSession) → dict[str, int]

Provides a lookup for common scorefetch/dbstatus refs.

Parameters:: session – The session to read through.
Returns:: ESPN games statuses -> SBSC status IDs.

async supercontest.dbsession.queries.get_max_season(session: AsyncSession) → int

Simple return of max season year.

Parameters:: session – The session to read through.
Returns:: The most recent season in the database.

async supercontest.dbsession.queries.get_max_week(session: AsyncSession, season: int) → int

Answers “how many weeks are in a season?”.

Basically “17 if season <= 2020 else 18” but hits the database to be sure.

Parameters:

session – The session to read through.
season – Season year.

Returns:

The number of weeks in that season.

async supercontest.dbsession.queries.get_max_weeks(session: AsyncSession) → dict[int, int]

get_max_week() for every season at once.

Parameters:: session – The session to read through.
Returns:: Season year to its number of weeks.

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

Returns all games in a given week.

Sort is by datetime chronologically ascending, then favored_team alphabetically.

Parameters:

session – The session to read through.
season – Season year.
week – Week number.

Returns:

All games in that week, ordered by datetime.

async supercontest.dbsession.queries.get_paid_league_for_season(session: AsyncSession, season: int) → 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:

session – The session to read through.
season – Season year.

Returns:

The paid league object.

async supercontest.dbsession.queries.get_paid_league_id_for_season(session: AsyncSession, season: int) → 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:

session – The session to read through.
season – Season year.

Returns:

The paid league ID.

async supercontest.dbsession.queries.get_paid_user_ids_for_season(session: AsyncSession, season: int) → list[int]

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:

session – The session to read through.
season – Season year.

Returns:

User IDs.

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

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

Parameters:

session – The session to read through.
season – Season year.
league_id – League ID.

Returns:

All picks for that league in that season.

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

Returns all picks in a given week.

Parameters:

session – The session to read through.
season – Season year.
week – Week number.

Returns:

All picks in that week.

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

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

Parameters:

session – The session to read through.
season – Season year.
week – Week number.
league_id – League ID.

Returns:

All picks for that league in that week.

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

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

Parameters:

session – The session to read through.
season – Season year.
week – Week number.
user_id – User ID.

Returns:

All picks for that user in that week.

async supercontest.dbsession.queries.get_prediction_id(session: AsyncSession, 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:

session – The session to read through.
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).

async supercontest.dbsession.queries.get_sorted_seasons(session: AsyncSession) → list[int]

Numerically sorted season years, ascending.

Parameters:: session – The session to read through.
Returns:: All season years, from oldest to newest.

async supercontest.dbsession.queries.get_stats_counts(session: AsyncSession, season: int | None, groupings: list[tuple[str, ...]]) → dict[tuple[str, ...], list[tuple[tuple[Any, ...], int]]]

Contestant counts for every combination of values, over several groupings in one query.

Parameters:

session – The session to read through.
season – Season year, or None for all seasons.
groupings – Each a tuple of column names from the statistics fact table: coverage, prediction, location, team, line, kickoff, game, margin, picks.

Returns:

For each grouping, (values, count) per distinct combination, the values in that grouping’s column order.

async supercontest.dbsession.queries.get_status_id_from_name(session: AsyncSession, name: str) → int

For a given name, get the SBSC status ID.

Parameters:

session – The session to read through.
name – The name of the status.

Returns:

The ID of that status row.

async supercontest.dbsession.queries.get_status_names(session: AsyncSession) → list[str]

Provides a simple list of all status names.

Parameters:: session – The session to read through.
Returns:: Status names (first letter capitalized).

async supercontest.dbsession.queries.get_team_id(session: AsyncSession, 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:

session – The session to read through.
name – The name of the team.

Returns:

Team ID.

async supercontest.dbsession.queries.get_user_from_email(session: AsyncSession, 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:

session – The session to read through.
email – Email.

Returns:

The user object.

async supercontest.dbsession.queries.get_user_ids_in_league(session: AsyncSession, league_id: int) → list[int]

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

Parameters:

session – The session to read through.
league_id – League ID.

Returns:

User IDs.

async supercontest.dbsession.queries.get_user_rows(session: AsyncSession) → list[UserRow]

Every user’s light row, read once per request: every user lookup below derives from it.

Parameters:: session – The session to read through.
Returns:: The rows, in id order.

async supercontest.dbsession.queries.get_users_with_any_email_pref(session: AsyncSession, prefs: tuple[Literal['email_when_picks_open', 'email_when_picks_closing', 'email_all_picks'], ...], scope: Literal['any', 'season'], season: int, include_new: bool) → list[str]

The verified addresses in the league, as scoped, with at least one preference on.

An account that never confirmed its address gets nothing: it is an abandoned sign-up, or someone else’s address.

Parameters:

session – The session to read through.
prefs – The preference names.
scope – Players of any season, or of season.
season – The current season.
include_new – Also new members who have not picked yet.

Returns:

The addresses, each once.

async supercontest.dbsession.queries.get_users_with_email_pref(session: AsyncSession, pref: Literal['email_when_picks_open', 'email_when_picks_closing', 'email_all_picks'], season: int, week: int) → list[str]

Who a weekly broadcast goes to: the league, then those of it who want this email.

The league is everyone who has made a pick this season, and in week 1, before anyone has, everyone who has made a pick in any season. New members (selects.get_new_user_ids()) are in it either way, so they hear from it until their first pick.

Parameters:

session – The session to read through.
pref – The preference, eg email_when_picks_open.
season – The current season.
week – The current week.

Returns:

The addresses.

async supercontest.dbsession.queries.get_week_id(session: AsyncSession, season: int, week: int) → int

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

Parameters:

session – The session to read through.
season – Season year.
week – Week number.

Returns:

Week ID.

async supercontest.dbsession.queries.get_week_totals(session: AsyncSession, season: int | None, league_id: int, finished_only: bool = False) → list[tuple[int, int, WeekTotal]]

Each user’s weeks, totalled, as the database keeps them.

Parameters:

session – The session to read through.
season – Season year; None for every season.
league_id – League ID; 0 for everyone.
finished_only – Only the weeks in which a user has a finished pick.

Returns:

(season, week, row), by season, week and user.

async supercontest.dbsession.queries.has_contest_history(session: AsyncSession, user_id: int) → bool

Whether a user has picked, or joined a league. Unmemoized: it guards a delete.

Parameters:

session – The session to read through.
user_id – User ID.

Returns:

If anything in the contest references them.

async supercontest.dbsession.queries.is_after_last_week(session: AsyncSession, season: int) → bool

If the season’s last week has ended.

The postseason stated directly, rather than as the complement of the other phases – which would also count a gap between two weeks as the postseason.

Parameters:

session – The session to read through.
season – Season year.

Returns:

True from the last week’s week_end on. Weeks are half-open (see supercontest.dbsession.calendar), so that instant is already past it.

async supercontest.dbsession.queries.is_league_in_season(session: AsyncSession, 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:

session – The session to read through.
league_id – League ID.
season – Season year.

Returns:

If the league is valid for that season.

async supercontest.dbsession.queries.is_paid_user(session: AsyncSession, 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:

session – The session to read through.
season – Season year.
user_id – User ID.

Returns:

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

async supercontest.dbsession.queries.is_preseason(session: AsyncSession) → bool

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

Parameters:: session – The session to read through.
Returns:: False if regseason, offseason, or postseason. True if preseason.

async supercontest.dbsession.queries.is_regseason(session: AsyncSession) → bool

If you’re in an active regular season week.

Parameters:: session – The session to read through.
Returns:: False if offseason, preseason, or postseason. True if regseason.

async supercontest.dbsession.queries.is_user_in_league(session: AsyncSession, 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:

session – The session to read through.
user_id – User ID.
league_id – League ID.

Returns:

If the user is in the league.

supercontest.dbsession.queries.mask_email(email: str) → str

An address as another member may see it: the ends of its local part, and its domain.

One asterisk for each character hidden, as a card number is masked:

six or more characters keep the first two and last two: brianm@gmail.com is br**nm@gmail.com;
three to five keep the first and last: brian@gmail.com is b***n@gmail.com;
two keep the first (b*), and one keeps nothing (*).

Enough for league mates to tell who it is, not enough to mail them.

Parameters:: email – The address.
Returns:: The masked address.

supercontest.dbsession.queries.now() → datetime

The request’s one “now”: captured once per request, so every time-dependent answer within it – season, week, phase, pick window – agrees.

Returns:: The instant, aware in UTC.