Question

Insert, Update, Delete

See SQLAlchemy’s ORM tutorial and other SQLAlchemy documentation for more information about modifying data with the ORM.

To insert data, pass the model object to db.session.add():

user = User()
db.session.add(user)
db.session.commit()

To update data, modify attributes on the model objects:

user.verified = True
db.session.commit()

To delete data, pass the model object to db.session.delete():

db.session.delete(user)
db.session.commit()

After modifying data, you must call db.session.commit() to commit the changes to the database. Otherwise, they will be discarded at the end of the request.

Select

See SQLAlchemy’s Querying Guide and other SQLAlchemy documentation for more information about querying data with the ORM.

Queries are executed through db.session.execute(). They can be constructed using select(). Executing a select returns a Result object that has many methods for working with the returned rows.

user = db.session.execute(db.select(User).filter_by(username=username)).scalar_one()

users = db.session.execute(db.select(User).order_by(User.username)).scalars()

Queries for Views

If you write a Flask view function it’s often useful to return a 404 Not Found error for missing entries. Flask-SQLAlchemy provides some extra query methods.

• SQLAlchemy.get_or_404() will raise a 404 if the row with the given id doesn’t exist, otherwise it will return the instance.

• SQLAlchemy.first_or_404() will raise a 404 if the query does not return any results, otherwise it will return the first result.

• SQLAlchemy.one_or_404() will raise a 404 if the query does not return exactly one result, otherwise it will return the result.

@app.route("/user-by-id/<int:id>")
def user_by_id(id):
    user = db.get_or_404(User, id)
    return render_template("show_user.html", user=user)

@app.route("/user-by-username/<username>")
def user_by_username(username):
    user = db.one_or_404(db.select(User).filter_by(username=username))
    return render_template("show_user.html", user=user)

You can add a custom message to the 404 error:

user = db.one_or_404(
    db.select(User).filter_by(username=username),
    description=f"No user named '{username}'."
)

Legacy Query Interface

You may see uses of Model.query or session.query to build queries. That query interface is considered legacy in SQLAlchemy. Prefer using the session.execute(select(...)) instead.

See Legacy Query Interface for documentation.