FLASK-SQLALCHEMY(1) Flask-SQLAlchemy FLASK-SQLALCHEMY(1)

flask-sqlalchemy - Flask-SQLAlchemy 3.0.5 [image]

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.

Flask-SQLAlchemy does not change how SQLAlchemy works or is used. See the SQLAlchemy documentation to learn how to work with the ORM in depth. The documentation here will only cover setting up the extension, not how to use SQLAlchemy.

Flask-SQLAlchemy simplifies using SQLAlchemy by automatically handling creating, using, and cleaning up the SQLAlchemy objects you'd normally work with. While it adds a few useful features, it still works like SQLAlchemy.

This page will walk you through the basic use of Flask-SQLAlchemy. For full capabilities and customization, see the rest of these docs, including the API docs for the SQLAlchemy object.

Flask-SQLAlchemy is a wrapper around SQLAlchemy. You should follow the SQLAlchemy Tutorial to learn about how to use it, and consult its documentation for detailed information about its features. These docs show how to set up Flask-SQLAlchemy itself, not how to use SQLAlchemy. Flask-SQLAlchemy sets up the engine, declarative model class, and scoped session automatically, so you can skip those parts of the SQLAlchemy tutorial.

Flask-SQLAlchemy is available on PyPI and can be installed with various Python tools. For example, to install or update the latest version using pip:

$ pip install -U Flask-SQLAlchemy

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 your Flask application object, load any config, and then initialize the SQLAlchemy extension class with the application by calling db.init_app. This example connects to a SQLite database, which is stored in the app's instance folder.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
# 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)

The db object gives you access to the db.Model class to define models, and the db.session to execute queries.

See Configuration for an explanation of connections strings and what other configuration keys are used. The SQLAlchemy object also takes some arguments to customize the objects it manages.

Subclass db.Model to define a model class. The db object makes the names 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.

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)

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

See Models and Tables for more information about defining and creating models and tables.

After all models and tables are defined, call SQLAlchemy.create_all() to create the table schema in the database. This requires an application context. Since you're not in a request at this point, create one manually.

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

If you define models in other modules, you must import them before calling create_all, otherwise SQLAlchemy will not know about them.

create_all does not update tables if they are already in the database. If you change a model's columns, use a migration library like Alembic with Flask-Alembic or Flask-Migrate to generate migrations that update the database schema.

Within a Flask view or CLI command, you can use db.session to execute queries and modify model data.

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

db.session.add(obj) adds an object to the session, to be inserted. Modifying an object's attributes updates the object. db.session.delete(obj) deletes an object. Remember to call db.session.commit() after modifying, adding, or deleting any data.

db.session.execute(db.select(...)) constructs a query to select data from the database. Building queries is the main feature of SQLAlchemy, so you'll want to read its tutorial on select to learn all about it. You'll usually use the Result.scalars() method to get a list of results, or the Result.scalar() method to get a single result.

@app.route("/users")
def user_list():
    users = db.session.execute(db.select(User).order_by(User.username)).scalars()
    return render_template("user/list.html", users=users)
@app.route("/users/create", methods=["GET", "POST"])
def user_create():
    if request.method == "POST":
        user = User(
            username=request.form["username"],
            email=request.form["email"],
        )
        db.session.add(user)
        db.session.commit()
        return redirect(url_for("user_detail", id=user.id))
    return render_template("user/create.html")
@app.route("/user/<int:id>")
def user_detail(id):
    user = db.get_or_404(User, id)
    return render_template("user/detail.html", user=user)
@app.route("/user/<int:id>/delete", methods=["GET", "POST"])
def user_delete(id):
    user = db.get_or_404(User, id)
    if request.method == "POST":
        db.session.delete(user)
        db.session.commit()
        return redirect(url_for("user_list"))
    return render_template("user/delete.html", user=user)

You may see uses of Model.query to build queries. This is an older interface for queries that is considered legacy in SQLAlchemy. Prefer using db.session.execute(db.select(...)) instead.

See Modifying and Querying Data for more information about queries.

For the most part, you should use SQLAlchemy as usual. The SQLAlchemy extension instance creates, configures, and gives access to the following things:

  • SQLAlchemy.Model declarative model base class. It sets the table name automatically instead of needing __tablename__.
  • SQLAlchemy.session is a session that is scoped to the current Flask application context. It is cleaned up after every request.
  • SQLAlchemy.metadata and SQLAlchemy.metadatas gives access to each metadata defined in the config.
  • SQLAlchemy.engine and SQLAlchemy.engines gives access to each engine defined in the config.
  • SQLAlchemy.create_all() creates all tables.
  • You must be in an active Flask application context to execute queries and to access the session and engine.

Configuration is loaded from the Flask app.config when SQLAlchemy.init_app() is called. The configuration is not read again after that. Therefore, all configuration must happen before initializing the application.

The database connection URI used for the default engine. It can be either a string or a SQLAlchemy URL instance. See below and Engine Configuration for examples.

At least one of this and SQLALCHEMY_BINDS must be set.

Changed in version 3.0: No longer defaults to an in-memory SQLite database if not set.

A dict of arguments to pass to sqlalchemy.create_engine() for the default engine.

This takes precedence over the engine_options argument to SQLAlchemy, which can be used to set default options for all engines.

Changed in version 3.0: Only applies to the default bind.

New in version 2.4.

A dict mapping bind keys to engine options. The value can be a string or a SQLAlchemy URL instance. Or it can be a dict of arguments, including the url key, that will be passed to sqlalchemy.create_engine(). The None key can be used to configure the default bind, but SQLALCHEMY_ENGINE_OPTIONS and SQLALCHEMY_DATABASE_URI take precedence.

At least one of this and SQLALCHEMY_DATABASE_URI must be set.

New in version 0.12.

The default value for echo and echo_pool for every engine. This is useful to quickly debug the connections and queries issued from SQLAlchemy.

Changed in version 3.0: Sets echo_pool in addition to echo.

If enabled, information about each query during a request will be recorded. Use get_recorded_queries() to get a list of queries that were issued during the request.

Changed in version 3.0: Not enabled automatically in debug or testing mode.

If enabled, all insert, update, and delete operations on models are recorded, then sent in models_committed and before_models_committed signals when session.commit() is called.

This adds a significant amount of overhead to every session. Prefer using SQLAlchemy's ORM Events directly for the exact information you need.

Changed in version 3.0: Disabled by default.

New in version 2.0.

Call db.session.commit() automatically if the request finishes without an unhandled exception.

Deprecated since version 3.0: Will be removed in Flask-SQLAlchemy 3.1.

