Being an avid hockey follower and fan of the National Hockey League’s Toronto Maple Leafs for more than twenty years, I have observed the recent surge of approaches to tackle the sport and its peculiarities based on application of analytical methods with great interest. Of course as a foundation of any data analysis there has to be some data first. Preferably in a standardized and suitable environment such as a relational database management system. My very personal endeavors to retrieve, structure, store and analyze hockey-related data will be subject of a larger series of posts that I am starting today. The series title already lays out the scope within this undertaking will be conducted. I will be looking at the National Hockey League as it provides (with all of its shortcomings) the most detailed data available in this regard. Python as programming language, PostgreSQL as database system and SQLAlchemy as SQL and mapping framework connecting both of the formerly mentioned will serve as my technology stack. But let’s get it on already.

As first part of this series I will be looking at NHL teams and divisions. This is fitting for the beginning as the accompanying data is not very complex and particularly static as well, i.e. it changes with very low frequency. We may provide a simple database entity, i.e. table, containing basic team data using the following SQL data definition statement:

CREATE TABLE nhl_db.nhl_teams
(
  team_id serial NOT NULL,
  nhl_id integer, -- Team id as used on the NHL website
  franchise_id integer, -- Franchise id as used on the NHL website
  name character varying NOT NULL,
  short_name character varying NOT NULL, -- Team nick name
  abbr character(3) NOT NULL,
  orig_abbr character(3) NOT NULL, -- Team abbreviation as used in official game documents on the NHL website
  url_abbr character(3), -- Team abbreviation as previously used in urls on the NHL website
  was_team integer,
  became_team integer,
  init_year integer,
  end_year integer,
  CONSTRAINT nhl_team_key PRIMARY KEY (team_id)
)

As you can see this table design accounts for the fact that from time to time franchises move around or change their names. In case a certain team is the current reincarnation of a given franchise, both became_team and end_year contain NULL values thus marking it as active.

To whom it may concern: Differentiating between the various abbreviation types is necessary due to the NHL using different ways of shorting a team name on game reports (N.J, L.A,…) and in URLs (NJD, LAK,…). Additionally I wasn’t happy with some of their choices for abbreviations so that I created my very own column.

Update (February 2016): With the current update of the NHL’s website the previous system of using abbreviations for team identification was abolished and actual team (and franchise) ids have been introduced. The according changes in the data model are marked above. As far as I have grasped the url_abbr column should no longer be necessary, I will keep it for safety and nostalgic reasons though.

After setting up the table data insertion may be conducted easily by preparing and applying the according SQL insert statements. Both nhl_id and franchise_id values have been retrieved from the NHL’s website.

INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (0, 'Anaheim Ducks', 'Ducks', 'ANA', 'ANA', 'ANA', 38, NULL, 2006, NULL, 24, 32);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (1, 'Boston Bruins', 'Bruins', 'BOS', 'BOS', 'BOS', NULL, NULL, 1924, NULL, 6, 6);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (2, 'Buffalo Sabres', 'Sabres', 'BUF', 'BUF', 'BUF', NULL, NULL, 1970, NULL, 7, 19);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (3, 'Calgary Flames', 'Flames', 'CGY', 'CGY', 'CGY', 31, NULL, 1980, NULL, 20, 21);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (4, 'Carolina Hurricanes', 'Hurricanes', 'CAR', 'CAR', 'CAR', 32, NULL, 1997, NULL, 12, 26);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (5, 'Chicago Blackhawks', 'Blackhawks', 'CHI', 'CHI', 'CHI', 43, NULL, 1986, NULL, 16, 11);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (6, 'Colorado Avalanche', 'Avalanche', 'COL', 'COL', 'COL', 33, NULL, 1995, NULL, 21, 27);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (7, 'Columbus Blue Jackets', 'Blue Jackets', 'CLB', 'CBJ', 'CBJ', NULL, NULL, 2000, NULL, 29, 36);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (8, 'Dallas Stars', 'Stars', 'DAL', 'DAL', 'DAL', 34, NULL, 1993, NULL, 25, 15);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (9, 'Detroit Red Wings', 'Red Wings', 'DET', 'DET', 'DET', NULL, NULL, 1926, NULL, 17, 12);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (10, 'Edmonton Oilers', 'Oilers', 'EDM', 'EDM', 'EDM', NULL, NULL, 1979, NULL, 22, 25);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (11, 'Florida Panthers', 'Panthers', 'FLA', 'FLA', 'FLA', NULL, NULL, 1993, NULL, 13, 33);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (12, 'Los Angeles Kings', 'Kings', 'LAK', 'L.A', 'LAK', NULL, NULL, 1967, NULL, 26, 14);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (13, 'Minnesota Wild', 'Wild', 'MIN', 'MIN', 'MIN', NULL, NULL, 2000, NULL, 30, 37);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (14, 'Montreal Canadiens', 'Canadiens', 'MTL', 'MTL', 'MTL', NULL, NULL, 1917, NULL, 8, 1);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (15, 'Nashville Predators', 'Predators', 'NSH', 'NSH', 'NSH', NULL, NULL, 1998, NULL, 18, 34);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (16, 'New Jersey Devils', 'Devils', 'NJD', 'N.J', 'NJD', 35, NULL, 1982, NULL, 1, 23);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (17, 'New York Islanders', 'Islanders', 'NYI', 'NYI', 'NYI', NULL, NULL, 1972, NULL, 2, 22);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (18, 'New York Rangers', 'Rangers', 'NYR', 'NYR', 'NYR', NULL, NULL, 1926, NULL, 3, 10);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (19, 'Ottawa Senators', 'Senators', 'OTW', 'OTT', 'OTT', NULL, NULL, 1992, NULL, 9, 30);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (20, 'Philadelphia Flyers', 'Flyers', 'PHI', 'PHI', 'PHI', NULL, NULL, 1967, NULL, 4, 16);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (21, 'Phoenix Coyotes', 'Coyotes', 'PHO', 'PHX', 'PHX', 36, 44, 1996, 2014, 27, 28);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (22, 'Pittsburgh Penguins', 'Penguins', 'PIT', 'PIT', 'PIT', NULL, NULL, 1967, NULL, 5, 17);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (23, 'San Jose Sharks', 'Sharks', 'SJS', 'S.J', 'SJS', NULL, NULL, 1991, NULL, 28, 29);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (24, 'St. Louis Blues', 'Blues', 'STL', 'STL', 'STL', NULL, NULL, 1967, NULL, 19, 18);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (25, 'Tampa Bay Lightning', 'Lightning', 'TBL', 'T.B', 'TBL', NULL, NULL, 1992, NULL, 14, 31);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (26, 'Toronto Maple Leafs', 'Maple Leafs', 'TOR', 'TOR', 'TOR', 52, NULL, 1927, NULL, 10, 5);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (27, 'Vancouver Canucks', 'Canucks', 'VAN', 'VAN', 'VAN', NULL, NULL, 1970, NULL, 23, 20);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (28, 'Washington Capitals', 'Capitals', 'WSH', 'WSH', 'WSH', NULL, NULL, 1974, NULL, 15, 24);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (29, 'Winnipeg Jets', 'Jets', 'WPG', 'WPG', 'WPG', 30, NULL, 2011, NULL, 52, 35);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (30, 'Atlanta Thrashers', 'Thrashers', 'ATL', 'ATL', 'ATL', NULL, 29, 1999, 2011, 11, 35);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (31, 'Atlanta Flames', 'Flames', 'ATF', 'AFM', NULL, NULL, 3, 1972, 1980, 47, 21);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (32, 'Hartford Whalers', 'Whalers', 'HFD', 'HFD', 'HFD', NULL, 4, 1979, 1997, 34, 26);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (33, 'Quebec Nordiques', 'Nordiques', 'QUE', 'QUE', 'QUE', NULL, 6, 1979, 1995, 32, 27);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (34, 'Minnesota North Stars', 'North Stars', 'MNS', 'MNS', 'MNS', NULL, 8, 1967, 1993, 31, 15);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (35, 'Colorado Rockies', 'Rockies', 'CLR', 'CLR', NULL, 37, 16, 1976, 1982, 35, 23);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (36, 'Winnipeg Jets', 'Jets', 'WIN', 'WIN', 'WIN', NULL, 21, 1979, 1996, 33, 28);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (37, 'Kansas City Scouts', 'Scouts', 'KCS', 'KCS', NULL, NULL, 35, 1974, 1976, 48, 23);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (38, 'Mighty Ducks of Anaheim', 'Mighty Ducks', 'MDA', 'ANA', NULL, NULL, 0, 1993, 2006, 24, 32);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (39, 'California Seals', 'Seals', 'CSE', 'CSE', NULL, NULL, 40, 1967, 1967, 56, 13);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (40, 'Oakland Seals', 'Seals', 'OAK', 'OAK', NULL, 39, 41, 1967, 1970, 46, 13);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (41, 'California Golden Seals', 'Golden Seals', 'CGS', 'CGS', NULL, 40, 42, 1970, 1976, 46, 13);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (42, 'Cleveland Barons', 'Barons', 'CBN', 'CBN', NULL, 41, NULL, 1976, 1978, 49, 13);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (43, 'Chicago Black Hawks', 'Black Hawks', 'CHB', 'CHI', NULL, NULL, 5, 1926, 1986, 16, 11);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (44, 'Arizona Coyotes', 'Coyotes', 'ARI', 'ARI', 'ARI', 21, NULL, 2014, NULL, 53, 28);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (45, 'Ottawa Senators (1917)', 'Senators (1917)', 'SEN', 'SEN', NULL, NULL, NULL, 1917, 1935, 36, 3);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (46, 'Hamilton Tigers', 'Tigers', 'HAM', 'HAM', NULL, NULL, NULL, 1919, 1925, 37, 4);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (47, 'Montreal Wanderers', 'Wanderers', 'MWN', 'MWN', NULL, NULL, NULL, 1917, 1918, 41, 2);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (48, 'Montreal Maroons', 'Maroons', 'MMR', 'MMR', NULL, NULL, NULL, 1924, 1938, 43, 7);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (49, 'New York Americans', 'Americans', 'NYA', 'NYA', NULL, NULL, NULL, 1925, 1942, 44, 8);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (50, 'Pittsburgh Pirates', 'Pirates', 'PIR', 'PIR', NULL, NULL, NULL, 1925, 1931, 38, 9);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (51, 'Toronto Arenas', 'Arenas', 'TAN', 'TAN', NULL, NULL, 52, 1918, 1919, 57, 5);
INSERT INTO nhl_db.nhl_teams (team_id, name, short_name, abbr, orig_abbr, url_abbr, was_team, became_team, init_year, end_year, nhl_id, franchise_id) VALUES (52, 'Toronto St. Patricks', 'St. Patricks', 'TSP', 'TSP', NULL, 51, 26, 1919, 1927, 58, 5);

