NFLPool 2017 Recap

The NFLPool 2017 season wrapped up a month ago. The application performed admirably. Every week I logged in, downloaded the weekly statistics from MySportsFeeds, and the scoring calculations updated and posted on the standings page. I emailed the players every other week with the update and link to the standings (and the reminder that the team standings points would not be final until the end of the season due to MySportsFeeds shows division standings doesn’t account for the correct tiebreakers). Everything looked good and it was working as expected.

Or so we thought.

After week 17 was complete, I ran the update again and sent out the preliminary results. I worked with Brad at MySportsFeeds to update the division standings feed to rank the teams correctly according to the NFL’s tiebreakers. Then, one player caught that some of his individual leaders weren’t assigning points correctly. Digging in, I saw that in my picks, some of my players weren’t having their points assigned either.

I was so focused on the team standings and not individual player standings that there was a bug in the code. Week 1 worked correctly, but weeks 2-17 did not calculate the individual player performance correctly – and none of us caught it! Kelly was able to fix the SQL query she wrote and voila, everything worked! The only catch was that a week had gone by and the way that I programmed the standings page to display the title it now says “Week 18” instead of “Week 17”.

I’m pretty proud of myself for creating my first Python application (even if I didn’t write the SQL queries that do the scoring calculations). Everything worked great and I’ve learned so much about Python (and still have so much to learn). I have a list of things to improve and enhance for the 2018 season. In no particular order:

  • Update / fix the datetime function when a user submits their picks. One player got locked out too soon.
  • The traversal to the standings is – this shows the current standings. This needs to add a year, to both allow players to see a previous season’s history – such as – I’ll probably need to add a template page for standings then to list out all the available years as well as figure out what I want to do in the navigation.
  • Write a function that if the week is 17, call it final on the template page’s header.
  • Figure out how curl can call the get request to update the stats – it has to use my login to access the admin panel’s URL to call the get request and I don’t know how to do that in curl.
  • Lots of other enhancement plans, such as porting the app from SQLite to MySQL, but the above list are the big ones. I’m trying to make sure I capture any bugs or enhancements on Github.

Speaking of MySQL, NFLPool was always intended to do two things:
1. Automate our NFLPool league
2. Serve as a testbed for MLBPool2

I’m happy to say that MLBPool2 is now under active development. I have exactly 8 weeks from today before the Major League Baseball season kicks off, so I have about 6 weeks to get it working. And I’ve already been able to get it to work with MySQL! But MLBPoo2 development is a different blog post for later this week.

What’s next for NFLPool

NFLPool has been up for six weeks and everything is working great. I’ve been updating the standings every Tuesday without any issues. I’ve taken the last month to catch my breath after my massive coding spree to get it launched and I’ve been thinking about what’s next. I have a few options:

  • Add some tests (all Python projects should have tests written, right?)
  • Adding documentation in reStructured Text to NFLPool
  • Re-visit some of the Python trainings now that I have a basic grasp of Python and learn some more “advanced” concepts (generators, list comprehensions, etc.)
  • Work more on the NFLPool admin panel (manage users better, add the ability to manage if a user has paid for the season, and a few more ideas)
  • Port NFLPool from SQLite to MySQL
  • Start working on MLBPool2 based on the NFLPool codebase

The choice became an easy one as a couple of weeks ago I was asked by Brian Okken if I would like to review a copy of his new book, Python Testing with pytest.

I shared with Brian that I’m coming at this from the perspective of someone new to Python – I’ll be digging into the book this weekend and I’ll be blogging my progress on adding tests to my project with what I’ve learned.

Thanks Brian for the review copy and I’m excited to learn about testing in Python!

NFLPool: a (kind of) Post Mortem

I’m now four weeks into NFLPool being live. The week leading up to and after the launch of NFLPool for NFL week 1 was kind of a blur. I wish I had taken better notes or wrote down everything that happened, but now being a month into it, here are some random thoughts.

Submitting Picks

Somewhere in my code, I screwed up the function to disallow making picks. The code should have refused to let a user make picks after 7pm CST on the Thursday kickoff of the first game, about twenty minutes before the game starts. Of course, that morning I headed up north for my annual guy weekend and didn’t have a computer. I got a few panicked text messages that one user couldn’t make his picks. I was able to manually take care of it when I came back the following Monday, but I have to track it down and fix it. It’s hard coded and I thought I did a midnight stop to account for the fact my server is on UTC time, but it obviously didn’t work. Listening to a Talk Python podcast just last week with the famous Kenneth Reitz, I might have to look into his Maya package (time zones for humans).