Changed in version 3.0: Removed SQLALCHEMY_NATIVE_UNICODE, SQLALCHEMY_POOL_SIZE, SQLALCHEMY_POOL_TIMEOUT, SQLALCHEMY_POOL_RECYCLE, and SQLALCHEMY_MAX_OVERFLOW.

See SQLAlchemy's documentation on Engine Configuration for a complete description of syntax, dialects, and options.

A basic database connection URL uses the following format. Username, password, host, and port are optional depending on the database type and configuration.

dialect://username:password@host:port/database

Here are some 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

SQLite does not use a user or host, so its URLs always start with _three_ slashes instead of two. The dbname value is a file path. Absolute paths start with a _fourth_ slash (on Linux or Mac). Relative paths are relative to the Flask application's instance_path.

Some default options are set for SQLite and MySQL engines to make them more usable by default in web applications.

SQLite relative file paths are relative to the Flask instance path instead of the current working directory. In-memory databases use a static pool and check_same_thread to work across requests.

MySQL (and MariaDB) servers are configured to drop connections that have been idle for 8 hours, which can result in an error like 2013: Lost connection to MySQL server during query. A default pool_recycle value of 2 hours (7200 seconds) is used to recreate connections before that timeout.

Because Flask-SQLAlchemy has support for multiple engines, there are rules for which config overrides other config. Most applications will only have a single database and only need to use SQLALCHEMY_DATABASE_URI and SQLALCHEMY_ENGINE_OPTIONS.

  • If the engine_options argument is given to SQLAlchemy, it sets default options for all engines. SQLALCHEMY_ECHO sets the default value for both echo and echo_pool for all engines.
  • The options for each engine in SQLALCHEMY_BINDS override those defaults.
  • SQLALCHEMY_ENGINE_OPTIONS overrides the None key in SQLALCHEMY_BINDS, and SQLALCHEMY_DATABASE_URI overrides the url key in that engine's options.

You can optionally construct the SQLAlchemy object with a custom MetaData object. This allows you to specify a custom constraint naming convention. This makes constraint names consistent and predictable, useful when using migrations, as described by Alembic.

from sqlalchemy import MetaData
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy(metadata=MetaData(naming_convention={
    "ix": 'ix_%(column_0_label)s',
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_%(constraint_name)s",
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s"
}))

Certain databases may be configured to close inactive connections after a period of time. MySQL and MariaDB are configured for this by default, but database services may also configure this type of limit. This can result in an error like 2013: Lost connection to MySQL server during query.

If you encounter this error, try setting pool_recycle in the engine options to a value less than the database's timeout.

Alternatively, you can try setting pool_pre_ping if you expect the database to close connections often, such as if it's running in a container that may restart.

See SQAlchemy's docs on dealing with disconnects for more information.

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

See SQLAlchemy's declarative documentation for full information about defining model classes declaratively.

Subclass db.Model to create a model class. This is a SQLAlchemy declarative base class, it will take Column attributes and create a table. Unlike plain SQLAlchemy, Flask-SQLAlchemy's model will automatically generate a table name if __tablename__ is not set and a primary key column is defined.

import sqlalchemy as sa
class User(db.Model):
    id = sa.Column(sa.Integer, primary_key=True)
    type = sa.Column(sa.String)

For convenience, the extension object provides access to names in the sqlalchemy and sqlalchemy.orm modules. So you can use db.Column instead of importing and using sqlalchemy.Column, although the two are equivalent.

Defining a model does not create it in the database. Use create_all() to create the models and tables after defining them. If you define models in submodules, you must import them so that SQLAlchemy knows about them before calling create_all.

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

See SQLAlchemy's table documentation for full information about defining table objects.

Create instances of db.Table to define tables. The class takes a table name, then any columns and other table parts such as columns and constraints. Unlike plain SQLAlchemy, the metadata argument is not required. A metadata will be chosen based on the bind_key argument, or the default will be used.

A common reason to create a table directly is when defining many to many relationships. The association table doesn't need its own model class, as it will be accessed through the relevant relationship attributes on the related models.

import sqlalchemy as sa
user_book_m2m = db.Table(
    "user_book",
    sa.Column("user_id", sa.ForeignKey(User.id), primary_key=True),
    sa.Column("book_id", sa.ForeignKey(Book.id), primary_key=True),
)

If you are connecting to a database that already has tables, SQLAlchemy can detect that schema and create tables with columns automatically. This is called reflection. Those tables can also be assigned to model classes with the __table__ attribute instead of defining the full model.

Call the reflect() method on the extension. It will reflect all the tables for each bind key. Each metadata's tables attribute will contain the detected table objects.

with app.app_context():
    db.reflect()
class User:
    __table__ = db.metadatas["auth"].tables["user"]

In most cases, it will be more maintainable to define the model classes yourself. You only need to define the models and columns you will actually use, even if you're connecting to a broader schema. IDEs will know the available attributes, and migration tools like Alembic can detect changes and generate schema migrations.

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.

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()

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}'."
)

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.

If you have a lot of results, you may only want to show a certain number at a time, allowing the user to click next and previous links to see pages of data. This is sometimes called pagination, and uses the verb paginate.

Call SQLAlchemy.paginate() on a select statement to get a Pagination object.

During a request, this will take page and per_page arguments from the query string request.args. Pass max_per_page to prevent users from requesting too many results on a single page. If not given, the default values will be page 1 with 20 items per page.

page = db.paginate(db.select(User).order_by(User.join_date))
return render_template("user/list.html", page=page)

The Pagination object's Pagination.items attribute is the list of items for the current page. The object can also be iterated over directly.

<ul>
  {% for user in page %}
    <li>{{ user.username }}
  {% endfor %}
</ul>

The Pagination object has attributes that can be used to create a page selection widget by iterating over page numbers and checking the current page. iter_pages() will produce up to three groups of numbers, separated by None. It defaults to showing 2 page numbers at either edge, 2 numbers before the current, the current, and 4 numbers after the current. For example, if there are 20 pages and the current page is 7, the following values are yielded.

users.iter_pages()
[1, 2, None, 5, 6, 7, 8, 9, 10, 11, None, 19, 20]

You can use the total attribute to show the total number of results, and first and last to show the range of items on the current page.

The following Jinja macro renders a simple pagination widget.

{% macro render_pagination(pagination, endpoint) %}
  <div class=page-items>
    {{ pagination.first }} - {{ pagination.last }} of {{ pagination.total }}
  </div>
  <div class=pagination>
    {% for page in pagination.iter_pages() %}
      {% if page %}
        {% if page != pagination.page %}
          <a href="{{ url_for(endpoint, page=page) }}">{{ page }}</a>
        {% else %}
          <strong>{{ page }}</strong>
        {% endif %}
      {% else %}
        <span class=ellipsis>…</span>
      {% endif %}
    {% endfor %}
  </div>
{% endmacro %}

