sqlalchemy 2.0.51: sql.elements.ColumnElement

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.sql.elements.ColumnElement 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.sql.elements.ColumnElement

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:b0a5e8ebd9d1ce6bfbb87aa812add1170b858974f99ad8ce52e67a5189d83787","contract":["sqlalchemy.sql.elements.ColumnElement represents column-level SQL expressions and serves as the base class for column clauses, literals, and labeled expressions","sqlalchemy.sql.elements.ColumnElement supports labeling and type casting to produce new ColumnElement instances with specific names and SQL types","sqlalchemy.sql.elements.ColumnElement supports rich comparison and clause generation including between, in_, and order-by modifiers","sqlalchemy.sql.elements.ColumnElement can be subclassed with custom compilation rules to implement custom SQL expressions usable in select statements"],"goal":"verify sqlalchemy.sql.elements.ColumnElement in pkg:pypi/sqlalchemy@2.0.51","kind":"HOW","packages":["pkg:pypi/sqlalchemy@2.0.51"],"schemaVersion":1,"symbols":["sqlalchemy.sql.elements.ColumnElement"]},"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.sql.elements.ColumnElement"],"verifierAdapter":"python@1"}

sqlalchemy==2.0.51

"""Demonstration of SQL column expressions using sqlalchemy.sql.elements.ColumnElement."""

from typing import Any, Sequence, Tuple
import sqlalchemy as sa
from sqlalchemy.sql.elements import ColumnElement, UnaryExpression
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.dialects import sqlite


def create_column_element(name: str, type_: Any = None) -> ColumnElement:
    """Create a ColumnElement representing a named column with an optional type."""
    col = sa.column(name, type_)
    if not isinstance(col, ColumnElement):
        raise TypeError(f"Expected ColumnElement, got {type(col)}")
    return col


def create_literal_element(value: Any) -> ColumnElement:
    """Create a ColumnElement representing a bound literal value."""
    lit = sa.literal(value)
    if not isinstance(lit, ColumnElement):
        raise TypeError(f"Expected ColumnElement, got {type(lit)}")
    return lit


def label_and_cast_element(
    element: ColumnElement,
    label_name: str,
    target_type: Any,
) -> Tuple[ColumnElement, ColumnElement]:
    """Label and cast a ColumnElement, producing new ColumnElement instances."""
    labeled = element.label(label_name)
    casted = element.cast(target_type)
    if not isinstance(labeled, ColumnElement) or not isinstance(casted, ColumnElement):
        raise TypeError("Both labeled and casted elements must be ColumnElement instances")
    return labeled, casted


def build_predicates_and_ordering(
    element: ColumnElement,
    lower: Any,
    upper: Any,
    values: Sequence[Any],
) -> Tuple[ColumnElement, ColumnElement, UnaryExpression]:
    """Generate between, in_, and order-by expressions from a ColumnElement."""
    between_expr = element.between(lower, upper)
    in_expr = element.in_(values)
    desc_expr = element.desc()
    if not isinstance(between_expr, ColumnElement) or not isinstance(in_expr, ColumnElement):
        raise TypeError("Filter expressions must be ColumnElement instances")
    if not isinstance(desc_expr, UnaryExpression):
        raise TypeError("Ordering modifier must be a UnaryExpression")
    return between_expr, in_expr, desc_expr


class CustomUpper(ColumnElement):
    """Custom ColumnElement that converts child string expression to uppercase."""

    inherit_cache = True

    def __init__(self, element: ColumnElement):
        self.element = element
        self.type = sa.String()


@compiles(CustomUpper)
def _compile_custom_upper(element: CustomUpper, compiler: Any, **kw: Any) -> str:
    """Compilation rule for CustomUpper column element."""
    return f"UPPER({compiler.process(element.element, **kw)})"


def build_custom_column_query(table_name: str, col_name: str, match_value: str) -> str:
    """Build and compile a SELECT statement using a custom ColumnElement subclass."""
    col = sa.column(col_name)
    custom_expr = CustomUpper(col)
    table = sa.table(table_name, col)
    stmt = sa.select(table).where(custom_expr == match_value)
    return str(stmt.compile(dialect=sqlite.dialect(), compile_kwargs={"literal_binds": True}))

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

"""Contract verification tests for sqlalchemy.sql.elements.ColumnElement."""

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__))))

import sqlalchemy as sa
from sqlalchemy.sql.elements import ColumnClause, ColumnElement, BindParameter, UnaryExpression
from sqlalchemy.dialects import sqlite
import sample


def test_column_element_representation() -> None:
    """Assertion 1: sqlalchemy.sql.elements.ColumnElement represents column-level SQL expressions and serves as the base class for column clauses, literals, and labeled expressions."""
    col = sample.create_column_element("price", sa.Integer)
    assert isinstance(col, ColumnElement)
    assert isinstance(col, ColumnClause)
    assert col.name == "price"
    assert isinstance(col.type, sa.Integer)

    lit = sample.create_literal_element(100)
    assert isinstance(lit, ColumnElement)
    assert isinstance(lit, BindParameter)
    assert lit.value == 100

    lbl = col.label("item_price")
    assert isinstance(lbl, ColumnElement)
    assert lbl.name == "item_price"


def test_label_and_type_casting() -> None:
    """Assertion 2: sqlalchemy.sql.elements.ColumnElement supports labeling and type casting to produce new ColumnElement instances with specific names and SQL types."""
    col = sample.create_column_element("score", sa.Integer)
    labeled, casted = sample.label_and_cast_element(col, "total_score", sa.Float)

    assert isinstance(labeled, ColumnElement)
    assert labeled.name == "total_score"

    assert isinstance(casted, ColumnElement)
    assert isinstance(casted.type, sa.Float)


def test_predicates_and_ordering() -> None:
    """Assertion 3: sqlalchemy.sql.elements.ColumnElement supports rich comparison and clause generation including between, in_, and order-by modifiers."""
    col = sample.create_column_element("quantity", sa.Integer)
    between_expr, in_expr, desc_expr = sample.build_predicates_and_ordering(col, 10, 50, [10, 20, 30])

    assert isinstance(between_expr, ColumnElement)
    assert isinstance(in_expr, ColumnElement)
    assert isinstance(desc_expr, UnaryExpression)
    assert desc_expr.element is col

    compiled_between = str(between_expr.compile(dialect=sqlite.dialect(), compile_kwargs={"literal_binds": True}))
    assert "quantity BETWEEN 10 AND 50" in compiled_between

    compiled_in = str(in_expr.compile(dialect=sqlite.dialect(), compile_kwargs={"literal_binds": True}))
    assert "quantity IN (10, 20, 30)" in compiled_in


def test_custom_subclass_compilation() -> None:
    """Assertion 4: sqlalchemy.sql.elements.ColumnElement can be subclassed with custom compilation rules to implement custom SQL expressions usable in select statements."""
    col = sample.create_column_element("username", sa.String)
    custom_expr = sample.CustomUpper(col)

    assert isinstance(custom_expr, ColumnElement)
    assert isinstance(custom_expr.type, sa.String)

    query_str = sample.build_custom_column_query("users", "username", "ALICE")
    assert "WHERE UPPER(users.username) = 'ALICE'" in query_str


def main() -> None:
    test_column_element_representation()
    test_label_and_type_casting()
    test_predicates_and_ordering()
    test_custom_subclass_compilation()
    print("All sqlalchemy.sql.elements.ColumnElement contract assertions passed successfully.")


if __name__ == "__main__":
    main()