Package nfldb

Module nfldb provides command line tools and a library for maintaining and querying a relational database of play-by-play NFL data. The data is imported from nflgame, which in turn gets its data from a JSON feed on NFL.com's live GameCenter pages. This data includes, but is not limited to, game schedules, scores, rosters and play-by-play data for every preseason, regular season and postseason game dating back to 2009.

Here is a small teaser that shows how to use nfldb to find the top five passers in the 2012 regular season:

#!python
import nfldb

db = nfldb.connect()
q = nfldb.Query(db)

q.game(season_year=2012, season_type='Regular')
for pp in q.sort('passing_yds').limit(5).as_aggregate():
    print ( pp.player, pp.passing_yds)

And the output is:

[andrew@Liger ~] python2 top-five.py
Drew Brees (NO, QB) 5177
Matthew Stafford (DET, QB) 4965
Tony Romo (DAL, QB) 4903
Tom Brady (NE, QB) 4799
Matt Ryan (ATL, QB) 4719

In theory, both nfldb and nflgame provide access to the same data. The difference is in the execution. In order to search data in nflgame, a large JSON file needs to be read from disk and loaded into Python data structures for each game. Conversely, nfldb's data is stored in a relational database, which can be searched and retrieved faster than nflgame by a few orders of magnitude. Moreover, the relational organization of data in nfldb allows for a convenient query interface to search NFL play data.

The database can be updated with real time data from active games by running the nfldb-update script included with this module as often as you're comfortable pinging NFL.com. (N.B. The JSON data itself only updates every 15 seconds, so running nfldb-update faster than that would be wasteful.) Roster updates are done automatically at a minimum interval of 12 hours.

nfldb has comprehensive API documentation and a wiki with examples.

nfldb can be used in conjunction with nflvid to search and watch NFL game footage.

Please join us in discord https://discord.gg/G7uay2

"""
Module nfldb provides command line tools and a library for maintaining
and querying a relational database of play-by-play NFL data. The data
is imported from [nflgame](https://github.com/derek-adair/nflgame),
which in turn gets its data from a JSON feed on NFL.com's live
GameCenter pages. This data includes, but is not limited to, game
schedules, scores, rosters and play-by-play data for every preseason,
regular season and postseason game dating back to 2009.

Here is a small teaser that shows how to use nfldb to find the top five
passers in the 2012 regular season:

    #!python
    import nfldb

    db = nfldb.connect()
    q = nfldb.Query(db)

    q.game(season_year=2012, season_type='Regular')
    for pp in q.sort('passing_yds').limit(5).as_aggregate():
        print ( pp.player, pp.passing_yds)

And the output is:

    [andrew@Liger ~] python2 top-five.py
    Drew Brees (NO, QB) 5177
    Matthew Stafford (DET, QB) 4965
    Tony Romo (DAL, QB) 4903
    Tom Brady (NE, QB) 4799
    Matt Ryan (ATL, QB) 4719

In theory, both `nfldb` and `nflgame` provide access to the same data.
The difference is in the execution. In order to search data in nflgame,
a large JSON file needs to be read from disk and loaded into Python
data structures for each game. Conversely, nfldb's data is stored in
a relational database, which can be searched and retrieved faster
than nflgame by a few orders of magnitude. Moreover, the relational
organization of data in nfldb allows for a convenient
[query interface](https://github.com/derek-adair/nfldb/wiki/An-introduction-to-the-query-interface) to search NFL play data.

The database can be updated with real time data from active games by
running the `nfldb-update` script included with this module as often
as you're comfortable pinging NFL.com. (N.B. The JSON data itself only
updates every 15 seconds, so running `nfldb-update` faster than that
would be wasteful.) Roster updates are done automatically at a minimum
interval of 12 hours.

nfldb has [comprehensive API documentation](http://nfldb.derekadair.com)
and a [wiki with examples](https://github.com/derek-adair/nfldb/wiki).

nfldb can be used in conjunction with
[nflvid](https://pypi.python.org/pypi/nflvid)
to
[search and watch NFL game footage](http://goo.gl/Mckaf0).

Please join us in discord https://discord.gg/G7uay2
"""


from nfldb.db import __pdoc__ as __db_pdoc__
from nfldb.db import api_version, connect, now, set_timezone, schema_version
from nfldb.db import Tx
from nfldb.query import __pdoc__ as __query_pdoc__
from nfldb.query import aggregate, current, guess_position, player_search
from nfldb.query import Query, QueryOR
from nfldb.team import standard_team
from nfldb.types import __pdoc__ as __types_pdoc__
from nfldb.types import stat_categories
from nfldb.types import Category, Clock, Enums, Drive, FieldPosition, Game
from nfldb.types import Play, Player, PlayPlayer, PossessionTime, Team
from nfldb.version import __pdoc__ as __version_pdoc__
from nfldb.version import __version__

__pdoc__ = __db_pdoc__
__pdoc__ = dict(__pdoc__, **__query_pdoc__)
__pdoc__ = dict(__pdoc__, **__types_pdoc__)
__pdoc__ = dict(__pdoc__, **__version_pdoc__)


# Export selected identifiers from sub-modules.
__all__ = [
    # nfldb.db
    'api_version', 'connect', 'now', 'set_timezone', 'schema_version',
    'Tx',

    # nfldb.query
    'aggregate', 'current', 'guess_position', 'player_search',
    'Query', 'QueryOR',

    # nfldb.team
    'standard_team',

    # nfldb.types
    'select_columns', 'stat_categories',
    'Category', 'Clock', 'Enums', 'Drive', 'FieldPosition', 'Game',
    'Play', 'Player', 'PlayPlayer', 'PossessionTime', 'Team',

    # nfldb.version
    '__version__',
]

Sub-modules

nfldb.category

nfldb.db

nfldb.query

nfldb.sql

nfldb.team

nfldb.types

nfldb.update

nfldb.version

Functions

def QueryOR(db)

Creates a disjunctive Query object, where every condition is combined disjunctively. Namely, it is an alias for nfldb.Query(db, orelse=True).

Expand source code

def QueryOR(db):
    """
    Creates a disjunctive `nfldb.Query` object, where every
    condition is combined disjunctively. Namely, it is an alias for
    `nfldb.Query(db, orelse=True)`.
    """
    return Query(db, orelse=True)

def aggregate(objs)

Given any collection of Python objects that provide a play_players attribute, aggregate() will return a list of PlayPlayer objects with statistics aggregated (summed) over each player. (As a special case, if an element in objs is itself a PlayPlayer object, then it is used and a play_players attribute is not rquired.)

For example, objs could be a mixed list of Game and Play objects.

The order of the list returned is stable with respect to the order of players obtained from each element in objs.

It is recommended to use Query.aggregate() and Query.as_aggregate() instead of this function since summing statistics in the database is much faster. However, this function is provided for aggregation that cannot be expressed by the query interface.

Expand source code

def aggregate(objs):
    """
    Given any collection of Python objects that provide a
    `play_players` attribute, `aggregate` will return a list of
    `PlayPlayer` objects with statistics aggregated (summed) over each
    player. (As a special case, if an element in `objs` is itself a
    `nfldb.PlayPlayer` object, then it is used and a `play_players`
    attribute is not rquired.)

    For example, `objs` could be a mixed list of `nfldb.Game` and
    `nfldb.Play` objects.

    The order of the list returned is stable with respect to the
    order of players obtained from each element in `objs`.

    It is recommended to use `nfldb.Query.aggregate` and
    `nfldb.Query.as_aggregate` instead of this function since summing
    statistics in the database is much faster. However, this function
    is provided for aggregation that cannot be expressed by the query
    interface.
    """
    summed = OrderedDict()
    for obj in objs:
        pps = [obj] if isinstance(obj, types.PlayPlayer) else obj.play_players
        for pp in pps:
            if pp.player_id not in summed:
                summed[pp.player_id] = pp._copy()
            else:
                summed[pp.player_id]._add(pp)
    return list(summed.values())

def connect(database=None, user=None, password=None, host=None, port=None, timezone=None, config_path='')

Returns a psycopg2._psycopg.connection object from the psycopg2.connect function. If database is None, then connect() will look for a environment variables and then a configuration file using nfldb.config with config_path. Otherwise, the connection will use the parameters given.

If database is None and no config file can be found, then an IOError exception is raised.

This function will also compare the current schema version of the database against the API version nfldb.api_version and assert that they are equivalent. If the schema library version is less than the the API version, then the schema will be automatically upgraded. If the schema version is newer than the library version, then this function will raise an assertion error. An assertion error will also be raised if the schema version is 0 and the database is not empty.

N.B. The timezone parameter should be set to a value that PostgreSQL will accept. Select from the pg_timezone_names view to get a list of valid time zones.

Expand source code

def connect(database=None, user=None, password=None, host=None, port=None,
            timezone=None, config_path=''):
    """
    Returns a `psycopg2._psycopg.connection` object from the
    `psycopg2.connect` function. If database is `None`, then `connect`
    will look for a environment variables and then a configuration file 
    using `nfldb.config` with `config_path`. Otherwise, the connection will use the parameters
    given.

    If `database` is `None` and no config file can be found, then an
    `IOError` exception is raised.

    This function will also compare the current schema version of the
    database against the API version `nfldb.api_version` and assert
    that they are equivalent. If the schema library version is less
    than the the API version, then the schema will be automatically
    upgraded. If the schema version is newer than the library version,
    then this function will raise an assertion error. An assertion
    error will also be raised if the schema version is 0 and the
    database is not empty.

    N.B. The `timezone` parameter should be set to a value that
    PostgreSQL will accept. Select from the `pg_timezone_names` view
    to get a list of valid time zones.
    """
    if database is None:
        conf, tried = config(config_path=config_path)
        if conf is None:
            raise IOError("Could not find valid environment variables nor configuration file. "
                          "Tried the following paths: %s" % tried)

        timezone, database = conf['timezone'], conf['database']
        user, password = conf['user'], conf['password']
        host, port = conf['host'], conf['port']

    conn = psycopg2.connect(database=database, user=user, password=password,
                            host=host, port=port)

    # Start the migration. Make sure if this is the initial setup that
    # the DB is empty.
    sversion = schema_version(conn)
    assert sversion <= api_version, \
        'Library with version %d is older than the schema with version %d' \
        % (api_version, sversion)
    assert sversion > 0 or (sversion == 0 and _is_empty(conn)), \
        'Schema has version 0 but is not empty.'
    set_timezone(conn, 'UTC')
    _migrate(conn, api_version)

    if timezone is not None:
        set_timezone(conn, timezone)

    # Bind SQL -> Python casting functions.
    from nfldb.types import Clock, _Enum, Enums, FieldPosition, PossessionTime
    _bind_type(conn, 'game_phase', _Enum._pg_cast(Enums.game_phase))
    _bind_type(conn, 'season_phase', _Enum._pg_cast(Enums.season_phase))
    _bind_type(conn, 'game_day', _Enum._pg_cast(Enums.game_day))
    _bind_type(conn, 'player_pos', _Enum._pg_cast(Enums.player_pos))
    _bind_type(conn, 'player_status', _Enum._pg_cast(Enums.player_status))
    _bind_type(conn, 'game_time', Clock._pg_cast)
    _bind_type(conn, 'pos_period', PossessionTime._pg_cast)
    _bind_type(conn, 'field_pos', FieldPosition._pg_cast)

    return conn

def current(db)

Returns a triple of Enums.season_phase, season year and week corresponding to values that nfldb thinks are current.

Note that this only queries the database. Only the nfldb-update script fetches the current state from NFL.com.

The values retrieved may be None if the season is over or if they haven't been updated yet by the nfldb-update script.

Expand source code

def current(db):
    """
    Returns a triple of `nfldb.Enums.season_phase`, season year and week
    corresponding to values that `nfldb` thinks are current.

    Note that this only queries the database. Only the `nfldb-update`
    script fetches the current state from NFL.com.

    The values retrieved may be `None` if the season is over or if they
    haven't been updated yet by the `nfldb-update` script.
    """
    with Tx(db, factory=tuple_cursor) as cursor:
        cursor.execute('SELECT season_type, season_year, week FROM meta')
        return cursor.fetchone()
    return tuple([None] * 3)

def guess_position(pps)

Given a list of PlayPlayer objects for the same player, guess the position of the player based on the statistics recorded.

Note that this only distinguishes the offensive positions of QB, RB, WR, P and K. If defensive stats are detected, then the position returned defaults to LB.

The algorithm used is simple majority vote. Whichever position is the most common is returned (and this may be UNK).

Expand source code

def guess_position(pps):
    """
    Given a list of `nfldb.PlayPlayer` objects for the same player,
    guess the position of the player based on the statistics recorded.

    Note that this only distinguishes the offensive positions of QB,
    RB, WR, P and K. If defensive stats are detected, then the position
    returned defaults to LB.

    The algorithm used is simple majority vote. Whichever position is
    the most common is returned (and this may be `UNK`).
    """
    if len(pps) == 0:
        return types.Enums.player_pos.UNK

    counts = defaultdict(int)
    for pp in pps:
        counts[pp.guess_position] += 1
    return max(list(counts.items()), key=lambda __count: __count[1])[0]

def now()

Returns the current date/time in UTC as a datetime.datetime object. It can be used to compare against date/times in any of the nfldb objects without worrying about timezones.

Expand source code

def now():
    """
    Returns the current date/time in UTC as a `datetime.datetime`
    object. It can be used to compare against date/times in any of the
    `nfldb` objects without worrying about timezones.
    """
    return datetime.datetime.now(pytz.utc)

def player_search(db, full_name, team=None, position=None, limit=1, soundex=False)

Given a database handle and a player's full name, this function searches the database for players with full names similar to the one given. Similarity is measured by the Levenshtein distance, or by Soundex similarity.

Results are returned as tuples. The first element is the is a Player object and the second element is the Levenshtein (or Soundex) distance. When limit is 1 (the default), then the return value is a tuple. When limit is more than 1, then the return value is a list of tuples.

If no results are found, then (None, None) is returned when limit == 1 or the empty list is returned when limit > 1.

If nfldb.team is not None, then only players currently on the team provided will be returned. Any players with an unknown team are therefore omitted.

If position is not None, then only players currently at that position will be returned. Any players with an unknown position are therefore omitted.

In order to use this function, the PostgreSQL levenshtein function must be available. If running this functions gives you an error about "No function matches the given name and argument types", then you can install the levenshtein function into your database by running the SQL query CREATE EXTENSION fuzzystrmatch<code> as a superuser like </code>postgres. For example:

#!bash
psql -U postgres -c 'CREATE EXTENSION fuzzystrmatch;' nfldb

Note that enabled the fuzzystrmatch extension also provides functions for comparing using Soundex.

Expand source code

def player_search(db, full_name, team=None, position=None,
                  limit=1, soundex=False):
    """
    Given a database handle and a player's full name, this function
    searches the database for players with full names *similar* to the
    one given. Similarity is measured by the
    [Levenshtein distance](http://en.wikipedia.org/wiki/Levenshtein_distance),
    or by [Soundex similarity](http://en.wikipedia.org/wiki/Soundex).

    Results are returned as tuples. The first element is the is a
    `nfldb.Player` object and the second element is the Levenshtein
    (or Soundex) distance. When `limit` is `1` (the default), then the
    return value is a tuple.  When `limit` is more than `1`, then the
    return value is a list of tuples.

    If no results are found, then `(None, None)` is returned when
    `limit == 1` or the empty list is returned when `limit > 1`.

    If `team` is not `None`, then only players **currently** on the
    team provided will be returned. Any players with an unknown team
    are therefore omitted.

    If `position` is not `None`, then only players **currently**
    at that position will be returned. Any players with an unknown
    position are therefore omitted.

    In order to use this function, the PostgreSQL `levenshtein`
    function must be available. If running this functions gives
    you an error about "No function matches the given name and
    argument types", then you can install the `levenshtein` function
    into your database by running the SQL query `CREATE EXTENSION
    fuzzystrmatch` as a superuser like `postgres`. For example:

        #!bash
        psql -U postgres -c 'CREATE EXTENSION fuzzystrmatch;' nfldb

    Note that enabled the `fuzzystrmatch` extension also provides
    functions for comparing using Soundex.
    """
    assert isinstance(limit, int) and limit >= 1

    if soundex:
        # Careful, soundex distances are sorted in reverse of Levenshtein
        # distances.
        # Difference yields an integer in [0, 4].
        # A 4 is an exact match.
        fuzzy = 'difference(full_name, %s)'
        q = '''
            SELECT {columns}
            FROM player
            WHERE {where}
            ORDER BY distance DESC LIMIT {limit}
        '''
    else:
        fuzzy = 'levenshtein(full_name, %s)'
        q = '''
            SELECT {columns}
            FROM player
            WHERE {where}
            ORDER BY distance ASC LIMIT {limit}
        '''
    qteam, qposition = '', ''
    results = []
    with Tx(db) as cursor:
        if team is not None:
            qteam = cursor.mogrify('team = %s', (team,)).decode('utf-8')
        if position is not None:
            qposition = cursor.mogrify('position = %s', (position,)).decode('utf-8')

        fuzzy_filled = cursor.mogrify(fuzzy, (full_name,))
        columns = types.Player._sql_select_fields(types.Player.sql_fields())
        columns.append('%s AS distance' % fuzzy_filled)
        q = q.format(
            columns=', '.join(columns),
            where=sql.ands(fuzzy_filled + ' IS NOT NULL', qteam, qposition),
            limit=limit)
        cursor.execute(q, (full_name,))

        for row in cursor.fetchall():
            r = (types.Player.from_row_dict(db, row), row['distance'])
            results.append(r)
    if limit == 1:
        if len(results) == 0:
            return (None, None)
        return results[0]
    return results

def schema_version(conn)

Returns the schema version of the given database. If the version is not stored in the database, then 0 is returned.

Expand source code

def schema_version(conn):
    """
    Returns the schema version of the given database. If the version
    is not stored in the database, then `0` is returned.
    """
    with Tx(conn) as c:
        try:
            c.execute('SELECT version FROM meta LIMIT 1', ['version'])
        except psycopg2.ProgrammingError:
            return 0
        if c.rowcount == 0:
            return 0
        return c.fetchone()['version']

def set_timezone(conn, timezone)

Sets the timezone for which all datetimes will be displayed as. Valid values are exactly the same set of values accepted by PostgreSQL. (Select from the pg_timezone_names view to get a list of valid time zones.)

Note that all datetimes are stored in UTC. This setting only affects how datetimes are viewed from select queries.

Expand source code

def set_timezone(conn, timezone):
    """
    Sets the timezone for which all datetimes will be displayed
    as. Valid values are exactly the same set of values accepted
    by PostgreSQL. (Select from the `pg_timezone_names` view to
    get a list of valid time zones.)

    Note that all datetimes are stored in UTC. This setting only
    affects how datetimes are viewed from select queries.
    """
    with Tx(conn) as c:
        c.execute('SET timezone = %s', (timezone,))

def standard_team(team)

Returns a standard abbreviation when team corresponds to a team known by nfldb (case insensitive). If no team can be found, then "UNK" is returned.

Expand source code

def standard_team(team):
    """
    Returns a standard abbreviation when team corresponds to a team
    known by nfldb (case insensitive). If no team can be found, then
    `"UNK"` is returned.
    """
    if not team or team.lower() == 'new york':
        return 'UNK'

    team = team.lower()
    for teams in [teams2, teams1]:
        for variants in teams:
            for variant in variants:
                if team == variant.lower():
                    return variants[0]
    return 'UNK'

Classes

class Category (category_id, gsis_number, category_type, is_real, description)

Represents meta data about a statistical category. This includes the category's scope, GSIS identifier, name and short description.

Expand source code

class Category (object):
    """
    Represents meta data about a statistical category. This includes
    the category's scope, GSIS identifier, name and short description.
    """
    __slots__ = ['category_id', 'gsis_number', 'category_type',
                 'is_real', 'description']

    def __init__(self, category_id, gsis_number, category_type,
                 is_real, description):
        self.category_id = category_id
        """
        A unique name for this category.
        """
        self.gsis_number = gsis_number
        """
        A unique numeric identifier for this category.
        """
        self.category_type = category_type
        """
        The scope of this category represented with
        `nfldb.Enums.category_scope`.
        """
        self.is_real = is_real
        """
        Whether this statistic is a real number or not. Currently,
        only the `defense_sk` statistic has `Category.is_real` set to
        `True`.
        """
        self.description = description
        """
        A free-form text description of this category.
        """

    @property
    def _sql_field(self):
        """
        The SQL definition of this column. Statistics are always
        NOT NULL and have a default value of `0`.

        When `Category.is_real` is `True`, then the SQL type is `real`.
        Otherwise, it's `smallint`.
        """
        typ = 'real' if self.is_real else 'smallint'
        default = '0.0' if self.is_real else '0'
        return '%s %s NOT NULL DEFAULT %s' % (self.category_id, typ, default)

    def __str__(self):
        return self.category_id

    def __eq__(self, other):
        return self.category_id == other.category_id

Instance variables

var category_id

A unique name for this category.

var category_type

The scope of this category represented with Enums.category_scope.

var description

A free-form text description of this category.

var gsis_number

