sqlalchemy 2.0.51: Text

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 sqlalchemy.Text 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.Text
Constraints:
  - executionContext: node
Required runtime conditions:
  - ecosystem: npm
  - language: javascript
  - moduleSystem: cjs
  - packageManager: npm@10.9.8
  - runtime: node@22.23.2

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:d6f8040368631a6e4258a3365458e7154285ff41d46dbecd17f556bf0fad0a88","contract":["sqlalchemy.Text is a TypeEngine subclass and String subclass that supports optional length and collation parameters","sqlalchemy.Text column stores and retrieves unbounded multiline and unicode text without truncation","sqlalchemy.Text columns support string filter expressions including contains, startswith, endswith, and is_not","sqlalchemy.Text columns distinguish empty string values from SQL NULL values","sqlalchemy.Text column datatype class is distinct from executable sqlalchemy.text clause constructs"],"goal":"verify sqlalchemy.Text in pkg:pypi/sqlalchemy@2.0.51","kind":"HOW","packages":["pkg:pypi/sqlalchemy@2.0.51"],"schemaVersion":1,"symbols":["sqlalchemy.Text"]},"contractCommand":["python","test/contract.py"],"environment":{"arch":"x64","distro":"ubuntu","ecosystem":"pypi","language":"python","libc":"glibc","libcVersion":"2.39","os":"linux","osVersionBucket":"24","packageManager":"pip","runtime":"python","schemaVersion":1},"license":"MIT-0","packages":["pkg:pypi/sqlalchemy@2.0.51"],"schemaVersion":1,"subject":"pkg:pypi/sqlalchemy@2.0.51","symbols":["sqlalchemy.Text"],"verifierAdapter":"python@1"}

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

"""SQLAlchemy 2.0 Text column type usage and verification sample."""

from typing import List, Optional
import sqlalchemy as sa
from sqlalchemy import String, Text, create_engine, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column


class Base(DeclarativeBase):
    pass


class Article(Base):
    __tablename__ = "articles"

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    title: Mapped[str] = mapped_column(String(100), nullable=False)
    content: Mapped[str] = mapped_column(Text, nullable=False)
    summary: Mapped[Optional[str]] = mapped_column(Text(length=500), nullable=True)
    notes: Mapped[Optional[str]] = mapped_column(Text, nullable=True)

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


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


def insert_article(
    session: Session,
    title: str,
    content: str,
    summary: Optional[str] = None,
    notes: Optional[str] = None,
) -> Article:
    """Insert an article with Text columns."""
    article = Article(
        title=title,
        content=content,
        summary=summary,
        notes=notes,
    )
    session.add(article)
    session.commit()
    session.refresh(article)
    return article


def find_by_content_substring(session: Session, substring: str) -> List[Article]:
    """Query articles where content contains substring."""
    stmt = (
        select(Article)
        .where(Article.content.contains(substring))
        .order_by(Article.id)
    )
    return list(session.scalars(stmt).all())


def find_articles_with_summary(session: Session) -> List[Article]:
    """Query articles where summary is non-null."""
    stmt = (
        select(Article)
        .where(Article.summary.is_not(None))
        .order_by(Article.id)
    )
    return list(session.scalars(stmt).all())


def update_article_content(
    session: Session, article_id: int, new_content: str
) -> Optional[Article]:
    """Update content of an existing article."""
    article = session.get(Article, article_id)
    if article is not None:
        article.content = new_content
        session.commit()
        session.refresh(article)
    return article


if __name__ == "__main__":
    engine = create_database()
    with Session(engine) as session:
        article = insert_article(
            session,
            title="Getting Started with SQLAlchemy Text",
            content="SQLAlchemy Text type handles arbitrarily large text fields.",
            summary="Introductory guide to Text type.",
        )
        print(f"Created article: {article}")
        results = find_by_content_substring(session, "arbitrarily large")
        print(f"Found {len(results)} article(s)")

{
  "schemaVersion": 1,
  "goal": "verify sqlalchemy.Text in pkg:pypi/sqlalchemy@2.0.51",
  "kind": "HOW",
  "packages": [
    "pkg:pypi/sqlalchemy@2.0.51"
  ],
  "symbols": [
    "sqlalchemy.Text"
  ],
  "constraints": {
    "executionContext": "node"
  },
  "runtimeConditions": {
    "ecosystem": "npm",
    "language": "javascript",
    "moduleSystem": "cjs",
    "packageManager": "npm@10.9.8",
    "runtime": "node@22.23.2"
  }
}

"""Contract verification test for sqlalchemy.Text in SQLAlchemy 2.0."""

import os
import sys

# Ensure sample root directory is on sys.path
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

import sqlalchemy as sa
from sqlalchemy import Text, select
from sqlalchemy.orm import Session
from sqlalchemy.types import String, TypeEngine

from sample import (
    Article,
    create_database,
    find_articles_with_summary,
    find_by_content_substring,
    insert_article,
    update_article_content,
)


def test_text_datatype_characteristics() -> None:
    """Verify sqlalchemy.Text type hierarchy and optional length property."""
    # Text is a TypeEngine subclass and String subclass
    assert issubclass(Text, TypeEngine)
    assert issubclass(Text, String)

    # Text instantiated with or without length
    default_text = Text()
    assert default_text.length is None
    assert default_text.collation is None

    sized_text = Text(length=1024, collation="NOCASE")
    assert sized_text.length == 1024
    assert sized_text.collation == "NOCASE"


def test_unbounded_text_roundtrip() -> None:
    """Verify Text column stores and retrieves large unicode and multiline content."""
    engine = create_database()

    # Generate a large multiline unicode payload
    lines = [
        "Line 1: UTF-8 characters: á, é, í, ó, ú, ñ, ü, ß",
        "Line 2: Math symbols: ∑, ∏, ∫, √, ∞, ≈, ≠, ≤, ≥",
        "Line 3: CJK characters: 漢字, 日本語, 한국어",
        "Line 4: Emoji & symbols: 🚀, 💻, 📊, ⚙️, 🔒",
        "Line 5: Repetitive block: " + ("abcdefghijklmnopqrstuvwxyz0123456789 " * 100),
    ]
    large_content = "\n".join(lines * 50)  # Multi-kilobyte text

    with Session(engine) as session:
        article = insert_article(
            session=session,
            title="Unicode and Multiline Benchmark",
            content=large_content,
            summary="Testing unbounded text storage",
        )
        article_id = article.id

        # Expire session cache and re-query
        session.expire_all()
        fetched = session.get(Article, article_id)
        assert fetched is not None
        assert fetched.content == large_content
        assert len(fetched.content) == len(large_content)
        assert "\nLine 3: CJK characters: 漢字, 日本語, 한국어\n" in fetched.content


def test_text_string_filtering_and_updates() -> None:
    """Verify Text column filtering (contains, startswith, endswith, like) and updates."""
    engine = create_database()

    with Session(engine) as session:
        a1 = insert_article(
            session,
            title="Post Alpha",
            content="Prefix: alpha content with unique keyword_xyz in body. Suffix.",
            summary="Alpha summary",
        )
        a2 = insert_article(
            session,
            title="Post Beta",
            content="Prefix: beta content with general details. Suffix.",
            summary=None,
        )

        # Filter by contains
        matches = find_by_content_substring(session, "keyword_xyz")
        assert len(matches) == 1
        assert matches[0].id == a1.id

        # Filter by startswith and endswith
        starts = session.scalars(
            select(Article).where(Article.content.startswith("Prefix:"))
        ).all()
        assert len(starts) == 2

        ends = session.scalars(
            select(Article).where(Article.content.endswith("Suffix."))
        ).all()
        assert len(ends) == 2

        # Filter by summary IS NOT NULL
        with_summary = find_articles_with_summary(session)
        assert len(with_summary) == 1
        assert with_summary[0].id == a1.id

        # Update text column
        updated = update_article_content(
            session, a1.id, "Updated new content for alpha"
        )
        assert updated is not None
        assert updated.content == "Updated new content for alpha"

        session.expire_all()
        refetched = session.get(Article, a1.id)
        assert refetched is not None
        assert refetched.content == "Updated new content for alpha"


def test_empty_string_vs_null_distinction() -> None:
    """Verify Text column differentiates empty string from SQL NULL."""
    engine = create_database()

    with Session(engine) as session:
        empty_note_doc = insert_article(
            session,
            title="Empty Notes",
            content="Has empty notes string",
            notes="",
        )
        null_note_doc = insert_article(
            session,
            title="Null Notes",
            content="Has NULL notes",
            notes=None,
        )

        session.expire_all()

        # Query where notes IS NULL
        null_docs = session.scalars(
            select(Article).where(Article.notes.is_(None))
        ).all()
        assert [d.id for d in null_docs] == [null_note_doc.id]

        # Query where notes == ""
        empty_docs = session.scalars(
            select(Article).where(Article.notes == "")
        ).all()
        assert [d.id for d in empty_docs] == [empty_note_doc.id]


def test_text_datatype_vs_text_construct() -> None:
    """Verify sqlalchemy.Text (datatype) vs sqlalchemy.text (clause construct)."""
    # Text is a type class
    assert isinstance(sa.Text, type)

    # sa.text is a callable function producing TextClause
    assert callable(sa.text)
    clause = sa.text("SELECT :val AS num")
    assert isinstance(clause, sa.sql.elements.TextClause)

    engine = create_database()
    with engine.connect() as conn:
        result = conn.execute(clause, {"val": 123}).scalar()
        assert result == 123

    # Executing the Text type class directly is not valid SQL and raises an error
    with Session(engine) as session:
        try:
            session.execute(sa.Text)  # type: ignore[arg-type]
            raise AssertionError("Passing Text type to execute should raise ArgumentError")
        except (sa.exc.ArgumentError, sa.exc.ObjectNotExecutableError):
            pass


def main() -> None:
    test_text_datatype_characteristics()
    test_unbounded_text_roundtrip()
    test_text_string_filtering_and_updates()
    test_empty_string_vs_null_distinction()
    test_text_datatype_vs_text_construct()
    print("All sqlalchemy.Text contract tests passed successfully.")


if __name__ == "__main__":
    main()