Importing NFL statistics into NFLPool

NFLPool has been live for almost two weeks – and hasn’t crashed (yet!) After the rush to get the site up and allow a user to make their picks before I left on vacation, there is one more large chunk of work to get to 1.0 release: calculate the score for all players every week of the NFL season.

I spent all of last week in the middle of Minnesota at a friend’s cabin. It was great to get away from both my day job and NFLPool, but I was loathe to ruin my daily coding streak on Github:

I did bring my laptop to the cabin, and the first couple of mornings I did some minor work, mostly starting the process to add docstrings to the code to document the different methods. Being a former documentation contributor to GNOME you would think that I would include documentation in both the code and the project, but no. I now understand why some developers don’t include documentation in the rush of getting something built. If you look closely, you’ll see in the second last column on the right of the screenshot I did stop coding for a few days and enjoyed my vacation.

Kelly’s help was invaluable (again) in creating the data model to store the picks. There are three tables used. In my last post, I had mentioned that Kelly had me re-do the way we store a player’s picks. To do this, we created a “pick type” table. This is a reference table for the different kids of picks the players make:

  • Individual Player Stats (passing leader, rushing leader, etc.)
  • Team Stats (Division winners, 2nd place, last place)
  • Wildcard playoff teams
  • Tiebreaker

class PickTypes(SqlAlchemyBase):
tablename = 'PickTypes'
pick_id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True, autoincrement=True)
name = sqlalchemy.Column(sqlalchemy.String)

When a player makes their picks, it’s stored in the PlayerPicks database table:

class PlayerPicks(SqlAlchemyBase):
tablename = 'PlayerPicks'
pick_id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True, autoincrement=True)
user_id = sqlalchemy.Column(sqlalchemy.String, sqlalchemy.ForeignKey(''))
season = sqlalchemy.Column(sqlalchemy.Integer, index=True)
date_submitted = sqlalchemy.Column(sqlalchemy.DATETIME)
conf_id = sqlalchemy.Column(sqlalchemy.Integer, sqlalchemy.ForeignKey('ConferenceInfo.conf_id'))
division_id = sqlalchemy.Column(sqlalchemy.Integer, sqlalchemy.ForeignKey('DivisionInfo.division_id'))
rank = sqlalchemy.Column(sqlalchemy.Integer)
team_id = sqlalchemy.Column(sqlalchemy.Integer, sqlalchemy.ForeignKey('TeamInfo.team_id'))
multiplier = sqlalchemy.Column(sqlalchemy.Integer, default=1)
player_id = sqlalchemy.Column(sqlalchemy.Integer, sqlalchemy.ForeignKey('ActiveNFLPlayers.player_id'))
pick_type = sqlalchemy.Column(sqlalchemy.Integer, sqlalchemy.ForeignKey('PickTypes.pick_id'))

There are 41 total picks a player makes stored as 41 rows in the database:

The next part I just couldn’t wrap my head around. Kelly recommended I make a table with just four columns:

class WeeklyPlayerResults(SqlAlchemyBase):
tablename = 'WeeklyPlayerResults'
pick_id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True, autoincrement=True, index=True)
season = sqlalchemy.Column(sqlalchemy.Integer)
week = sqlalchemy.Column(sqlalchemy.Integer)
points_earned = sqlalchemy.Column(sqlalchemy.Integer)

