Flask app 1 - word count with PostgreSQL and Flask-SQLAlchemy

The deployed app looks like this:

In this tutorial, we set up a basic skeleton including a PostgreSQL database along with SQLAlchemy. We'll run the app via "mod_wsgi" (Apache).

Github source : akadrone-flask

We want to setup virtualenv.

$ python3 --version
Python 3.4.3

$ virtualenv -p python3 venv3
$ source venv3/bin/activate
(venv3) ~$ 

Actually, in this tutorial, I used Python 2.7.12 instead of Python 3.4.3.

Install the requirements using /var/www/html/akadrone/requirements.txt:

(venv)/var/www/html/akadrone$ pip install -r requirements.txt

To exit from virtualenv, just type deactivate.

/var/www/html/akadrone/aka.py:

from flask import Flask

app = Flask(__name__)

@app.route('/')
def hello():
    return "Hello World!"

@app.route('/')
def hello_name(name):
    return "Hello {}!".format(name)

if __name__ == '__main__':
    app.run()

Run the app locally to make sure everything works:

(venv)$ python aka.py

Test it out by adding a name after the URL via http://localhost:5000/bob, then we'll get the following in the browser:

Hello bob!

Note that we can directly execute an app via app.run() since Flask(more specifically, Werkzeug) has an internal WSGI server for test.

/etc/apache2/sites-available/akadrone.example.com.conf:

<VirtualHost *:80>
    ServerName www.akadrone.example.com
    ServerAlias akadrone.example.com
    ErrorLog /var/www/akadrone.com/logs/error.log
    CustomLog /var/www/akadrone.com/logs/access.log combined

    #WSGIDaemonProcess akadrone user=www-data group=www-data threads=5 python-path=/home/k/MySites/akadrone:/home/k/MySites/akadrone/venv/lib/python2.7/site-packages
    WSGIDaemonProcess akadrone user=www-data group=www-data threads=5 python-path=/var/www/html/akadrone:/var/www/html/akadrone/venv/lib/python2.7/site-packages
    WSGIScriptAlias / /var/www/html/akadrone/flaskapp.wsgi
    <Directory /var/www/html/akadrone>
       WSGIProcessGroup akadrone
       WSGIApplicationGroup %{GLOBAL}
       Order allow,deny
       Allow from all
    </Directory>
    Alias /static /var/www/html/akadrone/static
    <Directory /var/www/html/akadrone/static/>
       Order allow,deny
       Allow from all
    </Directory>

</VirtualHost>

The WSGI specification provides a standard and efficient method for dynamic web applications to communicate with web servers. mod_wsgi provides a method for simply deploying WSGI applications with Apache. Let's install it:

$ sudo apt-get install libapache2-mod-wsgi

We need the followings:

1. PostgreSQL (9.4)
2. Psycopg2 (2.6.1) - a Python adapter for Postgres
3. Flask-SQLAlchemy (2.1) - Flask extension that provides SQLAlchemy support
4. Flask-Migrate (1.8.0) - extension that supports SQLAlchemy database migrations via Alembic

Install postgresql:

$ sudo apt-get install postgresql postgresql-contrib
$ sudo apt-get install python-psycopg2
$ sudo apt-get install libpq-dev

We can run the command we'd like with the postgres account directly with sudo.

For instance, in the last example, we just wanted to get to a Postgres prompt. We could do this in one step by running the single command psql as the postgres user with sudo like this:

$ sudo -u postgres psql
[sudo] password for k: 
psql (9.5.4)
Type "help" for help.

postgres=# CREATE DATABASE wordcount_dev;
CREATE DATABASE

postgres=# \l
                                    List of databases
     Name      |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
---------------+----------+----------+-------------+-------------+-----------------------
 postgres      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
               |          |          |             |             | postgres=CTc/postgres
 template1     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
               |          |          |             |             | postgres=CTc/postgres
 wordcount_dev | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)

postgres=# \q
$ 

In order to use the newly created database within the Flask app, we to need to install a few things:

$ pip install psycopg2==2.6.1 Flask-SQLAlchemy===2.1 Flask-Migrate==1.8.0
$ pip freeze > requirements.txt

For passwordless access, we need to configure access in /etc/postgresql/9.5/main/pg_hba.conf to trust:

# Database administrative login by Unix domain socket
local   all             all                                     trust

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

Then restart it:

$ sudo /etc/init.d/postgresql restart

Since we just have the postgres role configured within the database. We can create new roles from the command line with the createrole command. The --interactive flag will prompt us for the necessary values. For example, to create a role for k:

$ sudo -u postgres createuser --interactive
Enter name of role to add: k
Shall the new role be a superuser? (y/n) y

In models.py, we created a table (results) to store the word counts results.

from aka import db
from sqlalchemy.dialects.postgresql import JSON

class Result(db.Model):
    __tablename__ = 'results'

    id = db.Column(db.Integer, primary_key=True)
    url = db.Column(db.String())
    result_all = db.Column(JSON)
    result_no_stop_words = db.Column(JSON)

    def __init__(self, url, result_all, result_no_stop_words):
        self.url = url
        self.result_all = result_all
        self.result_no_stop_words = result_no_stop_words

    def __repr__(self):
        return '<id {}>'.format(self.id)

We imported the database connection(db) that we created in our aka.py file as well as JSON from SQLAlchemy's PostgreSQL dialects. JSON columns are fairly new to Postgres and are not available in every database supported by SQLAlchemy so we need to import it specifically.

Then, we created a Result() class and assigned it a table name of results and set the attributes that we want to store for a result:

