DEV Community

loading...
Cover image for Flask Database - How to connect to SQLite

Flask Database - How to connect to SQLite

Sm0ke
#Automation, my favorite programming language
Originally published at blog.appseed.us ・7 min read

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!



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
   |
   |-- ************************************
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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   
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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
   |
   |-- ************************************
Enter fullscreen mode Exit fullscreen mode

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                     )
Enter fullscreen mode Exit fullscreen mode

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'
Enter fullscreen mode Exit fullscreen mode

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
   |
   |-- ************************************
Enter fullscreen mode Exit fullscreen mode

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()
[]
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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( )
Enter fullscreen mode Exit fullscreen mode

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]
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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


Flask Database Sample - SQLite 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
Enter fullscreen mode Exit fullscreen mode

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 
Enter fullscreen mode Exit fullscreen mode

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."
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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()
Enter fullscreen mode Exit fullscreen mode

Flask Database Sample - Inspect after Migration.


Thanks for reading! For more resources and support please access:

Discussion (5)

Collapse
brandonwallace profile image
brandon_wallace

Very nice!!

Collapse
polakshahar profile image
Shahar Polak

Great post!! thank you for your insights:)

Collapse
sm0ke profile image
Sm0ke Author

Ty!

Collapse
zolidev profile image
Zoltán Szőgyényi

Cool article. Thanks for writing it :)

Collapse
sm0ke profile image
Sm0ke Author

Glad you like it!