Installation

create a project dir an cd into it.

create an environment

OSX

python3 -m venv venv

Activate the environment

. venv/bin/activate

install flask

pip install Flask

There is a regular SQLAlchemy package but flask-sqlalchemy is a flask specific extension that provides some useful defaults and helpers for flask.

pip install flask-sqlalchemy

Flask app

In our project dir we will create a app.py file and add the following lines to it:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

Database (Dev)

SQLite as a dev db

To quickly add a database for dev we can use a sqlite db (which is a file on disc) We need to specify the URI for the database (where the database is located). We do this with app.config. To use a relative path to our database (relative to current file that is) we use 3 forward slashes.

config and create

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///site.db'

Then we need to create a database instance and pass in the app as an argument.

db = SQLAlchemy(app)

Declaring Models

The next step is to create the SQLAlchemy database structure. We do this by creatinging class which represent each db table.

Things to keep in mind Flask-SQLAlchemy docs

The baseclass for all your models is called db.Model. It’s stored on the SQLAlchemy instance you have to create. See Quickstart for more details.
Some parts that are required in SQLAlchemy are optional in Flask-SQLAlchemy. For instance the table name is automatically set for you unless overridden. It’s derived from the class name converted to lowercase and with “CamelCase” converted to “camel_case”. To override the table name, set the tablename class attribute.

Material type model

Lets declare our material model. Our material type model will have 2 columns and one relationship. In this example we will declare our model in our app.py file. (On larger projects it is wise to do this in separate files)

id : The primary key
name : The name of the material type. We will limit this to 20 characters, the name needs to be unique and it is required (nullable is set to False)
materials (relationship) : We create a relationship with the Material table. Below are some more info on relationships.

We also implement a dunder repr method so we get a nice and informative text if the object is printed.

class MaterialType(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20), unique=True, nullable=False)
    materials = db.relationship('Material', backref='material_type', lazy=True)

    def __repr__(self):
        return f"MaterialType('{self.name}')"

Relationships

The most common relationships are one-to-many relationships. Because relationships are declared before they are established you can use strings to refer to classes that are not created yet (for instance if Material defines a relationship to Material Type which is declared later in the file).

Relationships are expressed with the relationship() function. However the foreign key has to be separately declared with the ForeignKey class, as we will do in the material model.

What does db.relationship() do? That function returns a new property that can do multiple things. In this case we told it to point to the Material class and load multiple of those. How does it know that this will return more than one address? Because SQLAlchemy guesses a useful default from your declaration.

So what do backref and lazy mean? backref is a simple way to also declare a new property on the Material class. You can then also use my_material.material_type to get to the material type at that material. lazy defines when SQLAlchemy will load the data from the database:

Read more about relationships Flask-SQLAlchemy docs

Surface Property model

The Surface property will be similar to the material type.

class SurfaceProperty(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20), unique=True, nullable=False)
    materials = db.relationship('Material', backref='surface_property', lazy=True)

    def __repr__(self):
        return f"SurfaceProperty('{self.name}')"

Material model

id : The primary key
name : The name of the material
material_type_id : A forign key refering to the material type table
surface_property_id : A forign key refering to the surface property table

class Material(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), nullable=False)
    material_type_id = db.Column(db.Integer, db.ForeignKey('material_type.id'), nullable=False)
    surface_property_id = db.Column(db.Integer, db.ForeignKey('surface_property.id'), nullable=False)

    def __repr__(self):
        return f"Material('{self.name}', '{self.material_type.name}', '{self.surface_property.name}')"

Something to note here is that when we declare the foreign keys use the name of the table not the model Class name, for instance db.ForeignKey(‘surface_property.id’). As stated above if we dont give the tables a name Flask-SQLAlchemy will create one for us from the class name converted to lowercase and with “CamelCase” converted to “camel_case”

Create and populate the db

To create and populate our db lets create a python file init_db.py. Here we import the db and our model classes. Note that when we use db.session.add() we queue up data to be added to the db. To commit the data we use db.session.commit()

from app import db, MaterialType, SurfaceProperty, Material

# create the sqlite db

db.create_all()

# create material types

for name in ['metal', 'plastic', 'wood']:
    mt = MaterialType(name=name)
    db.session.add(mt)

# create surface properties

for name in ['shiny', 'rough', 'rusty']:
    sp = SurfaceProperty(name=name)
    db.session.add(sp)

for i in range(1, 4):
    m = Material(name=f'mat_{i}', material_type_id=i, surface_property_id=i)
    db.session.add(m)

db.session.commit()

Then we run the file and the db should be created. Note that if a db already exists you will get an error.

Lets query the model in the terminal. Activate the venv and launch python. To query the material we need to import the model declaration

from app import Material

Material.query.all()
# [Material('mat_1', 'metal', 'shiny'),

#  Material('mat_2', 'plastic', 'rough'),

#  Material('mat_3', 'wood', 'rusty')]


Material.query.first()
# Material('mat_1', 'metal', 'shiny')


Material.query.filter_by(material_type_id=1).all()
# [Material('mat_1', 'metal', 'shiny')]