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__.pyto use it - Use
Flask CLIto 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
dbobject (SqlAlchemy interface) - Call
create_all()SqlAlchemy helper to create all tables - Import into the CLI context
StatsORM 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.txtfile - Update
app/__init__.pyto integrate the migrations - Update the
StatsModel 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()
Thanks for reading! For more resources and support please access:
- More production-ready Flask Starters - free & commercial projects
- More Flask Templates - provided by Creative-Tim
Top comments (5)
Very nice!!
Great post!! thank you for your insights:)
Ty!
Cool article. Thanks for writing it :)
Glad you like it!