1. the id of the result we stored
2. the url that we counted the words from
3. a full list of words that we counted
4. a list of words that we counted minus stop words

We want to manage database migrations to update a database's schema using Alembic which is part of Flask-Migrate.

The following manage.py does it:

import os
from flask_script import Manager
from flask_migrate import Migrate, MigrateCommand

from aka import app, db

app.config.from_object(os.environ['APP_SETTINGS'])

migrate = Migrate(app, db)
manager = Manager(app)

manager.add_command('db', MigrateCommand)


if __name__ == '__main__':
    manager.run()

In order to use Flask-Migrate, we imported Manager as well as Migrate and MigrateCommand to our manage.py file.

We also imported aka and db so we have access to them from within the script.

First, we set our config to get our environment created a migrate instance, with aka and db as the arguments, and set up a manager command to initialize a Manager instance for our app.

Finally, we added the db command to the manager so that we can run the migrations from the command line.

We should initialize Alembic to run the migrations:

$ python manage.py db init
db= <SQLAlchemy engine='postgresql://localhost/wordcount_dev'>
  Creating directory /home/k/MySites/akadrone/migrations ... done
  Creating directory /home/k/MySites/akadrone/migrations/versions ... done
  Generating /home/k/MySites/akadrone/migrations/env.py ... done
  Generating /home/k/MySites/akadrone/migrations/script.py.mako ... done
  Generating /home/k/MySites/akadrone/migrations/env.pyc ... done
  Generating /home/k/MySites/akadrone/migrations/README ... done
  Generating /home/k/MySites/akadrone/migrations/alembic.ini ... done
  Please edit configuration/connection/logging settings in '/home/k/MySites/akadrone/migrations/alembic.ini' before
  proceeding.

As we can see from the output, we have a new folder called migrations in the project. This holds the setup necessary for Alembic to run migrations against the project. Inside of migrations, we will see that it has a folder called versions, which will contain the migration scripts as they are created.

migrations/
|-- alembic.ini
|-- env.py
|-- env.pyc
|-- README
|-- script.py.mako
|-- versions

Let's create our first migration by running the migrate command:

$ python manage.py db migrate
db= <SQLAlchemy engine='postgresql://localhost/wordcount_dev'>
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table 'results'
  Generating /home/k/MySites/akadrone/migrations/versions/c09a10786652_.py ... done

Now we see in our versions folder there is a migration file. This file is auto-generated by Alembic based on the model.

migrations/
...
|--versions
    |-- c09a10786652_.py
    |-- c09a10786652_.pyc

Let's apply the upgrades to the database using the db upgrade command:

$ python manage.py db upgrade
db= <SQLAlchemy engine='postgresql://localhost/wordcount_dev'>
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 21a5c8b1cac8, empty message

Our database is now ready to use:

$ sudo -u postgres psql
[sudo] password for k: 
psql (9.5.4)

postgres-# \c wordcount_dev
You are now connected to database "wordcount_dev" as user "postgres".
wordcount_dev-# \dt
            List of relations
 Schema |      Name       | Type  | Owner 
--------+-----------------+-------+-------
 public | alembic_version | table | k
 public | results         | table | k
(2 rows)

wordcount_dev-# \d results
                                     Table "public.results"
        Column        |       Type        |                      Modifiers                       
----------------------+-------------------+------------------------------------------------------
 id                   | integer           | not null default nextval('results_id_seq'::regclass)
 url                  | character varying | 
 result_all           | json              | 
 result_no_stop_words | json              | 
Indexes:
    "results_pkey" PRIMARY KEY, btree (id)

wordcount_dev-#

Here are some of the files.

|-- aka.py
|-- app.py.saved
|-- config.py
|-- flaskapp.wsgi
|-- manage.py
|-- migrations
|   |-- alembic.ini
|   |-- env.py
|   |-- env.pyc
|   |-- README
|   |-- script.py.mako
|   |-- versions
|-- models.py
|-- requirements.txt

aka.py:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

app.config.from_object('config')
app_settings = app.config['APP_SETTINGS']
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://localhost/wordcount_dev'

db = SQLAlchemy(app)

from models import *

@app.route('/')
def hello():
    return "Hello World! with Postgresql and Flask-SQLAlchemy"

@app.route('/')
def hello_name(name):
    return "Hello {}!".format(name)

if __name__ == '__main__':
    app.run()

flaskapp.wsgi:

#!/usr/bin/python
import sys
import logging
logging.basicConfig(stream=sys.stderr)

sys.path.insert(0,"/home/k/MySites/akadrone")
from  aka import app as application

application.secret_key = 'akadrone secret key'

/etc/apache2/sites-available/akadrone.example.com.conf:

<VirtualHost *:80>
    ServerName www.akadrone.example.com
    ServerAlias akadrone.example.com
    ErrorLog /var/www/akadrone.com/logs/error.log
    CustomLog /var/www/akadrone.com/logs/access.log combined

    WSGIDaemonProcess akadrone user=www-data group=www-data threads=5 python-path=/home/k/MySites/akadrone:/home/k/MySites/akadrone/venv/lib/python2.7/site-packages
    WSGIScriptAlias / /var/www/html/akadrone/flaskapp.wsgi
    #<Directory /home/k/MySites/akadrone>
    <Directory /var/www/html/akadrone>
       WSGIProcessGroup akadrone
       WSGIApplicationGroup %{GLOBAL}
       Order allow,deny
       Allow from all
    </Directory>
    Alias /static /var/www/html/akadrone/static
    <Directory /var/www/html/akadrone/static/>
       Order allow,deny
       Allow from all
    </Directory>

</VirtualHost>

Flask by Example