Skip to main content
The application will use a SQLite database to store users and posts. Python comes with built-in support for SQLite in the sqlite3 module.

​
Why SQLite?

SQLite is convenient because it doesn’t require setting up a separate database server and is built-in to Python. However, if concurrent requests try to write to the database at the same time, they will slow down as each write happens sequentially.
Small applications won’t notice this. Once you become big, you may want to switch to a different database.

​
Connect to the Database

1

Create database connection function

Create flaskr/db.py and add the database connection code:
flaskr/db.py
import sqlite3
from datetime import datetime

import click
from flask import current_app, g


def get_db():
    """Connect to the application's configured database. The connection
    is unique for each request and will be reused if this is called
    again.
    """
    if 'db' not in g:
        g.db = sqlite3.connect(
            current_app.config['DATABASE'],
            detect_types=sqlite3.PARSE_DECLTYPES
        )
        g.db.row_factory = sqlite3.Row

    return g.db


def close_db(e=None):
    """If this request connected to the database, close the
    connection.
    """
    db = g.pop('db', None)

    if db is not None:
        db.close()
Key concepts:
  • g is a special object unique for each request, used to store data that might be accessed by multiple functions
  • current_app points to the Flask application handling the request
  • sqlite3.Row tells the connection to return rows that behave like dicts, allowing column access by name
2

Create the database schema

In SQLite, data is stored in tables and columns. Create flaskr/schema.sql with the SQL commands to create empty tables:
flaskr/schema.sql
DROP TABLE IF EXISTS user;
DROP TABLE IF EXISTS post;

CREATE TABLE user (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  username TEXT UNIQUE NOT NULL,
  password TEXT NOT NULL
);

CREATE TABLE post (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  author_id INTEGER NOT NULL,
  created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  FOREIGN KEY (author_id) REFERENCES user (id)
);
This creates two tables:
  • user table stores usernames and hashed passwords
  • post table stores blog posts with a foreign key reference to the author
3

Add initialization functions

Add functions to run the SQL commands to flaskr/db.py:
flaskr/db.py
def init_db():
    """Clear existing data and create new tables."""
    db = get_db()

    with current_app.open_resource('schema.sql') as f:
        db.executescript(f.read().decode('utf8'))


@click.command('init-db')
def init_db_command():
    """Clear existing data and create new tables."""
    init_db()
    click.echo('Initialized the database.')


sqlite3.register_converter(
    "timestamp", lambda v: datetime.fromisoformat(v.decode())
)
  • open_resource() opens a file relative to the flaskr package
  • click.command defines a command line command called init-db
  • register_converter tells Python how to interpret timestamp values from the database
4

Register with the application

The close_db and init_db_command functions need to be registered with the application instance. Add this function to flaskr/db.py:
flaskr/db.py
def init_app(app):
    """Register database functions with the Flask app. This is called by
    the application factory.
    """
    app.teardown_appcontext(close_db)
    app.cli.add_command(init_db_command)
  • app.teardown_appcontext() tells Flask to call that function when cleaning up after returning the response
  • app.cli.add_command() adds a new command that can be called with the flask command
5

Import and call from factory

Import and call this function from the factory in flaskr/__init__.py. Place the new code at the end of the factory function before returning the app:
flaskr/__init__.py
def create_app():
    app = ...
    # existing code omitted

    from . import db
    db.init_app(app)

    return app

​
Initialize the Database File

Now that init-db has been registered with the app, it can be called using the flask command: 
flask --app flaskr init-db
Output: 
Initialized the database.
There will now be a flaskr.sqlite file in the instance folder in your project.
If you’re still running the server from the previous page, you can either stop the server, or run this command in a new terminal. Remember to change to your project directory and activate the environment.

​
Complete Database Module

Here’s the complete flaskr/db.py file:
flaskr/db.py
import sqlite3
from datetime import datetime

import click
from flask import current_app
from flask import g


def get_db():
    """Connect to the application's configured database. The connection
    is unique for each request and will be reused if this is called
    again.
    """
    if "db" not in g:
        g.db = sqlite3.connect(
            current_app.config["DATABASE"], detect_types=sqlite3.PARSE_DECLTYPES
        )
        g.db.row_factory = sqlite3.Row

    return g.db


def close_db(e=None):
    """If this request connected to the database, close the
    connection.
    """
    db = g.pop("db", None)

    if db is not None:
        db.close()


def init_db():
    """Clear existing data and create new tables."""
    db = get_db()

    with current_app.open_resource("schema.sql") as f:
        db.executescript(f.read().decode("utf8"))


@click.command("init-db")
def init_db_command():
    """Clear existing data and create new tables."""
    init_db()
    click.echo("Initialized the database.")


sqlite3.register_converter("timestamp", lambda v: datetime.fromisoformat(v.decode()))


def init_app(app):
    """Register database functions with the Flask app. This is called by
    the application factory.
    """
    app.teardown_appcontext(close_db)
    app.cli.add_command(init_db_command)

Build docs developers (and LLMs) love

Get started for freeTalk to us