An active Flask application context is required to make queries and to access db.engine and db.session. This is because the session is scoped to the context so that it is cleaned up properly after every request or CLI command.

Regardless of how an application is initialized with the extension, it is not stored for later use. Instead, the extension uses Flask's current_app proxy to get the active application, which requires an active application context.

When Flask is handling a request or a CLI command, an application context will automatically be pushed. Therefore you don't need to do anything special to use the database during requests or CLI commands.

If you try to use the database when an application context is not active, you will see the following error.

RuntimeError: Working outside of application context.
This typically means that you attempted to use functionality that needed
the current application. To solve this, set up an application context
with app.app_context(). See the documentation for more information.

If you find yourself in a situation where you need the database and don't have a context, you can push one with app_context. This is common when calling db.create_all to create the tables, for example.

def create_app():
    app = Flask(__name__)
    app.config.from_object("project.config")
    import project.models
    with app.app_context():
        db.create_all()
    return app

If you test your application using the Flask test client to make requests to your endpoints, the context will be available as part of the request. If you need to test something about your database or models directly, rather than going through a request, you need to push a context manually.

Only push a context exactly where and for how long it's needed for each test. Do not push an application context globally for every test, as that can interfere with how the session is cleaned up.

def test_user_model(app):
    user = User()
    with app.app_context():
        db.session.add(user)
        db.session.commit()

If you find yourself writing many tests like that, you can use a pytest fixture to push a context for a specific test.

import pytest
@pytest.fixture
def app_ctx(app):
    with app.app_context():
        yield
@pytest.mark.usefixtures("app_ctx")
def test_user_model():
    user = User()
    db.session.add(user)
    db.session.commit()

SQLAlchemy can connect to more than one database at a time. It refers to different engines as "binds". Flask-SQLAlchemy simplifies how binds work by associating each engine with a short string, a "bind key", and then associating each model and table with a bind key. The session will choose what engine to use for a query based on the bind key of the thing being queried. If no bind key is given, the default engine is used.

The default bind is still configured by setting SQLALCHEMY_DATABASE_URI, and SQLALCHEMY_ENGINE_OPTIONS for any engine options. Additional binds are given in SQLALCHEMY_BINDS, a dict mapping bind keys to engine URLs. To specify engine options for a bind, the value can be a dict of engine options with the "url" key, instead of only a URL string.

SQLALCHEMY_DATABASE_URI = "postgresql:///main"
SQLALCHEMY_BINDS = {
    "meta": "sqlite:////path/to/meta.db",
    "auth": {
        "url": "mysql://localhost/users",
        "pool_recycle": 3600,
    },
}

Flask-SQLAlchemy will create a metadata and engine for each configured bind. Models and tables with a bind key will be registered with the corresponding metadata, and the session will query them using the corresponding engine.

To set the bind for a model, set the __bind_key__ class attribute. Not setting a bind key is equivalent to setting it to None, the default key.

class User(db.Model):
    __bind_key__ = "auth"
    id = db.Column(db.Integer, primary_key=True)

Models that inherit from this model will share the same bind key, or can override it.

To set the bind for a table, pass the bind_key keyword argument.

user_table = db.Table(
    "user",
    db.Column("id", db.Integer, primary_key=True),
    bind_key="auth",
)

Ultimately, the session looks up the bind key on the metadata associated with the model or table. That association happens during creation. Therefore, changing the bind key after creating a model or table will have no effect.

You may need to inspect the metadata or engine for a bind. Note that you should execute queries through the session, not directly on the engine.

The default engine is SQLAlchemy.engine, and the default metadata is SQLAlchemy.metadata. SQLAlchemy.engines and SQLAlchemy.metadatas are dicts mapping all bind keys.

The create_all() and drop_all() methods operate on all binds by default. The bind_key argument to these methods can be a string or None to operate on a single bind, or a list of strings or None to operate on a subset of binds. Because these methods access the engines, they must be called inside an application context.

# create tables for all binds
db.create_all()
# create tables for the default and "auth" binds
db.create_all(bind_key=[None, "auth"])
# create tables for the "meta" bind
db.create_all(bind_key="meta")
# drop tables for the default bind
db.drop_all(bind_key=None)

WARNING:

This feature is intended for debugging only.

Flask-SQLAlchemy can record some information about every query that executes during a request. This information can then be retrieved to aid in debugging performance. For example, it can reveal that a relationship performed too many individual selects, or reveal a query that took a long time.

To enable this feature, set SQLALCHEMY_RECORD_QUERIES to True in the Flask app config. Use get_recorded_queries() to get a list of query info objects. Each object has the following attributes:

The string of SQL generated by SQLAlchemy with parameter placeholders.
The parameters sent with the SQL statement.
Timing info about when the query started execution and when the results where returned. Accuracy and value depends on the operating system.
The time the query took in seconds.
A string description of where in your application code the query was executed. This may be unknown in certain cases.

WARNING:

Tracking changes adds significant overhead. In most cases, you'll be better served by using SQLAlchemy events directly.

Flask-SQLAlchemy can set up its session to track inserts, updates, and deletes for models, then send a Blinker signal with a list of these changes either before or during calls to session.flush() and session.commit().

To enable this feature, set SQLALCHEMY_TRACK_MODIFICATIONS in the Flask app config. Then add a listener to models_committed (emitted after the commit) or before_models_committed (emitted before the commit).

from flask_sqlalchemy.track_modifications import models_committed
def get_modifications(sender: Flask, changes: list[tuple[t.Any, str]]) -> None:
    ...
models_committed.connect(get_modifications)

The various objects managed by the extension can be customized by passing arguments to the SQLAlchemy constructor.

SQLAlchemy models all inherit from a declarative base class. This is exposed as db.Model in Flask-SQLAlchemy, which all models extend. This can be customized by subclassing the default and passing the custom class to model_class.

The following example gives every model an integer primary key, or a foreign key for joined-table inheritance.

NOTE:

Integer primary keys for everything is not necessarily the best database design (that's up to your project's requirements), this is only an example.
from flask_sqlalchemy.model import model
import sqlalchemy as sa
import sqlalchemy.orm
class IdModel(Model):
    @sa.orm.declared_attr
    def id(cls):
        for base in cls.__mro__[1:-1]:
            if getattr(base, "__table__", None) is not None:
                type = sa.ForeignKey(base.id)
                break
        else:
            type = sa.Integer
        return sa.Column(type, primary_key=True)
db = SQLAlchemy(model_class=IdModel)
class User(db.Model):
    name = db.Column(db.String)
class Employee(User):
    title = db.Column(db.String)

