FLASK-SQLALCHEMY(1) Flask-SQLAlchemy FLASK-SQLALCHEMY(1) NAME flask-sqlalchemy - Flask-SQLAlchemy 3.1.1 [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. USER GUIDE Quick Start 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 <#flask_sqlalchemy .SQLAlchemy> object. Check the SQLAlchemy Documentation 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 and scoped session automatically, so you can skip those parts of the SQLAlchemy tutorial. This guide assumes you are using SQLAlchemy 2.x, which has a new API for defining models and better support for Python type hints and data classes. If you are using SQLAlchemy 1.x, see Legacy Quickstart <>. Installation 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 Initialize the Extension First create the db object using the SQLAlchemy constructor. Pass a subclass of either DeclarativeBase or DeclarativeBaseNoMeta to the constructor. from flask import Flask from flask_sqlalchemy import SQLAlchemy from sqlalchemy.orm import DeclarativeBase class Base(DeclarativeBase): pass db = SQLAlchemy(model_class=Base) Learn more about customizing the base model class in Models and Tables <>. About the SQLAlchemy object Once constructed, the db object gives you access to the db.Model <# flask_sqlalchemy.SQLAlchemy.Model> class to define models, and the db.session <#flask_sqlalchemy.SQLAlchemy.session> to execute queries. The SQLAlchemy <#flask_sqlalchemy.SQLAlchemy> object also takes additional arguments to customize the objects it manages. Configure the Extension The next step is to connect the extension to your Flask app. The only required Flask app config is the SQLALCHEMY_DATABASE_URI <# flask_sqlalchemy.config.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 <#flask_sqlalchemy.SQLAlchemy> extension class with the application by calling db.init_app <#flask_sqlalchemy .SQLAlchemy.init_app>. This example connects to a SQLite database, which is stored in the app's instance folder. # 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) See Configuration <> for an explanation of connections strings and what other configuration keys are used. Define Models Subclass db.Model to define a model class. The model will generate a table name by converting the CamelCase class name to snake_case. from sqlalchemy import Integer, String from sqlalchemy.orm import Mapped, mapped_column 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) See Models and Tables <> for more information about defining and creating models and tables. Create the Tables After all models and tables are defined, call SQLAlchemy.create_all() <#flask_sqlalchemy.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. Query the Data 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/") def user_detail(id): user = db.get_or_404(User, id) return render_template("user/detail.html", user=user) @app.route("/user//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. What to Remember For the most part, you should use SQLAlchemy as usual. The SQLAlchemy <#flask_sqlalchemy.SQLAlchemy> extension instance creates, configures, and gives access to the following things: o SQLAlchemy.Model <#flask_sqlalchemy.SQLAlchemy.Model> declarative model base class. It sets the table name automatically instead of needing __tablename__. o SQLAlchemy.session <#flask_sqlalchemy.SQLAlchemy.session> is a session that is scoped to the current Flask application context. It is cleaned up after every request. o SQLAlchemy.metadata <#flask_sqlalchemy.SQLAlchemy.metadata> and SQLAlchemy.metadatas <#flask_sqlalchemy.SQLAlchemy.metadatas> gives access to each metadata defined in the config. o SQLAlchemy.engine <#flask_sqlalchemy.SQLAlchemy.engine> and SQLAlchemy.engines <#flask_sqlalchemy.SQLAlchemy.engines> gives access to each engine defined in the config. o SQLAlchemy.create_all() <#flask_sqlalchemy.SQLAlchemy.create_all> creates all tables. o You must be in an active Flask application context to execute queries and to access the session and engine. Configuration Configuration Keys Configuration is loaded from the Flask app.config when SQLAlchemy.init_app() <#flask_sqlalchemy.SQLAlchemy.init_app> is called. The configuration is not read again after that. Therefore, all configuration must happen before initializing the application. flask_sqlalchemy.config.SQLALCHEMY_DATABASE_URI 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. flask_sqlalchemy.config.SQLALCHEMY_ENGINE_OPTIONS A dict of arguments to pass to sqlalchemy.create_engine() for the default engine. This takes precedence over the engine_options argument to SQLAlchemy <#flask_sqlalchemy.SQLAlchemy>, which can be used to set default options for all engines. Changed in version 3.0: Only applies to the default bind. Added in version 2.4. flask_sqlalchemy.config.SQLALCHEMY_BINDS 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. Added in version 0.12. flask_sqlalchemy.config.SQLALCHEMY_ECHO 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. flask_sqlalchemy.config.SQLALCHEMY_RECORD_QUERIES If enabled, information about each query during a request will be recorded. Use get_recorded_queries() <#flask_sqlalchemy .record_queries.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. flask_sqlalchemy.config.SQLALCHEMY_TRACK_MODIFICATIONS If enabled, all insert, update, and delete operations on models are recorded, then sent in models_committed <#flask_sqlalchemy .track_modifications.models_committed> and before_models_committed <#flask_sqlalchemy.track_modifications .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. Added in version 2.0. Changed in version 3.1: Removed SQLALCHEMY_COMMIT_ON_TEARDOWN. Changed in version 3.0: Removed SQLALCHEMY_NATIVE_UNICODE, SQLALCHEMY_POOL_SIZE, SQLALCHEMY_POOL_TIMEOUT, SQLALCHEMY_POOL_RECYCLE, and SQLALCHEMY_MAX_OVERFLOW. Connection URL Format 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 . Default Driver Options 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. Engine Configuration Precedence 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. o If the engine_options argument is given to SQLAlchemy <# flask_sqlalchemy.SQLAlchemy>, it sets default options for all engines. SQLALCHEMY_ECHO sets the default value for both echo and echo_pool for all engines. o The options for each engine in SQLALCHEMY_BINDS override those defaults. o SQLALCHEMY_ENGINE_OPTIONS overrides the None key in SQLALCHEMY_BINDS, and SQLALCHEMY_DATABASE_URI overrides the url key in that engine's options. Timeouts 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. Models and Tables 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. Initializing the Base Class SQLAlchemy 2.x offers several possible base classes for your models: DeclarativeBase or DeclarativeBaseNoMeta . Create a subclass of one of those classes: from flask import Flask from flask_sqlalchemy import SQLAlchemy from sqlalchemy.orm import DeclarativeBase class Base(DeclarativeBase): pass If desired, you can enable SQLAlchemy's native support for data classes by adding MappedAsDataclass as an additional parent class. from sqlalchemy.orm import DeclarativeBase, MappedAsDataclass class Base(DeclarativeBase, MappedAsDataclass): pass You can optionally construct the SQLAlchemy <#flask_sqlalchemy .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 class Base(DeclarativeBase): 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" }) Initialize the Extension Once you've defined a base class, create the db object using the SQLAlchemy constructor. db = SQLAlchemy(model_class=Base) Defining Models See SQLAlchemy's declarative documentation for full information about defining model classes declaratively. Subclass db.Model to create a model class. 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. from sqlalchemy.orm import Mapped, mapped_column class User(db.Model): id: Mapped[int] = mapped_column(db.Integer, primary_key=True) username: Mapped[str] = mapped_column(db.String, unique=True, nullable=False) email: Mapped[str] = mapped_column(db.String) Defining a model does not create it in the database. Use create_all() <#flask_sqlalchemy.SQLAlchemy.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() Defining Tables 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), ) Reflecting Tables 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() <#flask_sqlalchemy.SQLAlchemy.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. Modifying and Querying Data Insert, Update, Delete See SQLAlchemy's ORM tutorial and other SQLAlchemy documentation for more information about modifying data with the ORM. To insert data, pass the model object to db.session.add(): user = User() db.session.add(user) db.session.commit() To update data, modify attributes on the model objects: user.verified = True db.session.commit() To delete data, pass the model object to db.session.delete(): db.session.delete(user) db.session.commit() After modifying data, you must call db.session.commit() to commit the changes to the database. Otherwise, they will be discarded at the end of the request. Select See SQLAlchemy's Querying Guide and other SQLAlchemy documentation for more information about querying data with the ORM. Queries are executed through db.session.execute(). They can be constructed using select() . Executing a select returns a Result object that has many methods for working with the returned rows. user = db.session.execute(db.select(User).filter_by(username=username)).scalar_one() users = db.session.execute(db.select(User).order_by(User.username)).scalars() Queries for Views If you write a Flask view function it's often useful to return a 404 Not Found error for missing entries. Flask-SQLAlchemy provides some extra query methods. o SQLAlchemy.get_or_404() <#flask_sqlalchemy.SQLAlchemy.get_or_404> will raise a 404 if the row with the given id doesn't exist, otherwise it will return the instance. o SQLAlchemy.first_or_404() <#flask_sqlalchemy.SQLAlchemy.first_or_404> will raise a 404 if the query does not return any results, otherwise it will return the first result. o SQLAlchemy.one_or_404() <#flask_sqlalchemy.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/") 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/") def user_by_username(username): user = db.one_or_404(db.select(User).filter_by(username=username)) return render_template("show_user.html", user=user) You can add a custom message to the 404 error: user = db.one_or_404( db.select(User).filter_by(username=username), description=f"No user named '{username}'." ) Legacy Query Interface You may see uses of Model.query or session.query to build queries. That query interface is considered legacy in SQLAlchemy. Prefer using the session.execute(select(...)) instead. See Legacy Query Interface <> for documentation. Paging Query Results 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() <#flask_sqlalchemy.SQLAlchemy.paginate> on a select statement to get a Pagination <#flask_sqlalchemy.pagination .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) Showing the Items The Pagination <#flask_sqlalchemy.pagination.Pagination> object's Pagination.items <#flask_sqlalchemy.pagination.Pagination.items> attribute is the list of items for the current page. The object can also be iterated over directly.
    {% for user in page %}
  • {{ user.username }} {% endfor %}
Page Selection Widget The Pagination <#flask_sqlalchemy.pagination.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() <#flask_sqlalchemy.pagination.Pagination.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 <#flask_sqlalchemy.pagination.Pagination.total> attribute to show the total number of results, and first <# flask_sqlalchemy.pagination.Pagination.first> and last <# flask_sqlalchemy.pagination.Pagination.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) %}
{{ pagination.first }} - {{ pagination.last }} of {{ pagination.total }}
{% endmacro %} Flask Application Context 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. Automatic 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. Manual Context 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 Tests 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() Multiple Databases with Binds 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. Configuring Binds The default bind is still configured by setting SQLALCHEMY_DATABASE_URI <#flask_sqlalchemy.config.SQLALCHEMY_DATABASE_URI>, and SQLALCHEMY_ENGINE_OPTIONS <#flask_sqlalchemy.config .SQLALCHEMY_ENGINE_OPTIONS> for any engine options. Additional binds are given in SQLALCHEMY_BINDS <#flask_sqlalchemy.config .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, }, } Defining Models and Tables with Binds 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. Accessing Metadata and Engines 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 <#flask_sqlalchemy.SQLAlchemy .engine>, and the default metadata is SQLAlchemy.metadata <# flask_sqlalchemy.SQLAlchemy.metadata>. SQLAlchemy.engines <# flask_sqlalchemy.SQLAlchemy.engines> and SQLAlchemy.metadatas <# flask_sqlalchemy.SQLAlchemy.metadatas> are dicts mapping all bind keys. Creating and Dropping Tables The create_all() <#flask_sqlalchemy.SQLAlchemy.create_all> and drop_all() <#flask_sqlalchemy.SQLAlchemy.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) Recording Query Information 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 <# flask_sqlalchemy.config.SQLALCHEMY_RECORD_QUERIES> to True in the Flask app config. Use get_recorded_queries() <#flask_sqlalchemy .record_queries.get_recorded_queries> to get a list of query info objects. Each 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 be unknown in certain cases. Tracking Modifications 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 <# flask_sqlalchemy.config.SQLALCHEMY_TRACK_MODIFICATIONS> in the Flask app config. Then add a listener to models_committed <#flask_sqlalchemy .track_modifications.models_committed> (emitted after the commit) or before_models_committed <#flask_sqlalchemy.track_modifications .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) Advanced Customization The various objects managed by the extension can be customized by passing arguments to the SQLAlchemy <#flask_sqlalchemy.SQLAlchemy> constructor. Model Class 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 sqlalchemy import Integer, String, ForeignKey from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, declared_attr class Base(DeclarativeBase): @declared_attr.cascading @classmethod def id(cls): for base in cls.__mro__[1:-1]: if getattr(base, "__table__", None) is not None: return mapped_column(ForeignKey(base.id), primary_key=True) else: return mapped_column(Integer, primary_key=True) db = SQLAlchemy(app, model_class=Base) class User(db.Model): name: Mapped[str] = mapped_column(String) class Employee(User): title: Mapped[str] = mapped_column(String) Abstract Models and Mixins 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 from sqlalchemy import DateTime, Integer, String from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, declared_attr class TimestampModel(db.Model): __abstract__ = True created: Mapped[datetime] = mapped_column(DateTime, nullable=False, default=datetime.utcnow) updated: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) class Author(db.Model): id: Mapped[int] = mapped_column(Integer, primary_key=True) username: Mapped[str] = mapped_column(String, unique=True, nullable=False) class Post(TimestampModel): id: Mapped[int] = mapped_column(Integer, primary_key=True) title: Mapped[str] = mapped_column(String, nullable=False) This can also be done with a mixin class, inheriting from db.Model separately. class TimestampMixin: created: Mapped[datetime] = mapped_column(DateTime, nullable=False, default=datetime.utcnow) updated: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) class Post(TimestampMixin, db.Model): id: Mapped[int] = mapped_column(Integer, primary_key=True) title: Mapped[str] = mapped_column(String, nullable=False) Disabling Table Name Generation 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 setting disable_autonaming=True in the SQLAlchemy constructor. class Base(sa_orm.DeclarativeBase): pass db = SQLAlchemy(app, model_class=Base, disable_autonaming=True) Session Class Flask-SQLAlchemy's Session <#flask_sqlalchemy.session.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}) Query Class 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}) API REFERENCE API Extension class flask_sqlalchemy.SQLAlchemy(app=None, *, metadata=None, session_options=None, query_class=, model_class=, 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 o app (Flask | None) -- Call init_app() on this Flask application now. o metadata (sa.MetaData | None) -- Use this as the default sqlalchemy.schema.MetaData . Useful for setting a naming convention. o 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. o query_class (type [Query <#flask_sqlalchemy.query .Query>]) -- Use this as the default query class for models and dynamic relationships. The query interface is considered legacy in SQLAlchemy. o 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. o 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. o add_models_to_shell (bool ) -- Add the db instance and all model classes to flask shell. o 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. 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. 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. 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. 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 o bind_key -- Used to select a different metadata. o args -- Arguments passed to the base class. These are typically the table's name, columns, and constraints. o 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_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 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 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. dynamic_loader(argument, **kwargs) 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. Parameters o argument (Any ) o kwargs (Any ) Return type RelationshipProperty [Any ] 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 <# flask_sqlalchemy.config.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. 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 <#flask_sqlalchemy .config.SQLALCHEMY_BINDS> config, and set defaults by passing the engine_options parameter to the extension. This requires that a Flask application context is active. 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 o statement (Select ) -- The select statement to execute. o description (str | None) -- A custom message to show on the error page. Return type Any Added in version 3.0. 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 o bind_key (str | None) -- The name of the engine. o kwargs (Any ) Return type Engine Deprecated since version 3.0: Will be removed in Flask-SQLAlchemy 3.2. Use engines[key] instead. 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 o entity (type [_O]) -- The model class to query. o ident (Any ) -- The primary key to query. o description (str | None) -- A custom message to show on the error page. o kwargs (Any ) -- Extra arguments passed to session.get(). Return type _O Changed in version 3.1: Pass extra keyword arguments to session.get(). Added in version 3.0. 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: o SQLALCHEMY_DATABASE_URI <#flask_sqlalchemy.config .SQLALCHEMY_DATABASE_URI> o SQLALCHEMY_ENGINE_OPTIONS <#flask_sqlalchemy.config .SQLALCHEMY_ENGINE_OPTIONS> o SQLALCHEMY_ECHO <#flask_sqlalchemy.config .SQLALCHEMY_ECHO> o SQLALCHEMY_BINDS <#flask_sqlalchemy.config .SQLALCHEMY_BINDS> o SQLALCHEMY_RECORD_QUERIES <#flask_sqlalchemy.config .SQLALCHEMY_RECORD_QUERIES> o SQLALCHEMY_TRACK_MODIFICATIONS <#flask_sqlalchemy .config.SQLALCHEMY_TRACK_MODIFICATIONS> 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. 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. 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 o statement (Select ) -- The select statement to execute. o description (str | None) -- A custom message to show on the error page. Return type Any 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 o select (Select ) -- The select statement to paginate. o page (int | None) -- The current page, used to calculate the offset. Defaults to the page query arg during a request, or 1 otherwise. o 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. o 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. o 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. o 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 <#flask_sqlalchemy.pagination .Pagination> Changed in version 3.0: The count query is more efficient. Added in version 3.0. 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 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. Changed in version 3.0: The Query class is set on backref. Parameters o args (Any ) o kwargs (Any ) Return type RelationshipProperty [Any ] 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. Changed in version 3.0: The session is scoped to the current app context. 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: t.ClassVar[Query <#flask_sqlalchemy.query.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. query_class Query class used by query. Defaults to SQLAlchemy.Query, which defaults to Query. alias of Query 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 o name (str ) o bases (tuple [type , ...]) o d (dict [str , t.Any]) o kwargs (t.Any) 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 o name (str ) o bases (tuple [type , ...]) o d (dict [str , t.Any]) o kwargs (t.Any) 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 o name (str ) o bases (tuple [type , ...]) o d (dict [str , t.Any]) o kwargs (t.Any) 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. Changed in version 3.0: Renamed from SignallingSession. Parameters o db (SQLAlchemy <#flask_sqlalchemy.SQLAlchemy>) o kwargs (t.Any) 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. 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 o mapper (Any | None) o clause (Any | None) o bind (Engine | Connection | None) o kwargs (Any ) 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(). 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. 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. 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 <#flask_sqlalchemy.pagination .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 <#flask_sqlalchemy.pagination .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 o left_edge (int ) -- How many pages to show from the first page. o left_current (int ) -- How many pages to show left of the current page. o right_current (int ) -- How many pages to show right of the current page. o right_edge (int ) -- How many pages to show from the last page. Return type 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. 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. Changed in version 3.0: Renamed to Query from BaseQuery. Parameters o entities (Union[_ColumnsClauseArgument[Any], Sequence[_ColumnsClauseArgument[Any]]]) o session (Optional[Session <#flask_sqlalchemy.session .Session>]) 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 get_or_404(ident, description=None) Like get() but aborts with a 404 Not Found error instead of returning None. Parameters o ident (Any ) -- The primary key to query. o 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 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 o page (int | None) -- The current page, used to calculate the offset. Defaults to the page query arg during a request, or 1 otherwise. o 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. o 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. o 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. o 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 <#flask_sqlalchemy.pagination .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. 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 <#flask_sqlalchemy.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. 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. ADDITIONAL INFORMATION BSD-3-Clause License 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. Changes Version 3.1.1 Released 2023-09-11 o Deprecate the __version__ attribute. Use feature detection, or importlib.metadata.version("flask-sqlalchemy"), instead. #5230 Version 3.1.0 Released 2023-09-11 o Drop support for Python 3.7. #1251 o Add support for the SQLAlchemy 2.x API via model_class parameter. #1140 o Bump minimum version of SQLAlchemy to 2.0.16. o Remove previously deprecated code. o Pass extra keyword arguments from get_or_404 to session.get. #1149 o Fix bug with finding right bind key for clause statements. #1211 Version 3.0.5 Released 2023-06-21 o Pagination.next() enforces max_per_page. #1201 o Improve type hint for get_or_404 return value to be non-optional. #1226 Version 3.0.4 Released 2023-06-19 o Fix type hint for get_or_404 return value. #1208 o Fix type hints for pyright (used by VS Code Pylance extension). #1205 Version 3.0.3 Released 2023-01-31 o Show helpful errors when mistakenly using multiple SQLAlchemy instances for the same app, or without calling init_app. #1151 o Fix issue with getting the engine associated with a model that uses polymorphic table inheritance. #1155 Version 3.0.2 Released 2022-10-14 o Update compatibility with SQLAlchemy 2. #1122 Version 3.0.1 Released 2022-10-11 o Export typing information instead of using external typeshed definitions. #1112 o If default engine options are set, but SQLALCHEMY_DATABASE_URI is not set, an invalid default bind will not be configured. #1117 Version 3.0.0 Released 2022-10-04 o Drop support for Python 2, 3.4, 3.5, and 3.6. o Bump minimum version of Flask to 2.2. o Bump minimum version of SQLAlchemy to 1.4.18. o Remove previously deprecated code. o 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. o 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 o 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. o SQLALCHEMY_DATABASE_URI does not default to sqlite:///:memory:. An error is raised if neither it nor SQLALCHEMY_BINDS define any engines. #731 o 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 o 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 o 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. o All parameters to SQLAlchemy except app are keyword-only. o Renamed the bind parameter to bind_key and removed the app parameter from various SQLAlchemy methods. o The extension object uses __getattr__ to alias names from the SQLAlchemy package, rather than copying them as attributes. o The extension object is stored directly as app.extensions["sqlalchemy"]. #698 o The session class can be customized by passing the class_ key in the session_options parameter. #327 o SignallingSession is renamed to Session. o Session.get_bind more closely matches the base implementation. o Model classes and the db instance are available without imports in flask shell. #1089 o 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 o Model repr distinguishes between transient and pending instances. #967 o A custom model class can implement __init_subclass__ with class parameters. #1002 o db.Table is a subclass instead of a function. o The engine_options parameter is applied as defaults before per-engine configuration. o 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 o Engines are created when calling init_app rather than the first time they are accessed. #698 o db.engines exposes the map of bind keys to engines for the current app. o get_engine, get_tables_for_bind, and get_binds are deprecated. o SQLite driver-level URIs that look like sqlite:///file:name.db?uri=true are supported. #998 #1045 o SQLite engines do not use NullPool if pool_size is 0. o MySQL engines use the "utf8mb4" charset by default. #875 o MySQL engines do not set pool_size to 10. o MySQL engines don't set a default for pool_recycle if not using a queue pool. #803 o Query is renamed from BaseQuery. o Added Query.one_or_404. o The query class is applied to backref in relationship. #417 o Creating Pagination objects manually is no longer a public API. They should be created with db.paginate or query.paginate. #1088 o Pagination.iter_pages and Query.paginate parameters are keyword-only. o Pagination is iterable, iterating over its items. #70 o Pagination count query is more efficient. o Pagination.iter_pages is more efficient. #622 o Pagination.iter_pages right_current parameter is inclusive. o Pagination per_page cannot be 0. #1091 o Pagination max_per_page defaults to 100. #1091 o Added Pagination.first and last properties, which give the number of the first and last item on the page. #567 o SQLALCHEMY_RECORD_QUERIES is disabled by default, and is not enabled automatically with app.debug or app.testing. #1092 o get_debug_queries is renamed to get_recorded_queries to better match the config and functionality. o 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. o SQLALCHEMY_TRACK_MODIFICATIONS is disabled by default. #727 o SQLALCHEMY_COMMIT_ON_TEARDOWN is deprecated. It can cause various design issues that are difficult to debug. Call db.session.commit() directly instead. #216 Version 2.5.1 Released 2021-03-18 o Fix compatibility with Python 2.7. Version 2.5.0 Released 2021-03-18 o Update to support SQLAlchemy 1.4. o SQLAlchemy URL objects are immutable. Some internal methods have changed to return a new URL instead of None. #885 Version 2.4.4 Released 2020-07-14 o Change base class of meta mixins to type. This fixes an issue caused by a regression in CPython 3.8.4. #852 Version 2.4.3 Released 2020-05-26 o Deprecate SQLALCHEMY_COMMIT_ON_TEARDOWN as it can cause various design issues that are difficult to debug. Call db.session.commit() directly instead. #216 Version 2.4.2 Released 2020-05-25 o Fix bad pagination when records are de-duped. #812 Version 2.4.1 Released 2019-09-24 o Fix AttributeError when using multiple binds with polymorphic models. #651 Version 2.4.0 Released 2019-04-24 o Drop support for Python 2.6 and 3.3. #687 o Address SQLAlchemy 1.3 deprecations. #684 o 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 o get_or_404() and first_or_404() now accept a description parameter to control the 404 message. #636 o Use time.perf_counter for Python 3 on Windows. #638 o Add an example of Flask's tutorial project, Flaskr, adapted for Flask-SQLAlchemy. #720 Version 2.3.2 Released 2017-10-11 o Don't mask the parent table for single-table inheritance models. #561 Version 2.3.1 Released 2017-10-05 o 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 o Raise the correct error when a model has a table name but no primary key. #556 o Fix repr on models that don't have an identity because they have not been flushed yet. #555 o Allow specifying a max_per_page limit for pagination, to avoid users specifying high values in the request args. #542 o For paginate with error_out=False, the minimum value for page is 1 and per_page is 0. #558 Version 2.3.0 Released 2017-09-28 o 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 o 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 o 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 o 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 o Models have a default repr that shows the model name and primary key. #530 o 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 Version 2.2 Released 2017-02-27, codename Dubnium o Minimum SQLAlchemy version is 0.8 due to use of sqlalchemy.inspect. o Added support for custom query_class and model_class as args to the SQLAlchemy constructor. #328 o Allow listening to SQLAlchemy events on db.session. #364 o Allow __bind_key__ on abstract models. #373 o Allow SQLALCHEMY_ECHO to be a string. #409 o Warn when SQLALCHEMY_DATABASE_URI is not set. #443 o Don't let pagination generate invalid page numbers. #460 o Drop support of Flask < 0.10. This means the db session is always tied to the app context and its teardown event. #461 o Tablename generation logic no longer accesses class properties unless they are declared_attr. #467 Version 2.1 Released 2015-10-23, codename Caesium o Table names are automatically generated in more cases, including subclassing mixins and abstract models. o Allow using a custom MetaData object. o Add support for binds parameter to session. Version 2.0 Released 2014-08-29, codename Bohrium o 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. o Added a way to control how signals for model modifications are tracked. o Made the SignallingSession a public interface and added a hook for customizing session creation. o If the bind parameter is given to the signalling session it will no longer cause an error that a parameter is given twice. o Added working table reflection support. o Enabled autoflush by default. o Consider SQLALCHEMY_COMMIT_ON_TEARDOWN harmful and remove from docs. Version 1.0 Released 2013-07-20, codename Aurum o Added Python 3.3 support. o Dropped Python 2.5 compatibility. o Various bugfixes. o Changed versioning format to do major releases for each update now. Version 0.16 o New distribution format (flask_sqlalchemy). o Added support for Flask 0.9 specifics. Version 0.15 o Added session support for multiple databases. Version 0.14 o Make relative sqlite paths relative to the application root. Version 0.13 o Fixed an issue with Flask-SQLAlchemy not selecting the correct binds. Version 0.12 o Added support for multiple databases. o Expose BaseQuery as db.Query. o Set default query_class for db.relation, db.relationship, and db.dynamic_loader to BaseQuery. o Improved compatibility with Flask 0.7. Version 0.11 o Fixed a bug introduced in 0.10 with alternative table constructors. Version 0.10 o Added support for signals. o Table names are now automatically set from the class name unless overridden. o Model.query now always works for applications directly passed to the SQLAlchemy constructor. Furthermore the property now raises a RuntimeError instead of being None. o Added session options to constructor. o Fixed a broken __repr__. o db.Table is now a factory function that creates table objects. This makes it possible to omit the metadata. Version 0.9 o Applied changes to pass the Flask extension approval process. Version 0.8 o Added a few configuration keys for creating connections. o Automatically activate connection recycling for MySQL connections. o Added support for the Flask testing mode. Version 0.7 o Initial public release Author Pallets Copyright 2010 Pallets 3.1.x December 15, 2025 FLASK-SQLALCHEMY(1)