supercontest.dbsession.selects

Actual reads to the cache/db.

Every function takes the caller’s AsyncSession first and awaits one statement on it. Example for raw SQL: await session.scalar(text("SELECT last_value FROM seasons_id_seq"))

WHAT A QUERY LOADS, IT SAYS. Every relationship in the models is lazy="raise": reading one that the query did not load is an error, never a silent query per row. So each select below that returns rows a caller walks states the whole graph it loads, with the loader options defined next – one per shape a caller reads – and a new caller that reads further extends the query, not the model.

Module Attributes

GAME_IN_FULL	A game as the API and the scoring jobs read it: its status, and both contestants in detail.
EmailPref	The notification preferences, each a boolean column on users.

Functions

are_games_in_week	Whether a week has any game, without loading one.
count_games_in_stats_scope	Counts the games the statistics pages cover.
get_all_users	Simply gives the full list of user objects.
get_contestants_in_week	Gets a list of all contestant objects in a given week.
get_finished_picks	Gets every pick on a finished game, with everything the results engine reads loaded.
get_game	One game by id, with nothing beyond its own columns loaded.
get_games	Gets a list of all game objects available to the app, in full (GAME_IN_FULL).
get_games_in_week	Gets a list of all game objects in a given week, in full (GAME_IN_FULL).
get_league_ids_for_user	Every league a user is a member of, across all seasons.
get_leagues	Get leagues associated with a season.
get_leagues_by_season	Every season's leagues, in one round trip, each season's in id order.
get_locations	Gets a list of all location objects available to the app.
get_new_user_ids	Verified users who joined since the previous season ended and have no earlier pick.
get_paid_league_for_season	Returns the official paid league for a specific season.
get_picks_in_season_for_league	A league's picks in a season, in full (_pick_in_full()).
get_picks_in_week	Gets a list of all pick objects in a given week, in full (_pick_in_full()).
get_picks_in_week_for_league	A league's picks in a week, in full (_pick_in_full()).
get_picks_in_week_for_user	One user's picks in a week, in full (_pick_in_full()).
get_player_ids	Everyone who has made a pick: in one season, or (None) in any.
get_predictions	Gets a list of all prediction objects available to the app.
get_stats_counts	Counts contestants for every combination of values, over several groupings at once.
get_statuses	Gets a list of all game status objects available to the app.
get_team	Returns the team object, given its name.
get_teams	Gets a list of all team objects available to the app.
get_user_from_email	Takes an email, returns a user object.
get_user_ids_in_league	The ids of a league's members; league 0, the free league, is everyone.
get_user_rows	Every user, as the columns every user lookup is answered from, in id order.
get_week_totals	Each user's weeks, totalled: the week_totals rows the database keeps.
has_contest_history	Whether any pick or league membership references a user.
is_user_in_league	Whether a membership row exists.

Classes

UserRow

What the app reads about a user, short of signing them in.

supercontest.dbsession.selects.EmailPref

The notification preferences, each a boolean column on users.

alias of Literal[‘email_when_picks_open’, ‘email_when_picks_closing’, ‘email_all_picks’]

supercontest.dbsession.selects.GAME_IN_FULL = (<sqlalchemy.orm.strategy_options.Load object>, <sqlalchemy.orm.strategy_options.Load object>)

A game as the API and the scoring jobs read it: its status, and both contestants in detail. The contestants are a second statement (selectinload) rather than a join, which would repeat every game’s columns once per contestant.

class supercontest.dbsession.selects.UserRow(id: int, first_name: str | None, last_name: str | None, email: str, is_superuser: bool, email_when_picks_open: bool, email_when_picks_closing: bool, email_all_picks: bool, is_verified: bool = True)

Bases: NamedTuple

What the app reads about a user, short of signing them in.

email: str

Alias for field number 3

email_all_picks: bool

Alias for field number 7

email_when_picks_closing: bool

Alias for field number 6

email_when_picks_open: bool

Alias for field number 5

first_name: str | None

Alias for field number 1

id: int

Alias for field number 0

is_superuser: bool

Alias for field number 4

is_verified: bool

Confirmed their address. Nothing is mailed to an account that has not.

last_name: str | None

Alias for field number 2

async supercontest.dbsession.selects.are_games_in_week(session: AsyncSession, season: int, week: int) → bool

Whether a week has any game, without loading one.

Parameters:

• session – The session to read through.

• season – Season year.

• week – Week number.

Returns:

If it does.

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

Counts the games the statistics pages cover.

Parameters:

• session – The session to read through.

• season – Season year, or None for all seasons.

Returns:

The number of games in scope.

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

Simply gives the full list of user objects.

Parameters:

session – The session to read through.

Returns:

All user objects in the database.

async supercontest.dbsession.selects.get_contestants_in_week(session: AsyncSession, season: int, week: int) → list[Contestant]

Gets a list of all contestant objects in a given week.

Each with its team, and its game in full (GAME_IN_FULL): the pick rules read the game’s status and find the opponent through the game’s contestants.

Parameters:

• session – The session to read through.

• season – Season year.

• week – Week number.

Returns:

All objects in the contestants table.

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

Gets every pick on a finished game, with everything the results engine reads loaded.

One round trip. The join that filters on the game’s status is reused to load the contestant, game and status (contains_eager); coverage, week and season come in on the same statement (joinedload). Every one of them is otherwise a lazy load per distinct row, which is what the all-time leaderboard used to cost.

Parameters:

session – The session to read through.

Returns:

The picks whose game has finished.

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

One game by id, with nothing beyond its own columns loaded.

Parameters:

