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('Account.id'))
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('https://api.mysportsfeeds.com/v1.1/pull/nfl/2016-2017-'
auth=HTTPBasicAuth(secret.msf_username, secret.msf_pw))
response = requests.get('https://api.mysportsfeeds.com/v1.1/pull/nfl/' + 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 weekly_msf_data.py 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 NFLPool.xyz.

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.