A table containing NHL divisions may be set up using the following definition:

CREATE TABLE nhl_db.nhl_divisions
(
  division_id serial NOT NULL,
  division_name character varying,
  year integer, -- Year in which this division was active.
  teams integer[], -- IDs of team constituting this division.
  conference character varying, -- Name of the Conference this division belongs to.
  CONSTRAINT nhl_division_key PRIMARY KEY (division_id)
)

Here a particular property of PostgreSQL is visible: the ability to store multidimensional arrays of varying length in a database column. In this case the teams column will contain the IDs of the teams that set up a certain division at a certain time. Unfortunately there is currently no support for referential integrity between the elements of an array (here: team_ids in column teams of table nhl_divisions) and the elements of another table (column team_id in table nhl_teams). Although amends have been made to introduce this feature in more than just one of the more recent releases, it hasn’t become part of the code base yet.

We will deal with inserting data into this table later on.

Subsequently an (updated) ERD of the current database configuration looks like the following:

Using SQLAlchemy it is now very easy to map these tables to Python classes. While it is very viable to use SQLAlchemy magic to set up the table structures from Python, I personally like to go the other way around: Having tables already defined in the database system and mapping it to Python by metadata reflection. Everything that is necessary to do so is the mapped class being inherited from the SQLAlchemy Base class and it having two special attributes __tablename__ and __autoload__.

# conducting necessary imports
from sqlalchemy import create_engine
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.schema import MetaData
from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import or_, and_
from sqlalchemy.sql.expression import func

# setting up database connection information
db_engine = 'postgresql'
user = '***
password = '***'
host = '***
port = '5432'
database = 'nhl_test'

conn_string = "%s://%s:%s@%s:%s/%s" % (db_engine, user, password, host, port, database)

# setting up database connection
Engine = create_engine(conn_string, echo = False, pool_size = 5)
# creating session
Session = sessionmaker(bind = Engine)
# setting up base class
Base = declarative_base(metadata = MetaData(schema = 'nhl_db', bind = Engine))

class NHLTeam(Base):
    u"""A class representing an NHL team.
    """
    __tablename__ = 'nhl_teams' # maps this class to table nhl_teams
    __autoload__ = True # sets up metadata reflection

    def __init__(self):
        pass

    def __str__(self):
        return self.name

