Complete worked example
python
from sqlalchemy import CheckConstraint, String, create_engine
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session
class Base(DeclarativeBase):
pass
class Role(Base):
__tablename__ = "roles"
__table_args__ = (CheckConstraint("length(title) > 0", name="ck_roles_title"),)
id: Mapped[int] = mapped_column(primary_key=True)
title: Mapped[str] = mapped_column(String(80))
score: Mapped[int | None] = mapped_column(nullable=True)
def inspect_example(title, score):
engine = create_engine("sqlite+pysqlite://", connect_args={"autocommit": False})
try:
Base.metadata.create_all(engine)
with Session(engine) as session, session.begin():
role = Role(title=title, score=score)
session.add(role)
session.flush()
return {"title": role.title, "score": role.score,
"scoreNullable": Role.__table__.c.score.nullable}
finally:
engine.dispose()- Reasoning
- The example creates only its owned schema, adds one Role inside a Session transaction and flushes it. The returned report contains the stored values and the column’s actual nullable flag. With title QA and a missing score, the expected score is None and scoreNullable is True. The optional annotation and the explicit nullable=True agree here. Explicit mapping configuration can override an inferred choice, so read both rather than trusting the annotation alone. The named CHECK is separate: an empty title reaches the database and is rejected with IntegrityError, even though its Python type is str.