Flask Database - How to configure and connect

Editor -

This article explains how to configure and connect Flask to an SQLite Database using SqlAchemy as a connection layer. The necessary code is added progressively on top of a simple codebase from zero to the migration phase. For simplicity, SQLite is used but settings can be upgraded with ease to use more powerful DBMS like MySQL or PostgreSQL - the source code for Flask Database Sample can be found on Github.

Thanks for reading! TL;DR;

Topics covered by this tutorial:

  • The initial codebase - the starting point
  • Add new dependencies - SqlAlchemy, Flask-Migrate
  • Integrate SqlAlchemy - ORM used to access the information
  • Configuration update - optional step
  • Add a new table ("Stats") and use Flask CLI to save data
  • Add a new column and "Migrate" the database
Flask Database - The Initial Codebase. 

Initial Codebase

The new code that implements the database access is added on top of a simple, open-source codebase that serves a few nice pages converted to Jinja Templates. The project can be downloaded directly from Github and used to experiment with all code samples presented from this point until the latest topic is covered.

< PROJECT ROOT >
   |
   |-- app/__init__.py         # App Constructor 
   |-- app/
   |    |-- static/            # Assets: JS, images and CSS
   |    |-- templates/          
   |         |-- includes/     # Page chunks, components
   |         |-- layouts/      # App Layouts (the master pages)
   |         |
   |       index.html          # The default page
   |         *.html            # All other pages 
   |
   |-- requirements.txt
   |-- run.py                  # WSGI Interface
   |
   |-- ************************************

Update Dependencies

The coded uses a few popular libraries that make all the hard work for us: SqlAlchemy, Flask-SqlAlchemy, and Flask-Migrate. Let's say a few words about each one:

SqlAlchemy - SQLAlchemy is the Python SQL toolkit and ORM (Object Relational Mapper) used by popular frameworks like Flaks, FastAPI and Django. This library helps us access the database using an object-oriented interface and avoid RAW SQL calls.

Flask-SQLAlchemy - is an extension that simplifies the usage of SqlAlchemy in Flask. The library provides useful defaults and extra helpers that make it easier to accomplish common tasks.

Flask-Migrate - open-source library that handles database migrations for Flask apps. The database operations are provided as command-line arguments under the flask db command.

# Contents of requirements.txt
sqlalchemy==1.4.5                <-- NEW    
flask_sqlalchemy==2.5.1          <-- NEW
flask_migrate==2.7.0             <-- NEW

Integrate SqlAlchemy

To use SqlAlchemy ORM a few simple steps must be followed: Import the library in the file that bootstrap our simple app, provide the minimal required configuration and bind the SqlAlchemy Object to the "app" object.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy <-- NEW
                                            
app = Flask(__name__)
                                            
# SQLAlchemy Settings
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///db.sqlite3' <-- NEW
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False           <-- NEW
                                                                   
db = SQLAlchemy (app) <-- NEW

The lines marked as "NEW" highlight the changes and we can see that only a few lines are required.

The most important configuration variable is the "SQLALCHEMY_DATABASE_URI" variable that specifies the database type to be used, credentials (user/pass) and the database name.

Configuration Update

The main file of our app looks nice but we can make a small improvement and move the configuration variables to an external file and get a cleaner code for our project. To code this optional change a new file should be created with all the configuration inside and use it later in the bootstrap file. Here are the changes:

Config.py - new file

class Config():
	
    # Set up the App SECRET_KEY
    SECRET_KEY = 'S_U_perS3crEt_KEY#9999'

    # This will create a file in <app> FOLDER
    SQLALCHEMY_DATABASE_URI = 'sqlite:///db.sqlite3'
    SQLALCHEMY_TRACK_MODIFICATIONS = False

App / __init__.py - App Bootstrapper

from flask import Flask

# Inject Flask magic
app = Flask(__name__)

# Load configuration
app.config.from_object('app.config.Config')        <-- NEW

# Construct the DB Object (SQLAlchemy interface)
db = SQLAlchemy (app)

At this point, the configuration is saved in a separate file and we can add more variables without affecting the readability of "__init__.py".

Codebase - the new structure:

< PROJECT ROOT >
   |
   |-- app/__init__.py      # App Constructor                 <-- UPDATED
   |-- app/
   |    |-- config.py       # App Config                      <-- NEW
   |    |
   |    |-- static/         # Assets: JS, images and CSS
   |    |-- templates/          
   |         |-- includes/  # Page chunks, components
   |         |-- layouts/   # App Layouts (the master pages)
   |         |
   |       index.html       # The default page
   |         *.html         # All other pages 
   |
   |-- requirements.txt
   |-- run.py               # WSGI Interface
   |
   |-- ************************************

Add New Table

With SqlAlchemy in place, we can add a new table and configure the app to use it. The necessary steps are:

  • Define the new table in app/models.py
  • Update __init__.py to use it
  • Use Flask CLI to create the table and the database

Models.py - new file

from app import db

class Stats(db.Model):

    id          = db.Column(db.Integer,   primary_key=True )
    month       = db.Column(db.String(64),    unique=True  )
    sold_units  = db.Column(db.Integer                     )

App / __init__.py - Update

from flask import Flask

# Inject Flask magic
app = Flask(__name__)

...

# Import routing to render the pages
from app import views, models          <-- UPDATED to include 'models'

