By now you're surely familiar with the benefits of Python's core SQLAlchemy library: the all-in-one solution for basically anything database related. Like most major Python libraries, SQLAlchemy has been ported into a version specifically compatible with Flask, aptly named Flask-SQLAlchemy.

Similar to the core SQLAlchemy package, Flask-SQLAlchemy provides an ORM for us to modify application data by easily creating defined models. Regardless of what your database of choice might be, Flask-SQLAlchemy will ensure that the models we create in Python will translate to the syntax of our chosen database. Given the ease-of-use and one-size-fits-all  nature of Flask-SQLAlchemy, it's no wonder that the library has been the de facto database library of choice for Flask since the very beginning (seriously, is there even another option?)

Configuring Flask-SQLAlchemy For Your Application

There are a few essential configuration variables we need to set upfront before interacting with our database. As is standard, we'll be using a class defined in config.py to handle our Flask config:

import os


class Config:
    """Set Flask configuration vars from .env file."""
    
    # General
    TESTING = os.environ["TESTING"]
    FLASK_DEBUG = os.environ["FLASK_DEBUG"]

    # Database
    SQLALCHEMY_DATABASE_URI = os.environ.get("SQLALCHEMY_DATABASE_URI")
    SQLALCHEMY_TRACK_MODIFICATIONS = os.environ.get("SQLALCHEMY_TRACK_MODIFICATIONS")

Let's break these down:

  • SQLALCHEMY_DATABASE_URI: the connection string we need to connect to our database. This follows the standard convention: [db_type]+[db_connector]://[username]:[password]@[host]:[port]/[db_name]
  • SQLALCHEMY_ECHO: When set to 'True', Flask-SQLAlchemy will log all database activity to Python's stderr for debugging purposes.
  • SQLALCHEMY_TRACK_MODIFICATIONS: Honestly, I just always set this to 'False,' otherwise an obnoxious warning appears every time you run your app reminding you that this option takes a lot of system resources.

Those are the big ones we should worry about. If you're into some next-level database shit, there are a few other pro-mode configuration variables which you can find here.

By using the exact naming conventions for the variables above, simply having them in our config file will automatically configure our database connections for us. We will never have to create engines, sessions, or connections.

Initiating Flask-SQLAlchemy With Our App

As always, we're going to use the Flask Application Factory method for initiating our app. If you're unfamiliar with the term, you're going to find this tutorial to be confusing and pretty much useless.

The most basic __init__.py file for Flask applications using Flask-SQLAlchemy should look like this:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()


def create_app():
    """Construct the core application."""
    app = Flask(__name__, instance_relative_config=False)
    db.init_app(app)
    app.config.from_object('config.Config')

    with app.app_context():
        # Imports
        from . import routes
        
        # Create tables for our models
        db.create_all()

        return app

Note the presence of db and its location: this our database object being set as a global variable outside of create_app(). Inside of create_app(), on the other hand, contains the line db.init_app(app). Even though we've set our db object globally, this means nothing until we initialize it after creating our application. We accomplish this by calling init_app() within create_app(), and passing our app as the parameter. Within the actual 'application context' is where we'll call create_all(), which we'll cover in a bit.

If that last paragraph sounded like total gibberish to you, you are not alone. The Flask Application Factory is perhaps one of the most odd and poorly explained concepts in Python software development- my best advice is to not become frustrated, take the copy + paste code above, and blindly accept the spoon-fed nonsense enough times until it becomes second nature. That's what I did, and even as I worked through this tutorial, I still came across obnoxious quirks that caught me off-guard.

Take note of import we make inside of the application context called routes. This is one of two files we haven't written just yet: once we create them, our application file structure will look something like this:

my-app
├── /application
│   ├── __init__.py
│   ├── routes.py
│   ├── models.py
├── .env
├── config.py
└── wsgi.py

Creating Database Models

Create a models.py file in our application directory. Here we'll import the db object that we created in __init__.py. Now we can create database models by defining classes in this file.

