Sm0ke

Posted on Apr 6, 2021 • Originally published at blog.appseed.us

Flask Database - How to connect to SQLite

#webdev #flask #python #appseed

Hello Coders,

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!

Flask Database Sample - source code (MIT License), Github
Flask Atlantis Dark - the same UI with more modules and features

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

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.

Jinja Atlantis Dark - Source Code
Jinja Atlantis Dark - LIVE Demo

< 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 - an 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' 
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False           

db = SQLAlchemy (app) <-- NEW

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:

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): 
        self.id          = id
        self.month       = month
        self.sold_units  = sold_units
        self.total_sales = total_sales      <-- NEW

How it works

Once Flask-Migrate is 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()