Flask Database - How to connect to SQLite
Tags: webdev, flask, python, appseed
Published: 2021-04-06 08:50:18

---
title: Flask Database - How to connect to SQLite
published: true
description: This tutorial explains how to configure Flask to manage an SQLite database using SqlAlchemy ORM - Free sample included
tags: webdev, flask, python, appseed
cover_image: https://raw.githubusercontent.com/app-generator/flask-database-sample/master/media/flask-database-sample-sqlite-view.png
canonical_url: https://blog.appseed.us/flask-database-how-to-create-sqlite/
---
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](https://github.com/app-generator/flask-database-sample) - source code (MIT License), Github
- [Flask Atlantis Dark](https://flask-atlantis-dark.appseed-srv1.com/) - 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

---

![Flask Database - The Initial Codebase.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4ifwn329knk977l91f7b.png)

--- 

## 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](https://github.com/app-generator/jinja-atlantis-dark) - Source Code
- [Jinja Atlantis Dark](https://jinja-atlantis-dark.appseed-srv1.com/) - LIVE Demo

---

```bash
< 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.

```txt
# 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.

---

```python
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](https://github.com/app-generator/flask-database-sample/blob/master/app/config.py) - new file

```python
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](https://github.com/app-generator/flask-database-sample/blob/master/app/__init__.py) - App Bootstrapper

```python
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:

```bash
< 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](https://github.com/app-generator/flask-database-sample/blob/master/app/models.py) - new file

```python
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

```bash
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:

```bash
< 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.

```python
$ 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`:

```python
>>> 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:

---

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

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

```python
>>> 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](https://sqlitebrowser.org/), a free tool to open the SQLite database:

---

![Flask Database Sample - SQLite Inspect.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/eyrjrz9959hoskrk0krd.png)

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


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


```python
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.

```bash
$ # 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.

```bash
$ 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.

```python
>>> # 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()
```

---

![Flask Database Sample - Inspect after Migration.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qecqk0lw5pgmjl2r5brn.png)

---

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

- More production-ready [Flask Starters](https://appseed.us/admin-dashboards/flask) - free & commercial projects
- More [Flask Templates](http://bit.ly/2ORcvl0) - provided by [Creative-Tim](https://bit.ly/3fKQZaL)