API

Extension

class flask_sqlalchemy.SQLAlchemy(app=None, *, metadata=None, session_options=None, query_class=Query, model_class=Model, engine_options=None, add_models_to_shell=True, disable_autonaming=False)

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.

Parameters:

  • 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 (_FSA_MCT) – 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.

  • disable_autonaming (bool)

Changed in version 3.1.0: The metadata parameter can still be used with SQLAlchemy 1.x classes, but is ignored when using SQLAlchemy 2.x style of declarative classes. Instead, specify metadata on your Base class.

Changed in version 3.1.0: Added the disable_autonaming parameter.

Changed in version 3.1.0: Changed model_class parameter to accepta SQLAlchemy 2.x declarative base subclass.

Changelog

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 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.

Query

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.

session

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.

Changelog

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

metadatas: dict[str | None, MetaData]

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.

Changelog

Added in version 3.0.

Table

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.

Parameters:

  • 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.

Changelog

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

Model

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__.

For code using the SQLAlchemy 1.x API, customize this model 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.

For code using the SQLAlchemy 2.x API, customize this model by subclassing sqlalchemy.orm.DeclarativeBase or sqlalchemy.orm.DeclarativeBaseNoMeta and passing the model_class parameter to the extension.

init_app(app)

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:

Parameters:

app (Flask) – The Flask application to initialize.

Return type:

None

property metadata: MetaData

The default metadata used by Model and Table if no bind key is set.

property engines: Mapping[str | None, Engine]

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.

Changelog

Added in version 3.0.

property engine: Engine

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.

get_engine(bind_key=None, **kwargs)

Get the engine for the given bind key for the current application. This requires that a Flask application context is active.

Parameters:

  • bind_key (str | None) – The name of the engine.

  • kwargs (Any)

Return type:

Engine

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

Changelog

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

get_or_404(entity, ident, *, description=None, **kwargs)

Like session.get() but aborts with a 404 Not Found error instead of returning None.

Parameters:

  • 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.

  • kwargs (Any) – Extra arguments passed to session.get().

Return type:

_O

Changed in version 3.1: Pass extra keyword arguments to session.get().

Changelog

Added in version 3.0.

first_or_404(statement, *, description=None)

Like Result.scalar(), but aborts with a 404 Not Found error instead of returning None.

Parameters:

  • statement (Select) – The select statement to execute.

  • description (str | None) – A custom message to show on the error page.

Return type:

Any

Changelog

Added in version 3.0.

one_or_404(statement, *, description=None)

Like Result.scalar_one(), but aborts with a 404 Not Found error instead of raising NoResultFound or MultipleResultsFound.

Parameters:

  • statement (Select) – The select statement to execute.

  • description (str | None) – A custom message to show on the error page.

Return type:

Any

Changelog

Added in version 3.0.

paginate(select, *, page=None, per_page=None, max_per_page=None, error_out=True, count=True)

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.

Parameters:

  • 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.

Return type:

Pagination

Changelog

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

Added in version 3.0.

create_all(bind_key='__all__')

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.

Parameters:

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

Return type:

None

Changelog

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_all(bind_key='__all__')

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

This requires that a Flask application context is active.

Parameters:

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

Return type:

None

Changelog

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.

reflect(bind_key='__all__')

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.

Parameters:

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

Return type:

None

Changelog

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.

relationship(*args, **kwargs)

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

Changelog

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

Parameters:
Return type:

RelationshipProperty[Any]

dynamic_loader(argument, **kwargs)

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

Changelog

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

Parameters:
Return type:

RelationshipProperty[Any]

Model

class flask_sqlalchemy.model.Model

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.

query_class

Query class used by query. Defaults to SQLAlchemy.Query, which defaults to Query.

alias of Query

query: t.ClassVar[Query]

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.

Metaclass mixins (SQLAlchemy 1.x)

If your code uses the SQLAlchemy 1.x API (the default for code that doesn’t specify a model_class), then these mixins are automatically applied to the Model class.

class flask_sqlalchemy.model.DefaultMeta(name, bases, d, **kwargs)

SQLAlchemy declarative metaclass that provides __bind_key__ and __tablename__ support.

Parameters:
class flask_sqlalchemy.model.BindMetaMixin(name, bases, d, **kwargs)

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.

Parameters:
class flask_sqlalchemy.model.NameMetaMixin(name, bases, d, **kwargs)

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.

Parameters:

Session

class flask_sqlalchemy.session.Session(db, **kwargs)

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.

Changelog

Changed in version 3.0: Renamed from SignallingSession.

Parameters:
get_bind(mapper=None, clause=None, bind=None, **kwargs)

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.

Changelog

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.

Parameters:
Return type:

Engine | Connection

Pagination

class flask_sqlalchemy.pagination.Pagination

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

Changelog

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.

page: int

The current page.

per_page: int

The maximum number of items on a page.

items: list[Any]

The items on the current page. Iterating over the pagination object is equivalent to iterating over the items.

total: int | None

The total number of items across all pages.

property first: int

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

Changelog

Added in version 3.0.

property last: int

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

Changelog

Added in version 3.0.

property pages: int

The total number of pages.

property has_prev: bool

True if this is not the first page.

property prev_num: int | None

The previous page number, or None if this is the first page.

prev(*, error_out=False)

Query the Pagination object for the previous page.

Parameters:

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.

Return type:

Pagination

property has_next: bool

True if this is not the last page.

property next_num: int | None

The next page number, or None if this is the last page.

next(*, error_out=False)

Query the Pagination object for the next page.

Parameters:

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.

Return type:

Pagination

iter_pages(*, left_edge=2, left_current=2, right_current=4, right_edge=2)

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
Parameters:

  • 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.

Return type:

Iterator[int | None]

Changelog

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.

Query

class flask_sqlalchemy.query.Query(entities, session=None)

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

This is the default query class for Model.query.

Changelog

Changed in version 3.0: Renamed to Query from BaseQuery.

Parameters:

  • entities (Union[_ColumnsClauseArgument[Any], Sequence[_ColumnsClauseArgument[Any]]])

  • session (Optional[Session])

get_or_404(ident, description=None)

Like get() but aborts with a 404 Not Found error instead of returning None.

Parameters:

  • ident (Any) – The primary key to query.

  • description (str | None) – A custom message to show on the error page.

Return type:

Any

first_or_404(description=None)

Like first() but aborts with a 404 Not Found error instead of returning None.

Parameters:

description (str | None) – A custom message to show on the error page.

Return type:

Any

one_or_404(description=None)

Like one() but aborts with a 404 Not Found error instead of raising NoResultFound or MultipleResultsFound.

Parameters:

description (str | None) – A custom message to show on the error page.

Return type:

Any

Changelog

Added in version 3.0.

paginate(*, page=None, per_page=None, max_per_page=None, error_out=True, count=True)

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

Parameters:

  • 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.

Return type:

Pagination

Changelog

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.

Record Queries

flask_sqlalchemy.record_queries.get_recorded_queries()

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:

statement

The string of SQL generated by SQLAlchemy with parameter placeholders.

parameters

The parameters sent with the SQL statement.

start_time / end_time

Timing info about when the query started execution and when the results where returned. Accuracy and value depends on the operating system.

duration

The time the query took in seconds.

location

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.

Changelog

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.

Return type:

list[_QueryInfo]

Track Modifications

flask_sqlalchemy.track_modifications.models_committed

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".

flask_sqlalchemy.track_modifications.before_models_committed

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