Connect Flask to a Database with Flask-SQLAlchemy
Tags: flask, python, sql, software
Published: 2019-10-25 20:46:11

---
title: Connect Flask to a Database with Flask-SQLAlchemy
published: true
date: 2019-02-06 13:00:00 UTC
tags: Flask,Python,SQL,Software
series: Build Flask Python Apps
canonical_url: https://hackersandslackers.com/manage-database-models-with-flask-sqlalchemy
---

 ![Connect Flask to a Database with Flask-SQLAlchemy](https://storage.googleapis.com/hackersandslackers-cdn/2019/10/flask-sqlachemy.jpg)

By now you're surely familiar with the benefits of Python's [SQLAlchemy library](https://dev.to/hackersandslackers/pythonic-database-management-with-sqlalchemy-37m3): 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:

```python
from os import environ

class Config:
    """Set Flask configuration vars from .env file."""
    
    # General
    TESTING = environ["TESTING"]
    FLASK_DEBUG = environ["FLASK_DEBUG"]
    SECRET_KEY = environ.get('SECRET_KEY')

    # Database
    SQLALCHEMY_DATABASE_URI = environ.get("SQLALCHEMY_DATABASE_URI")
    SQLALCHEMY_TRACK_MODIFICATIONS = environ.get("SQLALCHEMY_TRACK_MODIFICATIONS")
```
<figcaption>config.py</figcaption>

There are two variables here which are specific to Flask-SQLAlchemy:

`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](http://flask-sqlalchemy.pocoo.org/2.3/config/).

<!--kg-card-begin: html-->
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.
<!--kg-card-end: html-->

## Initiating Flask-SQLAlchemy

As always, we're going to use the [Flask Application Factory method](https://hackersandslackers.com/structuring-your-flask-app/) 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:

```python
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
```
<figcaption>init.py</figcaption>

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:

```shell
my-app
├── /application
│ ├── __init__.py
│ ├── routes.py
│ ├── models.py
├── .env
├── config.py
└── wsgi.py
```
<figcaption>Our app structure</figcaption>

## 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:

```python
"""Data models."""
from . import db


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

    __tablename__ = 'flasksqlalchemy-tutorial-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)
```
<figcaption>models.py</figcaption>

Each "column" accepts the following attributes:

- **Data Type:** Accepts one of the following: `String(size)`, `Text`, `DateTime`, `Float`, `Boolean`, `PickleType`, or `LargeBinary`.
- **primary\_key** (optional): When set to **True** , this field will be serve as the primary key in the corresponding database table.  
- **foreign\_key** (optional): Sets a foreign key relationship to a field in another table. The target column must have a **unique** constraint in order to build a relationship between two tables.
- **unique** (optional): When set to **True** , the field will have a unique constraint added in the database.
- **nullable** (optional): ** ** Accepts a Boolean value which specifies whether or not this column can accept empty values.

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

**Protip:** SQLAlchemy models have a few reserved variables names which can be used to further configure the corresponding database tables. Here are a few examples:    
- ` __tablename__`: Set the name of the resulting table.
- ` __table_args__ = {"schema": "my_name"}`: Sets the Postgres schema to save the table under.
- ` __table_args__ = {'extend_existing': True}`: Specifies that a table with this name already exists in the target database. When this is set, your model will not override existing tables Thus, it is possible that the fields in your model might not match up with columns in your table.

## Creating Our First User

With our **User** model created, we now have what we need to create users in our database on the fly. Here we'll crack open `routes.py` and make a simple route to create a user. This route will accept two query string parameters: `?user=` will accept a username, and `&email=` will accept an email address:

```python
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 create_user():
    """Create a user."""
    username = request.args.get('user')
    email = request.args.get('email')
    if username and email:
        new_user = User(
            username=username,
            email=email,
            created=dt.now(),
            bio="In West Philadelphia born and raised, on the playground is where I spent most of my days",
            admin=False
        ) # Create an instance of the User class
        db.session.add(new_user) # Adds new User record to database
        db.session.commit() # Commits all changes
    return make_response(f"{new_user} successfully created!")

```
<figcaption>routes.py</figcaption>

Check out how easy this is! All it takes to create a user is create an instance of the user class, add it to our session via `db.session.add(new_user)`, and commit the changes with `db.session.commit()`! This is the appeal of powering applications with a database ORM like SQLAlchemy: instead of bothering with handling SQL statements, we can now manage our data as though they were objects. In the context of building an app, data models grant us a safe, easy way to modify data.

The **new\_user** variable creates an instance of **User** locally in memory. We pass keyword arguments to set the values of each field in our class (_username_, _email_, _created_, and _bio_).

`db.session.add(new_user)` stages the new user we just created to be added to the database. As with any database interaction in Python, the change won't actually be committed until we explicitly give the OK. In Flask-SQLAlchemy, this is done via `db.session.commit()`.

## Querying Our New Data

We've created a user, but how can we make sure that duplicate records aren't created? Since the _username_ and _email_ fields have the **unique** constraint, our database technically won't allow this to happen. If we don't add the proper validation, attempting to insert multiple unique values into our table will cause the app to crash. Instead of crashing, let's build in a nice error message!

```python
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 create_user():
    """Create a user."""
    username = request.args.get('user')
    email = request.args.get('email')
    if username and email:
        existing_user = User.query.filter(User.username == username or User.email == email).first()
        if existing:
            return make_response(f'{username} ({email}) already created!')
        new_user = User(
            username=username,
            email=email,
            created=dt.now(),
            bio="In West Philadelphia born and raised, on the playground is where I spent most of my days",
            admin=False
        ) # Create an instance of the User class
        db.session.add(new_user) # Adds new User record to database
        db.session.commit() # Commits all changes
    return make_response(f"{new_user} successfully created!")

```
<figcaption>routes.py</figcaption>

Before we create a new user, the `existing_user` variable queries our user table to see if a record already exists with the information provided. To query records which belong to a data model, we write a statement like this:

```
[MODEL].query.[METHOD_1].[METHOD_1].[FIRST or ALL]
```

`[MODEL]` is our data model class name, which we follow this up with `.query`. Now, we can construct a query using a number of methods at our disposal:

- `filter([CRITERIA])`: Return all records which match a given criteria.
- `order_by([MODEL].[COLUMN])`: Sort returned records by a column.
- `get([VALUE])`: Retrieve a record by its primary key.
- `.limit([INTEGER])`: Set a maximum number of records to retrieve.

In our case, we use the `.filter()` method to retrieve existing users which already have our desired username or email taken (we use `first()` in our query instead of `all()`, because we're expecting a maximum of 1 record). If a record exists, we deliver an error to the user:

```shell
myuser (myuser_972@example.com) already created!
```

### Loading Records Into a Template

For our final trick, let's see our Jinja plays along with database records. I've modified our route below to render a template called **users.html**. This template will create an HTML page which lists all existing users:

```python
...

@app.route('/', methods=['GET'])
def create_user():
    """Create a user."""
    ...
    users = User.query.all()
    return render_template(
        'users.html',
        users=users,
        title="Show Users"
    )

```
<figcaption>routes.py</figcaption>

The statement `User.query.all()` will return _all_ instances of `User` in our database. We pass all of these records into our template with `users=users`. Here's our Jinja template:

```html
{% 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 %}
```
<figcaption>users.html</figcaption>

When we start our app, we should see the following on the homepage:

![Connect Flask to a Database with Flask-SQLAlchemy](https://storage.googleapis.com/hackersandslackers-cdn/2019/09/flasksqlalchemy-test.png)<figcaption>We have liftoff!</figcaption>

Looks like we can display existing users! Let's add a few more records to make sure everything is working:

![Connect Flask to a Database with Flask-SQLAlchemy](https://res-1.cloudinary.com/hackers-and-slackers/image/upload/f_auto,q_auto/v1/images/Screen-Shot-2019-02-06-at-12.39.07-AM.png)<figcaption>I Can't Believe It's Not Error Messages.<b>™</b></figcaption>

### Here, Take All My Stuff

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](https://github.com/toddbirchard/flasksqlalchemy-tutorial) 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__

{% github https://github.com/hackersandslackers/flask-sqlalchemy-tutorial %}