If behavior is only needed on some models rather than all models, use an abstract model base class to customize only those models. For example, if some models should track when they are created or updated.

from datetime import datetime
class TimestampModel(db.Model):
    __abstract__ = True
    created = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    updated = db.Column(db.DateTime, onupdate=datetime.utcnow)
class Author(db.Model):
    ...
class Post(TimestampModel):
    ...

This can also be done with a mixin class, inheriting from db.Model separately.

class TimestampMixin:
    created = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    updated = db.Column(db.DateTime, onupdate=datetime.utcnow)
class Post(TimestampMixin, db.Model):
    ...

Flask-SQLAlchemy's Session class chooses which engine to query based on the bind key associated with the model or table. However, there are other strategies such as horizontal sharding that can be implemented with a different session class. The class_ key to the session_options argument to the extension to change the session class.

Flask-SQLAlchemy will always pass the extension instance as the db argument to the session, so it must accept that to continue working. That can be used to get access to db.engines.

from sqlalchemy.ext.horizontal_shard import ShardedSession
from flask_sqlalchemy.session import Session
class CustomSession(ShardedSession, Session):
    ...
db = SQLAlchemy(session_options={"class_": CustomSession})

WARNING:

The query interface is considered legacy in SQLAlchemy. This includes session.query, Model.query, db.Query, and lazy="dynamic" relationships. Prefer using session.execute(select(...)) instead.

It is possible to customize the query interface used by the session, models, and relationships. This can be used to add extra query methods. For example, you could add a get_or method that gets a row or returns a default.

from flask_sqlalchemy.query import Query
class GetOrQuery(Query):
    def get_or(self, ident, default=None):
        out = self.get(ident)
        if out is None:
            return default
        return out
db = SQLAlchemy(query_class=GetOrQuery)
user = User.query.get_or(user_id, anonymous_user)

Passing the query_class argument will customize db.Query, db.session.query, Model.query, and db.relationship(lazy="dynamic") relationships. It's also possible to customize these on a per-object basis.

To customize a specific model's query property, set the query_class attribute on the model class.

class User(db.Model):
    query_class = GetOrQuery

To customize a specific dynamic relationship, pass the query_class argument to the relationship.

db.relationship(User, lazy="dynamic", query_class=GetOrQuery)

To customize only session.query, pass the query_cls key to the session_options argument to the constructor.

db = SQLAlchemy(session_options={"query_cls": GetOrQuery})

WARNING:

Metaclasses are an advanced topic, and you probably don't need to customize them to achieve what you want. It is mainly documented here to show how to disable table name generation.

The model metaclass is responsible for setting up the SQLAlchemy internals when defining model subclasses. Flask-SQLAlchemy adds some extra behaviors through mixins; its default metaclass, DefaultMeta, inherits them all.

  • BindMetaMixin: __bind_key__ sets the bind to use for the model.
  • NameMetaMixin: If the model does not specify a __tablename__ but does specify a primary key, a name is automatically generated.

You can add your own behaviors by defining your own metaclass and creating the declarative base yourself. Be sure to still inherit from the mixins you want (or just inherit from the default metaclass).

Passing a declarative base class instead of a simple model base class to model_class will cause Flask-SQLAlchemy to use this base instead of constructing one with the default metaclass.

from sqlalchemy.orm import declarative_base
from flask_sqlalchemy import SQLAlchemy
from flask_sqlalchemy.model import DefaultMeta, Model
class CustomMeta(DefaultMeta):
    def __init__(cls, name, bases, d):
        # custom class setup could go here
        # be sure to call super
        super(CustomMeta, cls).__init__(name, bases, d)
    # custom class-only methods could go here
CustomModel = declarative_base(cls=Model, metaclass=CustomMeta, name="Model")
db = SQLAlchemy(model_class=CustomModel)

You can also pass whatever other arguments you want to declarative_base() to customize the base class.

Some projects prefer to set each model's __tablename__ manually rather than relying on Flask-SQLAlchemy's detection and generation. The simple way to achieve that is to set each __tablename__ and not modify the base class. However, the table name generation can be disabled by defining a custom metaclass with only the BindMetaMixin and not the NameMetaMixin.

from sqlalchemy.orm import DeclarativeMeta, declarative_base
from flask_sqlalchemy.model import BindMetaMixin, Model
class NoNameMeta(BindMetaMixin, DeclarativeMeta):
    pass
CustomModel = declarative_base(cls=Model, metaclass=NoNameMeta, name="Model")
db = SQLAlchemy(model_class=CustomModel)

This creates a base that still supports the __bind_key__ feature but does not generate table names.

Integrates SQLAlchemy with Flask. This handles setting up one or more engines, associating tables and models with specific engines, and cleaning up connections and sessions after each request.

Only the engine configuration is specific to each application, other things like the model, table, metadata, and session are shared for all applications using that extension instance. Call init_app() to configure the extension on an application.

After creating the extension, create model classes by subclassing Model, and table classes with Table. These can be accessed before init_app() is called, making it possible to define the models separately from the application.

Accessing session and engine requires an active Flask application context. This includes methods like create_all() which use the engine.

This class also provides access to names in SQLAlchemy's sqlalchemy and sqlalchemy.orm modules. For example, you can use db.Column and db.relationship instead of importing sqlalchemy.Column and sqlalchemy.orm.relationship. This can be convenient when defining models.

  • app (Flask | None) -- Call init_app() on this Flask application now.
  • metadata (sa.MetaData | None) -- Use this as the default sqlalchemy.schema.MetaData. Useful for setting a naming convention.
  • session_options (dict[str, t.Any] | None) -- Arguments used by session to create each session instance. A scopefunc key will be passed to the scoped session, not the session instance. See sqlalchemy.orm.sessionmaker for a list of arguments.
  • query_class (type[Query]) -- Use this as the default query class for models and dynamic relationships. The query interface is considered legacy in SQLAlchemy.
  • model_class (type[Model] | sa_orm.DeclarativeMeta) -- Use this as the model base class when creating the declarative model class Model. Can also be a fully created declarative model class for further customization.
  • engine_options (dict[str, t.Any] | None) -- Default arguments used when creating every engine. These are lower precedence than application config. See sqlalchemy.create_engine() for a list of arguments.
  • add_models_to_shell (bool) -- Add the db instance and all model classes to flask shell.

Changed in version 3.0: An active Flask application context is always required to access session and engine.

Changed in version 3.0: Separate metadata are used for each bind key.

Changed in version 3.0: The engine_options parameter is applied as defaults before per-engine configuration.

Changed in version 3.0: The session class can be customized in session_options.

Changed in version 3.0: Added the add_models_to_shell parameter.

Changed in version 3.0: Engines are created when calling init_app rather than the first time they are accessed.

