Models and Tables¶
db.Model class to define models, or the
db.Table class to create tables.
Both handle Flask-SQLAlchemy’s bind keys to associate with a specific engine.
See SQLAlchemy’s declarative documentation for full information about defining model classes declaratively.
db.Model to create a model class. This is a SQLAlchemy declarative base
class, it will take
Column attributes and create a table. Unlike plain SQLAlchemy,
Flask-SQLAlchemy’s model will automatically generate a table name if
is not set and a primary key column is defined.
import sqlalchemy as sa class User(db.Model): id = sa.Column(sa.Integer, primary_key=True) type = sa.Column(sa.String)
For convenience, the extension object provides access to names in the
sqlalchemy.orm modules. So you can use
db.Column instead of importing and using
sqlalchemy.Column, although the two are equivalent.
Defining a model does not create it in the database. Use
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
with app.app_context(): db.create_all()
See SQLAlchemy’s table documentation for full information about defining table objects.
Create instances of
db.Table to define tables. The class takes a table name, then
any columns and other table parts such as columns and constraints. Unlike plain
metadata argument is not required. A metadata will be chosen based
bind_key argument, or the default will be used.
A common reason to create a table directly is when defining many to many relationships. The association table doesn’t need its own model class, as it will be accessed through the relevant relationship attributes on the related models.
import sqlalchemy as sa user_book_m2m = db.Table( "user_book", sa.Column("user_id", sa.ForeignKey(User.id), primary_key=True), sa.Column("book_id", sa.ForeignKey(Book.id), primary_key=True), )
If you are connecting to a database that already has tables, SQLAlchemy can detect that
schema and create tables with columns automatically. This is called reflection. Those
tables can also be assigned to model classes with the
__table__ attribute instead of
defining the full model.
reflect() method on the extension. It will reflect all the
tables for each bind key. Each metadata’s
tables attribute will contain the detected
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.