sqlalchemy 2.0.51: orm.Mapped, orm.mapped_column, orm.relationship

Clean-room public code sample — generation instructions

Write a brand-new, minimal, self-contained code sample in this clean-room directory.
Do not copy, paraphrase, or reference any existing project source. Work only from this spec.

A csx.json manifest scaffold already exists. Do not recreate it from memory. Preserve its case.goal, packages and symbols; fill its empty case.contract with exact assertions and correct its environment, commands and verifierAdapter for the files you generate.

Goal: verify selectinload relationship loading and cascade deletion in pkg:pypi/sqlalchemy@2.0.51
Kind: HOW

Use EXACTLY these public packages and versions:
  - pkg:pypi/sqlalchemy@2.0.51
Demonstrate these symbols/APIs:
  - sqlalchemy.orm.Mapped
  - sqlalchemy.orm.mapped_column
  - sqlalchemy.orm.relationship
  - sqlalchemy.orm.selectinload

Rules:
  - One focused purpose; the smallest project that proves the goal.
  - Include a contract test (test/contract.*) that runs OFFLINE and exits 0 exactly when the goal behavior works.
  - Pin every dependency with a lockfile so resolution is reproducible.
  - No secrets, credentials, or tokens. No real URLs (only example.com or localhost). No absolute paths.
  - No personal names, emails, company names, or project identifiers of any kind.
  - No binaries and no generated output (node_modules, dist, target, venv, .git, .env).
  - Keep it under 200 files and 256KB packed.

{"case":{"caseId":"case:sha256:0ba49d773dea064a058cfd0edd00bc978c09345845edacf20f415810b0ab7c26","contract":["sqlalchemy.orm.relationship maps parent-child models with cascade delete-orphan configuration","sqlalchemy.orm.selectinload eagerly loads child collections across session operations","Orphan removal and parent deletion cascade delete child entities from database"],"goal":"verify selectinload relationship loading and cascade deletion in pkg:pypi/sqlalchemy@2.0.51","kind":"HOW","packages":["pkg:pypi/sqlalchemy@2.0.51"],"schemaVersion":1,"symbols":["sqlalchemy.orm.Mapped","sqlalchemy.orm.mapped_column","sqlalchemy.orm.relationship","sqlalchemy.orm.selectinload"]},"contractCommand":["python","test/contract.py"],"environment":{"arch":"x64","distro":"ubuntu","ecosystem":"pypi","libc":"glibc","libcVersion":"2.39","os":"linux","osVersionBucket":"24","packageManager":"pip","schemaVersion":1},"license":"MIT-0","packages":["pkg:pypi/sqlalchemy@2.0.51"],"schemaVersion":1,"subject":"pkg:pypi/sqlalchemy@2.0.51","symbols":["sqlalchemy.orm.Mapped","sqlalchemy.orm.mapped_column","sqlalchemy.orm.relationship","sqlalchemy.orm.selectinload"],"verifierAdapter":"python@1"}

greenlet==3.5.5
sqlalchemy==2.0.51
typing-extensions==4.16.0

"""SQLAlchemy 2.0 selectinload relationship and cascade deletion sample."""

from typing import List, Optional
from sqlalchemy import ForeignKey, String, create_engine, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, selectinload, Session


class Base(DeclarativeBase):
    pass


class Author(Base):
    __tablename__ = "authors"

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    name: Mapped[str] = mapped_column(String(100), nullable=False)

    books: Mapped[List["Book"]] = relationship(
        back_populates="author",
        cascade="all, delete-orphan",
        order_by="Book.id",
    )

    def __repr__(self) -> str:
        return f"<Author(id={self.id}, name='{self.name}')>"


class Book(Base):
    __tablename__ = "books"

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    title: Mapped[str] = mapped_column(String(200), nullable=False)
    author_id: Mapped[int] = mapped_column(ForeignKey("authors.id"), nullable=False)

    author: Mapped["Author"] = relationship(
        back_populates="books",
    )

    def __repr__(self) -> str:
        return f"<Book(id={self.id}, title='{self.title}')>"


def create_database(db_url: str = "sqlite:///:memory:"):
    """Create engine and initialize tables."""
    engine = create_engine(db_url, echo=False)
    Base.metadata.create_all(engine)
    return engine


def create_author_with_books(session: Session, name: str, book_titles: List[str]) -> Author:
    """Create an author with associated books."""
    author = Author(name=name)
    author.books = [Book(title=title) for title in book_titles]
    session.add(author)
    session.commit()
    session.refresh(author)
    return author


