March 03, 2017 Adrien Agnel 11 min read

I have lately been attempting to develop a web app linked to a PostgreSQL database and despite all the tutorials available through the Internet, it has not been an easy task. So I have decided to gather all the sources or tips I have used to solve the errors I encountered and to provide with a boilerplate to help setting up a Flask app.

The objective of this post is to make it easier and faster for people to start using Flask and PostgreSQL.

If you have encountered any error in a related project please comment about it and explain how you solved it or provide any source that helped you.

By the end of this article you will, first, know that you are not alone encountering errors, second, find some answers to help you.

System

The code snippets have been tested with the following versions:

Flask 0.12

PostgreSQL 9.5

Python 2.7

Ubuntu 16.04

Please consider that when you reuse them.

What is needed to build the app?

Flask is a Python web developpement framework to build web applications. It comes with jinja2, a templating language for Python, and Werkzeug, a WSGI utility module.

PostgreSQL is an open source relational database system which, as its name suggests,

uses SQL.

SQLAlchemy is an Object Relational Mapper (ORM), it is a layer between

object oriented Python and the database schema of Postgres.

Alembic is a useful module to manage migrations with SQLAlchemy in Python. Migrations occur when one wants to change the database schema linked to the application, like adding a table or removing a column from a table. It can also be used to write or delete data in a table. Alembic enables developers not to manually upgrade their database and to easily revert any change: migrations go up and down. It is also useful to recreate databases from scratch, by following the migration flow.

Even if you don't use them directly, you will have to install libpq-dev, to communicate with Postgres backend, and psycopg2, a libpq wrapper in Python.

So many things, but how to use each of them?

Now, let's see how to connect the previous modules and software together. The good news is that almost everything is managed by itself.

Create an app.py file which will define and run the application. It is the entry point of the application. With Flask, it is as easy as importing the Flask class and initialize an instance with: app = Flask ( __name__ )

Add: if __name__ = '__main__' : app . run ( ) in app.py file and then enter python app.py in a terminal to get your app running. Easy, but it does not do many things yet...

So far, if you want something else than an error 404 when accessing the application, create the first route which will return Hello World! at the root of the application. To do so, add the following piece of code after the definition of the application instance. @app . route ( '/' ) def main ( ) : return 'Hello World!'

Set the application in debug mode so that the server is reloaded on any code change and provides detailed error messages, otherwise it should be restarted manually. In app.py , before app.run() : app . config [ 'DEBUG' ] = True

Initialize a database object from Flask-Alchemy with db = SQLAlchemy() to control the SQLAlchemy integration to the Flask applications. You might put it directly in the app.py or in another file usually called models.py . from flask_sqlalchemy import SQLAlchemy db = SQLAlchemy ( )

Configure Flask by providing the PostgreSQL URI so that the app is able to connect to the database, through : app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://DB_USER:PASSWORD@HOST/DATABASE' where you have to replace all the parameters in capital letters (after postgresq:// ). Find out more on URI definition for PostgreSQL here.

Back in app.py : POSTGRES = { 'user' : 'postgres' , 'pw' : 'password' , 'db' : 'my_database' , 'host' : 'localhost' , 'port' : '5432' , } app . config [ 'SQLALCHEMY_DATABASE_URI' ] = 'postgresql : // % ( user ) s : \ % ( pw ) s@ % ( host ) s : % ( port ) s / % ( db ) s' % POSTGRES

You also have to connect your SQLAlchemy object to your application with db.init_app(app) ,

to make sure that connections will not leak. To do so, you first have to import db in app.py . from models import db db . init_app ( app )

Your models.py file should include the definition of classes which define the models of your database tables. Such classes inherit from the class db.Model where db is your SQLAlchemy object. Further, you may want to define models implementing custom methods, like an home-made __repr__ or a json method to format objects or export it to json. It could be helpful to define a base model which will lay the ground for all your other models: class BaseModel ( db . Model ) : """Base data model for all objects""" __abstract__ = True class YourModel ( BaseModel ) : """model for one of your table""" __tablename__ = 'my_table'

Finally, you have to add a manage.py file to run database migrations and upgrades using flask_script and flask_migrate modules with: from flask_script import Manager from flask_migrate import Migrate , MigrateCommand from app import app , db manager = Manager ( app ) migrate = Migrate ( app , db ) manager . add_command ( 'db' , MigrateCommand )

You want to be abble to run the migrations command from the manager , these last lines are needed in manage.py : if __name__ == '__main__' : manager . run ( )

Installing PostgreSQL & code samples

Install Postgres and other requirements.

sudo apt-get update sudo apt-get install postgresql postgresql-contrib libpq-dev pip install psycopg2 Flask-SQLAlchemy Flask-Migrate

Optionnaly, if you want to modify some parameters in postgres, like the password of the user:

sudo -i -u postgres psql postgres=# ALTER USER postgres WITH ENCRYPTED PASSWORD 'password';

Then, still in psql, create a database "my_database":

postgres=# CREATE DATABASE my_database;

Here is what your code could look like, the previous paragraphs should enable you to understand the role of each line, and even better you should be able to modify it without breaking your app ;) e.g. if you prefer defining your db object in app.py .

Overall, your application folder should look like:

application_folder ├─ app.py ├─ manage.py └─ models.py

app.py file, used to run the app and connect the database to it.

