sqlalchemy 2.0.51: String

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.String 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.String

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:f4672a544db625fefcef0d6ed55d9a95eb216a613e4346d44aa7ed5a4b89075e","contract":["sqlalchemy.String defines column length and collation constraints with python_type str","sqlalchemy.String with mapped_column validates string persistence and nullability in declarative models","sqlalchemy.String supports SQL string expressions including like, ilike, startswith, endswith, contains, and concatenation"],"goal":"verify sqlalchemy.String in pkg:pypi/sqlalchemy@2.0.51","kind":"HOW","packages":["pkg:pypi/sqlalchemy@2.0.51"],"schemaVersion":1,"symbols":["sqlalchemy.String"]},"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.String"],"verifierAdapter":"python@1"}

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

"""SQLAlchemy 2.0 String column type and expression demonstration."""

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


class Base(DeclarativeBase):
    pass


class Document(Base):
    __tablename__ = "documents"

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    title: Mapped[str] = mapped_column(String(100), nullable=False)
    subtitle: Mapped[Optional[str]] = mapped_column(String(200), nullable=True)
    content: Mapped[str] = mapped_column(String(), nullable=False)

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


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


def add_document(
    session: Session,
    title: str,
    content: str,
    subtitle: Optional[str] = None,
) -> Document:
    """Insert a document record into the database."""
    doc = Document(title=title, subtitle=subtitle, content=content)
    session.add(doc)
    session.commit()
    session.refresh(doc)
    return doc


def search_documents_by_prefix(session: Session, prefix: str) -> List[Document]:
    """Query documents whose title starts with a specific prefix using like."""
    stmt = select(Document).where(Document.title.like(f"{prefix}%")).order_by(Document.id)
    return list(session.scalars(stmt).all())

{
  "schemaVersion": 1,
  "goal": "verify sqlalchemy.String in pkg:pypi/sqlalchemy@2.0.51",
  "kind": "HOW",
  "packages": [
    "pkg:pypi/sqlalchemy@2.0.51"
  ],
  "symbols": [
    "sqlalchemy.String"
  ]
}

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

import os
import sys

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

from sqlalchemy import create_engine, select, func, String
from sqlalchemy.orm import Session
from sample import Base, Document, create_database, add_document, search_documents_by_prefix


def test_string_type_definition() -> None:
    """Assertion 1: sqlalchemy.String defines column length and collation constraints with python_type str."""
    str_default = String()
    assert str_default.length is None, "Default String length must be None"
    assert str_default.python_type is str, "Default String python_type must be str"

    str_bounded = String(100)
    assert str_bounded.length == 100, "String(100) length must be 100"
    assert str_bounded.python_type is str, "Bounded String python_type must be str"

    str_collated = String(50, collation="utf8_bin")
    assert str_collated.length == 50, "String with collation length must be 50"
    assert str_collated.collation == "utf8_bin", "String collation must match"


def test_string_mapped_column_persistence() -> None:
    """Assertion 2: sqlalchemy.String with mapped_column validates string persistence and nullability in declarative models."""
    engine = create_database()

    # Check table column metadata
    table = Document.__table__
    assert "title" in table.columns, "Column 'title' must exist"
    assert isinstance(table.columns["title"].type, String), "Column 'title' must be String type"
    assert table.columns["title"].type.length == 100, "Column 'title' length must be 100"
    assert table.columns["title"].nullable is False, "Column 'title' must be non-nullable"

    assert "subtitle" in table.columns, "Column 'subtitle' must exist"
    assert table.columns["subtitle"].nullable is True, "Column 'subtitle' must be nullable"

    assert "content" in table.columns, "Column 'content' must exist"
    assert table.columns["content"].type.length is None, "Column 'content' has unbounded String"

    # Persist records
    with Session(engine) as session:
        doc1 = add_document(session, title="Guide to SQLAlchemy", content="Full text of chapter 1.", subtitle="Chapter 1: Basics")
        doc2 = add_document(session, title="Advanced SQLAlchemy", content="Full text of advanced topics.", subtitle=None)

        assert doc1.id is not None, "doc1 must have an id"
        assert doc2.id is not None, "doc2 must have an id"

        # Query and verify persistence
        persisted1 = session.scalar(select(Document).where(Document.title == "Guide to SQLAlchemy"))
        assert persisted1 is not None, "doc1 should be found"
        assert persisted1.subtitle == "Chapter 1: Basics"
        assert persisted1.content == "Full text of chapter 1."

        persisted2 = session.scalar(select(Document).where(Document.title == "Advanced SQLAlchemy"))
        assert persisted2 is not None, "doc2 should be found"
        assert persisted2.subtitle is None, "doc2 subtitle should be None"


def test_string_sql_expressions() -> None:
    """Assertion 3: sqlalchemy.String supports SQL string expressions including like, ilike, startswith, endswith, contains, and concatenation."""
    engine = create_database()

    with Session(engine) as session:
        doc1 = add_document(session, title="Alpha Beta", content="Sample content alpha", subtitle="Sub Alpha")
        doc2 = add_document(session, title="Beta Gamma", content="Sample content gamma", subtitle="Sub Gamma")
        doc3 = add_document(session, title="Delta Epsilon", content="Sample content delta", subtitle="Sub Delta")

        # like filter
        like_results = session.scalars(select(Document).where(Document.title.like("Alpha%"))).all()
        assert [d.title for d in like_results] == ["Alpha Beta"], "like filter failed"

        # ilike filter
        ilike_results = session.scalars(select(Document).where(Document.title.ilike("alpha%"))).all()
        assert [d.title for d in ilike_results] == ["Alpha Beta"], "ilike filter failed"

        # startswith
        startswith_results = session.scalars(select(Document).where(Document.title.startswith("Delta"))).all()
        assert [d.title for d in startswith_results] == ["Delta Epsilon"], "startswith filter failed"

        # endswith
        endswith_results = session.scalars(select(Document).where(Document.title.endswith("Gamma"))).all()
        assert [d.title for d in endswith_results] == ["Beta Gamma"], "endswith filter failed"

        # contains
        contains_results = session.scalars(select(Document).where(Document.title.contains("Epsilon"))).all()
        assert [d.title for d in contains_results] == ["Delta Epsilon"], "contains filter failed"

        # String concatenation (+)
        concat_stmt = select(Document.title + " - " + Document.subtitle).where(Document.id == doc1.id)
        concat_result = session.scalar(concat_stmt)
        assert concat_result == "Alpha Beta - Sub Alpha", f"String concat result unexpected: {concat_result}"

        # String length via func.length
        len_stmt = select(func.length(Document.title)).where(Document.id == doc1.id)
        len_result = session.scalar(len_stmt)
        assert len_result == len("Alpha Beta"), f"String length unexpected: {len_result}"


def main() -> None:
    test_string_type_definition()
    test_string_mapped_column_persistence()
    test_string_sql_expressions()
    print("All sqlalchemy.String contract assertions passed successfully.")


if __name__ == "__main__":
    main()