We may now add a way to search for team objects in the database, i.e. by implementing according class methods to search the database using an abbreviation or team id. Additionally we will add means to make teams comparable and sortable by using Python’s comparison methods.

    @classmethod
    def find(cls, abbr):
        session = Session()
        try:
            t = session.query(NHLTeam).filter(
                    func.lower(NHLTeam.abbr) == abbr.lower()
                ).one()
        except:
            t = None
        finally:
            session.close()
        return t

    @classmethod
    def find_by_id(cls, team_id):
        session = Session()
        try:
            t = session.query(NHLTeam).filter(
                    NHLTeam.team_id == team_id
                ).one()
        except:
            t = None
        finally:
            session.close()
        return t

    @classmethod
    def find_by_nhl_id(self, nhl_id):
        session = Session()
        try:
            t = session.query(NHLTeam).filter(
                NHLTeam.nhl_id == nhl_id
            ).one()
        except:
            t = None
        finally:
            session.close()
        return t

    def __eq__(self, other):
        return self.name == other.name
    
    def __ne__(self, other):
        return not self == other
    
    def __gt__(self, other):
        return self.name > other.name
    
    def __lt__(self, other):
        return self.name < other.name

Please note that this use of Session is actually not recommended. Instead a scoped session should be your choice in production circumstances. However here the more simple approach has been used here for the sake of clarity.

It is now possible to find an NHLTeam object by specifying its abbreviation:

if __name__ == '__main__':
    t = NHLTeam.find('TOR')
    print "Team by abbreviation '%s': %s" % ('TOR', t)
    t = NHLTeam.find_by_id(12)
    print "Team by id %d: %s" % (12, t)

This gives you:

Team with abbreviation 'TOR': Toronto Maple Leafs
Team with id 12: Los Angeles Kings

Similarly we may map the database table containing NHL Division information with an according Python class. As we're creating division data from Python as well, we're including a real constructor this time:

    class NHLDivision(Base):
    u"""A class representing an NHL division.
    """
    __tablename__ = 'nhl_divisions'
    __autoload__ = True

    def __init__(self, name, season, teams, conference = None):
        self.division_name = name
        self.year = season
        self.teams = list()
        for t in teams:
            self.teams.append(t.team_id)
        self.conference = conference

Given definitions and mappings for both team and division objects and having some source data ready, it is now convenient to insert division data using a special helper method:

def create_divisions(division_src_file):
    lines = open(division_src_file).readlines()
    
    session = Session()
    try:
        for line in lines:
            if line.startswith("#"):
                continue
            division_name, season, teams, conference = line.strip().split(";")
            season = int(season)
            # retrieving team abbreviations from source input line
            team_abbrs = teams[1:-1].split(',')
            teams = list()
            # converting abbreviations into team objects
            for t in team_abbrs:
                team = NHLTeam.find(t)
                teams.append(team)
            else:
                if conference:
                    division = NHLDivision(division_name, season, teams, conference)
                else:
                    division = NHLDivision(division_name, season, teams)
                session.add(division)
                
                ids = "{" + ",".join([str(t.team_id) for t in teams])  + "}"
                if conference:
                    output = "%s Division (%s Conference):\t%d\t%s" % (division_name, conference, season, ids)
                else:
                    output = "%s Division:\t%d\t%s" % (division_name, season, ids)
                print output
        else:
            session.commit()
    except:
        session.rollback()

if __name__ == '__main__':
    create_divisions(r"nhl_division_config.txt")

Now if we finally want to determine the league configuration for a given season we can do so by linking division data and the year in question by providing the NHLDivision object definition with the following class method:

    @classmethod
    def get_divisions_and_teams(cls, year):
        session = Session()
        # finding all divisions for this season
        divs = session.query(NHLDivision).filter(
            NHLDivision.year == year).all()
        # linking divisions and teams
        for d in divs:
            teams = list()
            for team_id in d.teams:
                team = NHLTeam.find_by_id(team_id)
                teams.append(team)
            print "%s Division:" % d.division_name
            for t in teams:
                print "\t", t

if __name__ == '__main__':
    NHLDivision.get_divisions_and_teams(2015)

Accordingly this results in:

Pacific Division:
        Anaheim Ducks
        Arizona Coyotes
        Calgary Flames
        Edmonton Oilers
        Los Angeles Kings
        San Jose Sharks
        Vancouver Canucks
Central Division:
        Chicago Blackhawks
        Colorado Avalanche
        Dallas Stars
        Minnesota Wild
        Nashville Predators
        St. Louis Blues
        Winnipeg Jets
Atlantic Division:
        Boston Bruins
        Buffalo Sabres
        Detroit Red Wings
        Florida Panthers
        Montreal Canadiens
        Ottawa Senators
        Tampa Bay Lightning
        Toronto Maple Leafs
Metropolitan Division:
        Carolina Hurricanes
        Columbus Blue Jackets
        New Jersey Devils
        New York Islanders
        New York Rangers
        Philadelphia Flyers
        Pittsburgh Penguins
        Washington Capitals

Class definitions for both NHLTeam and NHLDivision are available via GitHub. In the next installment of this series we will have a look at the table and class definition for NHL player data.