from flask import Flask from models import db app = Flask ( __name__ ) POSTGRES = { 'user' : 'postgres' , 'pw' : 'password' , 'db' : 'my_database' , 'host' : 'localhost' , 'port' : '5432' , } app . config [ 'DEBUG' ] = True app . config [ 'SQLALCHEMY_DATABASE_URI' ] = 'postgresql : // % ( user ) s : \ % ( pw ) s@ % ( host ) s : % ( port ) s / % ( db ) s' % POSTGRES db . init_app ( app ) @app . route ( "/" ) def main ( ) : return 'Hello World !' if __name__ == '__main__' : app . run ( )

models.py file to define tables models.

from flask_sqlalchemy import SQLAlchemy import datetime db = SQLAlchemy ( ) class BaseModel ( db . Model ) : """Base data model for all objects""" __abstract__ = True def __init__ ( self , * args ) : super ( ) . __init__ ( * args ) def __repr__ ( self ) : """Define a base way to print models""" return '%s(%s)' % ( self . __class__ . __name__ , { column : value for column , value in self . _to_dict ( ) . items ( ) } ) def json ( self ) : """ Define a base way to jsonify models, dealing with datetime objects """ return { column : value if not isinstance ( value , datetime . date ) else value . strftime ( '%Y-%m-%d' ) for column , value in self . _to_dict ( ) . items ( ) } class Station ( BaseModel , db . Model ) : """Model for the stations table""" __tablename__ = 'stations' id = db . Column ( db . Integer , primary_key = True ) lat = db . Column ( db . Float ) lng = db . Column ( db . Float )

manage.py file to run migrations.

from flask_script import Manager from flask_migrate import Migrate , MigrateCommand from app import app , db migrate = Migrate ( app , db ) manager = Manager ( app ) manager . add_command ( 'db' , MigrateCommand ) if __name__ == '__main__' : manager . run ( )

Finally, run database migrations and upgrades. In a terminal:

python manage.py db init

This will create a folder called migrations with alembic.ini and env.py files and a sub-folder migrations which will include your future migrations. It has to be run only once.

python manage.py db migrate

Generates a new migration in the migrations folder. The file is pre-filled based on the changes detected by alembic, edit the description message at the beginning of the file and make any change you want.

python manage.py db upgrade

Implements the changes in the migration files in the database and updates the version of the migration in the alembic_version table.

Common Mistakes - and Some Solutions

Could not connect to server

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not connect to server: Connection refused Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connections on port 5432?

The previous error stands when the declared host is "localhost" and the port is "5432" but it could be anything else depending on your context. It's likely your PostgreSQL server is not running or not allowing the chosen connection protocol. See PostgreSQL documentation about Client Connection Problems.

check that PostgreSQL server is running: ps -aux | grep "[p]ostgres" or service postgresql status

or start it if needed: /etc/init.d/postgresql start or service postgresql start - more information in the documentation.

or - more information in the documentation. if needed, modify the the config file indicated in the output of ps -aux , likely /etc/postgresql/X.X/main/postgresql.conf where X.X is your PostgreSQL version, to accept TCP/IP connections. Set listen_addresses='localhost' .

, likely where is your PostgreSQL version, to accept TCP/IP connections. Set . and check the pg_hba.conf file in the same repository, to make sure connections from localhost are allowed.

file in the same repository, to make sure connections from are allowed. restart PostgreSQL server: /etc/init.d/postgresql restart

No password supplied

OperationalError: fe_sendauth: no password supplied

To solve this issue, several options:

Change the uri of the database to something that does not require secured authentication, like : postgresql://database_name which changes the type of connection to the database.

which changes the type of connection to the database. Actually read the error message and provide a password, passing an empty string '' if your database user has no password will not work.

if your database user has no password will not work. Modify the connection rights associated with your database user in postgres configuration file named pg_hba.conf lileky located in /etc/postgresql/X.X/main where X.X is your PostgreSQL version. Writing something like:

lileky located in where is your PostgreSQL version. Writing something like: host all postgres 127.0.0.1 md5

Everything about the pg_hba.conf file here.

Class does not have a table or tablename specified

InvalidRequestError: Class does not have a table or tablename specified and does not inherit from an existing table-mapped class

This occurs when trying to define a base model. This is actually an abstract class, never instantiated as such but inherited, the parameter __abstract__ = True has to be set when defining the base model class so that SQLAlchemy does not try to create a table for this model as explained here.

class BaseModel ( db . Model ) : __abstract__ = True

Error when calling metaclass bases

TypeError: Error when calling the metaclass bases Cannot create a consistent method resolution order (MRO)

If you have created a base model (let's call it BaseModel ) which inherits from db.Model , and then use it to define other models which also inherit from db.Model , it is possible you mixed the inheritance order: BaseModel should be first and then db.Model so that the method resolution order is consistent and BaseModel methods are not overrided by db.Model methods which have previously been overrided by BaseModel methods. Find out more on stackoverflow.

Your class should begin with:

class YourModel ( BaseModel , db . Model ) :

No application bound to current context

Application not registered on db instance and no application bound to the current context

You have to link the application and the database object using db.init_app(app) or db.app = app (or both). Find out more on stackoverflow or in this blog post by Piotr Banaszkiewicz.

Alembic states that there is nothing to migrate

If it appears that Alembic does not detect change despite the few lines you just added to your models, then make sure that you did not defined several SQLAlchemy object: there should be just one db instance ( db = SQLAlchemy() ) that you import in the other files.

Let's say you wrote db = SQLAlchemy() in models.py , then in app.py you should have from models import db and nothing like a second db = SQLAlchemy()

Database is not up to date

alembic.util.exc.CommandError: Target database is not up to date.

Well, the last Alembic version available in the migrations/versions/ is not the one indicated in your database alembic_version table (created by Alembic). Run python manage.py db upgrade to implement the migrations changes in the database.

Some great resources