Monthly Archives: September 2015

An NHL hockey database using Python, PostgreSQL and SQLAlchemy (Pt. 1): Teams and Divisions

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:

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.

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

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

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.

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:

This gives you:

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:

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:

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:

Accordingly this results in:

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.