I added a Twitter field to the user registration page and made it optional. I have an idea for the future that I might add Tweepy support that would DM a player every week after the points have been updated with their score and rank. It turns out that if you make the field unique in SQLite that leaving the field blank only allows one person to not fill in the Twitter field. Who knew? I had to remove the unique requirement.


Kelly continued to work on the scoring calculations while I was gone that weekend. She had it working when I came back, but using the 2016 data there was a difference in what NFLPool was showing and what I had done by hand last year. Sure enough, the program was right. I had mistakenly given the person who won twenty extra points that he didn’t earn and he should have come in second place. The kicker? The person who was in second place and should have won NFLPool in 2016 was me. The good news is that since it was me and my mistake, I wasn’t out the $300 delta I should have won in the pool. But it sure would have been nice to have pocketed the extra $300…


There were some other gotchas at launch. I had PHP working on Nginx before installing NFLPool, but however I did it using FastCGI was getting in the way of Pyramid serving NFLPool. Disabling FastCGI fixed Pyramid. I also created a systemd service to manage NFLPool. Of course SELinux doesn’t like it and I have to run it as sudo. I still need to fix that at some point.

There’s a couple little things I want to work on as well, but I’ve taken the last few weeks to recover. The big thing I need to address is updating the stats every week. Right now, it’s a manual process. I’ve got administrative access built in, so I visit the admin panel and hit a button to update the stats. I need to figure out how I can do this via a cron job by calling a POST request, but I have no idea how I can use curl to automate this as it requires being logged in. I have some ideas on how to do this, but it’s going to require some research and planning and when I do implement it, I’ll write it up.

Lastly, with NFLPool successfully launched and running for a few weeks, I submitted it to the Talk Python Student Showcase. I just can’t speak highly enough of the Python for Entrepreneurs training that Michael Kennedy created. I would have ended up creating scripts to calculate the points – not a full featured website. If you’re looking to learn Python, look no further then all of the courses available on Talk Python.

NFLPool might be in maintenance and enhancement mode now that it’s launched, but this isn’t the end of my Python journey. I can’t and won’t claim that I know Python. I’ve only scratched the surface and I still find myself following tutorials to get stuff done. I have a number of things I still want to add to NFLPool, and I’m sure as we play additional seasons even more ideas will come to me. Plus, I need to do it all over again for MLBPool2….

I Hate SQL (Or how my wife finished NFLPool)

I mentioned in my last post (and a couple others) how invaluable my wife has been in my journey to learn Python. That was turned up to 11 last week and I (we) started working on the scoring calculations.

I started to write the scoring calculations exactly as you would expect a newbie coder to – step by step. With the changes to the data model Kelly recommended, I still have a hard time wrapping my head around how we’ve abstracted the pick information. For scoring, the first goal is find the top three players in their position. I made a SQL query using SQLAlchemy which returned with the NFL Player’s player_id and the needed stats – in this case interceptions, which I knew had resulted in a tie that I would have to account for. This returned a tuple: (123456, 1). I then used a list comprehension (yay, me!) to turn that into a list and quickly realized I had to reverse the tuple to sort it by interception and not player ID. Thanks to Stack Overflow, I even found a function that added rank to the list that even accounted for standard competition ranking.

My wife looked at that, shook her head, and told me there had to be a better way and it could all be done in SQL. That started a process that lasted a few hours to figure out how to do SQL directly in the app instead of using SQLAlchemy. After she spent hours coding all the SQL queries, I now have a hundreds of line of code that I can read, but don’t really understand. But it works! As she doesn’t really know Python, I have some clean up to do to conform to PEP8, but I’m not complaining at all.

The other thing I did a terrible job with was naming my database columns – they weren’t consistent. I fixed all those over the weekend, but I also had to change them all in production. Changing the database in production scares the hell out of me, especially as a handful of players have already created their accounts, but thankfully haven’t submitted their picks yet. The first round of changes to the production database went fine and Sunday night I made the second round of changes to accommodate the player picks table so we could correctly do scoring.

And of course things broke. I had just arrived in the office when Rollbar notified me in Slack and via email that the production website had errors. I am so grateful that Michael Kennedy included adding Rollbar support in the Python for Entrepreneurs course. Within ten minutes I had fixed both the fact that the submit picks page requires you to be logged in (instead of displaying an error) – not sure how I forgot to add it to that page – and second I fixed the database table that needed an update to correctly let a player submit their picks. I need to improve my QA skills.

I need to finish the QA testing on the scoring calculations by simulating the 2016 season, but it’s looking good so far. We’re 36 hours from picks locking and the season kicking off and I’m excited.

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.