Flask-SQLAlchemy

3.1.1 · active · verified Sun Mar 29

Flask-SQLAlchemy is an extension for Flask that adds support for SQLAlchemy to your application. It simplifies using SQLAlchemy with Flask by setting up common objects and patterns for using those objects, such as a session tied to each web request, models, and engines. The current version is 3.1.1, and it maintains an active development status with regular patch and minor releases.

Warnings

breaking Flask-SQLAlchemy 3.0 introduced significant breaking changes. The session is now scoped to the current application context (instead of thread-local), requiring an active application context for `db.session` and `db.engine` access. `SQLALCHEMY_DATABASE_URI` no longer defaults to an in-memory SQLite database if unset. Minimum Flask version is 2.2, and minimum SQLAlchemy is 1.4.18.
Fix: Ensure an active Flask application context (e.g., `with app.app_context():`) when interacting with `db.session` or `db.engine`. Explicitly set `SQLALCHEMY_DATABASE_URI` in your Flask config. Update Flask to >=2.2 and SQLAlchemy to >=1.4.18.
breaking Flask-SQLAlchemy 3.1 dropped support for Python 3.7 and bumped the minimum required SQLAlchemy version to 2.0.16. It also removed previously deprecated code and the `SQLALCHEMY_COMMIT_ON_TEARDOWN` configuration key.
Fix: Upgrade Python to 3.8+ and SQLAlchemy to 2.0.16+. Remove any usage of `SQLALCHEMY_COMMIT_ON_TEARDOWN` from your configuration.
deprecated The `__version__` attribute of the Flask-SQLAlchemy extension instance is deprecated.
Fix: Use `importlib.metadata.version("flask-sqlalchemy")` or feature detection instead of `db.__version__`.
gotcha Attempting to call `commit()` directly on a SQLAlchemy session object (e.g., `session.commit()`) instead of the Flask-SQLAlchemy `db.session` object will result in an `AttributeError`.
Fix: Always use `db.session.commit()` to commit changes within a Flask-SQLAlchemy application.
gotcha Encountering 'OperationalError: (sqlite3.OperationalError) no such table' often indicates a mismatch between your database schema and SQLAlchemy models, or that `db.create_all()` was not called (or failed).
Fix: Ensure `db.create_all()` is called within an application context when your app starts (e.g., in a CLI command or a 'first run' check). If models have changed, consider using a migration tool like Flask-Migrate instead of repeatedly calling `create_all()`.
gotcha Using `len(Model.query.all())` to count records is inefficient as it fetches all data into memory before counting. This can lead to performance issues, especially with large tables.
Fix: For counting, use SQLAlchemy's `func.count()`. For example: `db.session.execute(db.select(func.count(User.id))).scalar_one()`.

Install

pip install Flask-SQLAlchemy Install Flask-SQLAlchemy

Imports

SQLAlchemy
```
from flask_sqlalchemy import SQLAlchemy
```

Quickstart

This quickstart demonstrates how to initialize Flask-SQLAlchemy, define a simple ORM model, create database tables, and perform basic CRUD (Create, Read, Update, Delete) operations within a Flask application context. It uses SQLite for simplicity and disables SQLALCHEMY_TRACK_MODIFICATIONS for better performance.

import os
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from sqlalchemy import Integer, String

# Configure a basic Flask app
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = os.environ.get('DATABASE_URI', 'sqlite:///project.db')
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False # Recommended to disable

class Base(DeclarativeBase):
    pass

db = SQLAlchemy(model_class=Base)
db.init_app(app)

# Define a simple model
class User(db.Model):
    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    username: Mapped[str] = mapped_column(String, unique=True, nullable=False)
    email: Mapped[str] = mapped_column(String)

    def __repr__(self):
        return f'<User {self.username}>'

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

    # Example usage: create, add, commit
    if not User.query.filter_by(username='testuser').first():
        new_user = User(username='testuser', email='test@example.com')
        db.session.add(new_user)
        db.session.commit()
        print(f"Added user: {new_user.username}")

    # Example usage: query all users
    users = db.session.execute(db.select(User)).scalars().all()
    print("Current users:")
    for user in users:
        print(f"- {user.id}: {user.username} ({user.email})")

    # Example usage: update a user
    user_to_update = User.query.filter_by(username='testuser').first()
    if user_to_update:
        user_to_update.email = 'updated@example.com'
        db.session.commit()
        print(f"Updated user: {user_to_update.username}'s email to {user_to_update.email}")

    # Example usage: delete a user
    # user_to_delete = User.query.filter_by(username='testuser').first()
    # if user_to_delete:
    #     db.session.delete(user_to_delete)
    #     db.session.commit()
    #     print(f"Deleted user: {user_to_delete.username}")

    # Verify changes
    remaining_users = db.session.execute(db.select(User)).scalars().all()
    print("Users after operations:")
    for user in remaining_users:
        print(f"- {user.id}: {user.username} ({user.email})")

view raw JSON →