It took me a few minutes, but pick_id in this table contains (embeds?) the user_id```, pick_type, season, and the pick data. It made my head hurt.

The other thing she pointed out to me is I did a poor job implementing the data model with some inconsistent column names across tables. For example, the Account table uses id as the column name to store a user’s unique id – this should have been user_id. In other tables I use a generic id to store a primary key. I spent some time yesterday fixing almost all of these, with the exception of Accounts. The id used in Accounts has too many references in the code already, but where I could I changed id to primary_key and some other changes to make the column names more explicit. I then had to update the database in production, which always makes me extremely nervous. But if I’m going to do it, now is the time when I only have a handful of users and can touch all of the tables outside of Account in production.

But anyway, it was time to start working on the ability import statistics every week of the NFL season from MySportsFeeds. Here is where things get ugly. Calculating a player’s score weekly for 17 weeks over the course of the season is going to take a lot of testing. My goal is to use the data from last year’s players in 2016 to compare the app’s scoring mechanism to what I did by hand. If I do it right, they should match (unless I made mistakes last year). But MySportsFeeds changed the API from version 1.0 in 2016 to 1.1 in 2017 and the URL to pull the stats differs. I had hoped to start a new branch called 2016 in git to do testing, but I then lost a day when I screwed up my branches trying to merge between master, 2016 and pyramid (where I do all of the development work before merging to master, which I treat as production). So I’m doing it all in my development branch, pyramid, which results in a big if / else statement:

if season == 2016:
response = requests.get(''
auth=HTTPBasicAuth(secret.msf_username, secret.msf_pw))
response = requests.get('' + str(season) +
auth=HTTPBasicAuth(secret.msf_username, secret.msf_pw))

After the season is over, I’ll probably remove all 2016 references like this, but for now, I need them.

I had hoped to make one API call to MySportsFeeds in for all five of the individual NFL player stats (rushing, passing, etc) but to save a little database space, I wrote five methods for each category. That was easy enough: it pulled the right data and saved it to the database.

But doing the team stats (division winners, points for leaders by conference, etc.) was not nearly as easy. The division_team_standings.json from MySportsFeeds is a dictionary containing two lists. The first list is for NFL conference (AFC and NFC) and each conference has a list inside it for the four divisions. There has to be a more Pythonic way of iterating over this than what I did. (Maybe using a generator?) But I just don’t have the Python knowledge yet. I have it working, but I do this four times, once for each division:

while x < len(team_data):
rank = (team_data[x]["teamentry"][1]["rank"])
team_id = (team_data[x]["teamentry"][1]["team"]["ID"])

        x += 1

Using x, y, z, and athis returns 8 results four times, which includes both conferences. Ugly, but it works.

But when I added this to the WeeklyTeamStats table, I was creating 96 rows – the 32 NFL teams three times. I only wanted 32 rows – and it took me three hours of reading through the SQLAlchemy documentation and examples on StackOverflow to figure out how to do an update to the table instead of an insert. That’s one way to learn.

Now that I can store NFL data, next steps include:

  • Add picks for the 8 players from the 2016 season to test results
  • Then add a method to iterate through those picks and figure out which picks a player made are unique and assign it a multiplier of two (for the unique bonus)
  • Lastly, and most importantly, then calculate each player’s score based on the real results. And figure out how to display them.

I have two weeks before the first week of the 2017 NFL season when NFLPool players will want to see their results. No problem. I think.

NFLPool is live

It’s been a long journey, but the first release of NFLPool is now live at

We’ll call it a 0.5 release. Features include:

  • New installation service:
    • Upon starting the Pyramid app for the first time, the database and all of its tables are created.
    • I’ve built an admin panel where the administrator log into and kicks off the installation service:
      • Downloads all of the team information from MySportsFeeds including NFL team names, conference, and division information and inserts it into the database.
      • The admin is then directed to the next page to enter the season year. This page is also used each year to start a new season. The NFL schedule is then downloaded from MySportsFeeds and entered into the database.
      • Lastly the admin then downloads all of the active players in the NFL and this is inserted into the database.
  • NFLPool
    • Users can register and create an account. Thanks to Python’s passlib module, the user password is hashed and salted over 150,000 times to keep it secure. A lot of the functionality of the site requires you to be logged in.
    • Once a user is logged in, they can make their picks for the new season.
      • I’ve hardcoded the time allowed to make your picks. Picks need to be submitted by 6:59 p.m. the date of the first game (Thursday night kickoff before Labor Day.)
      • The picks are displayed to the user for a drop down box for each pick. Lots of coding to make this happen to query the database and then only display the right information. For example, when making the AFC East Division winner picks, only the Bills, Dolphins, Jets and Patriots are available in the drop down box. I’m not going to pretend I’m even close to understanding Chameleon templates, but it’s pretty cool how you can iterate over a list and display it dynamically in the template page.
      • After a user makes their picks, they can view them from their account page. It took a lot of coding and help from my wife to make the SQL query in SQLAlchemy – the SQL query itself is four left joins in SQL (four OUTERJOIN) in SQLAlchemy. The #sqlalchemy IRC channel on Freenode was helpful in getting to done on the query. Using Chameleon templates, it’s pretty cool that I can dynamically create the pages for each season for the user to view their picks. Personally, I think it will be cool after we have two or three years of data. Unfortunately, I haven’t figured out a way to iterate over the object that’s created yet to display it correctly. It’s #1 on the to-do list to get fixed. Most users won’t be submitting their picks until the day or two before kickoff in three weeks, so I’m telling myself I have a little time.

Now the hard work begins. I’m only calling it a 0.5 release because I still have to write all the code to import all of the player and team stats each week of the season from MySportsFeeds and then write the code to calculate each player’s score. Once that is done, we’ll call it 1.0.

One of the fun (or frustrating things) as I’ve come close launching is all of the stuff I keep thinking of that I could add. For example, after sending out the email yesterday to last year’s players that the new site was up and the new process, I realized I should build an admin panel page to view all accounts created on the site. I was pretty darn happy that I could knock that out in ten or fifteen minutes – I feel that I’ve got a handle on Pyramid’s viewmodels and services. I wrote the query and the template page and voila. The hardest part was where to put the tal:repeat call in the Chameleon template and table to make it pretty! (Which I got right on the second try.) I have a whole list of to-do items grouped by the Admin Panel, the app, creating the standings calculations and a future wish list that I don’t even want to think about. That doesn’t even get into the fact that a lot of my code is definitely not Pythonic – I’m guessing I could be using list comprehensions and a number of things to make the code better. I still have a lot to learn, but it’s working.

Lastly, I won’t even go into deployment. It was difficult at best. Lots of challenges with getting the nginx web server to work and I still don’t think I have the systemd service working exactly right. I’m very grateful for the help in the Pyramid IRC channel on Freenode – the Pyramid developers were super helpful in troubleshooting nginx. But I do have the whole site running SSL and redirects working to make it secure, so I have that going for me.

NFLPool Progress 8/13/17

We’re a day away from launch. The NFL season starts in just over three weeks and players will need to submit their picks for NFLPool for the 2017 season. The majority of the picks won’t come until days before the season kickoff, but it would be nice to iron out some bugs… like the last one that might prevent me from launching for a few more days (but more on that later).

The last few days have been filled with big and little things. I had three big things to finish at the end of last week:

  • Get the email service working (welcome email and password resets)
  • Fix password resets (which I broke)
  • Add logging

Getting the email service working was harder than expected. To use a third party email service, like Amazon’s Simple Email Service, you have to update your website’s DNS records. My first problem was Amazon couldn’t create my account. After getting that resolved, I couldn’t get it to recognize my DNS changes no matter what I tried. I ended up going with Mailjet, which is working great. Just a bit more expensive, but it works.

Next was a day lost to password resets, and when I asked Kelly for help, needing a second set of eyes, it turns out the change I made to Pyramid’s routing broke it. I should have never, ever changed it. This was another case of me thinking I’m clever than I really am.

The good news with the routing, is that I think I might be starting to understand it, which is going to help with showing picks and standings once NFLPool has more than one season under its belt. More on that in a bit.

Lastly, I added logging. I followed the Python for Entrepreneurs class exactly, and now I’m using the logbook module successfully and am a happy Rollbar customer. (Thanks again, Talk Python!) Of course this means every time I break something while in development, I get an email from Rollbar that I have an error in my site, but it will be worth it.

I also finished a few smaller things, including importing the NFL schedule from MySportsFeeds and adding it to the installation process. This allowed me to write a check that if the first game has started to redirect the user to a page telling them it’s too late to submit picks. (Sorry, not sorry).

Once those things were complete, I moved on to the last big piece of functionality I needed to complete: showing the player the picks they just submitted. And things completely spiraled out of control after that.

Months ago Kelly sketched out what my data model should look like for storing a user’s picks.

I didn’t understand it then and it was the major reason development stalled this past spring. I thought I knew better and coded it the way that made sense to me. And of course, she was right. So this weekend was all about starting over. She helped me understand what I needed to do, and I had to add a number of new tables to the database and re-write dozens of methods to create the pics submission form and process it to store in the database. (But the good news is that if and when I convert NFLPool to MLBPool2, this fixes a major issue with MLBPool2 as users can change their picks at the All Star Break. I can now capture those changes and assign the points half value per the rules).

Then it was on to trying to show the user their picks. And that’s where we are – it’s hopelessly broken. I did figure out two cool things in Pyramid, which is auto-creating the pages for picks based on the season (this is related to routes from above) and after another quick query fix from Kelly, that works great. She was able to write a SQL query in SQL that does correctly pull all of a user’s picks, but I can’t seem to convert that a SQLAlchemy query that actually works. I’ve asked for help on Reddit and next up will be on StackOverflow. It’s all about the _and operator in SQLAlchemy which is way beyond my rudimentary understanding of Python and SQLAlchemy.

The last challenge is that every time I start working on something, whether it’s the admin installation process, user picks, or the user account page, I think of something cool that I want to add before I launch. I keep having to stop myself and instead I’ve started adding them to a to-do list in Wunderlist. Tom Clancy once wrote: If you don’t write it down, it never happened as Jack Ryan’s wife told him and it’s something that has always stuck with me. At some point I’ll convert them to Github issues and label them as bugs or feature enhancements and add them to milestones, but for now there is just too much to do.

And this is just to get the site launched and be able to have users register and submit their picks. I haven’t even started on the ability to compute a user’s score…. So much to do, so little time.

But I did change the homepage of NFLPool to reflect I’m launching tomorrow. It’s time.

NFLPool Progess 8/8/17 – Making a user’s picks work

I’m not even sure where to start from last update on Friday. The last 48 hours have been a roller coaster of up and downs of getting stuck and then figuring it out.

After completing the the functionality to create a new installation and add a new season to NFLPoo (or update to a new season) l, I needed to start working on letting a user make their picks for the new season.

First, I had to revisit my data model on where I store all the team information about each NFL team, including the division and conference each team lives in. During the new install process, I’ve added some code that now automatically assigns this. For the most part, this information is static. The NFL hasn’t changed what teams are in each division since the 90s and the only thing that may change at some point is if a team relocates and their abbreviation changes. For example, with the Chargers moving from San Diego to Los Angeles, they went from SDC to LAC. The Raiders are still a few years away from moving to Las Vegas, so I’m just going to leave that hard coded as is.

Now it was time to start coding the form that shows to users to make their picks. I couldn’t decide if this should live within its own controller or in the account controller. In the end, I’ve moved all of the picks to its own controller. But to do so, I need to re-watch all of the training videos on the routes, controllers and services to make this happen in Pyramid. It just wasn’t clicking for me. I was stuck Sunday, had a breakthrough, and by Monday night I was stuck again with a similar, but different, issue. I just couldn’t wrap my head around why and how you have to pass a dictionary from the controller and how it interacts with the data layer in the service. But finally, it clicked.

I finished wiring that up and now I needed to dig deep into Chameleon templates and Bootstrap forms with the Bootstrap CSS. Talk about getting stuck and being frustrated. The example used in the Python for Entrepreneurs course is pretty basic and I needed to pass a lot of values from the controller – one for each pick. I don’t know if there is a better way to do it, but I’ve figured it out by calling the service and a given method for each of the queries. For example, you have to pick which teams will finish 1st, 2nd or Last in a division (four teams in a division). This is one method per division. The method looks at the database and does a SELECT on the TeamInfo table WHERE the conference is equal to AFC and the division is equal to East and return the four teams in that conference and division. I got that working and was able to return two of the divisions. I didn’t want to do them all as I just needed to prototype that I could have two queries working and stick them in the form.

After I got that working, I needed to learn and understand how I could pass those attributes (the team name) inside of a dropdown box. Let me tell you – Google results for Chameleon templates, even when adding some of the TAL markup, are few and far between. You sure do see a lot of Powerpoint templates featuring chameleons in the search results for some reason.

There was so much swearing involved. I could iterate over the list, but then it would display four objects and not the team name. I kept at it, realizing I needed to fix the query, and now the team name did display. This was just inside a paragraph tag. I needed this to display inside a dropdown box and instead I made four dropdown boxes appear. (Progress? At least I was still iterating over the list.) And then I was stuck and asked my wife for help. She’s never used Python or Chameleon templates, but after about ten or fifteen minutes, she figured out the issue. To make matters worse, I was using the wrong Bootstrap button group, so we fixed that and she also figured out in Chameleon how to make the team_id display as the team_name and pass the team_id back to the form when submitting. I’m not going to even pretend I understand Chameleon templates and using more than these values – I understand you can do all kinds of Python code right in the template, but I don’t need it right now and it makes my head hurt.

That was huge. I then spent the last couple of hours before bed trying to figure out how to capture the user_id of the person currently logged in and making their picks, so I could submit that to the database so I know who actually made the picks. Lots of review of the BaseController from the training and I got it.

By that time it was past my bedtime, but I had the pick submission process working. I could grab the user_id of the person submitting the picks, the date and time the picks were submitted, and the picks themselves all grabbed from the form and passed to the database. The form also needs a lot of styling, but I’ll take usability first.

I’m so close to launching I can taste it – and with no time to spare. This week I need to finish coding the picks form and get all the choices in there, and then I need to add the password reset functionality to Accounts and add logging from Rollbar. It would be nice to log when a pick is submitted and get a notification, as well as any errors. Then it comes time to deployment, of which I have many thoughts, but that’s for another post.

Studying vs. Learning

I came across a thread on /r/LearnPython a few weeks ago that summed up my experience with Python. The thread was the typical “I’m learning Python, but I feel stuck and I’m not learning”. Someone replied with encouragement, and pointed out that studying Python is very different than learning Python and how it works.

I can relate. It’s been just over a year since I started down this path and almost two years (where does the time go!) since I decided I wanted to learn Python.

I started by buying a couple of books. That didn’t work for me at all – I don’t learn from reading and trying to apply the exercises. The Python for Everybody specialization at Coursera that I took last summer, with the video lectures and online exercises was the first time I felt I was actually learning. But in actuality, I was still studying. The same with the Python Jumpstart course. Having gone through Python for Entrepreneurs, and finally starting to apply what I’ve studied, I feel that I’m now learning.

Even though a large chunk of what I’m building in the NFLPool web app using Pyramid is copying and pasting from Python for Entrepreneurs, I’m starting to feel confident that the concepts are sticking.

As of Wednesday, I had a couple of the bigger components working. Users can visit the website and register, and the site recognizes when they’re logged in and out. I also created an admin panel that creates a new install and imports the NFL team information into one of the database table. This information is static and never needs to be updated. It uses a GET / POST / REDIRECT pattern and then redirects to the next page to insert all of the active NFL players for the new season.

Yesterday I sat down and needed to change this. Mr. Kennedy is always talking about the GET / POST / REDIRECT pattern , and though I thought I understood it, when I built the first one above, it took me a bit to get the redirect working. But I needed to change it so the next redirect would create a form to update the database for a new season and then redirect to import the active players.

I sat down and wrote the template and HTML code for the page, then wrote the controller for the GET / POST / REDIRECT routes, and then the service to interact with the database. I did this for the first time without referencing the training materials! The sense of accomplishment was huge. Not only was I writing code for the Pyramid web framework correctly, I was using requests and JSON to get the data MySportsFeed, manipulate it, and then put it in the database.

Of course, later yesterday, I wanted to write it so when you updated to a new season, it took a variable from a form rather than be hardcoded, and was stuck for two hours when it work. But it turned out it wasn’t the Python code – after my wife helped me for an hour, we realized it was the HTML form that had the error. So that was big, too – the Python code was right! It was just my lack of knowledge around HTML causing the latest headache.

Now I have two big things for the weekend:

  1. Re-write all of the requests so they’re not hardcoded and pass the season attribute from the database to the request so every year I don’t have to update the code.
  2. Right the controller and service for players to make their picks. This one is going to be tough – I’ll need to grab the user ID from their logged in session (which I’m not 100% sure how to do yet) and then grab a lot of stuff from the database and put it into a dropdown box for selection – which I also have no idea how to do. I feel ok that I know how to do the query, but putting the query results into a dropdown box is scary.

Either way, for the first time, I’m applying what I’ve studied – and I finally feel that after over a year I’m actually learning Python.