A unique numeric identifier for this category.

var is_real

Whether this statistic is a real number or not. Currently, only the defense_sk statistic has Category.is_real set to True.

class Clock (phase, elapsed)

Represents a single point in time during a game. This includes the quarter and the game clock time in addition to other phases of the game such as before the game starts, half time, overtime and when the game ends.

Note that the clock time does not uniquely identify a play, since not all plays consume time on the clock. (e.g., A two point conversion.)

This class defines a total ordering on clock times. Namely, c1 < c2 if and only if c2 is closer to the end of the game than c1.

Introduces a new Clock object. phase should be a value from the Enums.game_phase enumeration while elapsed should be the number of seconds elapsed in the phase. Note that elapsed is only applicable when phase is a quarter (including overtime). In all other cases, it will be set to 0.

elapsed should be in the range [0, 900] where 900 corresponds to the clock time 0:00 and 0 corresponds to the clock time 15:00.

Expand source code

class Clock (object):
    """
    Represents a single point in time during a game. This includes the
    quarter and the game clock time in addition to other phases of the
    game such as before the game starts, half time, overtime and when
    the game ends.

    Note that the clock time does not uniquely identify a play, since
    not all plays consume time on the clock. (e.g., A two point
    conversion.)

    This class defines a total ordering on clock times. Namely, c1 < c2
    if and only if c2 is closer to the end of the game than c1.
    """

    _nonqs = (Enums.game_phase.Pregame, Enums.game_phase.Half,
              Enums.game_phase.Final)
    """
    The phases of the game that do not have a time component.
    """

    _phase_max = 900
    """
    The maximum number of seconds in a game phase.
    """

    @staticmethod
    def from_str(phase, clock):
        """
        Introduces a new `nfldb.Clock` object given strings of the game
        phase and the clock. `phase` may be one of the values in the
        `nfldb.Enums.game_phase` enumeration. `clock` must be a clock
        string in the format `MM:SS`, e.g., `4:01` corresponds to a
        game phase with 4 minutes and 1 second remaining.
        """
        assert getattr(Enums.game_phase, phase, None) is not None, \
            '"%s" is not a valid game phase. choose one of %s' \
            % (phase, list(map(str, Enums.game_phase)))

        minutes, seconds = list(map(int, clock.split(':', 1)))
        elapsed = Clock._phase_max - ((minutes * 60) + seconds)
        return Clock(Enums.game_phase[phase], int(elapsed))

    @staticmethod
    def _pg_cast(sqlv, cursor):
        """
        Casts a SQL string of the form `(game_phase, elapsed)` to a
        `nfldb.Clock` object.
        """
        phase, elapsed = list(map(str.strip, sqlv[1:-1].split(',')))
        return Clock(Enums.game_phase[phase], int(elapsed))

    def __init__(self, phase, elapsed):
        """
        Introduces a new `nfldb.Clock` object. `phase` should
        be a value from the `nfldb.Enums.game_phase` enumeration
        while `elapsed` should be the number of seconds elapsed in
        the `phase`. Note that `elapsed` is only applicable when
        `phase` is a quarter (including overtime). In all other
        cases, it will be set to `0`.

        `elapsed` should be in the range `[0, 900]` where `900`
        corresponds to the clock time `0:00` and `0` corresponds
        to the clock time `15:00`.
        """
        assert isinstance(phase, Enums.game_phase)
        assert 0 <= elapsed <= Clock._phase_max

        if phase in Clock._nonqs:
            elapsed = 0

        self.phase = phase
        """
        The phase represented by this clock object. It is guaranteed
        to have type `nfldb.Enums.game_phase`.
        """
        self.elapsed = elapsed
        """
        The number of seconds remaining in this clock's phase of the
        game. It is always set to `0` whenever the phase is not a
        quarter in the game.
        """

    def add_seconds(self, seconds):
        """
        Adds the number of seconds given to the current clock time
        and returns a new clock time. `seconds` may be positive
        or negative. If a boundary is reached (e.g., `Pregame` or
        `Final`), then subtracting or adding more seconds has no
        effect.
        """
        elapsed = self.elapsed + seconds
        phase_jump = 0
        if elapsed < 0 or elapsed > Clock._phase_max:
            phase_jump = elapsed // Clock._phase_max

        # Always skip over halftime.
        phase_val = self.phase.value + phase_jump
        if self.phase.value <= Enums.game_phase.Half.value <= phase_val:
            phase_val += 1
        elif phase_val <= Enums.game_phase.Half.value <= self.phase.value:
            phase_val -= 1

        try:
            phase = Enums.game_phase(phase_val)
            return Clock(phase, elapsed % (1 + Clock._phase_max))
        except ValueError:
            if phase_val < 0:
                return Clock(Enums.game_phase.Pregame, 0)
            return Clock(Enums.game_phase.Final, 0)

    @property
    def minutes(self):
        """
        If the clock has a time component, then the number of whole
        minutes **left in this phase** is returned. Otherwise, `0` is
        returned.
        """
        if self.elapsed == 0:
            return 0
        return (Clock._phase_max - self.elapsed) // 60

    @property
    def seconds(self):
        """
        If the clock has a time component, then the number of seconds
        **left in this phase** is returned. Otherwise, `0` is returned.
        """
        if self.elapsed == 0:
            return 0
        return (Clock._phase_max - self.elapsed) % 60

    def __str__(self):
        phase = self.phase
        if phase in Clock._nonqs:
            return phase.name
        else:
            return '%s %02d:%02d' % (phase.name, self.minutes, self.seconds)

    def __lt__(self, o):
        if self.__class__ is not o.__class__:
            return NotImplemented
        return (self.phase, self.elapsed) < (o.phase, o.elapsed)

    def __eq__(self, o):
        if self.__class__ is not o.__class__:
            return NotImplemented
        return self.phase == o.phase and self.elapsed == o.elapsed

    def __conform__(self, proto):
        if proto is ISQLQuote:
            return AsIs("ROW('%s', %d)::game_time"
                        % (self.phase.name, self.elapsed))
        return None

Static methods

def from_str(phase, clock)

Introduces a new Clock object given strings of the game phase and the clock. phase may be one of the values in the Enums.game_phase enumeration. clock must be a clock string in the format MM:SS, e.g., 4:01 corresponds to a game phase with 4 minutes and 1 second remaining.

Expand source code

@staticmethod
def from_str(phase, clock):
    """
    Introduces a new `nfldb.Clock` object given strings of the game
    phase and the clock. `phase` may be one of the values in the
    `nfldb.Enums.game_phase` enumeration. `clock` must be a clock
    string in the format `MM:SS`, e.g., `4:01` corresponds to a
    game phase with 4 minutes and 1 second remaining.
    """
    assert getattr(Enums.game_phase, phase, None) is not None, \
        '"%s" is not a valid game phase. choose one of %s' \
        % (phase, list(map(str, Enums.game_phase)))

    minutes, seconds = list(map(int, clock.split(':', 1)))
    elapsed = Clock._phase_max - ((minutes * 60) + seconds)
    return Clock(Enums.game_phase[phase], int(elapsed))

Instance variables

var elapsed

The number of seconds remaining in this clock's phase of the game. It is always set to 0 whenever the phase is not a quarter in the game.

var minutes

If the clock has a time component, then the number of whole minutes left in this phase is returned. Otherwise, 0 is returned.

Expand source code

@property
def minutes(self):
    """
    If the clock has a time component, then the number of whole
    minutes **left in this phase** is returned. Otherwise, `0` is
    returned.
    """
    if self.elapsed == 0:
        return 0
    return (Clock._phase_max - self.elapsed) // 60

var phase

The phase represented by this clock object. It is guaranteed to have type Enums.game_phase.

var seconds

If the clock has a time component, then the number of seconds left in this phase is returned. Otherwise, 0 is returned.

Expand source code

@property
def seconds(self):
    """
    If the clock has a time component, then the number of seconds
    **left in this phase** is returned. Otherwise, `0` is returned.
    """
    if self.elapsed == 0:
        return 0
    return (Clock._phase_max - self.elapsed) % 60

Methods

def add_seconds(self, seconds)

Adds the number of seconds given to the current clock time and returns a new clock time. seconds may be positive or negative. If a boundary is reached (e.g., Pregame or Final), then subtracting or adding more seconds has no effect.

Expand source code

def add_seconds(self, seconds):
    """
    Adds the number of seconds given to the current clock time
    and returns a new clock time. `seconds` may be positive
    or negative. If a boundary is reached (e.g., `Pregame` or
    `Final`), then subtracting or adding more seconds has no
    effect.
    """
    elapsed = self.elapsed + seconds
    phase_jump = 0
    if elapsed < 0 or elapsed > Clock._phase_max:
        phase_jump = elapsed // Clock._phase_max

    # Always skip over halftime.
    phase_val = self.phase.value + phase_jump
    if self.phase.value <= Enums.game_phase.Half.value <= phase_val:
        phase_val += 1
    elif phase_val <= Enums.game_phase.Half.value <= self.phase.value:
        phase_val -= 1

    try:
        phase = Enums.game_phase(phase_val)
        return Clock(phase, elapsed % (1 + Clock._phase_max))
    except ValueError:
        if phase_val < 0:
            return Clock(Enums.game_phase.Pregame, 0)
        return Clock(Enums.game_phase.Final, 0)

class Drive (db)

Represents a single drive in an NFL game. Each drive has an assortment of meta data, possibly including the start and end times, the start and end field positions, the result of the drive, the number of penalties and first downs, and more.

Each drive corresponds to zero or more plays. A drive usually corresponds to at least one play, but if the game is active, there exist valid ephemeral states where a drive has no plays.

Creates a new and empty Drive object with the given database connection.

