Complete worked example
python
from sqlalchemy import create_engine, event, ForeignKey, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, Session
class Base(DeclarativeBase):
pass
class Employer(Base):
__tablename__ = "employers"
id: Mapped[int] = mapped_column(primary_key=True)
roles: Mapped[list["Role"]] = relationship(back_populates="employer")
class Role(Base):
__tablename__ = "roles"
id: Mapped[int] = mapped_column(primary_key=True)
employer_id: Mapped[int] = mapped_column(ForeignKey("employers.id", ondelete="RESTRICT"))
employer: Mapped[Employer] = relationship(back_populates="roles")
from sqlalchemy import insert
from sqlalchemy.exc import IntegrityError
def inspect_example(counts):
engine = create_engine("sqlite+pysqlite://", connect_args={"autocommit": False})
@event.listens_for(engine, "connect")
def enable_foreign_keys(connection, record):
previous = connection.autocommit
connection.autocommit = True
try:
connection.execute("PRAGMA foreign_keys=ON").close()
finally:
connection.autocommit = previous
try:
Base.metadata.create_all(engine)
with Session(engine) as session, session.begin():
for i, count in enumerate(counts, 1):
session.add(Employer(id=i, roles=[Role() for _ in range(count)]))
with Session(engine) as session:
observed = [len(e.roles) for e in session.scalars(select(Employer).order_by(Employer.id))]
rejected = False
try:
with engine.begin() as connection:
connection.execute(insert(Role).values(id=9999, employer_id=9999))
except IntegrityError:
rejected = True
return {"counts": observed, "orphanRejected": rejected}
finally:
engine.dispose()- Reasoning
- The example adds each Employer with a supplied number of Role children and reads the resulting collection sizes. It then opens a separate Core transaction and deliberately tries to insert a role referencing employer 9999, which the fixture has never created. The expected IntegrityError proves the database rejected that orphan. For counts [2, 0, 1], the report retains all three employers and says orphanRejected is True. back_populates gives the two mapped attributes a matching relationship; it does not itself execute the PRAGMA or replace the database foreign key. The source keeps those responsibilities explicit.