Changed in version 3.0: All parameters except app are keyword-only.

Changed in version 3.0: The extension instance is stored directly as app.extensions["sqlalchemy"].

Changed in version 3.0: Setup methods are renamed with a leading underscore. They are considered internal interfaces which may change at any time.

Changed in version 3.0: Removed the use_native_unicode parameter and config.

Changed in version 3.0: The COMMIT_ON_TEARDOWN configuration is deprecated and will be removed in Flask-SQLAlchemy 3.1. Call db.session.commit() directly instead.

Changed in version 2.4: Added the engine_options parameter.

Changed in version 2.1: Added the metadata, query_class, and model_class parameters.

Changed in version 2.1: Use the same query class across session, Model.query and Query.

Changed in version 0.16: scopefunc is accepted in session_options.

Changed in version 0.10: Added the session_options parameter.

A SQLAlchemy declarative model class. Subclass this to define database models.

If a model does not set __tablename__, it will be generated by converting the class name from CamelCase to snake_case. It will not be generated if the model looks like it uses single-table inheritance.

If a model or parent class sets __bind_key__, it will use that metadata and database engine. Otherwise, it will use the default metadata and engine. This is ignored if the model sets metadata or __table__.

Customize this by subclassing Model and passing the model_class parameter to the extension. A fully created declarative model class can be passed as well, to use a custom metaclass.

The default query class used by Model.query and lazy="dynamic" relationships.

WARNING:

The query interface is considered legacy in SQLAlchemy.

Customize this by passing the query_class parameter to the extension.

A sqlalchemy.schema.Table class that chooses a metadata automatically.

Unlike the base Table, the metadata argument is not required. If it is not given, it is selected based on the bind_key argument.

  • bind_key -- Used to select a different metadata.
  • args -- Arguments passed to the base class. These are typically the table's name, columns, and constraints.
  • kwargs -- Arguments passed to the base class.

Changed in version 3.0: This is a subclass of SQLAlchemy's Table rather than a function.

Create tables that do not exist in the database by calling metadata.create_all() for all or some bind keys. This does not update existing tables, use a migration library for that.

This requires that a Flask application context is active.

bind_key (str | None | list[str | None]) -- A bind key or list of keys to create the tables for. Defaults to all binds.
None

Changed in version 3.0: Renamed the bind parameter to bind_key. Removed the app parameter.

Changed in version 0.12: Added the bind and app parameters.

Drop tables by calling metadata.drop_all() for all or some bind keys.

This requires that a Flask application context is active.

bind_key (str | None | list[str | None]) -- A bind key or list of keys to drop the tables from. Defaults to all binds.
None

Changed in version 3.0: Renamed the bind parameter to bind_key. Removed the app parameter.

Changed in version 0.12: Added the bind and app parameters.

A sqlalchemy.orm.dynamic_loader() that applies this extension's Query class for relationships and backrefs.

Changed in version 3.0: The Query class is set on backref.

  • argument (t.Any) --
  • kwargs (t.Any) --
sa_orm.RelationshipProperty[t.Any]
The default Engine for the current application, used by session if the Model or Table being queried does not set a bind key.

To customize, set the SQLALCHEMY_ENGINE_OPTIONS config, and set defaults by passing the engine_options parameter to the extension.

This requires that a Flask application context is active.

Map of bind keys to sqlalchemy.engine.Engine instances for current application. The None key refers to the default engine, and is available as engine.

To customize, set the SQLALCHEMY_BINDS config, and set defaults by passing the engine_options parameter to the extension.

This requires that a Flask application context is active.

New in version 3.0.

Like Result.scalar(), but aborts with a 404 Not Found error instead of returning None.
  • statement (Select) -- The select statement to execute.
  • description (str | None) -- A custom message to show on the error page.
Any

New in version 3.0.

Map all tables to their engine based on their bind key, which can be used to create a session with Session(binds=db.get_binds(app)).

This requires that a Flask application context is active.

Deprecated since version 3.0: Will be removed in Flask-SQLAlchemy 3.1. db.session supports multiple binds directly.

Changed in version 3.0: Removed the app parameter.

dict[Table, Engine]
Get the engine for the given bind key for the current application.

This requires that a Flask application context is active.

bind_key (str | None) -- The name of the engine.
Engine

Deprecated since version 3.0: Will be removed in Flask-SQLAlchemy 3.1. Use engines[key] instead.

Changed in version 3.0: Renamed the bind parameter to bind_key. Removed the app parameter.

Like session.get() but aborts with a 404 Not Found error instead of returning None.
  • entity (type[_O]) -- The model class to query.
  • ident (Any) -- The primary key to query.
  • description (str | None) -- A custom message to show on the error page.
_O

New in version 3.0.

Get all tables in the metadata for the given bind key.
bind_key (str | None) -- The bind key to get.
list[Table]

Deprecated since version 3.0: Will be removed in Flask-SQLAlchemy 3.1. Use metadata.tables instead.

Changed in version 3.0: Renamed the bind parameter to bind_key.

Initialize a Flask application for use with this extension instance. This must be called before accessing the database engine or session with the app.

This sets default configuration values, then configures the extension on the application and creates the engines for each bind key. Therefore, this must be called after the application has been configured. Changes to application config after this call will not be reflected.

The following keys from app.config are used:

  • SQLALCHEMY_DATABASE_URI
  • SQLALCHEMY_ENGINE_OPTIONS
  • SQLALCHEMY_ECHO
  • SQLALCHEMY_BINDS
  • SQLALCHEMY_RECORD_QUERIES
  • SQLALCHEMY_TRACK_MODIFICATIONS
app (Flask) -- The Flask application to initialize.
None
The default metadata used by Model and Table if no bind key is set.
Map of bind keys to sqlalchemy.schema.MetaData instances. The None key refers to the default metadata, and is available as metadata.

Customize the default metadata by passing the metadata parameter to the extension. This can be used to set a naming convention. When metadata for another bind key is created, it copies the default's naming convention.

New in version 3.0.

Like Result.scalar_one(), but aborts with a 404 Not Found error instead of raising NoResultFound or MultipleResultsFound.
  • statement (Select) -- The select statement to execute.
  • description (str | None) -- A custom message to show on the error page.
Any

New in version 3.0.

Apply an offset and limit to a select statment based on the current page and number of items per page, returning a Pagination object.

