Oct 13, 2017

Let’s up the ante from last time and introduce PostgreSQL into the mix, enabling us to add tv shows to a database table. What is special about this set up is that we are using a separate Docker container for Python, PostgreSQL, and nginx, which means that as long as you have Docker set up on a server you could deploy this immediately without any other external dependencies.

If you haven’t ever used Python, nginx, and Docker together, check out my previous post for more information about setting up basic API prior to PostgreSQL being added.

Sneak Peak

Below is a sneak peak of the bare bones of the TV app I am creating. The idea is you can add all of your favorite shows and it will list the previous and next episode air dates of currently airing shows, helping you keep track of what time and when they are. This Angular app enables adding shows with services that connect to the API we will build in this post.


Here is a rough outline of what I will go over in this post:

  1. PostgreSQL with Docker
  2. Full Docker Setup
  3. Installing Python Dependencies
  4. psycopg2
  5. Parser/API Updates
  6. Postman

A future post will have this app polished up more with user authentication as well as the front facing Angular app. For now let’s just play with Docker, PostgreSQL, and Python.

PostgreSQL with Docker

Setting this up is ridiculously easy and amazing. What is great about setting up your database with Docker is you don’t have to set anything up on a new server, you can go right to running the app or migrating data. What I will do here is add some code to the docker-compose.yml file to define the PostgreSQL database:

    restart: always
    image: postgres
      POSTGRES_USER: 'postgres'
      - /var/lib/postgresql/data
      - ./db/init.sql:/docker-entrypoint-initdb.d/init.sql

Nothing too crazy here, we are pulling the postgres image and setting up the username and password – you can change these to whatever you’d like. There’s not any real risk in having an insecure password since the 5432 port that PostgresSQL runs on is internal to the Docker network and not external. All traffic is routing through nginx on port 80, remember. So any attempts to connect to the database on port 5432 will be met with failure.

Note the volumes section, it contains both the data folder as well as an entry point for an SQL file that creates the database tables. Both of these volumes are persistent unless you use the command docker-compose down -v. See that file below, it is in the /db/ folder labeled init.sql:

            show_name varchar,
            show_id integer)

What happens is when we create the container network this file will be copied to the initdb entry point and run during database initialization. Hoorah!

Full Docker Setup

Below you can find the full docker-compose.yml file with the Python and nginx containers defined as well:

version: '2'
    restart: always
    image: postgres
      POSTGRES_USER: 'postgres'
      - /var/lib/postgresql/data
      - ./db/init.sql:/docker-entrypoint-initdb.d/init.sql
    restart: always
      context:  ./app
      dockerfile: Dockerfile
      - "8080"
    restart: always
      context:  ./nginx
      dockerfile: Dockerfile
      - "80:80"

Installing Python Dependencies

I have done some thinking since my last post and I realized it doesn’t make sense to work out of a virtual environment once you get to a certain point in the app, in part because you then lose a large benefit of using the container which is ensuring you are always running the app as it will be run while debugging. Because of that, my method of installing psycopg2 is very simple, just open up the /app/requirements.txt file from last post and add the single line:


Now, running ./env.sh from the /app/ directory will build a new environment image with psycopg2 installed. Good to go!


Now that we have our Python environment ready as well as a PostgreSQL database, it is time to write the code that will enable us to write and read from the database using psycopg2. You could also use something like SQLAlchemy if you want database abstraction.

I like to create a DB class that I can import into the main Flask app, see below db.py:

import psycopg2
class DB:
  def __init__(self):
      self.conn = psycopg2.connect("dbname='postgres' user='postgres' host='db' password='pass'")
      print('Connected to database')
  def addShow(self,show_id,show_name):
    id = self.get_show_id(show_name)
    if(id == []):
      cur = self.conn.cursor()
      cur.execute("""INSERT INTO Shows (show_id,show_name) VALUES (%s, %s)""",(show_id,show_name))
      print("Inserted into Shows", show_name,show_id)
      return show_id, show_name
      return id
  def get_show_id(self,show_name):
    cur = self.conn.cursor()
    cur.execute("""SELECT show_id,show_name FROM Shows WHERE LOWER(show_name) LIKE LOWER(%s)""",('%'+show_name+'%',))
    shows = cur.fetchall()
  def getAllShows(self):
    cur = self.conn.cursor()
    cur.execute("""SELECT show_id,show_name FROM Shows""")
    shows = cur.fetchall()

This code enables us to add shows to the database, list all of the shows, and also search for the show id by show name so we don’t have to always rely on the TvMaze database, speeding up our API significantly.

Parser/API Updates

Now that we can add shows to the database and retrieve the, let’s create the Python parser that actually interacts with the API and also the database in some basic ways, below is show_schedule.py which is functional in nature:

import requests
import json
import operator
def appendZero(str):
  if int(str) < 10:
    str = "0"+str
  return str
def show_id(show):
  urlshow = "%20".join(show.split(" "))
  base = 'http://api.tvmaze.com/singlesearch/shows?q='
  r = requests.get(base+urlshow)
  details = json.loads(r.text)
  id = details['id']
  name = details['name']
  return id,name
def get_details(ep,show):
  season = appendZero(str(ep['season']))
  episode = appendZero(str(ep['number']))
  name = show+" S"+season+"E"+episode+" "+ep['name']+" "+ep['airtime']
  href = "+".join(show.split(" "))+"+s"+season+"e"+episode
  _details = [ep['airdate'],name,href]
  return _details
def next_prev_ep(show_id):
  href = 'http://api.tvmaze.com/shows/'
  href = href + str(show_id) + '?embed[]=previousepisode&embed[]=nextepisode'
  r = requests.get(href)
  details = json.loads(r.text)
  next = []
  ep = details['_embedded']['previousepisode']
  if(details['status'] == 'Running'):
      ep = details['_embedded']['nextepisode']
  return next
def airing_shows(list):
  episodes = []
  for item in list:
    eps = next_prev_ep(item)
  return episodes

Compared to the previous post, this parser uses the database and also calls new TVMaze API locations that actually embed the next and previous episode, reducing the amount of requests required per show to 1/3 the original amount, significantly increasing the speed of the API.

And finally our app.py which contains all of the routes:

from flask import Flask, request, jsonify
from flask_restful import reqparse, Resource, Api
from show_schedule import airing_shows, show_id, next_prev_ep

from db import DB

app = Flask(__name__)
api = Api(app)
db = DB()
def options(self):
class Airing(Resource):
  def get(self):
    episodes = db.getAllShows()
    ids = [id[0] for id in episodes]
    _episodes = airing_shows(ids)
    return jsonify(_episodes)
class ShowID(Resource):
  def get(self,show):
    return jsonify(db.get_show_id(show))
  def post(self,show):
    return jsonify(id=sid,show=name)
class allShows(Resource):
  def get(self):
    return jsonify(db.getAllShows())
class NextPrev(Resource):
  def get(self,show):
    id, = db.get_show_id(show)
    if (id != []):
      episodes = next_prev_ep(id[0])
      return jsonify(episodes)
if __name__ == '__main__':
     app.run(host='', port=8080)

You can see we have some new routes that enable us to add a show by name to the database following the parser or get its ID by name from the database.


Once again, I would highly recommend using Postman to test requests before consuming your API with Angular. Here is an example of me sending a GET request to /airing/:

Future Posts

Future posts will get more into consuming this API with Angular and adding authentication. I previously wrote the SMS Marketing Tool which enabled me to also prepare a lot of the boilerplate required for authentication with multiple users. In order to have shows specific to each user, we will use a many-to-one relationship with two tables and then perform an inner join on the show id. Fun stuff!