A common example would be to start with a User model. The first variable we create is __tablename__, which will correspond to the name of the SQL table new users will be saved. Each additional variable we create within this model class will correspond a column in the database:

from . import db


class User(db.Model):
    """Model for user accounts."""

    __tablename__ = 'users'
    id = db.Column(db.Integer,
                   primary_key=True
                   )
    username = db.Column(db.String(64),
                         index=False,
                         unique=True,
                         nullable=False
                         )
    email = db.Column(db.String(80),
                      index=True,
                      unique=True,
                      nullable=False
                      )
    created = db.Column(db.DateTime,
                        index=False,
                        unique=False,
                        nullable=False
                        )
    bio = db.Column(db.Text,
                    index=False,
                    unique=False,
                    nullable=True
                    )
    admin = db.Column(db.Boolean,
                      index=False,
                      unique=False,
                      nullable=False
                      )
    
    def __repr__(self):
        return '<User {}>'.format(self.username)

Each "column" accepts the following attributes:

  • Data Type: Accepts one of the following: String(size), Text, DateTime, Float, Boolean, PickleType, or LargeBinary.
  • primary_key: Whether or not the column should serve as the primary key.
  • unique: Whether or not to enforce unique values for the column.
  • nullable: Denotes required fields.

With our first model created, you're already way closer to interacting with your database than you might think.

Creating Our First Entry

Let's create a user in our routes.py file.

from flask import request, render_template, make_response
from datetime import datetime as dt
from flask import current_app as app
from .models import db, User


@app.route('/', methods=['GET'])
def entry():
    """Endpoint to create a user."""
    new_user = User(username='myuser',
                    email='[email protected]',
                    created=dt.now(),
                    bio="In West Philadelphia born and raised, on the playground is where I spent most of my days",
                    admin=False
                    )
    db.session.add(new_user)
    db.session.commit()
    return make_response("User created!")

Check out how easy this is! All it takes to create a user is create an instance of the User class from models.py, add it to our session via db.session.add(new_user), and commit the changes with db.session.commit()! Let's see what happens when we run this app:

User Created!

That's what we like to see! If we access our database at this point, we can see that this exact record was created in our users table.

Querying Our New Data

Creating information is dope, but how can we confirm it exists? I've added a few things to routes.py to show us what's up:

from flask import request, render_template
from datetime import datetime as dt
from flask import current_app as app
from .models import db, User


@app.route('/', methods=['GET'])
def entry():
    """Endpoint to create a user."""
    new_user = User(username='myuser',
                    email='[email protected]',
                    created=dt.now(),
                    bio="In West Philadelphia born and raised, on the playground is where I spent most of my days",
                    admin=False
                    )
    db.session.add(new_user)
    db.session.commit()
    users = User.query.all()
    return render_template('users.html', users=users, title="Show Users")

The statement User.query.all() will return all instances of User in our database. I created a Jinja template to show us all records nicely:

{% extends "layout.html" %}

{% block content %}
  {% for user in users %}
    <ul id="user.username">
      <li>Username: {{ user.username }}</li>
      <li>Email: {{ user.email }}</li>
      <li>Created: {{ user.created }}</li>
      <li>Bio: {{ user.bio }}</li>
      <li>Admin: {{ user.admin }}</li>
    </ul>
  {% endfor %}
{% endblock %}

Thus, our app gives us:

We have liftoff!

So we can get a single user, but what about a whole table full of users? Well, all we need to do is keep changing the username and email address (our unique keys, to avoid a clash) when firing up the app, and each time it runs, it'll create a new user. Here's what comes back after running the app a few times with different values:

I Can't Believe It's Not Error Messages.

Here, Take All My Stuff

Sure, Flask-SQLAlchemy is great once you get going, but as we've already seen "getting set up" isn't always a walk in the park. This is one of those things that always seems to be wrong no matter how many times you've done it from memory.

As a parting gift, I've put the source for this tutorial up on Github for you to treasure and enjoy. No seriously, take it. Get it away from me. I'm done with Flask for today. I need to go play some Rocket League.

PS: Add me on PSN