The statement should select a model class, like select(User). This applies unique() and scalars() modifiers to the result, so compound selects will not return the expected results.

  • select (Select) -- The select statement to paginate.
  • page (int | None) -- The current page, used to calculate the offset. Defaults to the page query arg during a request, or 1 otherwise.
  • per_page (int | None) -- The maximum number of items on a page, used to calculate the offset and limit. Defaults to the per_page query arg during a request, or 20 otherwise.
  • max_per_page (int | None) -- The maximum allowed value for per_page, to limit a user-provided value. Use None for no limit. Defaults to 100.
  • error_out (bool) -- Abort with a 404 Not Found error if no items are returned and page is not 1, or if page or per_page is less than 1, or if either are not ints.
  • count (bool) -- Calculate the total number of values by issuing an extra count query. For very complex queries this may be inaccurate or slow, so it can be disabled and set manually if necessary.
Pagination

Changed in version 3.0: The count query is more efficient.

New in version 3.0.

Load table definitions from the database by calling metadata.reflect() for all or some bind keys.

This requires that a Flask application context is active.

bind_key (str | None | list[str | None]) -- A bind key or list of keys to reflect the tables from. Defaults to all binds.
None

Changed in version 3.0: Renamed the bind parameter to bind_key. Removed the app parameter.

Changed in version 0.12: Added the bind and app parameters.

A sqlalchemy.orm.relationship() that applies this extension's Query class for dynamic relationships and backrefs.

Changed in version 3.0: The Query class is set on backref.

  • args (t.Any) --
  • kwargs (t.Any) --
sa_orm.RelationshipProperty[t.Any]
A sqlalchemy.orm.scoping.scoped_session that creates instances of Session scoped to the current Flask application context. The session will be removed, returning the engine connection to the pool, when the application context exits.

Customize this by passing session_options to the extension.

This requires that a Flask application context is active.

Changed in version 3.0: The session is scoped to the current app context.

The base class of the SQLAlchemy.Model declarative model class.

To define models, subclass db.Model, not this. To customize db.Model, subclass this and pass it as model_class to SQLAlchemy. To customize db.Model at the metaclass level, pass an already created declarative model class as model_class.

__bind_key__
Use this bind key to select a metadata and engine to associate with this model's table. Ignored if metadata or __table__ is set. If not given, uses the default key, None.
__tablename__
The name of the table in the database. This is required by SQLAlchemy; however, Flask-SQLAlchemy will set it automatically if a model has a primary key defined. If the __table__ or __tablename__ is set explicitly, that will be used instead.
A SQLAlchemy query for a model. Equivalent to db.session.query(Model). Can be customized per-model by overriding query_class.

WARNING:

The query interface is considered legacy in SQLAlchemy. Prefer using session.execute(select()) instead.
Query class used by query. Defaults to SQLAlchemy.Query, which defaults to Query.

alias of Query

SQLAlchemy declarative metaclass that provides __bind_key__ and __tablename__ support.
  • name (str) --
  • bases (tuple[type, ...]) --
  • d (dict[str, t.Any]) --
  • kwargs (t.Any) --
Metaclass mixin that sets a model's metadata based on its __bind_key__.

If the model sets metadata or __table__ directly, __bind_key__ is ignored. If the metadata is the same as the parent model, it will not be set directly on the child model.

  • name (str) --
  • bases (tuple[type, ...]) --
  • d (dict[str, t.Any]) --
  • kwargs (t.Any) --
Metaclass mixin that sets a model's __tablename__ by converting the CamelCase class name to snake_case. A name is set for non-abstract models that do not otherwise define __tablename__. If a model does not define a primary key, it will not generate a name or __table__, for single-table inheritance.
  • name (str) --
  • bases (tuple[type, ...]) --
  • d (dict[str, t.Any]) --
  • kwargs (t.Any) --

A SQLAlchemy Session class that chooses what engine to use based on the bind key associated with the metadata associated with the thing being queried.

To customize db.session, subclass this and pass it as the class_ key in the session_options to SQLAlchemy.

Changed in version 3.0: Renamed from SignallingSession.

  • db (SQLAlchemy) --
  • kwargs (t.Any) --
Select an engine based on the bind_key of the metadata associated with the model or table being queried. If no bind key is set, uses the default bind.

Changed in version 3.0.3: Fix finding the bind for a joined inheritance model.

Changed in version 3.0: The implementation more closely matches the base SQLAlchemy implementation.

Changed in version 2.1: Support joining an external transaction.

  • mapper (Any | None) --
  • clause (Any | None) --
  • bind (Engine | Connection | None) --
  • kwargs (Any) --
Engine | Connection

A slice of the total items in a query obtained by applying an offset and limit to based on the current page and number of items per page.

Don't create pagination objects manually. They are created by SQLAlchemy.paginate() and Query.paginate().

Changed in version 3.0: Iterating over a pagination object iterates over its items.

Changed in version 3.0: Creating instances manually is not a public API.

The current page.
The maximum number of items on a page.
The items on the current page. Iterating over the pagination object is equivalent to iterating over the items.
The total number of items across all pages.
The number of the first item on the page, starting from 1, or 0 if there are no items.

New in version 3.0.

The number of the last item on the page, starting from 1, inclusive, or 0 if there are no items.

New in version 3.0.

The total number of pages.
True if this is not the first page.
The previous page number, or None if this is the first page.
Query the Pagination object for the previous page.
error_out (bool) -- Abort with a 404 Not Found error if no items are returned and page is not 1, or if page or per_page is less than 1, or if either are not ints.
Pagination
True if this is not the last page.
The next page number, or None if this is the last page.
Query the Pagination object for the next page.
error_out (bool) -- Abort with a 404 Not Found error if no items are returned and page is not 1, or if page or per_page is less than 1, or if either are not ints.
Pagination
Yield page numbers for a pagination widget. Skipped pages between the edges and middle are represented by a None.

For example, if there are 20 pages and the current page is 7, the following values are yielded.

1, 2, None, 5, 6, 7, 8, 9, 10, 11, None, 19, 20
  • left_edge (int) -- How many pages to show from the first page.
  • left_current (int) -- How many pages to show left of the current page.
  • right_current (int) -- How many pages to show right of the current page.
  • right_edge (int) -- How many pages to show from the last page.
Iterator[int | None]

Changed in version 3.0: Improved efficiency of calculating what to yield.

Changed in version 3.0: right_current boundary is inclusive.

Changed in version 3.0: All parameters are keyword-only.

SQLAlchemy Query subclass with some extra methods useful for querying in a web application.

This is the default query class for Model.query.

Changed in version 3.0: Renamed to Query from BaseQuery.

Like first() but aborts with a 404 Not Found error instead of returning None.
description (str | None) -- A custom message to show on the error page.
Any
Like get() but aborts with a 404 Not Found error instead of returning None.
  • ident (Any) -- The primary key to query.
  • description (str | None) -- A custom message to show on the error page.
