sqlalchemy 2.0.51: Column

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

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:c3c3be55a90fa8bc245489b1b91aa2227780c79584ea6231a4b0b95363a069ba","contract":["sqlalchemy.Column defines column schema metadata including name, type, primary_key, nullable, unique, and column defaults","sqlalchemy.Column supports SQL expression constructs including comparison predicates, sorting order, and label aliases","sqlalchemy.Column executes within Table operations to enforce types, populate defaults, and roundtrip data in queries"],"goal":"verify sqlalchemy.Column in pkg:pypi/sqlalchemy@2.0.51","kind":"HOW","packages":["pkg:pypi/sqlalchemy@2.0.51"],"schemaVersion":1,"symbols":["sqlalchemy.Column"]},"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.Column"],"verifierAdapter":"python@1"}

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

"""Demonstration of relational table column definition and operations using sqlalchemy.Column."""

from typing import Any, Dict, List, Optional
from sqlalchemy import (
    Column,
    ForeignKey,
    Integer,
    MetaData,
    String,
    Table,
    create_engine,
    insert,
    select,
    update,
)
from sqlalchemy.engine import Engine


def create_items_table(metadata: MetaData) -> Table:
    """Define items table demonstrating Column configuration with various types and attributes."""
    return Table(
        "items",
        metadata,
        Column("id", Integer, primary_key=True, autoincrement=True),
        Column("name", String(50), nullable=False, unique=True),
        Column("category", String(30), nullable=False, default="general"),
        Column("quantity", Integer, nullable=False, default=0),
        Column("description", String(200), nullable=True),
    )


def create_orders_table(metadata: MetaData, items_table: Table) -> Table:
    """Define orders table demonstrating Column with ForeignKey reference."""
    return Table(
        "orders",
        metadata,
        Column("order_id", Integer, primary_key=True, autoincrement=True),
        Column("item_id", Integer, ForeignKey("items.id"), nullable=False),
        Column("amount", Integer, nullable=False, default=1),
    )


def init_database(engine: Engine, metadata: MetaData) -> None:
    """Initialize schema on target database."""
    metadata.create_all(engine)


def insert_item(
    engine: Engine,
    table: Table,
    name: str,
    category: Optional[str] = None,
    quantity: int = 0,
    description: Optional[str] = None,
) -> int:
    """Insert an item using table columns and return inserted ID."""
    values: Dict[str, Any] = {"name": name, "quantity": quantity}
    if category is not None:
        values["category"] = category
    if description is not None:
        values["description"] = description

    stmt = insert(table).values(**values)
    with engine.begin() as conn:
        result = conn.execute(stmt)
        inserted_pk = result.inserted_primary_key
        return inserted_pk[0] if inserted_pk else 0


def select_items_by_category(
    engine: Engine,
    table: Table,
    category: str,
) -> List[Dict[str, Any]]:
    """Query items filtering on Column equality."""
    stmt = (
        select(table.c.id, table.c.name, table.c.category, table.c.quantity)
        .where(table.c.category == category)
        .order_by(table.c.id)
    )
    with engine.connect() as conn:
        rows = conn.execute(stmt).fetchall()
        return [
            {"id": row.id, "name": row.name, "category": row.category, "quantity": row.quantity}
            for row in rows
        ]


def update_item_quantity(
    engine: Engine,
    table: Table,
    item_id: int,
    new_quantity: int,
) -> int:
    """Update item quantity using Column expressions."""
    stmt = (
        update(table)
        .where(table.c.id == item_id)
        .values({table.c.quantity: new_quantity})
    )
    with engine.begin() as conn:
        result = conn.execute(stmt)
        return result.rowcount

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

"""Contract verification test for sqlalchemy.Column."""

import os
import sys

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

from sqlalchemy import (
    Column,
    Integer,
    MetaData,
    String,
    create_engine,
)
from sqlalchemy.sql.elements import BinaryExpression, Label
import sample


def test_column_metadata_and_attributes() -> None:
    """Assertion 1: sqlalchemy.Column defines column schema metadata including name, type, primary_key, nullable, unique, and column defaults."""
    metadata = MetaData()
    items = sample.create_items_table(metadata)

    # Column existence and instances
    assert "id" in items.c
    assert "name" in items.c
    assert "category" in items.c
    assert "quantity" in items.c
    assert "description" in items.c

    id_col = items.c.id
    assert isinstance(id_col, Column)
    assert id_col.name == "id"
    assert isinstance(id_col.type, Integer)
    assert id_col.primary_key is True
    assert id_col.nullable is False

    name_col = items.c.name
    assert isinstance(name_col, Column)
    assert name_col.name == "name"
    assert isinstance(name_col.type, String)
    assert name_col.nullable is False
    assert name_col.unique is True

    cat_col = items.c.category
    assert isinstance(cat_col, Column)
    assert cat_col.nullable is False
    assert cat_col.default is not None
    assert cat_col.default.arg == "general"

    qty_col = items.c.quantity
    assert isinstance(qty_col, Column)
    assert qty_col.default is not None
    assert qty_col.default.arg == 0

    desc_col = items.c.description
    assert isinstance(desc_col, Column)
    assert desc_col.nullable is True


def test_column_sql_expressions() -> None:
    """Assertion 2: sqlalchemy.Column supports SQL expression constructs including comparison predicates, sorting order, and label aliases."""
    metadata = MetaData()
    items = sample.create_items_table(metadata)
    orders = sample.create_orders_table(metadata, items)

    # Comparison expressions
    eq_expr = items.c.category == "hardware"
    assert isinstance(eq_expr, BinaryExpression)

    gt_expr = items.c.quantity > 5
    assert isinstance(gt_expr, BinaryExpression)

    # Ordering expressions
    desc_order = items.c.id.desc()
    assert desc_order.modifier is not None or "DESC" in str(desc_order)

    # Label aliasing
    labeled = items.c.name.label("item_title")
    assert isinstance(labeled, Label)
    assert labeled.name == "item_title"

    # Foreign key reference on column
    fk_col = orders.c.item_id
    assert len(fk_col.foreign_keys) == 1
    fk = list(fk_col.foreign_keys)[0]
    assert fk.target_fullname == "items.id"
    assert fk_col.references(items.c.id) is True


def test_column_execution_and_roundtrip() -> None:
    """Assertion 3: sqlalchemy.Column executes within Table operations to enforce types, populate defaults, and roundtrip data in queries."""
    engine = create_engine("sqlite:///:memory:")
    metadata = MetaData()
    items = sample.create_items_table(metadata)
    sample.init_database(engine, metadata)

    # Insert with defaults (category omitted -> default 'general', quantity omitted -> default 0)
    id1 = sample.insert_item(engine, items, name="Widget A")
    assert id1 == 1

    # Insert with explicit values
    id2 = sample.insert_item(
        engine, items, name="Tool B", category="tools", quantity=15, description="A useful tool"
    )
    assert id2 == 2

    id3 = sample.insert_item(
        engine, items, name="Tool C", category="tools", quantity=8
    )
    assert id3 == 3

    # Query with column equality
    tools = sample.select_items_by_category(engine, items, "tools")
    assert len(tools) == 2
    assert tools[0]["name"] == "Tool B"
    assert tools[0]["quantity"] == 15
    assert tools[0]["category"] == "tools"
    assert tools[1]["name"] == "Tool C"
    assert tools[1]["quantity"] == 8

    general_items = sample.select_items_by_category(engine, items, "general")
    assert len(general_items) == 1
    assert general_items[0]["name"] == "Widget A"
    assert general_items[0]["quantity"] == 0

    # Update using column reference
    rows_updated = sample.update_item_quantity(engine, items, item_id=id2, new_quantity=20)
    assert rows_updated == 1

    updated_tools = sample.select_items_by_category(engine, items, "tools")
    assert updated_tools[0]["quantity"] == 20


def main() -> None:
    test_column_metadata_and_attributes()
    test_column_sql_expressions()
    test_column_execution_and_roundtrip()
    print("All sqlalchemy.Column contract assertions passed successfully.")


if __name__ == "__main__":
    main()