Introduction

Installation

Flask-SQLAlchemy is available on PyPI.

pip install -Y Flask-SQLAlchemy

Configuration

The only required Flask app config is the SQLALCHEMY_DATABASE_URI key. That is a connection string that tells SQLAlchemy what database to connect to.

# create the extension
db = SQLAlchemy()

# create the app
app = Flask(__name__)

# configure the SQLite database, relative to the app instance folder
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///project.db"

# initialize the app with the extension
db.init_app(app)

Example connection strings:

# SQLite, relative to Flask instance path
sqlite:///project.db

# PostgreSQL
postgresql://scott:tiger@localhost/project

# MySQL / MariaDB
mysql://scott:tiger@localhost/project 

Models

The db.Model class is used to define models. We subclass db.Model to define a model class. The db object makes the name in sqlalchemy and sqlalchemy.orm available for convenience, such as db.Column. The model will generate a table name by converting the CamelCase class name to snake_case.

Example Model:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String, unique=True, nullable=False)
    email = db.Column(db.String)

This table name "user" and will automatically be assigned to the model's table.

Create the Tables

After all the models and tables are defined, call SQLAlchemy.create_all() to create all the table schema in the database. This requires an application context.

with app.app_context():
    db.create_all()

Query the Data

We can use db.session to execute queries and modify the model data.

SQLAlchemy automatically defines an __init__ method for each model that assigns any keyword arguments to corresponding database columns and other attributes.

• ADD: db.session.add(obj) adds an object to the session, to be inserted.
• UPDATE: Modifying an object's attributes updates the object.
• DELETE: db.session.delete(obj) deletes an object.

Remember to call db.session.commit() after modifying, adding, or deleting any data.

Select Query

The db.session.execute(db.select(...)) constructs a query to select data from the database. Building queries is the main feature of SQLAlchemy. You'll usually use the Result.scalars() method to get a list of results, or the Result.scalar() method to get a single result.

NOTE: Model.query can be used to build queries, however, this is an older interface for queries that is considered legacy in SQLAlchemy. Prefer using db.session.execute(db.select(...)) instead.

Example of select command:

# get the first user with the matching username
user = db.session.execute(
        db.select(User).filter_by(username=username)
       ).scalar_one()

# get all the users ordered by username 
users = db.session.execute(
        db.select(User).order_by(User.username)
        ).scalars()

We use SQLAlchemy queries to query the database: docs.sqlalchemy.org/en/14/orm/queryguide.html

Models and Tables

Use the db.Model class to define models, or the db.Table class to create tables. Both handle Flask-SQLAlchemy's bind keys.

Models

You can refer to section above: Models.