This constructor should not be used by clients. Instead, you should get Drive objects from Query or from one of the other constructors, like Drive.from_id() or Entity.from_row_dict(). (The latter is useful only if you're writing your own SQL queries.)

Expand source code

class Drive (SQLDrive):
    """
    Represents a single drive in an NFL game. Each drive has an
    assortment of meta data, possibly including the start and end
    times, the start and end field positions, the result of the drive,
    the number of penalties and first downs, and more.

    Each drive corresponds to **zero or more** plays. A drive usually
    corresponds to at least one play, but if the game is active, there
    exist valid ephemeral states where a drive has no plays.
    """
    __slots__ = SQLDrive.sql_fields() + ['_db', '_game', '_plays']

    @staticmethod
    def _from_nflgame(db, g, d):
        """
        Given `g` as a `nfldb.Game` object and `d` as a
        `nflgame.game.Drive` object, `_from_nflgame` converts `d` to a
        `nfldb.Drive` object.

        Generally, this function should not be used. It is called
        automatically by `nfldb.Game._from_nflgame`.
        """
        dbd = Drive(db)
        dbd.gsis_id = g.gsis_id
        dbd.drive_id = d.drive_num
        dbd.start_time = _nflgame_clock(d.time_start)
        dbd.start_field = FieldPosition(getattr(d.field_start, 'offset', None))
        dbd.end_field = FieldPosition(d.field_end.offset)
        dbd.end_time = _nflgame_clock(d.time_end)
        dbd.pos_team = nfldb.team.standard_team(d.team)
        dbd.pos_time = PossessionTime(d.pos_time.total_seconds())
        dbd.first_downs = d.first_downs
        dbd.result = d.result
        dbd.penalty_yards = d.penalty_yds
        dbd.yards_gained = d.total_yds
        dbd.play_count = d.play_cnt

        dbd._game = g
        candidates = []
        for play in d.plays:
            candidates.append(Play._from_nflgame(db, dbd, play))

        # At this point, some plays don't have valid game times. Fix it!
        # If we absolutely cannot fix it, drop the play. Maintain integrity!
        dbd._plays = []
        for play in candidates:
            if play.time is None:
                next = _next_play_with(candidates, play, lambda p: p.time)
                play.time = _play_time(dbd, play, next)
            if play.time is not None:
                dbd._plays.append(play)
        dbd._plays.sort(key=lambda p: p.play_id)
        return dbd

    @staticmethod
    def from_id(db, gsis_id, drive_id):
        """
        Given a GSIS identifier (e.g., `2012090500`) as a string
        and a integer drive id, this returns a `nfldb.Drive` object
        corresponding to the given identifiers.

        If no corresponding drive is found, then `None` is returned.
        """
        import nfldb.query
        q = nfldb.query.Query(db)
        q.drive(gsis_id=gsis_id, drive_id=drive_id).limit(1)
        drives = q.as_drives()
        if len(drives) == 0:
            return None
        return drives[0]

    @staticmethod
    def fill_games(db, drives):
        """
        Given a list of `drives`, fill all of their `game` attributes
        using as few queries as possible.
        """
        _fill(db, Game, drives, '_game')

    def __init__(self, db):
        """
        Creates a new and empty `nfldb.Drive` object with the given
        database connection.

        This constructor should not be used by clients. Instead, you
        should get `nfldb.Drive` objects from `nfldb.Query` or from one
        of the other constructors, like `nfldb.Drive.from_id` or
        `nfldb.Drive.from_row_dict`. (The latter is useful only if you're
        writing your own SQL queries.)
        """
        self._db = db
        self._game = None
        self._plays = None

        self.gsis_id = None
        """
        The GSIS identifier for the game that this drive belongs to.
        """
        self.drive_id = None
        """
        The numeric drive identifier for this drive. It may be
        interpreted as a sequence number.
        """
        self.start_field = None
        """
        The starting field position of this drive represented
        with `nfldb.FieldPosition`.
        """
        self.start_time = None
        """
        The starting clock time of this drive, represented with
        `nfldb.Clock`.
        """
        self.end_field = None
        """
        The ending field position of this drive represented with
        `nfldb.FieldPosition`.
        """
        self.end_time = None
        """
        The ending clock time of this drive, represented with
        `nfldb.Clock`.
        """
        self.pos_team = None
        """
        The team in possession during this drive, represented as
        a team abbreviation string. Use the `nfldb.Team` constructor
        to get more information on a team.
        """
        self.pos_time = None
        """
        The possession time of this drive, represented with
        `nfldb.PossessionTime`.
        """
        self.first_downs = None
        """
        The number of first downs that occurred in this drive.
        """
        self.result = None
        """
        A freeform text field straight from NFL's GameCenter data that
        sometimes contains the result of a drive (e.g., `Touchdown`).
        """
        self.penalty_yards = None
        """
        The number of yards lost or gained from penalties in this
        drive.
        """
        self.yards_gained = None
        """
        The total number of yards gained or lost in this drive.
        """
        self.play_count = None
        """
        The total number of plays executed by the offense in this
        drive.
        """
        self.time_inserted = None
        """The date and time that this drive was added."""
        self.time_updated = None
        """The date and time that this drive was last updated."""

    @property
    def game(self):
        """
        Returns the `nfldb.Game` object that contains this drive. The
        game is retrieved from the database if it hasn't been already.
        """
        if self._game is None:
            return Game.from_id(self._db, self.gsis_id)
        return self._game

    @property
    def plays(self):
        """
        A list of all `nfldb.Play`s in this drive. They are
        automatically retrieved from the database if they haven't been
        already.

        If there are no plays in the drive, then an empty list is
        returned.
        """
        if self._plays is None:
            import nfldb.query
            q = nfldb.query.Query(self._db)
            q.sort([('time', 'asc'), ('play_id', 'asc')])
            q.play(gsis_id=self.gsis_id, drive_id=self.drive_id)
            self._plays = q.as_plays()
            for p in self._plays:
                p._drive = self
        return self._plays

    def score(self, before=False):
        """
        Returns the score of the game immediately after this drive as a
        tuple of the form `(home_score, away_score)`.

        If `before` is `True`, then the score will *not* include this
        drive.
        """
        if before:
            return self.game.score_at_time(self.start_time)
        else:
            return self.game.score_at_time(self.end_time)

    @property
    def play_players(self):
        """
        A list of `nfldb.PlayPlayer` objects in this drive. Data is
        retrieved from the database if it hasn't been already.
        """
        pps = []
        for play in self.plays:
            for pp in play.play_players:
                pps.append(pp)
        return pps

    def _save(self, cursor):
        super(Drive, self)._save(cursor)
        if not self._plays:
            return

        # Remove any plays that are stale.
        cursor.execute('''
            DELETE FROM play
            WHERE gsis_id = %s AND drive_id = %s AND NOT (play_id = ANY (%s))
        ''', (self.gsis_id, self.drive_id, [p.play_id for p in self._plays]))
        for play in (self._plays or []):
            play._save(cursor)

    def __str__(self):
        s = '[%-12s] %-3s from %-6s to %-6s '
        s += '(lasted %s - %s to %s)'
        return s % (
            self.result, self.pos_team, self.start_field, self.end_field,
            self.pos_time, self.start_time, self.end_time,
        )

Ancestors

• SQLDrive
• Entity

Static methods

def fill_games(db, drives)

Given a list of drives, fill all of their game attributes using as few queries as possible.

Expand source code

@staticmethod
def fill_games(db, drives):
    """
    Given a list of `drives`, fill all of their `game` attributes
    using as few queries as possible.
    """
    _fill(db, Game, drives, '_game')

def from_id(db, gsis_id, drive_id)

Given a GSIS identifier (e.g., 2012090500) as a string and a integer drive id, this returns a Drive object corresponding to the given identifiers.

If no corresponding drive is found, then None is returned.

Expand source code

@staticmethod
def from_id(db, gsis_id, drive_id):
    """
    Given a GSIS identifier (e.g., `2012090500`) as a string
    and a integer drive id, this returns a `nfldb.Drive` object
    corresponding to the given identifiers.

    If no corresponding drive is found, then `None` is returned.
    """
    import nfldb.query
    q = nfldb.query.Query(db)
    q.drive(gsis_id=gsis_id, drive_id=drive_id).limit(1)
    drives = q.as_drives()
    if len(drives) == 0:
        return None
    return drives[0]

Instance variables

var drive_id

The numeric drive identifier for this drive. It may be interpreted as a sequence number.

var end_field

The ending field position of this drive represented with FieldPosition.

var end_time

The ending clock time of this drive, represented with Clock.

var first_downs

The number of first downs that occurred in this drive.

var game

Returns the Game object that contains this drive. The game is retrieved from the database if it hasn't been already.

Expand source code

@property
def game(self):
    """
    Returns the `nfldb.Game` object that contains this drive. The
    game is retrieved from the database if it hasn't been already.
    """
    if self._game is None:
        return Game.from_id(self._db, self.gsis_id)
    return self._game

var gsis_id

The GSIS identifier for the game that this drive belongs to.

var penalty_yards

The number of yards lost or gained from penalties in this drive.

var play_count

The total number of plays executed by the offense in this drive.

var play_players

A list of PlayPlayer objects in this drive. Data is retrieved from the database if it hasn't been already.

Expand source code

@property
def play_players(self):
    """
    A list of `nfldb.PlayPlayer` objects in this drive. Data is
    retrieved from the database if it hasn't been already.
    """
    pps = []
    for play in self.plays:
        for pp in play.play_players:
            pps.append(pp)
    return pps

var plays

A list of all Plays in this drive. They are automatically retrieved from the database if they haven't been already.

If there are no plays in the drive, then an empty list is returned.

Expand source code

@property
def plays(self):
    """
    A list of all `nfldb.Play`s in this drive. They are
    automatically retrieved from the database if they haven't been
    already.

    If there are no plays in the drive, then an empty list is
    returned.
    """
    if self._plays is None:
        import nfldb.query
        q = nfldb.query.Query(self._db)
        q.sort([('time', 'asc'), ('play_id', 'asc')])
        q.play(gsis_id=self.gsis_id, drive_id=self.drive_id)
        self._plays = q.as_plays()
        for p in self._plays:
            p._drive = self
    return self._plays

var pos_team

The team in possession during this drive, represented as a team abbreviation string. Use the Team constructor to get more information on a team.

var pos_time

The possession time of this drive, represented with PossessionTime.

var result

A freeform text field straight from NFL's GameCenter data that sometimes contains the result of a drive (e.g., Touchdown).

var start_field

The starting field position of this drive represented with FieldPosition.

var start_time

The starting clock time of this drive, represented with Clock.

var time_inserted

The date and time that this drive was added.

var time_updated

The date and time that this drive was last updated.

var yards_gained

The total number of yards gained or lost in this drive.

Methods

def score(self, before=False)

Returns the score of the game immediately after this drive as a tuple of the form (home_score, away_score).

If before is True, then the score will not include this drive.

Expand source code

def score(self, before=False):
    """
    Returns the score of the game immediately after this drive as a
    tuple of the form `(home_score, away_score)`.

    If `before` is `True`, then the score will *not* include this
    drive.
    """
    if before:
        return self.game.score_at_time(self.start_time)
    else:
        return self.game.score_at_time(self.end_time)

Inherited members

• SQLDrive:
  • from_row_dict
  • from_row_tuple
  • sql_fields

class Enums

Enums groups all enum types used in the database schema. All possible values for each enum type are represented as lists. The ordering of each list is the same as the ordering in the database. In particular, this ordering specifies a total ordering that can be used in Python code to compare values in the same enumeration.

Expand source code

class Enums (object):
    """
    Enums groups all enum types used in the database schema.
    All possible values for each enum type are represented as lists.
    The ordering of each list is the same as the ordering in the
    database. In particular, this ordering specifies a total ordering
    that can be used in Python code to compare values in the same
    enumeration.
    """

    game_phase = _Enum('game_phase',
                       ['Pregame', 'Q1', 'Q2', 'Half',
                        'Q3', 'Q4', 'OT', 'OT2', 'Final'])
    """
    Represents the phase of the game. e.g., `Q1` or `Half`.
    """

    season_phase = _Enum('season_phase',
                         ['Preseason', 'Regular', 'Postseason'])
    """
    Represents one of the three phases of an NFL season: `Preseason`,
    `Regular` or `Postseason`.
    """

    game_day = _Enum('game_day',
                     ['Sunday', 'Monday', 'Tuesday', 'Wednesday',
                      'Thursday', 'Friday', 'Saturday'])
    """
    The day of the week on which a game was played. The week starts
    on `Sunday`.
    """

    player_pos = _Enum('player_pos',
                       ['C', 'CB', 'DB', 'DE', 'DL', 'DT', 'FB', 'FS', 'G',
                        'ILB', 'K', 'LB', 'LS', 'MLB', 'NT', 'OG', 'OL', 'OLB',
                        'OT', 'P', 'QB', 'RB', 'SAF', 'SS', 'T', 'TE', 'WR',
                        'UNK'])
    """
    The set of all possible player positions in abbreviated form.
    """

    player_status = _Enum('player_status',
                          ['Active', 'InjuredReserve', 'NonFootballInjury',
                           'Suspended', 'PUP', 'UnsignedDraftPick',
                           'Exempt', 'Unknown'])
    """
    The current status of a player that is actively on a
    roster. The statuses are taken from the key at the bottom of
    http://goo.gl/HHsnjD
    """

    category_scope = _Enum('category_scope', ['play', 'player'])
    """
    The scope of a particular statistic. Typically, statistics refer
    to a specific `player`, but sometimes a statistic refers to the
    totality of a play. For example, `third_down_att` is a `play`
    statistic that records third down attempts.

    Currently, `play` and `player` are the only possible values.

    Note that this type is not represented directly in the database
    schema. Values of this type are constructed from data in
    `category.py`.
    """

    _nflgame_season_phase = {
        'PRE': season_phase.Preseason,
        'REG': season_phase.Regular,
        'POST': season_phase.Postseason,
    }
    """
    Maps a season type in `nflgame` to a `nfldb.Enums.season_phase`.
    """

    _nflgame_game_phase = {
        'Pregame': game_phase.Pregame,
        'Halftime': game_phase.Half,
        'Final': game_phase.Final,
        'final': game_phase.Final,
        1: game_phase.Q1,
        2: game_phase.Q2,
        3: game_phase.Half,
        4: game_phase.Q3,
        5: game_phase.Q4,
        6: game_phase.OT,
        7: game_phase.OT2,
    }
    """
    Maps a game phase in `nflgame` to a `nfldb.Enums.game_phase`.
    """

    _nflgame_game_day = {
        'Sun': game_day.Sunday,
        'Mon': game_day.Monday,
        'Tue': game_day.Tuesday,
        'Wed': game_day.Wednesday,
        'Thu': game_day.Thursday,
        'Fri': game_day.Friday,
        'Sat': game_day.Saturday,
    }
    """
    Maps a game day of the week in `nflgame` to a
    `nfldb.Enums.game_day`.
    """

    _nflgame_player_status = {
        'ACT': player_status.Active,
        'RES': player_status.InjuredReserve,
        'NON': player_status.NonFootballInjury,
        'Suspended': player_status.Suspended,
        'PUP': player_status.PUP,
        'UDF': player_status.UnsignedDraftPick,
        'EXE': player_status.Exempt,
        # Everything else is `player_status.Unknown`
    }

Class variables

var category_scope

The scope of a particular statistic. Typically, statistics refer to a specific player, but sometimes a statistic refers to the totality of a play. For example, third_down_att is a play statistic that records third down attempts.

Currently, play and player are the only possible values.

Note that this type is not represented directly in the database schema. Values of this type are constructed from data in category.py.

var game_day

The day of the week on which a game was played. The week starts on Sunday.

var game_phase

Represents the phase of the game. e.g., Q1 or Half.

var player_pos

The set of all possible player positions in abbreviated form.

var player_status

The current status of a player that is actively on a roster. The statuses are taken from the key at the bottom of http://goo.gl/HHsnjD

var season_phase

Represents one of the three phases of an NFL season: Preseason, Regular or Postseason.

class FieldPosition (offset)

Represents field position.

The representation is an integer offset where the 50 yard line corresponds to '0'. Being in one's own territory corresponds to a negative offset while being in the opponent's territory corresponds to a positive offset.

e.g., NE has the ball on the NE 45, the offset is -5. e.g., NE has the ball on the NYG 2, the offset is 48.

This class also defines a total ordering on field positions. Namely, given f1 and f2, f1 < f2 if and only if f2 is closer to the goal line for the team with possession of the football.

Makes a new FieldPosition given a field offset. offset must be in the integer range [-50, 50].

Expand source code

class FieldPosition (object):
    """
    Represents field position.

    The representation is an integer offset where the 50 yard line
    corresponds to '0'. Being in one's own territory corresponds to a
    negative offset while being in the opponent's territory corresponds
    to a positive offset.

    e.g., NE has the ball on the NE 45, the offset is -5.
    e.g., NE has the ball on the NYG 2, the offset is 48.

    This class also defines a total ordering on field
    positions. Namely, given f1 and f2, f1 < f2 if and only if f2
    is closer to the goal line for the team with possession of the
    football.
    """
    __slots__ = ['_offset']

    @staticmethod
    def _pg_cast(sqlv, cursor):
        if not sqlv:
            return FieldPosition(None)
        return FieldPosition(int(sqlv[1:-1]))

    @staticmethod
    def from_str(pos):
        """
        Given a string `pos` in the format `FIELD YARDLINE`, this
        returns a new `FieldPosition` object representing the yardline
        given. `FIELD` must be the string `OWN` or `OPP` and `YARDLINE`
        must be an integer in the range `[0, 50]`.

        For example, `OPP 19` corresponds to an offset of `31`
        and `OWN 5` corresponds to an offset of `-45`. Midfield can be
        expressed as either `MIDFIELD`, `OWN 50` or `OPP 50`.
        """
        if pos.upper() == 'MIDFIELD':
            return FieldPosition(0)

        field, yrdline = pos.split(' ')
        field, yrdline = field.upper(), int(yrdline)
        assert field in ('OWN', 'OPP')
        assert 0 <= yrdline <= 50

        if field == 'OWN':
            return FieldPosition(yrdline - 50)
        else:
            return FieldPosition(50 - yrdline)

    def __init__(self, offset):
        """
        Makes a new `nfldb.FieldPosition` given a field `offset`.
        `offset` must be in the integer range [-50, 50].
        """
        if offset is None:
            self._offset = None
            return
        assert -50 <= offset <= 50
        self._offset = offset

    def _add_yards(self, yards):
        """
        Returns a new `nfldb.FieldPosition` with `yards` added to this
        field position. The value of `yards` may be negative.
        """
        assert self.valid
        newoffset = max(-50, min(50, self._offset + yards))
        return FieldPosition(newoffset)

    @property
    def valid(self):
        """
        Returns `True` if and only if this field position is known and
        valid.

        Invalid field positions cannot be compared with other field
        positions.
        """
        return self._offset is not None

    def __add__(self, other):
        if isinstance(other, FieldPosition):
            toadd = other._offset
        else:
            toadd = other
        newoffset = max(-50, min(50, self._offset + toadd))
        return FieldPosition(newoffset)

    def __lt__(self, other):
        if self.__class__ is not other.__class__:
            return NotImplemented
        if not self.valid:
            return True
        if not other.valid:
            return False
        return self._offset < other._offset

    def __eq__(self, other):
        if self.__class__ is not other.__class__:
            return NotImplemented
        return self._offset == other._offset

    def __str__(self):
        if not self.valid:
            return 'N/A'
        elif self._offset > 0:
            return 'OPP %d' % (50 - self._offset)
        elif self._offset < 0:
            return 'OWN %d' % (50 + self._offset)
        else:
            return 'MIDFIELD'

    def __conform__(self, proto):
        if proto is ISQLQuote:
            if not self.valid:
                return AsIs("NULL")
            else:
                return AsIs("ROW(%d)::field_pos" % self._offset)
        return None

Static methods

def from_str(pos)

Given a string pos in the format FIELD YARDLINE, this returns a new FieldPosition object representing the yardline given. FIELD must be the string OWN or OPP and YARDLINE must be an integer in the range [0, 50].

For example, OPP 19 corresponds to an offset of 31 and OWN 5 corresponds to an offset of -45. Midfield can be expressed as either MIDFIELD, OWN 50 or OPP 50.

Expand source code

@staticmethod
def from_str(pos):
    """
    Given a string `pos` in the format `FIELD YARDLINE`, this
    returns a new `FieldPosition` object representing the yardline
    given. `FIELD` must be the string `OWN` or `OPP` and `YARDLINE`
    must be an integer in the range `[0, 50]`.

    For example, `OPP 19` corresponds to an offset of `31`
    and `OWN 5` corresponds to an offset of `-45`. Midfield can be
    expressed as either `MIDFIELD`, `OWN 50` or `OPP 50`.
    """
    if pos.upper() == 'MIDFIELD':
        return FieldPosition(0)

    field, yrdline = pos.split(' ')
    field, yrdline = field.upper(), int(yrdline)
    assert field in ('OWN', 'OPP')
    assert 0 <= yrdline <= 50

    if field == 'OWN':
        return FieldPosition(yrdline - 50)
    else:
        return FieldPosition(50 - yrdline)

Instance variables

var valid

Returns True if and only if this field position is known and valid.

Invalid field positions cannot be compared with other field positions.

Expand source code

@property
def valid(self):
    """
    Returns `True` if and only if this field position is known and
    valid.

    Invalid field positions cannot be compared with other field
    positions.
    """
    return self._offset is not None

class Game (db)

Represents a single NFL game in the preseason, regular season or post season. Each game has an assortment of meta data, including a quarterly breakdown of scores, turnovers, the time the game started, the season week the game occurred in, and more.

Each game corresponds to zero or more drives. A game usually corresponds to at least one drive, but if the game is active, there exist valid ephemeral states where a game has no drives.

Creates a new and empty Game object with the given database connection.

This constructor should not be used by clients. Instead, you should get Game objects from Query or from one of the other constructors, like Game.from_id() or Entity.from_row_dict(). (The latter is useful only if you're writing your own SQL queries.)

Expand source code

class Game (SQLGame):
    """
    Represents a single NFL game in the preseason, regular season or
    post season. Each game has an assortment of meta data, including
    a quarterly breakdown of scores, turnovers, the time the game
    started, the season week the game occurred in, and more.

    Each game corresponds to **zero or more** drives. A game usually
    corresponds to at least one drive, but if the game is active, there
    exist valid ephemeral states where a game has no drives.
    """
    __slots__ = SQLGame.sql_fields() + ['_db', '_drives', '_plays']

    # Document instance variables for derived SQL fields.
    __pdoc__['Game.winner'] = '''The winner of this game.'''
    __pdoc__['Game.loser'] = '''The loser of this game.'''

    @staticmethod
    def _from_nflgame(db, g):
        """
        Converts a `nflgame.game.Game` object to a `nfldb.Game`
        object.

        `db` should be a psycopg2 connection returned by
        `nfldb.connect`.
        """
        dbg = Game(db)
        dbg.gsis_id = g.eid
        dbg.gamekey = g.gamekey
        dbg.start_time = _nflgame_start_time(g.schedule)
        dbg.week = g.schedule['week']
        dbg.day_of_week = Enums._nflgame_game_day[g.schedule['wday']]
        dbg.season_year = g.schedule['year']
        dbg.season_type = Enums._nflgame_season_phase[g.schedule['season_type']]
        dbg.finished = g.game_over()
        dbg.home_team = nfldb.team.standard_team(g.home)
        dbg.home_score = g.score_home
        dbg.home_score_q1 = g.score_home_q1
        dbg.home_score_q2 = g.score_home_q2
        dbg.home_score_q3 = g.score_home_q3
        dbg.home_score_q4 = g.score_home_q4
        dbg.home_score_q5 = g.score_home_q5
        dbg.home_turnovers = int(g.data['home']['to'])
        dbg.away_team = nfldb.team.standard_team(g.away)
        dbg.away_score = g.score_away
        dbg.away_score_q1 = g.score_away_q1
        dbg.away_score_q2 = g.score_away_q2
        dbg.away_score_q3 = g.score_away_q3
        dbg.away_score_q4 = g.score_away_q4
        dbg.away_score_q5 = g.score_away_q5
        dbg.away_turnovers = int(g.data['away']['to'])

        # If it's been 8 hours since game start, we always conclude finished!
        if (now() - dbg.start_time).total_seconds() >= (60 * 60 * 8):
            dbg.finished = True

        dbg._drives = []
        for drive in g.drives:
            if not hasattr(drive, 'game'):
                continue
            dbg._drives.append(Drive._from_nflgame(db, dbg, drive))
        dbg._drives.sort(key=lambda d: d.drive_id)
        return dbg

    @staticmethod
    def _from_schedule(db, s):
        """
        Converts a schedule dictionary from the `nflgame.schedule`
        module to a bare-bones `nfldb.Game` object.
        """
        # This is about as evil as it gets. Duck typing to the MAX!
        class _Game (object):
            def __init__(self):
                self.schedule = s
                self.home, self.away = s['home'], s['away']
                self.eid = s['eid']
                self.gamekey = s['gamekey']
                self.drives = []
                self.game_over = lambda: False

                zeroes = ['score_%s', 'score_%s_q1', 'score_%s_q2',
                          'score_%s_q3', 'score_%s_q4', 'score_%s_q5']
                for which, k in itertools.product(('home', 'away'), zeroes):
                    setattr(self, k % which, 0)
                self.data = {'home': {'to': 0}, 'away': {'to': 0}}
        return Game._from_nflgame(db, _Game())

    @staticmethod
    def from_id(db, gsis_id):
        """
        Given a GSIS identifier (e.g., `2012090500`) as a string,
        returns a `nfldb.Game` object corresponding to `gsis_id`.

        If no corresponding game is found, `None` is returned.
        """
        import nfldb.query
        q = nfldb.query.Query(db)
        games = q.game(gsis_id=gsis_id).limit(1).as_games()
        if len(games) == 0:
            return None
        return games[0]

    def __init__(self, db):
        """
        Creates a new and empty `nfldb.Game` object with the given
        database connection.

        This constructor should not be used by clients. Instead, you
        should get `nfldb.Game` objects from `nfldb.Query` or from one
        of the other constructors, like `nfldb.Game.from_id` or
        `nfldb.Game.from_row_dict`. (The latter is useful only if you're
        writing your own SQL queries.)
        """
        self._db = db
        """
        The psycopg2 database connection.
        """
        self._drives = None
        self._plays = None

        self.gsis_id = None
        """
        The NFL GameCenter id of the game. It is a string
        with 10 characters. The first 8 correspond to the date of the
        game, while the last 2 correspond to an id unique to the week that
        the game was played.
        """
        self.gamekey = None
        """
        Another unique identifier for a game used by the
        NFL. It is a sequence number represented as a 5 character string.
        The gamekey is specifically used to tie games to other resources,
        like the NFL's content delivery network.
        """
        self.start_time = None
        """
        A Python datetime object corresponding to the start time of
        the game. The timezone of this value will be equivalent to the
        timezone specified by `nfldb.set_timezone` (which is by default
        set to the value specified in the configuration file).
        """
        self.week = None
        """
        The week number of this game. It is always relative
        to the phase of the season. Namely, the first week of preseason
        is 1 and so is the first week of the regular season.
        """
        self.day_of_week = None
        """
        The day of the week this game was played on.
        Possible values correspond to the `nfldb.Enums.game_day` enum.
        """
        self.season_year = None
        """
        The year of the season of this game. This
        does not necessarily match the year that the game was played. For
        example, games played in January 2013 are in season 2012.
        """
        self.season_type = None
        """
        The phase of the season. e.g., `Preseason`,
        `Regular season` or `Postseason`. All valid values correspond
        to the `nfldb.Enums.season_phase`.
        """
        self.finished = None
        """
        A boolean that is `True` if and only if the game has finished.
        """
        self.home_team = None
        """
        The team abbreviation for the home team. Use the `nfldb.Team`
        constructor to get more information on a team.
        """
        self.home_score = None
        """The current total score for the home team."""
        self.home_score_q1 = None
        """The 1st quarter score for the home team."""
        self.home_score_q2 = None
        """The 2nd quarter score for the home team."""
        self.home_score_q3 = None
        """The 3rd quarter score for the home team."""
        self.home_score_q4 = None
        """The 4th quarter score for the home team."""
        self.home_score_q5 = None
        """The OT quarter score for the home team."""
        self.home_turnovers = None
        """Total turnovers for the home team."""
        self.away_team = None
        """
        The team abbreviation for the away team. Use the `nfldb.Team`
        constructor to get more information on a team.
        """
        self.away_score = None
        """The current total score for the away team."""
        self.away_score_q1 = None
        """The 1st quarter score for the away team."""
        self.away_score_q2 = None
        """The 2nd quarter score for the away team."""
        self.away_score_q3 = None
        """The 3rd quarter score for the away team."""
        self.away_score_q4 = None
        """The 4th quarter score for the away team."""
        self.away_score_q5 = None
        """The OT quarter score for the away team."""
        self.away_turnovers = None
        """Total turnovers for the away team."""
        self.time_inserted = None
        """The date and time that this game was added."""
        self.time_updated = None
        """The date and time that this game was last updated."""
        self.winner = None
        """The team abbreviation for the winner of this game."""
        self.loser = None
        """The team abbreviation for the loser of this game."""

    @property
    def is_playing(self):
        """
        Returns `True` is the game is currently being played and
        `False` otherwise.

        A game is being played if it is not finished and if the current
        time proceeds the game's start time.
        """
        return not self.finished and now() >= self.start_time

    @property
    def drives(self):
        """
        A list of `nfldb.Drive`s for this game. They are automatically
        loaded from the database if they haven't been already.

        If there are no drives found in the game, then an empty list
        is returned.
        """
        if self._drives is None:
            import nfldb.query
            q = nfldb.query.Query(self._db)
            self._drives = q.drive(gsis_id=self.gsis_id).as_drives()
            for d in self._drives:
                d._game = self
        return self._drives

    @property
    def plays(self):
        """
        A list of `nfldb.Play` objects in this game. Data is retrieved
        from the database if it hasn't been already.
        """
        if self._plays is None:
            import nfldb.query
            q = nfldb.query.Query(self._db)
            q.sort([('time', 'asc'), ('play_id', 'asc')])
            self._plays = q.play(gsis_id=self.gsis_id).as_plays()
        return self._plays

    def plays_range(self, start, end):
        """
        Returns a list of `nfldb.Play` objects for this game in the
        time range specified. The range corresponds to a half-open
        interval, i.e., `[start, end)`. Namely, all plays starting at
        or after `start` up to plays starting *before* `end`.

        The plays are returned in the order in which they occurred.

        `start` and `end` should be instances of the
        `nfldb.Clock` class. (Hint: Values can be created with the
        `nfldb.Clock.from_str` function.)
        """
        import nfldb.query as query

        q = query.Query(self._db)
        q.play(gsis_id=self.gsis_id, time__ge=start, time__lt=end)
        q.sort([('time', 'asc'), ('play_id', 'asc')])
        return q.as_plays()

    def score_in_plays(self, plays):
        """
        Returns the scores made by the home and away teams from the
        sequence of plays given. The scores are returned as a `(home,
        away)` tuple. Note that this method assumes that `plays` is
        sorted in the order in which the plays occurred.
        """
        # This method is a heuristic to compute the total number of points
        # scored in a set of plays. Naively, this should be a simple summation
        # of the `points` attribute of each field. However, it seems that
        # the JSON feed (where this data comes from) heavily biases toward
        # omitting XPs. Therefore, we attempt to add them. A brief outline
        # of the heuristic follows.
        #
        # In *most* cases, a TD is followed by either an XP attempt or a 2 PTC
        # attempt by the same team. Therefore, after each TD, we look for the
        # next play that fits this criteria, while being careful not to find
        # a play that has already counted toward the score. If no play was
        # found, then we assume there was an XP attempt and that it was good.
        # Otherwise, if a play is found matching the given TD, the point total
        # of that play is added to the score.
        #
        # Note that this relies on the property that every TD is paired with
        # an XP/2PTC with respect to the final score of a game. Namely, when
        # searching for the XP/2PTC after a TD, it may find a play that came
        # after a different TD. But this is OK, so long as we never double
        # count any particular play.
        def is_twopta(p):
            return (p.passing_twopta > 0
                    or p.receiving_twopta > 0
                    or p.rushing_twopta > 0)

        counted = set()  # don't double count
        home, away = 0, 0
        for i, p in enumerate(plays):
            pts = p.points
            if pts > 0 and p.play_id not in counted:
                counted.add(p.play_id)

                if pts == 6:
                    def after_td(p2):
                        return (p.pos_team == p2.pos_team
                                and (p2.kicking_xpa > 0 or is_twopta(p2))
                                and p2.play_id not in counted)

                    next = _next_play_with(plays, p, after_td)
                    if next is None:
                        pts += 1
                    elif next.play_id not in counted:
                        pts += next.points
                        counted.add(next.play_id)
                if p.scoring_team == self.home_team:
                    home += pts
                else:
                    away += pts
        return home, away

    def score_at_time(self, time):
        """
        Returns the score of the game at the time specified as a
        `(home, away)` tuple.

        `time` should be an instance of the `nfldb.Clock` class.
        (Hint: Values can be created with the `nfldb.Clock.from_str`
        function.)
        """
        start = Clock.from_str('Pregame', '0:00')
        return self.score_in_plays(self.plays_range(start, time))

    @property
    def play_players(self):
        """
        A list of `nfldb.PlayPlayer` objects in this game. Data is
        retrieved from the database if it hasn't been already.
        """
        pps = []
        for play in self.plays:
            for pp in play.play_players:
                pps.append(pp)
        return pps

    @property
    def players(self):
        """
        A list of tuples of player data. The first element is the team
        the player was on during the game and the second element is a
        `nfldb.Player` object corresponding to that player's meta data
        (including the team he's currently on). The list is returned
        without duplicates and sorted by team and player name.
        """
        pset = set()
        players = []
        for pp in self.play_players:
            if pp.player_id not in pset:
                players.append((pp.team, pp.player))
                pset.add(pp.player_id)
        return sorted(players)

    def _save(self, cursor):
        super(Game, self)._save(cursor)
        if not self._drives:
            return

        # Remove any drives that are stale.
        cursor.execute('''
            DELETE FROM drive
            WHERE gsis_id = %s AND NOT (drive_id = ANY (%s))
        ''', (self.gsis_id, [d.drive_id for d in self._drives]))
        for drive in (self._drives or []):
            drive._save(cursor)

    def __str__(self):
        return '%s %d week %d on %s at %s, %s (%d) at %s (%d)' \
               % (self.season_type, self.season_year, self.week,
                  self.start_time.strftime('%m/%d'),
                  self.start_time.strftime('%I:%M%p'),
                  self.away_team, self.away_score,
                  self.home_team, self.home_score)

Ancestors

• SQLGame
• Entity

Static methods

def from_id(db, gsis_id)

Given a GSIS identifier (e.g., 2012090500) as a string, returns a Game object corresponding to gsis_id.

If no corresponding game is found, None is returned.

Expand source code

@staticmethod
def from_id(db, gsis_id):
    """
    Given a GSIS identifier (e.g., `2012090500`) as a string,
    returns a `nfldb.Game` object corresponding to `gsis_id`.

    If no corresponding game is found, `None` is returned.
    """
    import nfldb.query
    q = nfldb.query.Query(db)
    games = q.game(gsis_id=gsis_id).limit(1).as_games()
    if len(games) == 0:
        return None
    return games[0]

Instance variables

var away_score

The current total score for the away team.

var away_score_q1

The 1st quarter score for the away team.

var away_score_q2

The 2nd quarter score for the away team.

var away_score_q3

The 3rd quarter score for the away team.

var away_score_q4

The 4th quarter score for the away team.

var away_score_q5

The OT quarter score for the away team.

var away_team

The team abbreviation for the away team. Use the Team constructor to get more information on a team.

var away_turnovers

Total turnovers for the away team.

var day_of_week

The day of the week this game was played on. Possible values correspond to the Enums.game_day enum.

var drives

A list of Drives for this game. They are automatically loaded from the database if they haven't been already.

If there are no drives found in the game, then an empty list is returned.

Expand source code

@property
def drives(self):
    """
    A list of `nfldb.Drive`s for this game. They are automatically
    loaded from the database if they haven't been already.

    If there are no drives found in the game, then an empty list
    is returned.
    """
    if self._drives is None:
        import nfldb.query
        q = nfldb.query.Query(self._db)
        self._drives = q.drive(gsis_id=self.gsis_id).as_drives()
        for d in self._drives:
            d._game = self
    return self._drives

var finished

A boolean that is True if and only if the game has finished.

var gamekey

Another unique identifier for a game used by the NFL. It is a sequence number represented as a 5 character string. The gamekey is specifically used to tie games to other resources, like the NFL's content delivery network.

var gsis_id

The NFL GameCenter id of the game. It is a string with 10 characters. The first 8 correspond to the date of the game, while the last 2 correspond to an id unique to the week that the game was played.

var home_score

The current total score for the home team.

var home_score_q1

The 1st quarter score for the home team.

var home_score_q2

The 2nd quarter score for the home team.

var home_score_q3

The 3rd quarter score for the home team.

var home_score_q4

The 4th quarter score for the home team.

var home_score_q5

The OT quarter score for the home team.

var home_team

The team abbreviation for the home team. Use the Team constructor to get more information on a team.

var home_turnovers

Total turnovers for the home team.

var is_playing

Returns True is the game is currently being played and False otherwise.

A game is being played if it is not finished and if the current time proceeds the game's start time.

Expand source code

@property
def is_playing(self):
    """
    Returns `True` is the game is currently being played and
    `False` otherwise.

    A game is being played if it is not finished and if the current
    time proceeds the game's start time.
    """
    return not self.finished and now() >= self.start_time

var loser

The loser of this game.

var play_players

A list of PlayPlayer objects in this game. Data is retrieved from the database if it hasn't been already.

Expand source code

@property
def play_players(self):
    """
    A list of `nfldb.PlayPlayer` objects in this game. Data is
    retrieved from the database if it hasn't been already.
    """
    pps = []
    for play in self.plays:
        for pp in play.play_players:
            pps.append(pp)
    return pps

var players

A list of tuples of player data. The first element is the team the player was on during the game and the second element is a Player object corresponding to that player's meta data (including the team he's currently on). The list is returned without duplicates and sorted by team and player name.

Expand source code

@property
def players(self):
    """
    A list of tuples of player data. The first element is the team
    the player was on during the game and the second element is a
    `nfldb.Player` object corresponding to that player's meta data
    (including the team he's currently on). The list is returned
    without duplicates and sorted by team and player name.
    """
    pset = set()
    players = []
    for pp in self.play_players:
        if pp.player_id not in pset:
            players.append((pp.team, pp.player))
            pset.add(pp.player_id)
    return sorted(players)

var plays

A list of Play objects in this game. Data is retrieved from the database if it hasn't been already.

Expand source code

@property
def plays(self):
    """
    A list of `nfldb.Play` objects in this game. Data is retrieved
    from the database if it hasn't been already.
    """
    if self._plays is None:
        import nfldb.query
        q = nfldb.query.Query(self._db)
        q.sort([('time', 'asc'), ('play_id', 'asc')])
        self._plays = q.play(gsis_id=self.gsis_id).as_plays()
    return self._plays

var season_type

The phase of the season. e.g., Preseason, Regular season or Postseason. All valid values correspond to the Enums.season_phase.

var season_year

The year of the season of this game. This does not necessarily match the year that the game was played. For example, games played in January 2013 are in season 2012.

var start_time

A Python datetime object corresponding to the start time of the game. The timezone of this value will be equivalent to the timezone specified by set_timezone() (which is by default set to the value specified in the configuration file).

var time_inserted

The date and time that this game was added.

var time_updated

The date and time that this game was last updated.

var week

The week number of this game. It is always relative to the phase of the season. Namely, the first week of preseason is 1 and so is the first week of the regular season.

var winner

The winner of this game.

Methods

def plays_range(self, start, end)

Returns a list of Play objects for this game in the time range specified. The range corresponds to a half-open interval, i.e., [start, end). Namely, all plays starting at or after start up to plays starting before end.

The plays are returned in the order in which they occurred.

start and end should be instances of the Clock class. (Hint: Values can be created with the Clock.from_str() function.)

Expand source code

def plays_range(self, start, end):
    """
    Returns a list of `nfldb.Play` objects for this game in the
    time range specified. The range corresponds to a half-open
    interval, i.e., `[start, end)`. Namely, all plays starting at
    or after `start` up to plays starting *before* `end`.

    The plays are returned in the order in which they occurred.

    `start` and `end` should be instances of the
    `nfldb.Clock` class. (Hint: Values can be created with the
    `nfldb.Clock.from_str` function.)
    """
    import nfldb.query as query

    q = query.Query(self._db)
    q.play(gsis_id=self.gsis_id, time__ge=start, time__lt=end)
    q.sort([('time', 'asc'), ('play_id', 'asc')])
    return q.as_plays()

def score_at_time(self, time)

Returns the score of the game at the time specified as a (home, away) tuple.

time should be an instance of the Clock class. (Hint: Values can be created with the Clock.from_str() function.)

Expand source code

def score_at_time(self, time):
    """
    Returns the score of the game at the time specified as a
    `(home, away)` tuple.

    `time` should be an instance of the `nfldb.Clock` class.
    (Hint: Values can be created with the `nfldb.Clock.from_str`
    function.)
    """
    start = Clock.from_str('Pregame', '0:00')
    return self.score_in_plays(self.plays_range(start, time))

def score_in_plays(self, plays)

Returns the scores made by the home and away teams from the sequence of plays given. The scores are returned as a (home, away)<code> tuple. Note that this method assumes that </code>plays is sorted in the order in which the plays occurred.

Expand source code

def score_in_plays(self, plays):
    """
    Returns the scores made by the home and away teams from the
    sequence of plays given. The scores are returned as a `(home,
    away)` tuple. Note that this method assumes that `plays` is
    sorted in the order in which the plays occurred.
    """
    # This method is a heuristic to compute the total number of points
    # scored in a set of plays. Naively, this should be a simple summation
    # of the `points` attribute of each field. However, it seems that
    # the JSON feed (where this data comes from) heavily biases toward
    # omitting XPs. Therefore, we attempt to add them. A brief outline
    # of the heuristic follows.
    #
    # In *most* cases, a TD is followed by either an XP attempt or a 2 PTC
    # attempt by the same team. Therefore, after each TD, we look for the
    # next play that fits this criteria, while being careful not to find
    # a play that has already counted toward the score. If no play was
    # found, then we assume there was an XP attempt and that it was good.
    # Otherwise, if a play is found matching the given TD, the point total
    # of that play is added to the score.
    #
    # Note that this relies on the property that every TD is paired with
    # an XP/2PTC with respect to the final score of a game. Namely, when
    # searching for the XP/2PTC after a TD, it may find a play that came
    # after a different TD. But this is OK, so long as we never double
    # count any particular play.
    def is_twopta(p):
        return (p.passing_twopta > 0
                or p.receiving_twopta > 0
                or p.rushing_twopta > 0)

    counted = set()  # don't double count
    home, away = 0, 0
    for i, p in enumerate(plays):
        pts = p.points
        if pts > 0 and p.play_id not in counted:
            counted.add(p.play_id)

            if pts == 6:
                def after_td(p2):
                    return (p.pos_team == p2.pos_team
                            and (p2.kicking_xpa > 0 or is_twopta(p2))
                            and p2.play_id not in counted)

                next = _next_play_with(plays, p, after_td)
                if next is None:
                    pts += 1
                elif next.play_id not in counted:
                    pts += next.points
                    counted.add(next.play_id)
            if p.scoring_team == self.home_team:
                home += pts
            else:
                away += pts
    return home, away

Inherited members

• SQLGame:
  • from_row_dict
  • from_row_tuple
  • sql_fields

class Play (db)

Represents a single play in an NFL game. Each play has an assortment of meta data, possibly including the time on the clock in which the ball was snapped, the starting field position, the down, yards to go, etc. Not all plays have values for each field (for example, a timeout is considered a play but has no data for Play.down or Play.yardline).

In addition to meta data describing the context of the game at the time the ball was snapped, plays also have statistics corresponding to the fields in nfldb.stat_categories with a Category.category_type of play. For example, third_down_att, fourth_down_failed and fourth_down_conv. While the binary nature of these fields suggest a boolean value, they are actually integers. This makes them amenable to aggregation.

Plays are also associated with player statistics or "events" that occurred in a play. For example, in a single play one player could pass the ball to another player. This is recorded as two different player statistics: a pass and a reception. Each one is represented as a PlayPlayer object. Plays may have zero or more of these player statistics.

Finally, it is important to note that there are (currently) some useful statistics missing. For example, there is currently no reliable means of determining the time on the clock when the play finished. Also, there is no field describing the field position at the end of the play, although this may be added in the future.

Most of the statistical fields are documented on the statistical categories wiki page. Each statistical field is an instance attribute in this class.

Creates a new and empty Play object with the given database connection.

This constructor should not be used by clients. Instead, you should get Play objects from Query or from one of the other constructors, like Play.from_id() or Entity.from_row_dict(). (The latter is useful only if you're writing your own SQL queries.)

Expand source code

class Play (SQLPlay):
    """
    Represents a single play in an NFL game. Each play has an
    assortment of meta data, possibly including the time on the clock
    in which the ball was snapped, the starting field position, the
    down, yards to go, etc. Not all plays have values for each field
    (for example, a timeout is considered a play but has no data for
    `nfldb.Play.down` or `nfldb.Play.yardline`).

    In addition to meta data describing the context of the game at the time
    the ball was snapped, plays also have statistics corresponding to the
    fields in `nfldb.stat_categories` with a `nfldb.Category.category_type`
    of `play`. For example, `third_down_att`, `fourth_down_failed` and
    `fourth_down_conv`. While the binary nature of these fields suggest
    a boolean value, they are actually integers. This makes them amenable
    to aggregation.

    Plays are also associated with player statistics or "events" that
    occurred in a play. For example, in a single play one player could
    pass the ball to another player. This is recorded as two different
    player statistics: a pass and a reception. Each one is represented
    as a `nfldb.PlayPlayer` object. Plays may have **zero or more** of
    these player statistics.

    Finally, it is important to note that there are (currently) some
    useful statistics missing. For example, there is currently no
    reliable means of determining the time on the clock when the play
    finished.  Also, there is no field describing the field position at
    the end of the play, although this may be added in the future.

    Most of the statistical fields are documented on the
    [statistical categories](http://goo.gl/YY587P)
    wiki page. Each statistical field is an instance attribute in
    this class.
    """
    __slots__ = SQLPlay.sql_fields() + ['_db', '_drive', '_play_players']

    # Document instance variables for derived SQL fields.
    # We hide them from the public interface, but make the doco
    # available to nfldb-mk-stat-table. Evil!
    __pdoc__['Play.offense_yds'] = None
    __pdoc__['_Play.offense_yds'] = \
        '''
        Corresponds to any yardage that is manufactured by the offense.
        Namely, the following fields:
        `nfldb.Play.passing_yds`,
        `nfldb.Play.rushing_yds`,
        `nfldb.Play.receiving_yds` and
        `nfldb.Play.fumbles_rec_yds`.

        This field is useful when searching for plays by net yardage
        regardless of how the yards were obtained.
        '''
    __pdoc__['Play.offense_tds'] = None
    __pdoc__['_Play.offense_tds'] = \
        '''
        Corresponds to any touchdown manufactured by the offense via
        a passing, reception, rush or fumble recovery.
        '''
    __pdoc__['Play.defense_tds'] = None
    __pdoc__['_Play.defense_tds'] = \
        '''
        Corresponds to any touchdown manufactured by the defense.
        e.g., a pick-6, fumble recovery TD, punt/FG block TD, etc.
        '''
    __pdoc__['Play.points'] = \
        """
        The number of points scored in this player statistic. This
        accounts for touchdowns, extra points, two point conversions,
        field goals and safeties.
        """

    @staticmethod
    def _from_nflgame(db, d, p):
        """
        Given `d` as a `nfldb.Drive` object and `p` as a
        `nflgame.game.Play` object, `_from_nflgame` converts `p` to a
        `nfldb.Play` object.
        """
        # Fix up some fields so they meet the constraints of the schema.
        # The `time` field is cleaned up afterwards in
        # `nfldb.Drive._from_nflgame`, since it needs data about surrounding
        # plays.
        time = None if not p.time else _nflgame_clock(p.time)
        yardline = FieldPosition(getattr(p.yardline, 'offset', None))
        down = p.down if 1 <= p.down <= 4 else None
        team = p.team if p.team is not None and len(p.team) > 0 else 'UNK'

        dbplay = Play(db)
        dbplay.gsis_id = d.gsis_id
        dbplay.drive_id = d.drive_id
        dbplay.play_id = int(p.playid)
        dbplay.time = time
        dbplay.pos_team = team
        dbplay.yardline = yardline
        dbplay.down = down
        dbplay.yards_to_go = p.yards_togo
        dbplay.description = p.desc
        dbplay.note = p.note
        for k in list(_play_categories.keys()):
            if p._stats.get(k, 0) != 0:
                setattr(dbplay, k, p._stats[k])
        # Note that `Play` objects also normally contain aggregated
        # statistics, but we forgo that here because this constructor
        # is only used to load plays into the database.

        dbplay._drive = d
        dbplay._play_players = []
        for pp in p.players:
            dbpp = PlayPlayer._from_nflgame(db, dbplay, pp)
            dbplay._play_players.append(dbpp)
        return dbplay

    @staticmethod
    def from_id(db, gsis_id, drive_id, play_id):
        """
        Given a GSIS identifier (e.g., `2012090500`) as a string,
        an integer drive id and an integer play id, this returns a
        `nfldb.Play` object corresponding to the given identifiers.

        If no corresponding play is found, then `None` is returned.
        """
        import nfldb.query
        q = nfldb.query.Query(db)
        q.play(gsis_id=gsis_id, drive_id=drive_id, play_id=play_id).limit(1)
        plays = q.as_plays()
        if len(plays) == 0:
            return None
        return plays[0]

    @staticmethod
    def fill_drives(db, plays):
        """
        Given a list of `plays`, fill all of their `drive` attributes
        using as few queries as possible. This will also fill the
        drives with game data.
        """
        _fill(db, Drive, plays, '_drive')
        Drive.fill_games(db, [p._drive for p in plays])

    def __init__(self, db):
        """
        Creates a new and empty `nfldb.Play` object with the given
        database connection.

        This constructor should not be used by clients. Instead, you
        should get `nfldb.Play` objects from `nfldb.Query` or from one
        of the other constructors, like `nfldb.Play.from_id` or
        `nfldb.Play.from_row_dict`. (The latter is useful only if you're
        writing your own SQL queries.)
        """
        self._db = db
        self._drive = None
        self._play_players = None

        self.gsis_id = None
        """
        The GSIS identifier for the game that this play belongs to.
        """
        self.drive_id = None
        """
        The numeric drive identifier for this play. It may be
        interpreted as a sequence number.
        """
        self.play_id = None
        """
        The numeric play identifier for this play. It can typically
        be interpreted as a sequence number scoped to the week that
        this game was played, but it's unfortunately not completely
        consistent.
        """
        self.time = None
        """
        The time on the clock when the play started, represented with
        a `nfldb.Clock` object.
        """
        self.pos_team = None
        """
        The team in possession during this play, represented as
        a team abbreviation string. Use the `nfldb.Team` constructor
        to get more information on a team.
        """
        self.yardline = None
        """
        The starting field position of this play represented with
        `nfldb.FieldPosition`.
        """
        self.down = None
        """
        The down on which this play begin. This may be `0` for
        "special" plays like timeouts or 2 point conversions.
        """
        self.yards_to_go = None
        """
        The number of yards to go to get a first down or score a
        touchdown at the start of the play.
        """
        self.description = None
        """
        A (basically) free-form text description of the play. This is
        typically what you see on NFL GameCenter web pages.
        """
        self.note = None
        """
        A miscellaneous note field (as a string). Not sure what it's
        used for.
        """
        self.time_inserted = None
        """
        The date and time that this play was added to the
        database. This can be very useful when sorting plays by the
        order in which they occurred in real time. Unfortunately, such
        a sort requires that play data is updated relatively close to
        when it actually occurred.
        """
        self.time_updated = None
        """The date and time that this play was last updated."""

    @property
    def drive(self):
        """
        The `nfldb.Drive` object that contains this play. The drive is
        retrieved from the database if it hasn't been already.
        """
        if self._drive is None:
            self._drive = Drive.from_id(self._db, self.gsis_id, self.drive_id)
        return self._drive

    @property
    def play_players(self):
        """
        A list of all `nfldb.PlayPlayer`s in this play. They are
        automatically retrieved from the database if they haven't been
        already.

        If there are no players attached to this play, then an empty
        list is returned.
        """
        if self._play_players is None:
            import nfldb.query
            q = nfldb.query.Query(self._db)
            q.play_player(gsis_id=self.gsis_id, drive_id=self.drive_id,
                          play_id=self.play_id)
            self._play_players = q.as_play_players()
            for pp in self._play_players:
                pp._play = self
        return self._play_players

    @property
    def scoring_team(self):
        """
        If this is a scoring play, returns the team that scored points.
        Otherwise, returns None.

        N.B. `nfldb.Play.scoring_team` returns a valid team if and only
        if `nfldb.Play.points` is greater than 0.
        """
        for pp in self.play_players:
            t = pp.scoring_team
            if t is not None:
                return t
        return None

    def score(self, before=False):
        """
        Returns the score of the game immediately after this play as a
        tuple of the form `(home_score, away_score)`.

        If `before` is `True`, then the score will *not* include this
        play.
        """
        game = Game.from_id(self._db, self.gsis_id)
        if not before:
            return game.score_at_time(self.time.add_seconds(1))

        s = game.score_at_time(self.time)
        # The heuristic in `nfldb.Game.score_in_plays` blends TDs and XPs
        # into a single play (with respect to scoring). So we have to undo
        # that if we want the score of the game after a TD but before an XP.
        if self.kicking_xpmade == 1:
            score_team = self.scoring_team
            if score_team == game.home_team:
                return (s[0] - 1, s[1])
            return (s[0], s[1] - 1)
        return s

    def _save(self, cursor):
        super(Play, self)._save(cursor)

        # Remove any "play players" that are stale.
        cursor.execute('''
            DELETE FROM play_player
            WHERE gsis_id = %s AND drive_id = %s AND play_id = %s
                  AND NOT (player_id = ANY (%s))
        ''', (self.gsis_id, self.drive_id, self.play_id,
              [p.player_id for p in (self._play_players or [])]))
        for pp in (self._play_players or []):
            pp._save(cursor)

    def __str__(self):
        if self.down:
            return '(%s, %s, %s, %d and %d) %s' \
                   % (self.pos_team, self.yardline, self.time.phase,
                      self.down, self.yards_to_go, self.description)
        elif self.pos_team:
            return '(%s, %s, %s) %s' \
                   % (self.pos_team, self.yardline, self.time.phase,
                      self.description)
        else:
            return '(%s) %s' % (self.time.phase, self.description)

    def __getattr__(self, k):
        if k in Play.__slots__:
            return 0
        raise AttributeError(k)

Ancestors

• SQLPlay
• Entity

Static methods

def fill_drives(db, plays)

Given a list of plays, fill all of their drive attributes using as few queries as possible. This will also fill the drives with game data.

Expand source code

@staticmethod
def fill_drives(db, plays):
    """
    Given a list of `plays`, fill all of their `drive` attributes
    using as few queries as possible. This will also fill the
    drives with game data.
    """
    _fill(db, Drive, plays, '_drive')
    Drive.fill_games(db, [p._drive for p in plays])

def from_id(db, gsis_id, drive_id, play_id)

Given a GSIS identifier (e.g., 2012090500) as a string, an integer drive id and an integer play id, this returns a Play object corresponding to the given identifiers.

If no corresponding play is found, then None is returned.

Expand source code

@staticmethod
def from_id(db, gsis_id, drive_id, play_id):
    """
    Given a GSIS identifier (e.g., `2012090500`) as a string,
    an integer drive id and an integer play id, this returns a
    `nfldb.Play` object corresponding to the given identifiers.

    If no corresponding play is found, then `None` is returned.
    """
    import nfldb.query
    q = nfldb.query.Query(db)
    q.play(gsis_id=gsis_id, drive_id=drive_id, play_id=play_id).limit(1)
    plays = q.as_plays()
    if len(plays) == 0:
        return None
    return plays[0]

Instance variables

var description

A (basically) free-form text description of the play. This is typically what you see on NFL GameCenter web pages.

var down

The down on which this play begin. This may be 0 for "special" plays like timeouts or 2 point conversions.

var drive

The Drive object that contains this play. The drive is retrieved from the database if it hasn't been already.

Expand source code

@property
def drive(self):
    """
    The `nfldb.Drive` object that contains this play. The drive is
    retrieved from the database if it hasn't been already.
    """
    if self._drive is None:
        self._drive = Drive.from_id(self._db, self.gsis_id, self.drive_id)
    return self._drive

var drive_id

The numeric drive identifier for this play. It may be interpreted as a sequence number.

var game_date

Return an attribute of instance, which is of type owner.

var gsis_id

The GSIS identifier for the game that this play belongs to.

var note

A miscellaneous note field (as a string). Not sure what it's used for.

var play_id

The numeric play identifier for this play. It can typically be interpreted as a sequence number scoped to the week that this game was played, but it's unfortunately not completely consistent.

var play_players

A list of all PlayPlayers in this play. They are automatically retrieved from the database if they haven't been already.

If there are no players attached to this play, then an empty list is returned.

Expand source code

@property
def play_players(self):
    """
    A list of all `nfldb.PlayPlayer`s in this play. They are
    automatically retrieved from the database if they haven't been
    already.

    If there are no players attached to this play, then an empty
    list is returned.
    """
    if self._play_players is None:
        import nfldb.query
        q = nfldb.query.Query(self._db)
        q.play_player(gsis_id=self.gsis_id, drive_id=self.drive_id,
                      play_id=self.play_id)
        self._play_players = q.as_play_players()
        for pp in self._play_players:
            pp._play = self
    return self._play_players

var points

The number of points scored in this player statistic. This accounts for touchdowns, extra points, two point conversions, field goals and safeties.

var pos_team

The team in possession during this play, represented as a team abbreviation string. Use the Team constructor to get more information on a team.

var scoring_team

If this is a scoring play, returns the team that scored points. Otherwise, returns None.

N.B. Play.scoring_team returns a valid team if and only if Play.points is greater than 0.

Expand source code

@property
def scoring_team(self):
    """
    If this is a scoring play, returns the team that scored points.
    Otherwise, returns None.

    N.B. `nfldb.Play.scoring_team` returns a valid team if and only
    if `nfldb.Play.points` is greater than 0.
    """
    for pp in self.play_players:
        t = pp.scoring_team
        if t is not None:
            return t
    return None

var time

The time on the clock when the play started, represented with a Clock object.

var time_inserted

The date and time that this play was added to the database. This can be very useful when sorting plays by the order in which they occurred in real time. Unfortunately, such a sort requires that play data is updated relatively close to when it actually occurred.

var time_updated

The date and time that this play was last updated.

var yardline

The starting field position of this play represented with FieldPosition.

var yards_to_go

The number of yards to go to get a first down or score a touchdown at the start of the play.

Methods

def score(self, before=False)

Returns the score of the game immediately after this play as a tuple of the form (home_score, away_score).

If before is True, then the score will not include this play.

Expand source code

def score(self, before=False):
    """
    Returns the score of the game immediately after this play as a
    tuple of the form `(home_score, away_score)`.

    If `before` is `True`, then the score will *not* include this
    play.
    """
    game = Game.from_id(self._db, self.gsis_id)
    if not before:
        return game.score_at_time(self.time.add_seconds(1))

    s = game.score_at_time(self.time)
    # The heuristic in `nfldb.Game.score_in_plays` blends TDs and XPs
    # into a single play (with respect to scoring). So we have to undo
    # that if we want the score of the game after a TD but before an XP.
    if self.kicking_xpmade == 1:
        score_team = self.scoring_team
        if score_team == game.home_team:
            return (s[0] - 1, s[1])
        return (s[0], s[1] - 1)
    return s

Inherited members

• SQLPlay:
  • from_row_dict
  • from_row_tuple
  • sql_fields

class PlayPlayer (db)

A "play player" is a statistical grouping of categories for a single player inside a play. For example, passing the ball to a receiver necessarily requires two "play players": the pass (by player X) and the reception (by player Y). Statistics that aren't included, for example, are blocks and penalties. (Although penalty information can be gleaned from a play's free-form Play.description attribute.)

Each PlayPlayer object belongs to exactly one Play and exactly one Player.

Any statistical categories not relevant to this particular play and player default to 0.

Most of the statistical fields are documented on the statistical categories wiki page. Each statistical field is an instance attribute in this class.

Creates a new and empty PlayPlayer object with the given database connection.

This constructor should not be used by clients. Instead, you should get PlayPlayer objects from Query or from one of the other constructors, like nfldb.PlayPlayer.from_id or Entity.from_row_dict(). (The latter is useful only if you're writing your own SQL queries.)

Expand source code

class PlayPlayer (SQLPlayPlayer):
    """
    A "play player" is a statistical grouping of categories for a
    single player inside a play. For example, passing the ball to
    a receiver necessarily requires two "play players": the pass
    (by player X) and the reception (by player Y). Statistics that
    aren't included, for example, are blocks and penalties. (Although
    penalty information can be gleaned from a play's free-form
    `nfldb.Play.description` attribute.)

    Each `nfldb.PlayPlayer` object belongs to exactly one
    `nfldb.Play` and exactly one `nfldb.Player`.

    Any statistical categories not relevant to this particular play
    and player default to `0`.

    Most of the statistical fields are documented on the
    [statistical categories](http://goo.gl/wZstcY)
    wiki page. Each statistical field is an instance attribute in
    this class.
    """
    __slots__ = SQLPlayPlayer.sql_fields() \
        + ['_db', '_play', '_player', '_fields']

    # Document instance variables for derived SQL fields.
    # We hide them from the public interface, but make the doco
    # available to nfldb-mk-stat-table. Evil!
    __pdoc__['PlayPlayer.offense_yds'] = None
    __pdoc__['_PlayPlayer.offense_yds'] = \
        '''
        Corresponds to any yardage that is manufactured by the offense.
        Namely, the following fields:
        `nfldb.PlayPlayer.passing_yds`,
        `nfldb.PlayPlayer.rushing_yds`,
        `nfldb.PlayPlayer.receiving_yds` and
        `nfldb.PlayPlayer.fumbles_rec_yds`.

        This field is useful when searching for plays by net yardage
        regardless of how the yards were obtained.
        '''
    __pdoc__['PlayPlayer.offense_tds'] = None
    __pdoc__['_PlayPlayer.offense_tds'] = \
        '''
        Corresponds to any touchdown manufactured by the offense via
        a passing, reception, rush or fumble recovery.
        '''
    __pdoc__['PlayPlayer.defense_tds'] = None
    __pdoc__['_PlayPlayer.defense_tds'] = \
        '''
        Corresponds to any touchdown manufactured by the defense.
        e.g., a pick-6, fumble recovery TD, punt/FG block TD, etc.
        '''
    __pdoc__['PlayPlayer.points'] = \
        """
        The number of points scored in this player statistic. This
        accounts for touchdowns, extra points, two point conversions,
        field goals and safeties.
        """

    @staticmethod
    def _from_nflgame(db, p, pp):
        """
        Given `p` as a `nfldb.Play` object and `pp` as a
        `nflgame.player.PlayPlayerStats` object, `_from_nflgame`
        converts `pp` to a `nfldb.PlayPlayer` object.
        """
        team = nfldb.team.standard_team(pp.team)

        dbpp = PlayPlayer(db)
        dbpp.gsis_id = p.gsis_id
        dbpp.drive_id = p.drive_id
        dbpp.play_id = p.play_id
        dbpp.player_id = pp.playerid
        dbpp.team = team
        for k in list(_player_categories.keys()):
            if pp._stats.get(k, 0) != 0:
                setattr(dbpp, k, pp._stats[k])

        dbpp._play = p
        dbpp._player = Player._from_nflgame(db, pp)
        return dbpp

    @staticmethod
    def fill_plays(db, play_players):
        """
        Given a list of `play_players`, fill all of their `play` attributes
        using as few queries as possible. This will also fill the
        plays with drive data and each drive with game data.
        """
        _fill(db, Play, play_players, '_play')
        Play.fill_drives(db, [pp._play for pp in play_players])
        Drive.fill_games(db, [pp._play._drive for pp in play_players])

    @staticmethod
    def fill_players(db, play_players):
        """
        Given a list of `play_players`, fill all of their `player`
        attributes using as few queries as possible.
        """
        _fill(db, Player, play_players, '_player')

    def __init__(self, db):
        """
        Creates a new and empty `nfldb.PlayPlayer` object with the
        given database connection.

        This constructor should not be used by clients. Instead,
        you should get `nfldb.PlayPlayer` objects
        from `nfldb.Query` or from one of the other
        constructors, like `nfldb.PlayPlayer.from_id` or
        `nfldb.PlayPlayer.from_row_dict`. (The latter is useful only if
        you're writing your own SQL queries.)
        """
        self._db = db
        self._play = None
        self._player = None
        self._fields = None

        self.gsis_id = None
        """
        The GSIS identifier for the game that this "play player"
        belongs to.
        """
        self.drive_id = None
        """
        The numeric drive identifier for this "play player". It may be
        interpreted as a sequence number.
        """
        self.play_id = None
        """
        The numeric play identifier for this "play player". It can
        typically be interpreted as a sequence number scoped to its
        corresponding game.
        """
        self.player_id = None
        """
        The player_id linking these stats to a `nfldb.Player` object.
        Use `nfldb.PlayPlayer.player` to access player meta data.

        N.B. This is the GSIS identifier string. It always has length
        10.
        """
        self.team = None
        """
        The team that this player belonged to when he recorded the
        statistics in this play.
        """

    @property
    def fields(self):
        """The set of non-zero statistical fields set."""
        if self._fields is None:
            self._fields = set()
            for k in list(_player_categories.keys()):
                if getattr(self, k, 0) != 0:
                    self._fields.add(k)
        return self._fields

    @property
    def play(self):
        """
        The `nfldb.Play` object that this "play player" belongs
        to. The play is retrieved from the database if necessary.
        """
        if self._play is None:
            self._play = Play.from_id(self._db, self.gsis_id, self.drive_id,
                                      self.play_id)
        return self._play

    @property
    def player(self):
        """
        The `nfldb.Player` object that this "play player"
        corresponds to. The player is retrieved from the database if
        necessary.
        """
        if self._player is None:
            self._player = Player.from_id(self._db, self.player_id)
        return self._player

    @property
    def scoring_team(self):
        """
        If this is a scoring statistic, returns the team that scored.
        Otherwise, returns None.

        N.B. `nfldb.PlayPlayer.scoring_team` returns a valid team if
        and only if `nfldb.PlayPlayer.points` is greater than 0.
        """
        if self.points > 0:
            return self.team
        return None

    @property
    def guess_position(self):
        """
        Guesses the position of this player based on the statistical
        categories present.

        Note that this only distinguishes the offensive positions of
        QB, RB, WR, P and K. If defensive stats are detected, then
        the position returned defaults to LB.
        """
        stat_to_pos = [
            ('passing_att', 'QB'), ('rushing_att', 'RB'),
            ('receiving_tar', 'WR'), ('punting_tot', 'P'),
            ('kicking_tot', 'K'), ('kicking_fga', 'K'), ('kicking_xpa', 'K'),
        ]
        for c in stat_categories:
            if c.startswith('defense_'):
                stat_to_pos.append((c, 'LB'))
        for stat, pos in stat_to_pos:
            if getattr(self, stat) != 0:
                return Enums.player_pos[pos]
        return Enums.player_pos.UNK

    def _save(self, cursor):
        if self._player is not None:
            self._player._save(cursor)
        super(PlayPlayer, self)._save(cursor)

    def _add(self, b):
        """
        Given two `nfldb.PlayPlayer` objects, `_add` accumulates `b`
        into `self`. Namely, no new `nfldb.PlayPlayer` objects are
        created.

        Both `self` and `b` must refer to the same player, or else an
        assertion error is raised.

        The `nfldb.aggregate` function should be used to sum collections
        of `nfldb.PlayPlayer` objects (or objects that can provide
        `nfldb.PlayPlayer` objects).
        """
        a = self
        assert a.player_id == b.player_id
        a.gsis_id = a.gsis_id if a.gsis_id == b.gsis_id else None
        a.drive_id = a.drive_id if a.drive_id == b.drive_id else None
        a.play_id = a.play_id if a.play_id == b.play_id else None
        a.team = a.team if a.team == b.team else None

        for cat in _player_categories:
            setattr(a, cat, getattr(a, cat) + getattr(b, cat))

        # Try to copy player meta data too.
        if a._player is None and b._player is not None:
            a._player = b._player

        # A play attached to aggregate statistics is always wrong.
        a._play = None

    def _copy(self):
        """Returns a copy of `self`."""
        pp = PlayPlayer(self._db)
        pp.gsis_id = self.gsis_id
        pp.drive_id = self.drive_id
        pp.play_id = self.play_id
        pp.player_id = self.player_id
        pp.team = self.team

        ga, sa = getattr, setattr
        for k in _player_categories:
            v = getattr(self, k, 0)
            if v != 0:
                sa(pp, k, v)
        pp._player = self._player
        pp._play = self._play
        return pp

    def __add__(self, b):
        pp = self._copy()
        pp.add(b)
        return pp

    def __str__(self):
        d = {}
        for cat in _player_categories:
            v = getattr(self, cat, 0)
            if v != 0:
                d[cat] = v
        return repr(d)

    def __getattr__(self, k):
        if k in PlayPlayer.__slots__:
            return 0
        raise AttributeError(k)

Ancestors

• SQLPlayPlayer
• Entity

Static methods

def fill_players(db, play_players)

Given a list of play_players, fill all of their player attributes using as few queries as possible.

Expand source code

@staticmethod
def fill_players(db, play_players):
    """
    Given a list of `play_players`, fill all of their `player`
    attributes using as few queries as possible.
    """
    _fill(db, Player, play_players, '_player')

def fill_plays(db, play_players)

Given a list of play_players, fill all of their play attributes using as few queries as possible. This will also fill the plays with drive data and each drive with game data.

Expand source code

@staticmethod
def fill_plays(db, play_players):
    """
    Given a list of `play_players`, fill all of their `play` attributes
    using as few queries as possible. This will also fill the
    plays with drive data and each drive with game data.
    """
    _fill(db, Play, play_players, '_play')
    Play.fill_drives(db, [pp._play for pp in play_players])
    Drive.fill_games(db, [pp._play._drive for pp in play_players])

Instance variables

var drive_id

The numeric drive identifier for this "play player". It may be interpreted as a sequence number.

var fields

The set of non-zero statistical fields set.

Expand source code

@property
def fields(self):
    """The set of non-zero statistical fields set."""
    if self._fields is None:
        self._fields = set()
        for k in list(_player_categories.keys()):
            if getattr(self, k, 0) != 0:
                self._fields.add(k)
    return self._fields

var gsis_id

The GSIS identifier for the game that this "play player" belongs to.

var guess_position

Guesses the position of this player based on the statistical categories present.

Note that this only distinguishes the offensive positions of QB, RB, WR, P and K. If defensive stats are detected, then the position returned defaults to LB.

Expand source code

@property
def guess_position(self):
    """
    Guesses the position of this player based on the statistical
    categories present.

    Note that this only distinguishes the offensive positions of
    QB, RB, WR, P and K. If defensive stats are detected, then
    the position returned defaults to LB.
    """
    stat_to_pos = [
        ('passing_att', 'QB'), ('rushing_att', 'RB'),
        ('receiving_tar', 'WR'), ('punting_tot', 'P'),
        ('kicking_tot', 'K'), ('kicking_fga', 'K'), ('kicking_xpa', 'K'),
    ]
    for c in stat_categories:
        if c.startswith('defense_'):
            stat_to_pos.append((c, 'LB'))
    for stat, pos in stat_to_pos:
        if getattr(self, stat) != 0:
            return Enums.player_pos[pos]
    return Enums.player_pos.UNK

var play

The Play object that this "play player" belongs to. The play is retrieved from the database if necessary.

Expand source code

@property
def play(self):
    """
    The `nfldb.Play` object that this "play player" belongs
    to. The play is retrieved from the database if necessary.
    """
    if self._play is None:
        self._play = Play.from_id(self._db, self.gsis_id, self.drive_id,
                                  self.play_id)
    return self._play

var play_id

The numeric play identifier for this "play player". It can typically be interpreted as a sequence number scoped to its corresponding game.

var player

The Player object that this "play player" corresponds to. The player is retrieved from the database if necessary.

Expand source code

@property
def player(self):
    """
    The `nfldb.Player` object that this "play player"
    corresponds to. The player is retrieved from the database if
    necessary.
    """
    if self._player is None:
        self._player = Player.from_id(self._db, self.player_id)
    return self._player

var player_id

The player_id linking these stats to a Player object. Use PlayPlayer.player to access player meta data.

N.B. This is the GSIS identifier string. It always has length 10.

var points

The number of points scored in this player statistic. This accounts for touchdowns, extra points, two point conversions, field goals and safeties.

var scoring_team

If this is a scoring statistic, returns the team that scored. Otherwise, returns None.

N.B. PlayPlayer.scoring_team returns a valid team if and only if PlayPlayer.points is greater than 0.

Expand source code

@property
def scoring_team(self):
    """
    If this is a scoring statistic, returns the team that scored.
    Otherwise, returns None.

    N.B. `nfldb.PlayPlayer.scoring_team` returns a valid team if
    and only if `nfldb.PlayPlayer.points` is greater than 0.
    """
    if self.points > 0:
        return self.team
    return None

var team

The team that this player belonged to when he recorded the statistics in this play.

Inherited members

• SQLPlayPlayer:
  • from_row_dict
  • from_row_tuple
  • sql_fields

class Player (db)

A representation of an NFL player. Note that the representation is inherently ephemeral; it always corresponds to the most recent knowledge about a player.

Most of the fields in this object can have a None value. This is because the source JSON data only guarantees that a GSIS identifier and abbreviated name will be available. The rest of the player meta data is scraped from NFL.com's team roster pages (which invites infrequent uncertainty).

Creates a new and empty Player object with the given database connection.

This constructor should not be used by clients. Instead, you should get Player objects from Query or from one of the other constructors, like Player.from_id() or Entity.from_row_dict(). (The latter is useful only if you're writing your own SQL queries.)

Expand source code

class Player (SQLPlayer):
    """
    A representation of an NFL player. Note that the representation
    is inherently ephemeral; it always corresponds to the most recent
    knowledge about a player.

    Most of the fields in this object can have a `None` value. This is
    because the source JSON data only guarantees that a GSIS identifier
    and abbreviated name will be available. The rest of the player meta
    data is scraped from NFL.com's team roster pages (which invites
    infrequent uncertainty).
    """
    __slots__ = SQLPlayer.sql_fields() + ['_db']

    _existing = None
    """
    A cache of existing player ids in the database.
    This is only used when saving data to detect if a player
    needs to be added.
    """

    @staticmethod
    def _from_nflgame(db, p):
        """
        Given `p` as a `nflgame.player.PlayPlayerStats` object,
        `_from_nflgame` converts `p` to a `nfldb.Player` object.
        """
        dbp = Player(db)
        dbp.player_id = p.playerid
        dbp.gsis_name = p.name

        if p.player is not None:
            meta = ['full_name', 'first_name', 'last_name', 'team', 'position',
                    'profile_id', 'profile_url', 'uniform_number', 'birthdate',
                    'college', 'height', 'weight', 'years_pro', 'status']
            for k in meta:
                v = getattr(p.player, k, '')
                if not v:
                    # Normalize all empty values to `None`
                    v = None
                setattr(dbp, k, v)

            # Convert position and status values to an enumeration.
            dbp.position = getattr(Enums.player_pos,
                                   dbp.position or '',
                                   Enums.player_pos.UNK)

            trans = Enums._nflgame_player_status
            dbp.status = trans.get(dbp.status or '',
                                   Enums.player_status.Unknown)

        if getattr(dbp, 'position', None) is None:
            dbp.position = Enums.player_pos.UNK
        if getattr(dbp, 'status', None) is None:
            dbp.status = Enums.player_status.Unknown

        dbp.team = nfldb.team.standard_team(getattr(dbp, 'team', ''))
        return dbp

    @staticmethod
    def _from_nflgame_player(db, p):
        """
        Given `p` as a `nflgame.player.Player` object,
        `_from_nflgame_player` converts `p` to a `nfldb.Player` object.
        """
        # This hack translates `nflgame.player.Player` to something like
        # a `nflgame.player.PlayPlayerStats` object that can be converted
        # with `nfldb.Player._from_nflgame`.
        class _Player (object):
            def __init__(self):
                self.playerid = p.player_id
                self.name = p.gsis_name
                self.player = p
        return Player._from_nflgame(db, _Player())

    @staticmethod
    def from_id(db, player_id):
        """
        Given a player GSIS identifier (e.g., `00-0019596`) as a string,
        returns a `nfldb.Player` object corresponding to `player_id`.
        This function will always execute a single SQL query.

        If no corresponding player is found, `None` is returned.
        """
        import nfldb.query
        q = nfldb.query.Query(db)
        players = q.player(player_id=player_id).limit(1).as_players()
        if len(players) == 0:
            return None
        return players[0]

    def __init__(self, db):
        """
        Creates a new and empty `nfldb.Player` object with the given
        database connection.

        This constructor should not be used by clients. Instead, you
        should get `nfldb.Player` objects from `nfldb.Query` or from
        one of the other constructors, like `nfldb.Player.from_id` or
        `nfldb.Player.from_row_dict`. (The latter is useful only if
        you're writing your own SQL queries.)
        """
        self._db = db

        self.player_id = None
        """
        The player_id linking this object `nfldb.PlayPlayer` object.

        N.B. This is the GSIS identifier string. It always has length
        10.
        """
        self.gsis_name = None
        """
        The name of a player from the source GameCenter data. This
        field is guaranteed to contain a name.
        """
        self.full_name = None
        """The full name of a player."""
        self.first_name = None
        """The first name of a player."""
        self.last_name = None
        """The last name of a player."""
        self.team = None
        """
        The team that the player is currently active on. If the player
        is no longer playing or is a free agent, this value may
        correspond to the `UNK` (unknown) team.
        """
        self.position = None
        """
        The current position of a player if it's available. This may
        be **not** be `None`. If the position is not known, then the
        `UNK` enum is used from `nfldb.Enums.player_pos`.
        """
        self.profile_id = None
        """
        The profile identifier used on a player's canonical NFL.com
        profile page. This is used as a foreign key to connect varying
        sources of information.
        """
        self.profile_url = None
        """The NFL.com profile URL for this player."""
        self.uniform_number = None
        """A player's uniform number as an integer."""
        self.birthdate = None
        """A player's birth date as a free-form string."""
        self.college = None
        """A player's college as a free-form string."""
        self.height = None
        """A player's height as a free-form string."""
        self.weight = None
        """A player's weight as a free-form string."""
        self.years_pro = None
        """The number of years a player has played as an integer."""
        self.status = None
        """The current status of this player as a free-form string."""

    def _save(self, cursor):
        if Player._existing is None:
            Player._existing = set()
            cursor.execute('SELECT player_id FROM player')
            for row in cursor.fetchall():
                Player._existing.add(row['player_id'])
        if self.player_id not in Player._existing:
            super(Player, self)._save(cursor)
            Player._existing.add(self.player_id)

    def __str__(self):
        name = self.full_name if self.full_name else self.gsis_name
        if not name:
            name = self.player_id  # Yikes.
        return '%s (%s, %s)' % (name, self.team, self.position)

    def __lt__(self, other):
        if self.__class__ is not other.__class__:
            return NotImplemented
        if self.full_name and other.full_name:
            return self.full_name < other.full_name
        return self.gsis_name < other.gsis_name

    def __eq__(self, other):
        if self.__class__ is not other.__class__:
            return NotImplemented
        return self.player_id == other.player_id

Ancestors

• SQLPlayer
• Entity

Static methods

def from_id(db, player_id)

Given a player GSIS identifier (e.g., 00-0019596) as a string, returns a Player object corresponding to player_id. This function will always execute a single SQL query.

If no corresponding player is found, None is returned.

Expand source code

@staticmethod
def from_id(db, player_id):
    """
    Given a player GSIS identifier (e.g., `00-0019596`) as a string,
    returns a `nfldb.Player` object corresponding to `player_id`.
    This function will always execute a single SQL query.

    If no corresponding player is found, `None` is returned.
    """
    import nfldb.query
    q = nfldb.query.Query(db)
    players = q.player(player_id=player_id).limit(1).as_players()
    if len(players) == 0:
        return None
    return players[0]

Instance variables

var birthdate

A player's birth date as a free-form string.

var college

A player's college as a free-form string.

var first_name

The first name of a player.

var full_name

The full name of a player.

var gsis_name

The name of a player from the source GameCenter data. This field is guaranteed to contain a name.

var height

A player's height as a free-form string.

var last_name

The last name of a player.

var player_id

The player_id linking this object PlayPlayer object.

N.B. This is the GSIS identifier string. It always has length 10.

var position

The current position of a player if it's available. This may be not be None. If the position is not known, then the UNK enum is used from Enums.player_pos.

var profile_id

The profile identifier used on a player's canonical NFL.com profile page. This is used as a foreign key to connect varying sources of information.

var profile_url

The NFL.com profile URL for this player.

var status

The current status of this player as a free-form string.

var team

The team that the player is currently active on. If the player is no longer playing or is a free agent, this value may correspond to the UNK (unknown) team.

var uniform_number

A player's uniform number as an integer.

var weight

A player's weight as a free-form string.

var years_pro

The number of years a player has played as an integer.

Inherited members

• SQLPlayer:
  • from_row_dict
  • from_row_tuple
  • sql_fields

class PossessionTime (seconds)

Represents the possession time of a drive in seconds.

This class defines a total ordering on possession times. Namely, p1 < p2 if and only if p2 corresponds to a longer time of possession than p1.

Returns a PossessionTime object given the number of seconds of the possession.

Expand source code

class PossessionTime (object):
    """
    Represents the possession time of a drive in seconds.

    This class defines a total ordering on possession times. Namely, p1
    < p2 if and only if p2 corresponds to a longer time of possession
    than p1.
    """
    __slots__ = ['_seconds']

    @staticmethod
    def from_str(clock_str):
        """
        Introduces a `nfldb.PossessionTime` object from a string
        formatted as clock time. For example, `2:00` corresponds to
        `120` seconds and `14:39` corresponds to `879` seconds.
        """
        minutes, seconds = list(map(int, clock_str.split(':', 1)))
        return PossessionTime((minutes * 60) + seconds)

    @staticmethod
    def _pg_cast(sqlv, cursor):
        return PossessionTime(int(sqlv[1:-1]))

    def __init__(self, seconds):
        """
        Returns a `nfldb.PossessionTime` object given the number of
        seconds of the possession.
        """
        assert isinstance(seconds, int)
        self._seconds = seconds

    @property
    def valid(self):
        """
        Returns `True` if and only if this possession time has a valid
        representation.

        Invalid possession times cannot be compared with other
        possession times.
        """
        return self._seconds is not None

    @property
    def total_seconds(self):
        """
        The total seconds elapsed for this possession.
        `0` is returned if this is not a valid possession time.
        """
        return self._seconds if self.valid else 0

    @property
    def minutes(self):
        """
        The number of whole minutes for a possession.
        e.g., `0:59` would be `0` minutes and `4:01` would be `4`
        minutes.
        `0` is returned if this is not a valid possession time.
        """
        return (self._seconds // 60) if self.valid else 0

    @property
    def seconds(self):
        """
        The seconds portion of the possession time.
        e.g., `0:59` would be `59` seconds and `4:01` would be `1`
        second.
        `0` is returned if this is not a valid possession time.
        """
        return (self._seconds % 60) if self.valid else 0

    def __str__(self):
        if not self.valid:
            return 'N/A'
        else:
            return '%02d:%02d' % (self.minutes, self.seconds)

    def __lt__(self, other):
        if self.__class__ is not other.__class__:
            return NotImplemented
        assert self.valid and other.valid
        return self._seconds < other._seconds

    def __eq__(self, other):
        if self.__class__ is not other.__class__:
            return NotImplemented
        return self._seconds == other._seconds

    def __conform__(self, proto):
        if proto is ISQLQuote:
            if not self.valid:
                return AsIs("NULL")
            else:
                return AsIs("ROW(%d)::pos_period" % self._seconds)
        return None

Static methods

def from_str(clock_str)

Introduces a PossessionTime object from a string formatted as clock time. For example, 2:00 corresponds to 120 seconds and 14:39 corresponds to 879 seconds.

Expand source code

@staticmethod
def from_str(clock_str):
    """
    Introduces a `nfldb.PossessionTime` object from a string
    formatted as clock time. For example, `2:00` corresponds to
    `120` seconds and `14:39` corresponds to `879` seconds.
    """
    minutes, seconds = list(map(int, clock_str.split(':', 1)))
    return PossessionTime((minutes * 60) + seconds)

Instance variables

var minutes

The number of whole minutes for a possession. e.g., 0:59 would be 0 minutes and 4:01 would be 4 minutes. 0 is returned if this is not a valid possession time.

Expand source code

@property
def minutes(self):
    """
    The number of whole minutes for a possession.
    e.g., `0:59` would be `0` minutes and `4:01` would be `4`
    minutes.
    `0` is returned if this is not a valid possession time.
    """
    return (self._seconds // 60) if self.valid else 0

var seconds

The seconds portion of the possession time. e.g., 0:59 would be 59 seconds and 4:01 would be 1 second. 0 is returned if this is not a valid possession time.

Expand source code

@property
def seconds(self):
    """
    The seconds portion of the possession time.
    e.g., `0:59` would be `59` seconds and `4:01` would be `1`
    second.
    `0` is returned if this is not a valid possession time.
    """
    return (self._seconds % 60) if self.valid else 0

var total_seconds

The total seconds elapsed for this possession. 0 is returned if this is not a valid possession time.

Expand source code

@property
def total_seconds(self):
    """
    The total seconds elapsed for this possession.
    `0` is returned if this is not a valid possession time.
    """
    return self._seconds if self.valid else 0

var valid

Returns True if and only if this possession time has a valid representation.

Invalid possession times cannot be compared with other possession times.

Expand source code

@property
def valid(self):
    """
    Returns `True` if and only if this possession time has a valid
    representation.

    Invalid possession times cannot be compared with other
    possession times.
    """
    return self._seconds is not None

class Query (db, orelse=False)

A query represents a set of criteria to search nfldb's PostgreSQL database. Its primary feature is to provide a high-level API for searching NFL game, drive, play and player data very quickly.

The basic workflow is to specify all of the search criteria that you want, and then use one of the as_* methods to actually perform the search and return results from the database.

For example, to get all Patriots games as Game objects from the 2012 regular season, we could do:

#!python
q = Query(db).game(season_year=2012, season_type='Regular', team='NE')
for game in q.as_games():
    print game

Other comparison operators like < or >= can also be used. To use them, append a suffix like __lt to the end of a field name. So to get all games with a home score greater than or equal to 50:

#!python
q = Query(db).game(home_score__ge=50)
for game in q.as_games():
    print game

Other suffixes are available: __lt for <, __le for <=, __gt for >, __ge for >=, __ne for != and __eq for ==. Although, the __eq suffix is used by default and is therefore never necessary to use.

More criteria can be specified by chaining search criteria. For example, to get only plays as Play objects where Tom Brady threw a touchdown pass:

#!python
q = Query(db).game(season_year=2012, season_type='Regular')
q.player(full_name="Tom Brady").play(passing_tds=1)
for play in q.as_plays():
    print play

By default, all critera specified are combined conjunctively (i.e., all criteria must be met for each result returned). However, sometimes you may want to specify disjunctive criteria (i.e., any of the criteria can be met for a result to be returned). To do this for a single field, simply use a list. For example, to get all Patriot games from the 2009 to 2013 seasons:

#!python
q = Query(db).game(season_type='Regular', team='NE')
q.game(season_year=[2009, 2010, 2011, 2012, 2013])
for game in q.as_games():
    print game

Disjunctions can also be applied to multiple fields by creating a Query object with QueryOR(). For example, to find all games where either team had more than 50 points:

#!python
q = QueryOR(db).game(home_score__ge=50, away_score__ge=50)
for game in q.as_games():
    print game

Finally, multiple queries can be combined with Query.andalso(). For example, to restrict the last search to games in the 2012 regular season:

#!python
big_score = QueryOR(db).game(home_score__ge=50, away_score__ge=50)

q = Query(db).game(season_year=2012, season_type='Regular')
q.andalso(big_score)
for game in q.as_games():
    print game

This is only the beginning of what can be done. More examples that run the gamut can be found on nfldb's wiki.

Introduces a new Query object. Criteria can be added with any combination of the Query.game(), Query.drive(), Query.play(), Query.player() and Query.aggregate() methods. Results can then be retrieved with any of the as_* methods: Query.as_games(), Query.as_drives(), Query.as_plays(), Query.as_play_players(), Query.as_players() and Query.as_aggregate().

Note that if aggregate criteria are specified with Query.aggregate(), then the only way to retrieve results is with the Query.as_aggregate() method. Invoking any of the other as_* methods will raise an assertion error.

Expand source code

class Query (Condition):
    """
    A query represents a set of criteria to search nfldb's PostgreSQL
    database. Its primary feature is to provide a high-level API for
    searching NFL game, drive, play and player data very quickly.

    The basic workflow is to specify all of the search criteria that
    you want, and then use one of the `as_*` methods to actually
    perform the search and return results from the database.

    For example, to get all Patriots games as `nfldb.Game` objects from
    the 2012 regular season, we could do:

        #!python
        q = Query(db).game(season_year=2012, season_type='Regular', team='NE')
        for game in q.as_games():
            print game

    Other comparison operators like `<` or `>=` can also be used. To use
    them, append a suffix like `__lt` to the end of a field name. So to get
    all games with a home score greater than or equal to 50:

        #!python
        q = Query(db).game(home_score__ge=50)
        for game in q.as_games():
            print game

    Other suffixes are available: `__lt` for `<`, `__le` for `<=`,
    `__gt` for `>`, `__ge` for `>=`, `__ne` for `!=` and `__eq` for
    `==`. Although, the `__eq` suffix is used by default and is
    therefore never necessary to use.

    More criteria can be specified by chaining search criteria. For
    example, to get only plays as `nfldb.Play` objects where Tom Brady
    threw a touchdown pass:

        #!python
        q = Query(db).game(season_year=2012, season_type='Regular')
        q.player(full_name="Tom Brady").play(passing_tds=1)
        for play in q.as_plays():
            print play

    By default, all critera specified are combined conjunctively (i.e.,
    all criteria must be met for each result returned). However,
    sometimes you may want to specify disjunctive criteria (i.e., any
    of the criteria can be met for a result to be returned). To do this
    for a single field, simply use a list. For example, to get all
    Patriot games from the 2009 to 2013 seasons:

        #!python
        q = Query(db).game(season_type='Regular', team='NE')
        q.game(season_year=[2009, 2010, 2011, 2012, 2013])
        for game in q.as_games():
            print game

    Disjunctions can also be applied to multiple fields by creating a
    `nfldb.Query` object with `nfldb.QueryOR`. For example, to find
    all games where either team had more than 50 points:

        #!python
        q = QueryOR(db).game(home_score__ge=50, away_score__ge=50)
        for game in q.as_games():
            print game

    Finally, multiple queries can be combined with `nfldb.Query.andalso`.
    For example, to restrict the last search to games in the 2012 regular
    season:

        #!python
        big_score = QueryOR(db).game(home_score__ge=50, away_score__ge=50)

        q = Query(db).game(season_year=2012, season_type='Regular')
        q.andalso(big_score)
        for game in q.as_games():
            print game

    This is only the beginning of what can be done. More examples that run
    the gamut can be found on
    [nfldb's wiki](https://github.com/derek-adair/nfldb/wiki).
    """

    def __init__(self, db, orelse=False):
        """
        Introduces a new `nfldb.Query` object. Criteria can be
        added with any combination of the `nfldb.Query.game`,
        `nfldb.Query.drive`, `nfldb.Query.play`, `nfldb.Query.player`
        and `nfldb.Query.aggregate` methods. Results can
        then be retrieved with any of the `as_*` methods:
        `nfldb.Query.as_games`, `nfldb.Query.as_drives`,
        `nfldb.Query.as_plays`, `nfldb.Query.as_play_players`,
        `nfldb.Query.as_players` and `nfldb.Query.as_aggregate`.

        Note that if aggregate criteria are specified with
        `nfldb.Query.aggregate`, then the **only** way to retrieve
        results is with the `nfldb.Query.as_aggregate` method. Invoking
        any of the other `as_*` methods will raise an assertion error.
        """

        self._db = db
        """A psycopg2 database connection object."""

        self._sort_exprs = None
        """Expressions used to sort the results."""

        self._limit = None
        """The number of results to limit the search to."""

        self._andalso = []
        """A list of conjunctive conditions."""

        self._orelse = []
        """
        A list of disjunctive conditions applied to
        `Query._andalso`.
        """

        self._default_cond = self._orelse if orelse else self._andalso
        """
        Whether to use conjunctive or disjunctive conditions by
        default.
        """

        # The aggregate counter-parts of the above.
        self._agg_andalso, self._agg_orelse = [], []
        if orelse:
            self._agg_default_cond = self._agg_orelse
        else:
            self._agg_default_cond = self._agg_andalso

    def sort(self, exprs):
        """
        Specify sorting criteria for the result set returned by
        using sort expressions. A sort expression is a tuple with
        two elements: a field to sort by and the order to use. The
        field should correspond to an attribute of the objects you're
        returning and the order should be `asc` for ascending (smallest
        to biggest) or `desc` for descending (biggest to smallest).

        For example, `('passing_yds', 'desc')` would sort plays by the
        number of passing yards in the play, with the biggest coming
        first.

        Remember that a sort field must be an attribute of the
        results being returned. For example, you can't sort plays by
        `home_score`, which is an attribute of a `nfldb.Game` object.
        If you require this behavior, you will need to do it in Python
        with its `sorted` built in function. (Or alternatively, use
        two separate queries if the result set is large.)

        You may provide multiple sort expressions. For example,
        `[('gsis_id', 'asc'), ('time', 'asc'), ('play_id', 'asc')]`
        would sort plays in the order in which they occurred within
        each game.

        `exprs` may also just be a string specifying a single
        field which defaults to a descending order. For example,
        `sort('passing_yds')` sorts plays by passing yards in
        descending order.

        If `exprs` is set to the empty list, then sorting will be
        disabled for this query.

        Note that sorting criteria can be combined with
        `nfldb.Query.limit` to limit results which can dramatically
        speed up larger searches. For example, to fetch the top 10
        passing plays in the 2012 season:

            #!python
            q = Query(db).game(season_year=2012, season_type='Regular')
            q.sort('passing_yds').limit(10)
            for p in q.as_plays():
                print p

        A more naive approach might be to fetch all plays and sort them
        with Python:

            #!python
            q = Query(db).game(season_year=2012, season_type='Regular')
            plays = q.as_plays()

            plays = sorted(plays, key=lambda p: p.passing_yds, reverse=True)
            for p in plays[:10]:
                print p

        But this is over **43 times slower** on my machine than using
        `nfldb.Query.sort` and `nfldb.Query.limit`. (The performance
        difference is due to making PostgreSQL perform the search and
        restricting the number of results returned to process.)
        """
        self._sort_exprs = exprs
        return self

    def limit(self, count):
        """
        Limits the number of results to the integer `count`. If `count` is
        `0` (the default), then no limiting is done.

        See the documentation for `nfldb.Query.sort` for an example on how
        to combine it with `nfldb.Query.limit` to get results quickly.
        """
        self._limit = count
        return self

    def _sorter(self, default_entity):
        return Sorter(default_entity, self._sort_exprs, self._limit)

    def _assert_no_aggregate(self):
        assert len(self._agg_andalso) == 0 and len(self._agg_orelse) == 0, \
            'aggregate criteria are only compatible with as_aggregate'

    def andalso(self, *conds):
        """
        Adds the list of `nfldb.Query` objects in `conds` to this
        query's list of conjunctive conditions.
        """
        self._andalso += conds
        return self

    def orelse(self, *conds):
        """
        Adds the list of `nfldb.Query` objects in `conds` to this
        query's list of disjunctive conditions.
        """
        self._orelse += conds
        return self

    def game(self, **kw):
        """
        Specify search criteria for an NFL game. The possible fields
        correspond to columns in the `game` table (or derived columns).
        They are documented as instance variables in the `nfldb.Game`
        class. Additionally, there are some special fields that provide
        convenient access to common conditions:

          * **team** - Find games that the team given played in, regardless
                       of whether it is the home or away team.

        Please see the documentation for `nfldb.Query` for examples on
        how to specify search criteria.

        Please
        [open an issue](https://github.com/derek-adair/nfldb/issues/new)
        if you can think of other special fields to add.
        """
        if 'team' in kw:
            team = kw.pop('team')
            ors = {'home_team': team, 'away_team': team}
            self.andalso(Query(self._db, orelse=True).game(**ors))
        _append_conds(self._default_cond, types.Game, kw)
        return self

    def drive(self, **kw):
        """
        Specify search criteria for a drive. The possible fields
        correspond to columns in the `drive` table (or derived
        columns). They are documented as instance variables in the
        `nfldb.Drive` class.

        Please see the documentation for `nfldb.Query` for examples on
        how to specify search criteria.
        """
        _append_conds(self._default_cond, types.Drive, kw)
        return self

    def play(self, **kw):
        """
        Specify search criteria for a play. The possible fields
        correspond to columns in the `play` or `play_player` tables (or
        derived columns). They are documented as instance variables in
        the `nfldb.Play` and `nfldb.PlayPlayer` classes. Additionally,
        the fields listed on the
        [statistical categories](http://goo.gl/1qYG3C)
        wiki page may be used. That includes **both** `play` and
        `player` statistical categories.

        Please see the documentation for `nfldb.Query` for examples on
        how to specify search criteria.
        """
        _append_conds(self._default_cond, types.Play, kw)
        return self

    def play_player(self, **kw):
        """
        Specify search criteria for individual play player statistics.
        The allowed fields are the columns in the `play_player`
        table.  They are documented as instance variables in the
        `nfldb.PlayPlayer` class. Additionally, the fields listed on
        the [statistical categories](http://goo.gl/1qYG3C) wiki page
        may be used. (Only the `player` statistical categories.)

        This method differs from `nfldb.Query.play` in that it can be
        used to select for individual player statistics in a play. In
        particular, there are *zero or more* player statistics for
        every play.
        """
        # Technically, it isn't necessary to handle derived fields manually
        # since their SQL can be generated automatically, but it can be
        # much faster to express them in terms of boolean logic with other
        # fields rather than generate them.
        for field, value in list(kw.items()):
            nosuff = _no_comp_suffix(field)
            suff = _comp_suffix(field)

            def replace_or(*fields):
                q = Query(self._db, orelse=True)
                ors = dict([('%s__%s' % (f, suff), value) for f in fields])
                self.andalso(q.play_player(**ors))

            if nosuff in types.PlayPlayer._derived_combined:
                replace_or(*types.PlayPlayer._derived_combined[nosuff])
                kw.pop(field)

        # Now add the rest of the query.
        _append_conds(self._default_cond, types.PlayPlayer, kw)
        return self

    def player(self, **kw):
        """
        Specify search criteria for a player. The possible fields
        correspond to columns in the `player` table (or derived
        columns). They are documented as instance variables in the
        `nfldb.Player` class.

        Please see the documentation for `nfldb.Query` for examples on
        how to specify search criteria.
        """
        _append_conds(self._default_cond, types.Player, kw)
        return self

    def aggregate(self, **kw):
        """
        This is just like `nfldb.Query.play_player`, except the search
        parameters are applied to aggregate statistics.

        For example, to retrieve all quarterbacks who passed for at
        least 4000 yards in the 2012 season:

            #!python
            q = Query(db).game(season_year=2012, season_type='Regular')
            q.aggregate(passing_yds__ge=4000)
            for pp in q.as_aggregate():
                print pp.player, pp.passing_yds

        Aggregate results can also be sorted:

            #!python
            for pp in q.sort('passing_yds').as_aggregate():
                print pp.player, pp.passing_yds

        Note that this method can **only** be used with
        `nfldb.Query.as_aggregate`. Use with any of the other
        `as_*` methods will result in an assertion error. Note
        though that regular criteria can still be specified with
        `nfldb.Query.game`, `nfldb.Query.play`, etc. (Regular criteria
        restrict *what to aggregate* while aggregate criteria restrict
        *aggregated results*.)
        """
        _append_conds(self._agg_default_cond, types.PlayPlayer, kw)
        return self

    def _make_join_query(self, cursor, entity, only_prim=False, sorter=None,
                         ent_fillers=None):
        if sorter is None:
            sorter = self._sorter(entity)

        entities = self._entities()
        entities.update(sorter.entities)
        for ent in ent_fillers or []:
            entities.add(ent)
        entities.discard(entity)

        # If we're joining the `player` table with any other table except
        # `play_player`, then we MUST add `play_player` as a joining table.
        # It is the only way to bridge players and games/drives/plays.
        #
        # TODO: This could probably be automatically deduced in general case,
        # but we only have one case so just check for it manually.
        if (entity is not types.PlayPlayer and types.Player in entities) \
                or (entity is types.Player and len(entities) > 0):
            entities.add(types.PlayPlayer)

        if only_prim:
            columns = entity._sql_tables['primary']
            fields = entity._sql_select_fields(fields=columns)
        else:
            fields = []
            for ent in ent_fillers or []:
                fields += ent._sql_select_fields(fields=ent.sql_fields())
            fields += entity._sql_select_fields(fields=entity.sql_fields())
        args = {
            'columns': ', '.join(fields),
            'from': entity._sql_from(),
            'joins': entity._sql_join_all(entities),
            'where': sql.ands(self._sql_where(cursor)),
            'groupby': '',
            'sortby': sorter.sql(),
        }

        # We need a GROUP BY if we're joining with a table that has more
        # specific information. e.g., selecting from game with criteria
        # for plays.
        if any(entity._sql_relation_distance(to) > 0 for to in entities):
            fields = []
            for table, _ in entity._sql_tables['tables']:
                fields += entity._sql_primary_key(table)
            args['groupby'] = 'GROUP BY ' + ', '.join(fields)

        q = '''
            SELECT {columns} {from} {joins}
            WHERE {where}
            {groupby}
            {sortby}
        '''.format(**args)
        return q

    def as_games(self):
        """
        Executes the query and returns the results as a list of
        `nfldb.Game` objects.
        """
        self._assert_no_aggregate()

        results = []
        with Tx(self._db, factory=tuple_cursor) as cursor:
            q = self._make_join_query(cursor, types.Game)
            cursor.execute(q)
            for row in cursor.fetchall():
                results.append(types.Game.from_row_tuple(self._db, row))
        return results

    def as_drives(self):
        """
        Executes the query and returns the results as a list of
        `nfldb.Drive` objects.
        """
        self._assert_no_aggregate()

        results = []
        with Tx(self._db, factory=tuple_cursor) as cursor:
            q = self._make_join_query(cursor, types.Drive)
            cursor.execute(q)
            for row in cursor.fetchall():
                results.append(types.Drive.from_row_tuple(self._db, row))
        return results

    def as_plays(self, fill=True):
        """
        Executes the query and returns the results as a dictionary
        of `nlfdb.Play` objects that don't have the `play_player`
        attribute filled. The keys of the dictionary are play id
        tuples with the spec `(gsis_id, drive_id, play_id)`.

        The primary key membership SQL expression is also returned.
        """
        def make_pid(play):
            return (play.gsis_id, play.drive_id, play.play_id)

        self._assert_no_aggregate()

        # This is pretty terrifying.
        # Apparently PostgreSQL can change the order of rows returned
        # depending on the columns selected. So e.g., if you sort by `down`
        # and limit to 20 results, you might get a different 20 plays if
        # you change which columns you're selecting.
        # This is pertinent here because if we're filling plays with player
        # statistics, then we are assuming that this order never changes.
        # To make the ordering consistent, we add the play's primary key to
        # the existing sort criteria, which guarantees that the sort will
        # always be the same.
        # (We are careful not to override the user specified
        # `self._sort_exprs`.)
        #
        # That was a lie. We override the user settings if the user asks
        # to sort by `gsis_id`, `drive_id` or `play_id`.
        consistent = [(c, 'asc') for c in ['gsis_id', 'drive_id', 'play_id']]
        sorter = Sorter(types.Play, self._sort_exprs, self._limit)
        sorter.add_exprs(*consistent)

        if not fill:
            results = []
            with Tx(self._db, factory=tuple_cursor) as cursor:
                init = types.Play.from_row_tuple
                q = self._make_join_query(cursor, types.Play, sorter=sorter)
                cursor.execute(q)
                for row in cursor.fetchall():
                    results.append(init(self._db, row))
            return results
        else:
            plays = OrderedDict()
            with Tx(self._db, factory=tuple_cursor) as cursor:
                init_play = types.Play.from_row_tuple
                q = self._make_join_query(cursor, types.Play, sorter=sorter)
                cursor.execute(q)
                for row in cursor.fetchall():
                    play = init_play(self._db, row)
                    play._play_players = []
                    plays[make_pid(play)] = play

                # Run the above query *again* as a subquery.
                # This time, only fetch the primary key, and use that to
                # fetch all the `play_player` records in one swoop.
                aliases = {'play_player': 'pp'}
                ids = self._make_join_query(cursor, types.Play,
                                            only_prim=True, sorter=sorter)
                from_tables = types.PlayPlayer._sql_from(aliases=aliases)
                columns = types.PlayPlayer._sql_select_fields(
                    fields=types.PlayPlayer.sql_fields(), aliases=aliases)
                q = '''
                    SELECT {columns} {from_tables}
                    WHERE (pp.gsis_id, pp.drive_id, pp.play_id) IN ({ids})
                '''.format(columns=', '.join(columns),
                           from_tables=from_tables, ids=ids)

                init_pp = types.PlayPlayer.from_row_tuple
                cursor.execute(q)
                for row in cursor.fetchall():
                    pp = init_pp(self._db, row)
                    plays[make_pid(pp)]._play_players.append(pp)
            return list(plays.values())

    def as_play_players(self):
        """
        Executes the query and returns the results as a list of
        `nlfdb.PlayPlayer` objects.

        This provides a way to access player statistics directly
        by bypassing play data. Usually the results of this method
        are passed to `nfldb.aggregate`. It is recommended to use
        `nfldb.Query.aggregate` and `nfldb.Query.as_aggregate` when
        possible, since it is significantly faster to sum statistics in
        the database as opposed to Python.
        """
        self._assert_no_aggregate()

        results = []
        with Tx(self._db, factory=tuple_cursor) as cursor:
            init = types.PlayPlayer.from_row_tuple
            q = self._make_join_query(cursor, types.PlayPlayer)
            cursor.execute(q)
            for row in cursor.fetchall():
                results.append(init(self._db, row))
        return results

    def as_players(self):
        """
        Executes the query and returns the results as a list of
        `nfldb.Player` objects.
        """
        self._assert_no_aggregate()

        results = []
        with Tx(self._db) as cursor:
            q = self._make_join_query(cursor, types.Player)
            cursor.execute(q)

            for row in cursor.fetchall():
                results.append(types.Player.from_row_dict(self._db, row))
        return results

    def as_aggregate(self):
        """
        Executes the query and returns the results as aggregated
        `nfldb.PlayPlayer` objects. This method is meant to be a more
        restricted but much faster version of `nfldb.aggregate`.
        Namely, this method uses PostgreSQL to compute the aggregate
        statistics while `nfldb.aggregate` computes them in Python
        code.

        If any sorting criteria is specified, it is applied to the
        aggregate *player* values only.
        """
        class AggPP (types.PlayPlayer):
            @classmethod
            def _sql_field(cls, name, aliases=None):

                if name in cls._derived_combined:
                    fields = cls._derived_combined[name]
                    fields = [cls._sql_field(f, aliases=aliases) for f in fields]
                    return ' + '.join(fields)
                elif name == 'points':
                    fields = ['(%s * %d)' % (cls._sql_field(f, aliases=aliases), pval)
                              for f, pval in cls._point_values]
                    return ' + '.join(fields)
                else:
                    sql = super(AggPP, cls)._sql_field(name, aliases=aliases)
                    return 'SUM(%s)' % sql

        joins = ''
        results = []
        with Tx(self._db) as cur:
            for ent in self._entities():
                if ent is types.PlayPlayer:
                    continue
                joins += types.PlayPlayer._sql_join_to_all(ent)

            sum_fields = list(types._player_categories.keys()) \
                + AggPP._sql_tables['derived']
            select_sum_fields = AggPP._sql_select_fields(sum_fields)
            where = self._sql_where(cur)
            having = self._sql_where(cur, aggregate=True)
            q = '''
                SELECT
                    play_player.player_id AS play_player_player_id, {sum_fields}
                FROM play_player
                {joins}
                WHERE {where}
                GROUP BY play_player.player_id
                HAVING {having}
                {order}
            '''.format(
                sum_fields=', '.join(select_sum_fields),
                joins=joins,
                where=sql.ands(where),
                having=sql.ands(having),
                order=self._sorter(AggPP).sql(),
            )

            init = AggPP.from_row_dict
            cur.execute(q)
            for row in cur.fetchall():
                results.append(init(self._db, row))
        return results

    def _entities(self):
        """
        Returns all the entity types referenced in the search criteria.
        """
        tabs = set()
        for cond in self._andalso + self._orelse:
            tabs = tabs.union(cond._entities())
        return tabs

    def show_where(self, aggregate=False):
        """
        Returns an approximate WHERE clause corresponding to the
        criteria specified in `self`. Note that the WHERE clause given
        is never explicitly used for performance reasons, but one hopes
        that it describes the criteria in `self`.

        If `aggregate` is `True`, then aggregate criteria for the
        `play` and `play_player` tables is shown with aggregate
        functions applied.
        """
        with Tx(self._db) as cur:
            return self._sql_where(cur, aggregate=aggregate)
        return ''

    def _sql_where(self, cursor, aliases=None, aggregate=False):
        """
        Returns a WHERE expression representing the search criteria
        in `self` and restricted to the tables in `tables`.

        If `aggregate` is `True`, then the appropriate aggregate
        functions are used.
        """
        if aggregate:
            return Condition._disjunctions(
                cursor, [self._agg_andalso] + [[c] for c in self._agg_orelse],
                aliases=aliases, aggregate=aggregate)
        else:
            return Condition._disjunctions(
                cursor, [self._andalso] + [[c] for c in self._orelse],
                aliases=aliases, aggregate=aggregate)

Ancestors

• Condition

Methods

def aggregate(self, **kw)

This is just like Query.play_player(), except the search parameters are applied to aggregate statistics.

For example, to retrieve all quarterbacks who passed for at least 4000 yards in the 2012 season:

#!python
q = Query(db).game(season_year=2012, season_type='Regular')
q.aggregate(passing_yds__ge=4000)
for pp in q.as_aggregate():
    print pp.player, pp.passing_yds

Aggregate results can also be sorted:

#!python
for pp in q.sort('passing_yds').as_aggregate():
    print pp.player, pp.passing_yds

Note that this method can only be used with Query.as_aggregate(). Use with any of the other as_* methods will result in an assertion error. Note though that regular criteria can still be specified with Query.game(), Query.play(), etc. (Regular criteria restrict what to aggregate while aggregate criteria restrict aggregated results.)

Expand source code

def aggregate(self, **kw):
    """
    This is just like `nfldb.Query.play_player`, except the search
    parameters are applied to aggregate statistics.

    For example, to retrieve all quarterbacks who passed for at
    least 4000 yards in the 2012 season:

        #!python
        q = Query(db).game(season_year=2012, season_type='Regular')
        q.aggregate(passing_yds__ge=4000)
        for pp in q.as_aggregate():
            print pp.player, pp.passing_yds

    Aggregate results can also be sorted:

        #!python
        for pp in q.sort('passing_yds').as_aggregate():
            print pp.player, pp.passing_yds

    Note that this method can **only** be used with
    `nfldb.Query.as_aggregate`. Use with any of the other
    `as_*` methods will result in an assertion error. Note
    though that regular criteria can still be specified with
    `nfldb.Query.game`, `nfldb.Query.play`, etc. (Regular criteria
    restrict *what to aggregate* while aggregate criteria restrict
    *aggregated results*.)
    """
    _append_conds(self._agg_default_cond, types.PlayPlayer, kw)
    return self

def andalso(self, *conds)

Adds the list of Query objects in conds to this query's list of conjunctive conditions.

Expand source code

def andalso(self, *conds):
    """
    Adds the list of `nfldb.Query` objects in `conds` to this
    query's list of conjunctive conditions.
    """
    self._andalso += conds
    return self

def as_aggregate(self)

Executes the query and returns the results as aggregated PlayPlayer objects. This method is meant to be a more restricted but much faster version of aggregate(). Namely, this method uses PostgreSQL to compute the aggregate statistics while aggregate() computes them in Python code.

If any sorting criteria is specified, it is applied to the aggregate player values only.

Expand source code

def as_aggregate(self):
    """
    Executes the query and returns the results as aggregated
    `nfldb.PlayPlayer` objects. This method is meant to be a more
    restricted but much faster version of `nfldb.aggregate`.
    Namely, this method uses PostgreSQL to compute the aggregate
    statistics while `nfldb.aggregate` computes them in Python
    code.

    If any sorting criteria is specified, it is applied to the
    aggregate *player* values only.
    """
    class AggPP (types.PlayPlayer):
        @classmethod
        def _sql_field(cls, name, aliases=None):

            if name in cls._derived_combined:
                fields = cls._derived_combined[name]
                fields = [cls._sql_field(f, aliases=aliases) for f in fields]
                return ' + '.join(fields)
            elif name == 'points':
                fields = ['(%s * %d)' % (cls._sql_field(f, aliases=aliases), pval)
                          for f, pval in cls._point_values]
                return ' + '.join(fields)
            else:
                sql = super(AggPP, cls)._sql_field(name, aliases=aliases)
                return 'SUM(%s)' % sql

    joins = ''
    results = []
    with Tx(self._db) as cur:
        for ent in self._entities():
            if ent is types.PlayPlayer:
                continue
            joins += types.PlayPlayer._sql_join_to_all(ent)

        sum_fields = list(types._player_categories.keys()) \
            + AggPP._sql_tables['derived']
        select_sum_fields = AggPP._sql_select_fields(sum_fields)
        where = self._sql_where(cur)
        having = self._sql_where(cur, aggregate=True)
        q = '''
            SELECT
                play_player.player_id AS play_player_player_id, {sum_fields}
            FROM play_player
            {joins}
            WHERE {where}
            GROUP BY play_player.player_id
            HAVING {having}
            {order}
        '''.format(
            sum_fields=', '.join(select_sum_fields),
            joins=joins,
            where=sql.ands(where),
            having=sql.ands(having),
            order=self._sorter(AggPP).sql(),
        )

        init = AggPP.from_row_dict
        cur.execute(q)
        for row in cur.fetchall():
            results.append(init(self._db, row))
    return results

def as_drives(self)

Executes the query and returns the results as a list of Drive objects.

Expand source code

def as_drives(self):
    """
    Executes the query and returns the results as a list of
    `nfldb.Drive` objects.
    """
    self._assert_no_aggregate()

    results = []
    with Tx(self._db, factory=tuple_cursor) as cursor:
        q = self._make_join_query(cursor, types.Drive)
        cursor.execute(q)
        for row in cursor.fetchall():
            results.append(types.Drive.from_row_tuple(self._db, row))
    return results

def as_games(self)

Executes the query and returns the results as a list of Game objects.

Expand source code

def as_games(self):
    """
    Executes the query and returns the results as a list of
    `nfldb.Game` objects.
    """
    self._assert_no_aggregate()

    results = []
    with Tx(self._db, factory=tuple_cursor) as cursor:
        q = self._make_join_query(cursor, types.Game)
        cursor.execute(q)
        for row in cursor.fetchall():
            results.append(types.Game.from_row_tuple(self._db, row))
    return results

def as_play_players(self)

Executes the query and returns the results as a list of nlfdb.PlayPlayer objects.

This provides a way to access player statistics directly by bypassing play data. Usually the results of this method are passed to aggregate(). It is recommended to use Query.aggregate() and Query.as_aggregate() when possible, since it is significantly faster to sum statistics in the database as opposed to Python.

Expand source code

def as_play_players(self):
    """
    Executes the query and returns the results as a list of
    `nlfdb.PlayPlayer` objects.

    This provides a way to access player statistics directly
    by bypassing play data. Usually the results of this method
    are passed to `nfldb.aggregate`. It is recommended to use
    `nfldb.Query.aggregate` and `nfldb.Query.as_aggregate` when
    possible, since it is significantly faster to sum statistics in
    the database as opposed to Python.
    """
    self._assert_no_aggregate()

    results = []
    with Tx(self._db, factory=tuple_cursor) as cursor:
        init = types.PlayPlayer.from_row_tuple
        q = self._make_join_query(cursor, types.PlayPlayer)
        cursor.execute(q)
        for row in cursor.fetchall():
            results.append(init(self._db, row))
    return results

def as_players(self)

Executes the query and returns the results as a list of Player objects.

Expand source code

def as_players(self):
    """
    Executes the query and returns the results as a list of
    `nfldb.Player` objects.
    """
    self._assert_no_aggregate()

    results = []
    with Tx(self._db) as cursor:
        q = self._make_join_query(cursor, types.Player)
        cursor.execute(q)

        for row in cursor.fetchall():
            results.append(types.Player.from_row_dict(self._db, row))
    return results

def as_plays(self, fill=True)

Executes the query and returns the results as a dictionary of nlfdb.Play objects that don't have the play_player attribute filled. The keys of the dictionary are play id tuples with the spec (gsis_id, drive_id, play_id).

The primary key membership SQL expression is also returned.

Expand source code

def as_plays(self, fill=True):
    """
    Executes the query and returns the results as a dictionary
    of `nlfdb.Play` objects that don't have the `play_player`
    attribute filled. The keys of the dictionary are play id
    tuples with the spec `(gsis_id, drive_id, play_id)`.

    The primary key membership SQL expression is also returned.
    """
    def make_pid(play):
        return (play.gsis_id, play.drive_id, play.play_id)

    self._assert_no_aggregate()

    # This is pretty terrifying.
    # Apparently PostgreSQL can change the order of rows returned
    # depending on the columns selected. So e.g., if you sort by `down`
    # and limit to 20 results, you might get a different 20 plays if
    # you change which columns you're selecting.
    # This is pertinent here because if we're filling plays with player
    # statistics, then we are assuming that this order never changes.
    # To make the ordering consistent, we add the play's primary key to
    # the existing sort criteria, which guarantees that the sort will
    # always be the same.
    # (We are careful not to override the user specified
    # `self._sort_exprs`.)
    #
    # That was a lie. We override the user settings if the user asks
    # to sort by `gsis_id`, `drive_id` or `play_id`.
    consistent = [(c, 'asc') for c in ['gsis_id', 'drive_id', 'play_id']]
    sorter = Sorter(types.Play, self._sort_exprs, self._limit)
    sorter.add_exprs(*consistent)

    if not fill:
        results = []
        with Tx(self._db, factory=tuple_cursor) as cursor:
            init = types.Play.from_row_tuple
            q = self._make_join_query(cursor, types.Play, sorter=sorter)
            cursor.execute(q)
            for row in cursor.fetchall():
                results.append(init(self._db, row))
        return results
    else:
        plays = OrderedDict()
        with Tx(self._db, factory=tuple_cursor) as cursor:
            init_play = types.Play.from_row_tuple
            q = self._make_join_query(cursor, types.Play, sorter=sorter)
            cursor.execute(q)
            for row in cursor.fetchall():
                play = init_play(self._db, row)
                play._play_players = []
                plays[make_pid(play)] = play

            # Run the above query *again* as a subquery.
            # This time, only fetch the primary key, and use that to
            # fetch all the `play_player` records in one swoop.
            aliases = {'play_player': 'pp'}
            ids = self._make_join_query(cursor, types.Play,
                                        only_prim=True, sorter=sorter)
            from_tables = types.PlayPlayer._sql_from(aliases=aliases)
            columns = types.PlayPlayer._sql_select_fields(
                fields=types.PlayPlayer.sql_fields(), aliases=aliases)
            q = '''
                SELECT {columns} {from_tables}
                WHERE (pp.gsis_id, pp.drive_id, pp.play_id) IN ({ids})
            '''.format(columns=', '.join(columns),
                       from_tables=from_tables, ids=ids)

            init_pp = types.PlayPlayer.from_row_tuple
            cursor.execute(q)
            for row in cursor.fetchall():
                pp = init_pp(self._db, row)
                plays[make_pid(pp)]._play_players.append(pp)
        return list(plays.values())

def drive(self, **kw)

Specify search criteria for a drive. The possible fields correspond to columns in the drive table (or derived columns). They are documented as instance variables in the Drive class.

Please see the documentation for Query for examples on how to specify search criteria.

Expand source code

def drive(self, **kw):
    """
    Specify search criteria for a drive. The possible fields
    correspond to columns in the `drive` table (or derived
    columns). They are documented as instance variables in the
    `nfldb.Drive` class.

    Please see the documentation for `nfldb.Query` for examples on
    how to specify search criteria.
    """
    _append_conds(self._default_cond, types.Drive, kw)
    return self

def game(self, **kw)

Specify search criteria for an NFL game. The possible fields correspond to columns in the game table (or derived columns). They are documented as instance variables in the Game class. Additionally, there are some special fields that provide convenient access to common conditions:

• team - Find games that the team given played in, regardless of whether it is the home or away team.

Please see the documentation for Query for examples on how to specify search criteria.

Please open an issue if you can think of other special fields to add.

Expand source code

def game(self, **kw):
    """
    Specify search criteria for an NFL game. The possible fields
    correspond to columns in the `game` table (or derived columns).
    They are documented as instance variables in the `nfldb.Game`
    class. Additionally, there are some special fields that provide
    convenient access to common conditions:

      * **team** - Find games that the team given played in, regardless
                   of whether it is the home or away team.

    Please see the documentation for `nfldb.Query` for examples on
    how to specify search criteria.

    Please
    [open an issue](https://github.com/derek-adair/nfldb/issues/new)
    if you can think of other special fields to add.
    """
    if 'team' in kw:
        team = kw.pop('team')
        ors = {'home_team': team, 'away_team': team}
        self.andalso(Query(self._db, orelse=True).game(**ors))
    _append_conds(self._default_cond, types.Game, kw)
    return self

def limit(self, count)

Limits the number of results to the integer count. If count is 0 (the default), then no limiting is done.

See the documentation for Query.sort() for an example on how to combine it with Query.limit() to get results quickly.

Expand source code

def limit(self, count):
    """
    Limits the number of results to the integer `count`. If `count` is
    `0` (the default), then no limiting is done.

    See the documentation for `nfldb.Query.sort` for an example on how
    to combine it with `nfldb.Query.limit` to get results quickly.
    """
    self._limit = count
    return self

def orelse(self, *conds)

Adds the list of Query objects in conds to this query's list of disjunctive conditions.

Expand source code

def orelse(self, *conds):
    """
    Adds the list of `nfldb.Query` objects in `conds` to this
    query's list of disjunctive conditions.
    """
    self._orelse += conds
    return self

def play(self, **kw)

Specify search criteria for a play. The possible fields correspond to columns in the play or play_player tables (or derived columns). They are documented as instance variables in the Play and PlayPlayer classes. Additionally, the fields listed on the statistical categories wiki page may be used. That includes both play and player statistical categories.

Please see the documentation for Query for examples on how to specify search criteria.

Expand source code

def play(self, **kw):
    """
    Specify search criteria for a play. The possible fields
    correspond to columns in the `play` or `play_player` tables (or
    derived columns). They are documented as instance variables in
    the `nfldb.Play` and `nfldb.PlayPlayer` classes. Additionally,
    the fields listed on the
    [statistical categories](http://goo.gl/1qYG3C)
    wiki page may be used. That includes **both** `play` and
    `player` statistical categories.

    Please see the documentation for `nfldb.Query` for examples on
    how to specify search criteria.
    """
    _append_conds(self._default_cond, types.Play, kw)
    return self

def play_player(self, **kw)

Specify search criteria for individual play player statistics. The allowed fields are the columns in the play_player table. They are documented as instance variables in the PlayPlayer class. Additionally, the fields listed on the statistical categories wiki page may be used. (Only the player statistical categories.)

This method differs from Query.play() in that it can be used to select for individual player statistics in a play. In particular, there are zero or more player statistics for every play.

Expand source code

def play_player(self, **kw):
    """
    Specify search criteria for individual play player statistics.
    The allowed fields are the columns in the `play_player`
    table.  They are documented as instance variables in the
    `nfldb.PlayPlayer` class. Additionally, the fields listed on
    the [statistical categories](http://goo.gl/1qYG3C) wiki page
    may be used. (Only the `player` statistical categories.)

    This method differs from `nfldb.Query.play` in that it can be
    used to select for individual player statistics in a play. In
    particular, there are *zero or more* player statistics for
    every play.
    """
    # Technically, it isn't necessary to handle derived fields manually
    # since their SQL can be generated automatically, but it can be
    # much faster to express them in terms of boolean logic with other
    # fields rather than generate them.
    for field, value in list(kw.items()):
        nosuff = _no_comp_suffix(field)
        suff = _comp_suffix(field)

        def replace_or(*fields):
            q = Query(self._db, orelse=True)
            ors = dict([('%s__%s' % (f, suff), value) for f in fields])
            self.andalso(q.play_player(**ors))

        if nosuff in types.PlayPlayer._derived_combined:
            replace_or(*types.PlayPlayer._derived_combined[nosuff])
            kw.pop(field)

    # Now add the rest of the query.
    _append_conds(self._default_cond, types.PlayPlayer, kw)
    return self

def player(self, **kw)

Specify search criteria for a player. The possible fields correspond to columns in the player table (or derived columns). They are documented as instance variables in the Player class.

Please see the documentation for Query for examples on how to specify search criteria.

Expand source code

def player(self, **kw):
    """
    Specify search criteria for a player. The possible fields
    correspond to columns in the `player` table (or derived
    columns). They are documented as instance variables in the
    `nfldb.Player` class.

    Please see the documentation for `nfldb.Query` for examples on
    how to specify search criteria.
    """
    _append_conds(self._default_cond, types.Player, kw)
    return self

def show_where(self, aggregate=False)

Returns an approximate WHERE clause corresponding to the criteria specified in self. Note that the WHERE clause given is never explicitly used for performance reasons, but one hopes that it describes the criteria in self.

If aggregate() is True, then aggregate criteria for the play and play_player tables is shown with aggregate functions applied.

Expand source code

def show_where(self, aggregate=False):
    """
    Returns an approximate WHERE clause corresponding to the
    criteria specified in `self`. Note that the WHERE clause given
    is never explicitly used for performance reasons, but one hopes
    that it describes the criteria in `self`.

    If `aggregate` is `True`, then aggregate criteria for the
    `play` and `play_player` tables is shown with aggregate
    functions applied.
    """
    with Tx(self._db) as cur:
        return self._sql_where(cur, aggregate=aggregate)
    return ''

def sort(self, exprs)

Specify sorting criteria for the result set returned by using sort expressions. A sort expression is a tuple with two elements: a field to sort by and the order to use. The field should correspond to an attribute of the objects you're returning and the order should be asc for ascending (smallest to biggest) or desc for descending (biggest to smallest).

For example, ('passing_yds', 'desc') would sort plays by the number of passing yards in the play, with the biggest coming first.

Remember that a sort field must be an attribute of the results being returned. For example, you can't sort plays by home_score, which is an attribute of a Game object. If you require this behavior, you will need to do it in Python with its sorted built in function. (Or alternatively, use two separate queries if the result set is large.)

You may provide multiple sort expressions. For example, [('gsis_id', 'asc'), ('time', 'asc'), ('play_id', 'asc')] would sort plays in the order in which they occurred within each game.

exprs may also just be a string specifying a single field which defaults to a descending order. For example, sort('passing_yds') sorts plays by passing yards in descending order.

If exprs is set to the empty list, then sorting will be disabled for this query.

Note that sorting criteria can be combined with Query.limit() to limit results which can dramatically speed up larger searches. For example, to fetch the top 10 passing plays in the 2012 season:

#!python
q = Query(db).game(season_year=2012, season_type='Regular')
q.sort('passing_yds').limit(10)
for p in q.as_plays():
    print p

A more naive approach might be to fetch all plays and sort them with Python:

#!python
q = Query(db).game(season_year=2012, season_type='Regular')
plays = q.as_plays()

plays = sorted(plays, key=lambda p: p.passing_yds, reverse=True)
for p in plays[:10]:
    print p

But this is over 43 times slower on my machine than using Query.sort() and Query.limit(). (The performance difference is due to making PostgreSQL perform the search and restricting the number of results returned to process.)

Expand source code

def sort(self, exprs):
    """
    Specify sorting criteria for the result set returned by
    using sort expressions. A sort expression is a tuple with
    two elements: a field to sort by and the order to use. The
    field should correspond to an attribute of the objects you're
    returning and the order should be `asc` for ascending (smallest
    to biggest) or `desc` for descending (biggest to smallest).

    For example, `('passing_yds', 'desc')` would sort plays by the
    number of passing yards in the play, with the biggest coming
    first.

    Remember that a sort field must be an attribute of the
    results being returned. For example, you can't sort plays by
    `home_score`, which is an attribute of a `nfldb.Game` object.
    If you require this behavior, you will need to do it in Python
    with its `sorted` built in function. (Or alternatively, use
    two separate queries if the result set is large.)

    You may provide multiple sort expressions. For example,
    `[('gsis_id', 'asc'), ('time', 'asc'), ('play_id', 'asc')]`
    would sort plays in the order in which they occurred within
    each game.

    `exprs` may also just be a string specifying a single
    field which defaults to a descending order. For example,
    `sort('passing_yds')` sorts plays by passing yards in
    descending order.

    If `exprs` is set to the empty list, then sorting will be
    disabled for this query.

    Note that sorting criteria can be combined with
    `nfldb.Query.limit` to limit results which can dramatically
    speed up larger searches. For example, to fetch the top 10
    passing plays in the 2012 season:

        #!python
        q = Query(db).game(season_year=2012, season_type='Regular')
        q.sort('passing_yds').limit(10)
        for p in q.as_plays():
            print p

    A more naive approach might be to fetch all plays and sort them
    with Python:

        #!python
        q = Query(db).game(season_year=2012, season_type='Regular')
        plays = q.as_plays()

        plays = sorted(plays, key=lambda p: p.passing_yds, reverse=True)
        for p in plays[:10]:
            print p

    But this is over **43 times slower** on my machine than using
    `nfldb.Query.sort` and `nfldb.Query.limit`. (The performance
    difference is due to making PostgreSQL perform the search and
    restricting the number of results returned to process.)
    """
    self._sort_exprs = exprs
    return self

class Team (db, abbr)

Represents information about an NFL team. This includes its standard three letter abbreviation, city and mascot name.

Introduces a new team given an abbreviation and a database connection. The database connection is used to retrieve other team information if it isn't cached already. The abbreviation given is passed to standard_team() for you.

Expand source code

class Team (object):
    """
    Represents information about an NFL team. This includes its
    standard three letter abbreviation, city and mascot name.
    """
    # BUG: If multiple databases are used with different team information,
    # this class won't behave correctly since it's using a global cache.

    __slots__ = ['team_id', 'city', 'name']
    __cache = defaultdict(dict)

    def __new__(cls, db, abbr):
        abbr = nfldb.team.standard_team(abbr)
        if abbr in Team.__cache:
            return Team.__cache[abbr]
        return object.__new__(cls)

    def __init__(self, db, abbr):
        """
        Introduces a new team given an abbreviation and a database
        connection. The database connection is used to retrieve other
        team information if it isn't cached already. The abbreviation
        given is passed to `nfldb.standard_team` for you.
        """
        if hasattr(self, 'team_id'):
            # Loaded from cache.
            return

        self.team_id = nfldb.team.standard_team(abbr)
        """
        The unique team identifier represented as its standard
        2 or 3 letter abbreviation.
        """
        self.city = None
        """
        The city where this team resides.
        """
        self.name = None
        """
        The full "mascot" name of this team.
        """
        if self.team_id not in Team.__cache:
            with Tx(db) as cur:
                cur.execute('SELECT * FROM team WHERE team_id = %s',
                            (self.team_id,))
                row = cur.fetchone()
                self.city = row['city']
                self.name = row['name']
            Team.__cache[self.team_id] = self

    def __str__(self):
        return '%s %s' % (self.city, self.name)

    def __conform__(self, proto):
        if proto is ISQLQuote:
            return AsIs("'%s'" % self.team_id)
        return None

Instance variables

var city

The city where this team resides.

var name

The full "mascot" name of this team.

var team_id

The unique team identifier represented as its standard 2 or 3 letter abbreviation.

class Tx (psycho_conn, name=None, factory=None)

Tx is a with compatible class that abstracts a transaction given a connection. If an exception occurs inside the with block, then rollback is automatically called. Otherwise, upon exit of the with block, commit is called.

Tx blocks can be nested inside other Tx blocks. Nested Tx blocks never commit or rollback a transaction. Instead, the exception is passed along to the caller. Only the outermost transaction will commit or rollback the entire transaction.

Use it like so:

#!python
with Tx(conn) as cursor:
    ...

Which is meant to be roughly equivalent to the following:

#!python
with conn:
    with conn.cursor() as curs:
        ...

This should only be used when you're running SQL queries directly. (Or when interfacing with another part of the API that requires a database cursor.)

psycho_conn is a DB connection returned from connect(), name is passed as the name argument to the cursor constructor (for server-side cursors), and factory is passed as the cursor_factory parameter to the cursor constructor.

Note that the default cursor factory is psycopg2.extras.RealDictCursor. However, using psycopg2.extensions.cursor (the default tuple cursor) can be much more efficient when fetching large result sets.

Expand source code

class Tx (object):
    """
    Tx is a `with` compatible class that abstracts a transaction given
    a connection. If an exception occurs inside the `with` block, then
    rollback is automatically called. Otherwise, upon exit of the with
    block, commit is called.

    Tx blocks can be nested inside other Tx blocks. Nested Tx blocks
    never commit or rollback a transaction. Instead, the exception is
    passed along to the caller. Only the outermost transaction will
    commit or rollback the entire transaction.

    Use it like so:

        #!python
        with Tx(conn) as cursor:
            ...

    Which is meant to be roughly equivalent to the following:

        #!python
        with conn:
            with conn.cursor() as curs:
                ...

    This should only be used when you're running SQL queries directly.
    (Or when interfacing with another part of the API that requires
    a database cursor.)
    """
    def __init__(self, psycho_conn, name=None, factory=None):
        """
        `psycho_conn` is a DB connection returned from `nfldb.connect`,
        `name` is passed as the `name` argument to the cursor
        constructor (for server-side cursors), and `factory` is passed
        as the `cursor_factory` parameter to the cursor constructor.

        Note that the default cursor factory is
        `psycopg2.extras.RealDictCursor`. However, using
        `psycopg2.extensions.cursor` (the default tuple cursor) can be
        much more efficient when fetching large result sets.
        """
        tstatus = psycho_conn.get_transaction_status()
        self.__name = name
        self.__nested = tstatus == TRANSACTION_STATUS_INTRANS
        self.__conn = psycho_conn
        self.__cursor = None
        self.__factory = factory
        if self.__factory is None:
            self.__factory = RealDictCursor

    def __enter__(self):
        # No biscuits for the psycopg2 author. Changed the public API in
        # 2.5 in a very very subtle way.
        # In 2.4, apparently `name` cannot be `None`. Why? I don't know.
        if self.__name is None:
            self.__cursor = self.__conn.cursor(cursor_factory=self.__factory)
        else:
            self.__cursor = self.__conn.cursor(self.__name, self.__factory)
        c = self.__cursor

        if _SHOW_QUERIES:
            class _ (object):
                def execute(self, *args, **kwargs):
                    global _NUM_QUERIES

                    _NUM_QUERIES += 1
                    c.execute(*args, **kwargs)
                    print(c.query, file=sys.stderr, end='\n\n')

                def __getattr__(self, k):
                    return getattr(c, k)
            return _()
        else:
            return c

    def __exit__(self, typ, value, traceback):
        if not self.__cursor.closed:
            self.__cursor.close()
        if typ is not None:
            if not self.__nested:
                self.__conn.rollback()
            return False
        else:
            if not self.__nested:
                self.__conn.commit()
            return True