def get_authors_with_books(session: Session) -> List[Author]:
    """Load all authors with books eagerly loaded via selectinload."""
    stmt = select(Author).options(selectinload(Author.books)).order_by(Author.id)
    return list(session.scalars(stmt).all())


if __name__ == "__main__":
    engine = create_database()
    with Session(engine) as session:
        author = create_author_with_books(session, "Alice", ["Book One", "Book Two"])
        print(f"Created author: {author} with {len(author.books)} books")
        loaded_authors = get_authors_with_books(session)
        print(f"Loaded {len(loaded_authors)} authors with selectinload")

{
  "schemaVersion": 1,
  "goal": "verify selectinload relationship loading and cascade deletion in pkg:pypi/sqlalchemy@2.0.51",
  "kind": "HOW",
  "packages": [
    "pkg:pypi/sqlalchemy@2.0.51"
  ],
  "symbols": [
    "sqlalchemy.orm.Mapped",
    "sqlalchemy.orm.mapped_column",
    "sqlalchemy.orm.relationship",
    "sqlalchemy.orm.selectinload"
  ]
}

"""Contract verification test for SQLAlchemy 2.0 relationship loading and cascade deletion."""

import os
import sys

# Ensure project root is on sys.path for local module imports
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from sqlalchemy import select
from sqlalchemy.orm import Session
from sample import Author, Book, create_database, create_author_with_books, get_authors_with_books


def test_relationship_mapping() -> None:
    """Assertion 1: sqlalchemy.orm.relationship maps parent-child models with cascade delete-orphan configuration."""
    assert hasattr(Author, "books"), "Author model must have books relationship attribute"
    assert hasattr(Book, "author"), "Book model must have author relationship attribute"

    # Check relationship property
    rel_books = Author.__mapper__.relationships["books"]
    assert rel_books.back_populates == "author", "Author.books must back_populate 'author'"
    assert "delete-orphan" in rel_books.cascade, "Author.books cascade must include delete-orphan"

    rel_author = Book.__mapper__.relationships["author"]
    assert rel_author.back_populates == "books", "Book.author must back_populate 'books'"


def test_selectinload_eager_loading() -> None:
    """Assertion 2: sqlalchemy.orm.selectinload eagerly loads child collections across session operations."""
    engine = create_database()
    with Session(engine) as session:
        create_author_with_books(session, "Alice", ["Book One", "Book Two"])
        create_author_with_books(session, "Bob", ["Book Three"])

    # Open a new session and verify eager loading with selectinload
    with Session(engine) as session:
        authors = get_authors_with_books(session)
        assert len(authors) == 2, f"Expected 2 authors, got {len(authors)}"

        alice = authors[0]
        assert alice.name == "Alice"
        assert len(alice.books) == 2
        assert [b.title for b in alice.books] == ["Book One", "Book Two"]

        bob = authors[1]
        assert bob.name == "Bob"
        assert len(bob.books) == 1
        assert bob.books[0].title == "Book Three"


def test_cascade_orphan_deletion() -> None:
    """Assertion 3: Orphan removal and parent deletion cascade delete child entities from database."""
    engine = create_database()
    with Session(engine) as session:
        author = create_author_with_books(session, "Charlie", ["Draft 1", "Draft 2", "Draft 3"])
        author_id = author.id
        book_ids = [b.id for b in author.books]
        assert len(book_ids) == 3

        # Remove one book from the relationship collection (orphan removal)
        removed_book = author.books.pop(0)
        session.commit()

        # Check orphan book is deleted from DB
        orphaned = session.scalars(select(Book).where(Book.id == removed_book.id)).first()
        assert orphaned is None, "Orphaned book must be removed from database"

        # Delete the entire author
        session.delete(author)
        session.commit()

        # Check author and all remaining child books are deleted
        deleted_author = session.scalars(select(Author).where(Author.id == author_id)).first()
        assert deleted_author is None, "Deleted author must not exist in database"

        remaining_books = session.scalars(select(Book).where(Book.author_id == author_id)).all()
        assert len(remaining_books) == 0, "All cascaded child books must be deleted"


def main() -> None:
    test_relationship_mapping()
    test_selectinload_eager_loading()
    test_cascade_orphan_deletion()
    print("All contract assertions passed successfully.")


if __name__ == "__main__":
    main()