Any
Like one() but aborts with a 404 Not Found error instead of raising NoResultFound or MultipleResultsFound.
description (str | None) -- A custom message to show on the error page.
Any

New in version 3.0.

Apply an offset and limit to the query based on the current page and number of items per page, returning a Pagination object.
  • page (int | None) -- The current page, used to calculate the offset. Defaults to the page query arg during a request, or 1 otherwise.
  • per_page (int | None) -- The maximum number of items on a page, used to calculate the offset and limit. Defaults to the per_page query arg during a request, or 20 otherwise.
  • max_per_page (int | None) -- The maximum allowed value for per_page, to limit a user-provided value. Use None for no limit. Defaults to 100.
  • error_out (bool) -- Abort with a 404 Not Found error if no items are returned and page is not 1, or if page or per_page is less than 1, or if either are not ints.
  • count (bool) -- Calculate the total number of values by issuing an extra count query. For very complex queries this may be inaccurate or slow, so it can be disabled and set manually if necessary.
Pagination

Changed in version 3.0: All parameters are keyword-only.

Changed in version 3.0: The count query is more efficient.

Changed in version 3.0: max_per_page defaults to 100.

Get the list of recorded query information for the current session. Queries are recorded if the config SQLALCHEMY_RECORD_QUERIES is enabled.

Each query info object has the following attributes:

The string of SQL generated by SQLAlchemy with parameter placeholders.
The parameters sent with the SQL statement.
Timing info about when the query started execution and when the results where returned. Accuracy and value depends on the operating system.
The time the query took in seconds.
A string description of where in your application code the query was executed. This may not be possible to calculate, and the format is not stable.

Changed in version 3.0: Renamed from get_debug_queries.

Changed in version 3.0: The info object is a dataclass instead of a tuple.

Changed in version 3.0: The info object attribute context is renamed to location.

Changed in version 3.0: Not enabled automatically in debug or testing mode.

list[_QueryInfo]

This Blinker signal is sent after the session is committed if there were changed models in the session.

The sender is the application that emitted the changes. The receiver is passed the changes argument with a list of tuples in the form (instance, operation). The operations are "insert", "update", and "delete".

This signal works exactly like models_committed but is emitted before the commit takes place.

Copyright 2010 Pallets

Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:

1.
Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
2.
Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.
3.
Neither the name of the copyright holder nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

Released 2023-06-21

  • Pagination.next() enforces max_per_page. #1201
  • Improve type hint for get_or_404 return value to be non-optional. #1226

Released 2023-06-19

  • Fix type hint for get_or_404 return value. #1208
  • Fix type hints for pyright (used by VS Code Pylance extension). #1205

Released 2023-01-31

  • Show helpful errors when mistakenly using multiple SQLAlchemy instances for the same app, or without calling init_app. #1151
  • Fix issue with getting the engine associated with a model that uses polymorphic table inheritance. #1155

Released 2022-10-14

•
Update compatibility with SQLAlchemy 2. #1122

Released 2022-10-11

  • Export typing information instead of using external typeshed definitions. #1112
  • If default engine options are set, but SQLALCHEMY_DATABASE_URI is not set, an invalid default bind will not be configured. #1117

Released 2022-10-04

  • Drop support for Python 2, 3.4, 3.5, and 3.6.
  • Bump minimum version of Flask to 2.2.
  • Bump minimum version of SQLAlchemy to 1.4.18.
  • Remove previously deprecated code.
  • The session is scoped to the current app context instead of the thread. This requires that an app context is active. This ensures that the session is cleaned up after every request.
  • An active Flask application context is always required to access session and engine, regardless of if an application was passed to the constructor. #508#944
  • Different bind keys use different SQLAlchemy MetaData registries, allowing tables in different databases to have the same name. Bind keys are stored and looked up on the resulting metadata rather than the model or table.
  • SQLALCHEMY_DATABASE_URI does not default to sqlite:///:memory:. An error is raised if neither it nor SQLALCHEMY_BINDS define any engines. #731
  • Configuring SQLite with a relative path is relative to app.instance_path instead of app.root_path. The instance folder is created if necessary. #462
  • Added get_or_404, first_or_404, one_or_404, and paginate methods to the extension object. These use SQLAlchemy's preferred session.execute(select()) pattern instead of the legacy query interface. #1088
  • Setup methods that create the engines and session are renamed with a leading underscore. They are considered internal interfaces which may change at any time.
  • All parameters to SQLAlchemy except app are keyword-only.
  • Renamed the bind parameter to bind_key and removed the app parameter from various SQLAlchemy methods.
  • The extension object uses __getattr__ to alias names from the SQLAlchemy package, rather than copying them as attributes.
  • The extension object is stored directly as app.extensions["sqlalchemy"]. #698
  • The session class can be customized by passing the class_ key in the session_options parameter. #327
  • SignallingSession is renamed to Session.
  • Session.get_bind more closely matches the base implementation.
  • Model classes and the db instance are available without imports in flask shell. #1089
  • The CamelCase to snake_case table name converter handles more patterns correctly. If model that was already created in the database changed, either use Alembic to rename the table, or set __tablename__ to keep the old name. #406
  • Model repr distinguishes between transient and pending instances. #967
  • A custom model class can implement __init_subclass__ with class parameters. #1002
  • db.Table is a subclass instead of a function.
  • The engine_options parameter is applied as defaults before per-engine configuration.
  • SQLALCHEMY_BINDS values can either be an engine URL, or a dict of engine options including URL, for each bind. SQLALCHEMY_DATABASE_URI and SQLALCHEMY_ENGINE_OPTIONS correspond to the None key and take precedence. #783
  • Engines are created when calling init_app rather than the first time they are accessed. #698
  • db.engines exposes the map of bind keys to engines for the current app.
  • get_engine, get_tables_for_bind, and get_binds are deprecated.
  • SQLite driver-level URIs that look like sqlite:///file:name.db?uri=true are supported. #998#1045
  • SQLite engines do not use NullPool if pool_size is 0.
  • MySQL engines use the "utf8mb4" charset by default. #875
  • MySQL engines do not set pool_size to 10.
  • MySQL engines don't set a default for pool_recycle if not using a queue pool. #803
  • Query is renamed from BaseQuery.
  • Added Query.one_or_404.
  • The query class is applied to backref in relationship. #417
  • Creating Pagination objects manually is no longer a public API. They should be created with db.paginate or query.paginate. #1088
  • Pagination.iter_pages and Query.paginate parameters are keyword-only.
  • Pagination is iterable, iterating over its items. #70
  • Pagination count query is more efficient.
  • Pagination.iter_pages is more efficient. #622
  • Pagination.iter_pages right_current parameter is inclusive.
  • Pagination per_page cannot be 0. #1091
  • Pagination max_per_page defaults to 100. #1091
  • Added Pagination.first and last properties, which give the number of the first and last item on the page. #567
  • SQLALCHEMY_RECORD_QUERIES is disabled by default, and is not enabled automatically with app.debug or app.testing. #1092
  • get_debug_queries is renamed to get_recorded_queries to better match the config and functionality.
  • Recorded query info is a dataclass instead of a tuple. The context attribute is renamed to location. Finding the location uses a more inclusive check.
  • SQLALCHEMY_TRACK_MODIFICATIONS is disabled by default. #727
  • SQLALCHEMY_COMMIT_ON_TEARDOWN is deprecated. It can cause various design issues that are difficult to debug. Call db.session.commit() directly instead. #216

