Skip to content
Logo Theodo

Developping a Flask Web App with a PostreSQL Database - Making all the Possible Errors

Adrien Agnel10 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:

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.

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.

No password supplied

OperationalError: fe_sendauth: no password supplied

To solve this issue, several options:

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

Adrien Agnel

Adrien Agnel

Web Developer at Theodo

Theodo France

Liked this article?