• session – The session to read through.

• game_id – The game’s id.

Returns:

The game, or None.

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

Gets a list of all game objects available to the app, in full (GAME_IN_FULL).

Parameters:

session – The session to read through.

Returns:

All objects in the games table.

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

Gets a list of all game objects in a given week, in full (GAME_IN_FULL).

Parameters:

• session – The session to read through.

• season – Season year.

• week – Week number.

Returns:

All objects in the games table.

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

Every league a user is a member of, across all seasons.

Parameters:

• session – The session to read through.

• user_id – User ID.

Returns:

The league ids; the free league (0) has no rows and is never among them.

async supercontest.dbsession.selects.get_leagues(session: AsyncSession, season: int | None) → list[League]

Get leagues associated with a season.

Parameters:

• session – The session to read through.

• season – Season year.

Returns:

League objects.

async supercontest.dbsession.selects.get_leagues_by_season(session: AsyncSession) → dict[int, list[League]]

Every season’s leagues, in one round trip, each season’s in id order.

Parameters:

session – The session to read through.

Returns:

Season year to its leagues; a season with none is absent.

async supercontest.dbsession.selects.get_locations(session: AsyncSession) → list[Location]

Gets a list of all location objects available to the app.

Parameters:

session – The session to read through.

Returns:

All objects from the locations table.

async supercontest.dbsession.selects.get_new_user_ids(session: AsyncSession, season: int) → set[int]

Verified users who joined since the previous season ended and have no earlier pick.

Joined is users.created_at, set at sign-up from 2026-09-29. An older account has none, and counts as joined since then if its id – ids are handed out in sign-up order – is above that of the last user to pick in the previous season. With no previous season, any verified user with no earlier pick is new.

Parameters:

• session – The session to read through.

• season – The current season.

Returns:

User ids.

async supercontest.dbsession.selects.get_paid_league_for_season(session: AsyncSession, season: int) → League

Returns 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.

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

A league’s picks in a season, in full (_pick_in_full()).

Parameters:

• session – The session to read through.

• season – Season year.

• league_id – League ID; 0 for everyone.

Returns:

The picks.

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

Gets a list of all pick objects in a given week, in full (_pick_in_full()).

Parameters:

• session – The session to read through.

• season – Season year.

• week – Week number.

Returns:

All objects in the picks table.

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

A league’s picks in a week, in full (_pick_in_full()).

Parameters:

• session – The session to read through.

• season – Season year.

• week – Week number.

• league_id – League ID; 0 for everyone.

Returns:

The picks.

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

One user’s picks in a week, in full (_pick_in_full()).

Parameters:

• session – The session to read through.

• season – Season year.

• week – Week number.

• user_id – User ID.

Returns:

The picks.

async supercontest.dbsession.selects.get_player_ids(session: AsyncSession, season: int | None) → set[int]

Everyone who has made a pick: in one season, or (None) in any.

Parameters:

• session – The session to read through.

• season – The season, or None for any.

Returns:

User ids.

async supercontest.dbsession.selects.get_predictions(session: AsyncSession) → list[Prediction]

Gets a list of all prediction objects available to the app.

Parameters:

session – The session to read through.

Returns:

All objects from the predictions table.

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

Counts contestants for every combination of values, over several groupings at once.

One statement, however many groupings: GROUP BY GROUPING SETS, with GROUPING() telling each result row’s set apart. A count depends only on WHICH columns are grouped, not their order, so groupings over the same columns share one grouping set.

Parameters:

• session – The session to read through.

• season – Season year, or None for all seasons.

• groupings – Each a tuple of column names from _stats_facts().

Returns:

For each grouping, one entry per distinct combination: the values in that grouping’s column order, and the count. Ordered by value, so the result is deterministic.

async supercontest.dbsession.selects.get_statuses(session: AsyncSession) → list[Status]

Gets a list of all game status objects available to the app.

Parameters:

session – The session to read through.

Returns:

All objects in the statuses table.

async supercontest.dbsession.selects.get_team(session: AsyncSession, name: str) → Team

Returns the team object, 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 object.

async supercontest.dbsession.selects.get_teams(session: AsyncSession) → list[Team]

Gets a list of all team objects available to the app.

Parameters:

session – The session to read through.

Returns:

All objects from the teams table.

async supercontest.dbsession.selects.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.

Compared with lower(), not ilike: ilike reads _ and % as wildcards, and _ is common in addresses, so a_b@x.com would also have matched aXb@x.com – and this lookup decides who is added to the paid league.

Parameters:

• session – The session to read through.

• email – Email.

Returns:

The user object.

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

The ids of a league’s members; league 0, the free league, is everyone.

Parameters:

• session – The session to read through.

• league_id – The league ID.

Returns:

The user ids.

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

Every user, as the columns every user lookup is answered from, in id order.

One light read, rather than one per question (names, addresses, admins, preferences): supercontest.dbsession.queries keeps it for the request.

Parameters:

session – The session to read through.

Returns:

One row per user.

async supercontest.dbsession.selects.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: the week_totals rows the database keeps.

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.selects.has_contest_history(session: AsyncSession, user_id: int) → bool

Whether any pick or league membership references a user.

Parameters:

• session – The session to read through.

• user_id – User ID.

Returns:

If the user has either.

async supercontest.dbsession.selects.is_user_in_league(session: AsyncSession, league_id: int, user_id: int) → bool

Whether a membership row exists. Unmemoized: the paid-league write decides on it.

Parameters:

• session – The session to read through.

• league_id – League ID.

• user_id – User ID.

Returns:

If the user is in the league.