Released 2021-03-18

•
Fix compatibility with Python 2.7.

Released 2021-03-18

  • Update to support SQLAlchemy 1.4.
  • SQLAlchemy URL objects are immutable. Some internal methods have changed to return a new URL instead of None. #885

Released 2020-07-14

•
Change base class of meta mixins to type. This fixes an issue caused by a regression in CPython 3.8.4. #852

Released 2020-05-26

•
Deprecate SQLALCHEMY_COMMIT_ON_TEARDOWN as it can cause various design issues that are difficult to debug. Call db.session.commit() directly instead. #216

Released 2020-05-25

•
Fix bad pagination when records are de-duped. #812

Released 2019-09-24

•
Fix AttributeError when using multiple binds with polymorphic models. #651

Released 2019-04-24

  • Drop support for Python 2.6 and 3.3. #687
  • Address SQLAlchemy 1.3 deprecations. #684
  • Make engine configuration more flexible. Added the engine_options parameter and SQLALCHEMY_ENGINE_OPTIONS config. Deprecated the individual engine option config keys SQLALCHEMY_NATIVE_UNICODE, SQLALCHEMY_POOL_SIZE, SQLALCHEMY_POOL_TIMEOUT, SQLALCHEMY_POOL_RECYCLE, and SQLALCHEMY_MAX_OVERFLOW. #684
  • get_or_404() and first_or_404() now accept a description parameter to control the 404 message. #636
  • Use time.perf_counter for Python 3 on Windows. #638
  • Add an example of Flask's tutorial project, Flaskr, adapted for Flask-SQLAlchemy. #720

Released 2017-10-11

•
Don't mask the parent table for single-table inheritance models. #561

Released 2017-10-05

  • If a model has a table name that matches an existing table in the metadata, use that table. Fixes a regression where reflected tables were not picked up by models. #551
  • Raise the correct error when a model has a table name but no primary key. #556
  • Fix repr on models that don't have an identity because they have not been flushed yet. #555
  • Allow specifying a max_per_page limit for pagination, to avoid users specifying high values in the request args. #542
  • For paginate with error_out=False, the minimum value for page is 1 and per_page is 0. #558

Released 2017-09-28

  • Multiple bugs with __tablename__ generation are fixed. Names will be generated for models that define a primary key, but not for single-table inheritance subclasses. Names will not override a declared_attr. PrimaryKeyConstraint is detected. #541
  • Passing an existing declarative_base() as model_class to SQLAlchemy.__init__ will use this as the base class instead of creating one. This allows customizing the metaclass used to construct the base. #546
  • The undocumented DeclarativeMeta internals that the extension uses for binds and table name generation have been refactored to work as mixins. Documentation is added about how to create a custom metaclass that does not do table name generation. #546
  • Model and metaclass code has been moved to a new models module. _BoundDeclarativeMeta is renamed to DefaultMeta; the old name will be removed in 3.0. #546
  • Models have a default repr that shows the model name and primary key. #530
  • Fixed a bug where using init_app would cause connectors to always use the current_app rather than the app they were created for. This caused issues when multiple apps were registered with the extension. #547

Released 2017-02-27, codename Dubnium

  • Minimum SQLAlchemy version is 0.8 due to use of sqlalchemy.inspect.
  • Added support for custom query_class and model_class as args to the SQLAlchemy constructor. #328
  • Allow listening to SQLAlchemy events on db.session. #364
  • Allow __bind_key__ on abstract models. #373
  • Allow SQLALCHEMY_ECHO to be a string. #409
  • Warn when SQLALCHEMY_DATABASE_URI is not set. #443
  • Don't let pagination generate invalid page numbers. #460
  • Drop support of Flask < 0.10. This means the db session is always tied to the app context and its teardown event. #461
  • Tablename generation logic no longer accesses class properties unless they are declared_attr. #467

Released 2015-10-23, codename Caesium

  • Table names are automatically generated in more cases, including subclassing mixins and abstract models.
  • Allow using a custom MetaData object.
  • Add support for binds parameter to session.

Released 2014-08-29, codename Bohrium

  • Changed how the builtin signals are subscribed to skip non-Flask-SQLAlchemy sessions. This will also fix the attribute error about model changes not existing.
  • Added a way to control how signals for model modifications are tracked.
  • Made the SignallingSession a public interface and added a hook for customizing session creation.
  • If the bind parameter is given to the signalling session it will no longer cause an error that a parameter is given twice.
  • Added working table reflection support.
  • Enabled autoflush by default.
  • Consider SQLALCHEMY_COMMIT_ON_TEARDOWN harmful and remove from docs.

Released 2013-07-20, codename Aurum

  • Added Python 3.3 support.
  • Dropped Python 2.5 compatibility.
  • Various bugfixes.
  • Changed versioning format to do major releases for each update now.

  • New distribution format (flask_sqlalchemy).
  • Added support for Flask 0.9 specifics.

•
Added session support for multiple databases.

•
Make relative sqlite paths relative to the application root.

•
Fixed an issue with Flask-SQLAlchemy not selecting the correct binds.

  • Added support for multiple databases.
  • Expose BaseQuery as db.Query.
  • Set default query_class for db.relation, db.relationship, and db.dynamic_loader to BaseQuery.
  • Improved compatibility with Flask 0.7.

•
Fixed a bug introduced in 0.10 with alternative table constructors.

  • Added support for signals.
  • Table names are now automatically set from the class name unless overridden.
  • Model.query now always works for applications directly passed to the SQLAlchemy constructor. Furthermore the property now raises a RuntimeError instead of being None.
  • Added session options to constructor.
  • Fixed a broken __repr__.
  • db.Table is now a factory function that creates table objects. This makes it possible to omit the metadata.

•
Applied changes to pass the Flask extension approval process.

  • Added a few configuration keys for creating connections.
  • Automatically activate connection recycling for MySQL connections.
  • Added support for the Flask testing mode.

•
Initial public release

Pallets

2024 Pallets

April 15, 2024 3.0.x