Codebase - the new structure:

< PROJECT ROOT >
   |
   |-- app/__init__.py      # App Constructor                 <-- UPDATED
   |-- app/
   |    |-- config.py       # App Config                      
   |    |-- models.py       # App Config                      <-- NEW
   |    |
   |    |-- static/         # Assets: JS, images and CSS
   |    |-- templates/          
   |         |-- includes/  # Page chunks, components
   |         |-- layouts/   # App Layouts (the master pages)
   |         |
   |       index.html       # The default page
   |         *.html         # All other pages 
   |
   |-- requirements.txt
   |-- run.py               # WSGI Interface
   |
   |-- ************************************

The new step is to create the database and the new "Stats" table using the Flask CLI.

$ flask shell
Python 3.8.4 ...
App: app [development]
>>> from app import db
>>> db.create_all()  
>>>
>>> from app.models import Stats
>>> Stats.query.all()
[]

The above code snippet does the following:

  • Invoke db object (SqlAlchemy interface)
  • Call create_all() SqlAlchemy helper to create all tables
  • Import into the CLI context Stats ORM object
  • List all rows via helper query.all()

Obviously, we have an empty list - no rows defined so far. Let's create new records using the CLI:

>>> from app.models import Stats
>>> Stats.query.all()
[] # No rows yet
>>> 
>>> # Define a new object
>>> ian = Stats(id=1, month='Jan', sold_units=540) 
>>> db.session.add(ian) # Add the new object to the DB Session
>>> db.session.commit() # Save changes in the database
>>> 
>>> Stats.query.all()
[Jan - 540] # we have an object now

To speed up the process and combine "session.add()" and "session.commit()" we can code a helper inside the model class:

# Contents of `app/models.py`
...
class Stats(db.Model):
...
    def save(self):

        # Added object to db session    
        db.session.add ( self )

        # Commit changes (save on database)
        db.session.commit( )
...

Now we can use the helper to save the object using  a single line:

>>> # Define the new object
>>> febr = Stats(id=2, month='Feb', sold_units=480) 
>>>
>>> # Call the new helper  
>>> febr.save() 
>>> 
>>> Stats.query.all()
[Jan - 540, Feb - 480]

In the same way we can add more information:

>>> feb = Stats(id=2,  month='Feb', sold_units=480)
>>> mar = Stats(id=3,  month='Mar', sold_units=430)
>>> apr = Stats(id=4,  month='Apr', sold_units=550)
>>> may = Stats(id=5,  month='May', sold_units=530)
>>> jun = Stats(id=6,  month='Jun', sold_units=453)
>>> jul = Stats(id=7,  month='Jul', sold_units=380)
>>> aug = Stats(id=8,  month='Aug', sold_units=434)
>>> sep = Stats(id=9,  month='Sep', sold_units=568)
>>> oct = Stats(id=10, month='Oct', sold_units=610)
>>> nov = Stats(id=11, month='Nov', sold_units=700)
>>> dec = Stats(id=12, month='Dec', sold_units=900)

To visualize the changes we can use SQLiteBrowser,  a free tool to open the SQLite database:

SQLite - DB Inspect.

DB Migration

The meaning of "migration" term in this context is when we modify an existing database by adding a new table or alter an existing table: add/remove columns, change column type.  To plain the concept, we will add a new table to the "Stats" table. Changes required by this phase of the project are listed below:

  • Add flask_migrate to the requirements.txt file
  • Update app/__init__.py to integrate the migrations
  • Update the "Stats" Model to includes the new column

App / __init__.py - Update

# Import Flask 
from flask import Flask
... 
from flask_migrate    import Migrate    <-- NEW

# Inject Flask magic
app = Flask(__name__)

# Enable migration for our application
Migrate(app, db)                        <-- NEW              

# Import routing to render the pages
from app import views, models

Stats Model - The new definition:

class Stats(db.Model):

    id          = db.Column(db.Integer,   primary_key=True )
    month       = db.Column(db.String(64),    unique=True  )
    sold_units  = db.Column(db.Integer                     )
    total_sales = db.Column(db.Integer                     )  <-- NEW

    def __init__(self, id, month, sold_units, total_sales):   <-- UPDATED
        self.id          = id
        self.month       = month
        self.sold_units  = sold_units
        self.total_sales = total_sales                        <-- NEW

How it works

Once Flask-Migrate is properly integrated we should run db init and db migrate to generate the initial state for our database.

$ # This command will create a migrations folder
$ flask db init
$ flask db migrate -m "Initial migration."

After this step is complete, we will add the new field (total_sales) to the Stats table and apply the new changes to the database.

$ flask db migrate -m "Stats - Added Total_Sales Column"
Generating ... migrations\versions\d26f9f5f6e4f_stats_added_total_sales_column.py ...  done
$ flask db upgrade
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> d26f9f5f6e4f, Stats - Added Total_Sales Column

From this point, we can use the new field and make a quick test via Flask CLI.

>>> # Import Stats model
>>> from app.models import Stats
>>>
>>> # Gets the first DB object  
>>> ian = Stats.query.all()[0]
>>>
>>> # Update the save the object
>>> ian.total_sales = 1000
>>> ian.save()
Inspect after migration - Flask Database. 
Thanks for reading! For more resources and support please access: