Declarative Models (2.0 Style)
26 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)A model is a Python class that stands for one table. In SQLAlchemy 2.0 you write the columns as type hints, so one line says the Python type, the SQL type and whether the value may be empty.
A class stands for a table
Declarative model is a Python class, based on DeclarativeBase, whose Mapped attributes describe the columns of one database table.
You need one place that describes your tables. A base class collects every model. Each model gets a table name, and each attribute with a Mapped[...] hint becomes a column.
class Base(DeclarativeBase): pass
class Product(Base): __tablename__ = "product" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] = mapped_column(String(80)) price: Mapped[int]Example 1 · Read the table a model describes
from sqlalchemy import create_engine, select, func, String, CheckConstraint, UniqueConstraintfrom sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session
class Base(DeclarativeBase): pass
class Product(Base): __tablename__ = "product" id: Mapped[int] = mapped_column(primary_key=True) sku: Mapped[str] = mapped_column(String(20), unique=True) name: Mapped[str] = mapped_column(String(80)) note: Mapped[str | None] price: Mapped[int] stock: Mapped[int] = mapped_column(default=0)
engine = create_engine("sqlite://")Base.metadata.create_all(engine)
print(Product.__table__.name)for col in Product.__table__.columns: print(col.name, col.type, col.nullable)product id INTEGER False sku VARCHAR(20) False name VARCHAR(80) False note VARCHAR True price INTEGER False stock INTEGER False
What this code does
__table__is the real table object that SQLAlchemy built from your class.- Only
noteis nullable, because its hint ends with| None.
Mapped[int] tells Python and SQLAlchemy that the value is an integer. Plain price: Mapped[int] works without mapped_column when you need no options.
The type hint decides nullable
mapped_column is the function that adds column options to a Mapped attribute, such as primary_key, unique, index, a length, or a default value.
Most columns should always have a value. SQLAlchemy 2.0 reads this from the hint: a plain type is NOT NULL, and a type with | None may be empty. mapped_column adds the options.
class Product(Base): __tablename__ = "product" id: Mapped[int] = mapped_column(primary_key=True) sku: Mapped[str] = mapped_column(String(20), unique=True) note: Mapped[str | None] stock: Mapped[int] = mapped_column(default=0)Example 2 · Python defaults on insert
from sqlalchemy import create_engine, select, func, String, CheckConstraint, UniqueConstraintfrom sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session
class Base(DeclarativeBase): pass
class Product(Base): __tablename__ = "product" id: Mapped[int] = mapped_column(primary_key=True) sku: Mapped[str] = mapped_column(String(20), unique=True) name: Mapped[str] = mapped_column(String(80)) note: Mapped[str | None] price: Mapped[int] stock: Mapped[int] = mapped_column(default=0)
engine = create_engine("sqlite://")Base.metadata.create_all(engine)
with Session(engine) as s: p = Product(sku="M1", name="Mouse", price=500) s.add(p) s.commit() print(p.stock, p.note) q = Product(sku="M2", name="Monitor", price=9000, stock=4) s.add(q) s.commit() print(q.stock)0 None 4
What this code does
- The mouse has no stock value, so Python's
default=0is used. - A value passed in the constructor wins over the default.
note can be empty. sku must be unique. default=0 is a Python default: SQLAlchemy fills it when you insert without a stock value.
Constraints and the generated SQL
Metadata is the object, found at Base.metadata, that holds every table defined by your models and can create them all with create_all.
Rules belong in the database too, not only in Python. __table_args__ holds table-level constraints. create_all turns every model into a CREATE TABLE statement and runs it for tables that do not exist yet.
class Product(Base): __tablename__ = "product" __table_args__ = ( CheckConstraint("price >= 0", name="ck_price_not_negative"), ) id: Mapped[int] = mapped_column(primary_key=True) price: Mapped[int]
Base.metadata.create_all(engine)Example 3 · The CREATE TABLE behind a model
from sqlalchemy import String, CheckConstraintfrom sqlalchemy.dialects import sqlitefrom sqlalchemy.orm import DeclarativeBase, Mapped, mapped_columnfrom sqlalchemy.schema import CreateTable
class Base(DeclarativeBase): pass
class Product(Base): __tablename__ = "product" __table_args__ = (CheckConstraint("price >= 0", name="ck_price_not_negative"),) id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] = mapped_column(String(80)) price: Mapped[int]
sql = str(CreateTable(Product.__table__).compile(dialect=sqlite.dialect()))print("\n".join(line.strip() for line in sql.strip().splitlines()))CREATE TABLE product ( id INTEGER NOT NULL, name VARCHAR(80) NOT NULL, price INTEGER NOT NULL, PRIMARY KEY (id), CONSTRAINT ck_price_not_negative CHECK (price >= 0) )
What this code does
- Each
Mappedline became a column with NOT NULL, and the primary key was added. - The check constraint is part of the table, with the name we gave it.
Example 4 · The database enforces the rules
from sqlalchemy import create_engine, select, func, String, CheckConstraint, UniqueConstraintfrom sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session
class Base(DeclarativeBase): pass
class Product(Base): __tablename__ = "product" id: Mapped[int] = mapped_column(primary_key=True) sku: Mapped[str] = mapped_column(String(20), unique=True) name: Mapped[str] = mapped_column(String(80)) note: Mapped[str | None] price: Mapped[int] stock: Mapped[int] = mapped_column(default=0)
engine = create_engine("sqlite://")Base.metadata.create_all(engine)
from sqlalchemy.exc import IntegrityError
with Session(engine) as s: s.add(Product(sku="A1", name="Pen", price=10)) s.commit() s.add(Product(sku="A1", name="Ink", price=20)) try: s.commit() except IntegrityError as err: s.rollback() print("unique failed:", "product.sku" in str(err.orig)) s.add(Product(sku="B1", name="Pad", price=30)) s.commit() print(s.scalar(select(func.count()).select_from(Product)))unique failed: True 2
What this code does
- The second
A1breaks the unique constraint, so the commit fails withIntegrityError. - After
rollback()the session works again, and the next product is saved.
create_all is fine for tests and small tools. For a real app, a migration tool such as Alembic changes tables safely over time (the third module of this section).
Next step
Check your understanding
11 quick questions