Flask-SQLAlchemy

3.1.1 · active · verified Thu Apr 09

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.

Common errors

```
ModuleNotFoundError: No module named 'flask_sqlalchemy'
```
cause This error occurs when the `flask-sqlalchemy` package is not installed in the Python environment being used, or there's an issue with the virtual environment or Python interpreter selection.

fix Install the package using pip: `pip install Flask-SQLAlchemy` (or `pip3 install Flask-SQLAlchemy` for Python 3 specific environments). Ensure your IDE or terminal is using the correct Python interpreter and virtual environment.
```
RuntimeError: Working outside of application context.
```
cause This error happens when you try to use Flask-SQLAlchemy's `db` object (or related functionalities like `db.create_all()`) without an active Flask application context, which is required for the extension to know which application it's associated with.

fix Wrap the code that interacts with `db` within an application context. For instance, `with app.app_context(): db.create_all()` for standalone scripts or ensure the code runs within a request context in a Flask application.
```
OperationalError: (sqlite3.OperationalError) no such table
```
cause This error typically indicates a mismatch between your SQLAlchemy models and the actual database schema; the table referenced in your code does not exist in the connected database, often because `db.create_all()` was not run or migration tools were not used after model changes.

fix Ensure your models are correctly defined and then create the database tables by calling `db.create_all()` within an application context. For schema changes in a production environment, use a migration tool like Flask-Migrate.
```
AttributeError: 'Session' object has no attribute 'commit'
```
cause This error occurs when attempting to call the `commit()` method directly on a raw SQLAlchemy session object, rather than through the Flask-SQLAlchemy `db.session` object.

fix Use the Flask-SQLAlchemy session to commit changes: `db.session.commit()`.
```
AttributeError: module 'sqlalchemy' has no attribute '__all__'
```
cause This error usually stems from a compatibility issue between Flask-SQLAlchemy and a newly installed or upgraded SQLAlchemy 2.0+, as the `__all__` attribute was removed in SQLAlchemy 2.0.

fix Ensure you are using Flask-SQLAlchemy version 3.0.2 or later, which includes fixes for SQLAlchemy 2.0 compatibility. If the issue persists, consider temporarily pinning your SQLAlchemy version to `sqlalchemy<2.0`.

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 →