sqlalchemy 2.0.51: DateTime

# sqlalchemy DateTime API

This sample pins `sqlalchemy` 2.0.51 and verifies `sqlalchemy.DateTime` for schema definitions and ORM mappings.

The contract verifies:
- `DateTime` instantiated with default timezone flag has `timezone=False` and `python_type` `datetime.datetime`.
- `DateTime` instantiated with `timezone=True` reflects `timezone` property as `True`.
- Core `Table` definition using `DateTime` correctly persists and retrieves naive `datetime` objects across SQLite database round-trips.
- Core `Table` `DateTime(timezone=True)` round-trips datetime values and returns naive datetime objects under SQLite default dialect.
- Declarative ORM models using `mapped_column(DateTime)` persist and query datetime values and preserve `None` for nullable columns.
- SQLite dialect `bind_processor` formats datetime to ISO string and `result_processor` parses strings into `datetime.datetime` instances.

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

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:0a1c31bdc43ce581bcfc3c87e27bc8003415105b4d304912d63be544a400e33e","contract":["DateTime instantiated with default timezone flag has timezone=False and python_type datetime.datetime.","DateTime instantiated with timezone=True reflects timezone property as True.","Core Table definition using DateTime correctly persists and retrieves naive datetime objects across SQLite database round-trips.","Core Table DateTime(timezone=True) round-trips datetime values and returns naive datetime objects under SQLite default dialect.","Declarative ORM models using mapped_column(DateTime) persist and query datetime values and preserve None for nullable columns.","SQLite dialect bind_processor formats datetime to ISO string and result_processor parses strings into datetime.datetime instances."],"goal":"verify sqlalchemy.DateTime in pkg:pypi/sqlalchemy@2.0.51","kind":"HOW","packages":["pkg:pypi/sqlalchemy@2.0.51"],"schemaVersion":1,"symbols":["sqlalchemy.DateTime"]},"contractCommand":["python","test/contract.py"],"environment":{"arch":"x64","distro":"ubuntu","ecosystem":"pypi","executionContext":"python","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.DateTime"],"verifierAdapter":"python@1"}

sqlalchemy==2.0.51

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

import datetime
from datetime import timezone
import sqlalchemy
from sqlalchemy import (
    MetaData,
    Table,
    Column,
    Integer,
    DateTime,
    create_engine,
    select,
)
from sqlalchemy.orm import (
    DeclarativeBase,
    Mapped,
    mapped_column,
    Session,
)

# 1. Verify DateTime instantiation, timezone flag, and python_type
dt_naive = DateTime()
assert dt_naive.timezone is False, f"Expected timezone=False, got {dt_naive.timezone}"
assert dt_naive.python_type is datetime.datetime, f"Expected datetime.datetime, got {dt_naive.python_type}"

dt_tz = DateTime(timezone=True)
assert dt_tz.timezone is True, f"Expected timezone=True, got {dt_tz.timezone}"
assert dt_tz.python_type is datetime.datetime, f"Expected datetime.datetime, got {dt_tz.python_type}"

# 2. Verify Core Table definition and SQLite execution
metadata = MetaData()
events_table = Table(
    "events",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("created_at", DateTime(timezone=False), nullable=False),
    Column("scheduled_at", DateTime(timezone=True), nullable=True),
)

engine = create_engine("sqlite:///:memory:")
metadata.create_all(engine)

fixed_naive = datetime.datetime(2026, 9, 6, 12, 30, 45)
fixed_aware = datetime.datetime(2026, 9, 6, 12, 30, 45, tzinfo=timezone.utc)

with engine.connect() as conn:
    conn.execute(
        events_table.insert().values(
            id=1,
            created_at=fixed_naive,
            scheduled_at=fixed_aware,
        )
    )
    conn.commit()

    row = conn.execute(select(events_table).where(events_table.c.id == 1)).one()
    # Naive datetime round-trips with exact equality
    assert row.created_at == fixed_naive, f"Expected {fixed_naive}, got {row.created_at}"
    # SQLite DATETIME dialect stores ISO strings and returns naive datetime on query
    assert row.scheduled_at == fixed_naive, f"Expected {fixed_naive}, got {row.scheduled_at}"
    assert row.scheduled_at.tzinfo is None, f"Expected tzinfo is None, got {row.scheduled_at.tzinfo}"

# 3. Verify Declarative ORM model with mapped_column(DateTime)
class Base(DeclarativeBase):
    pass

class AuditRecord(Base):
    __tablename__ = "audit_records"
    id: Mapped[int] = mapped_column(primary_key=True)
    logged_at: Mapped[datetime.datetime] = mapped_column(DateTime())
    resolved_at: Mapped[datetime.datetime | None] = mapped_column(DateTime(), nullable=True, default=None)

Base.metadata.create_all(engine)

with Session(engine) as session:
    record = AuditRecord(id=1, logged_at=fixed_naive, resolved_at=None)
    session.add(record)
    session.commit()

    fetched = session.get(AuditRecord, 1)
    assert fetched is not None, "Failed to retrieve AuditRecord"
    assert fetched.logged_at == fixed_naive, f"Expected {fixed_naive}, got {fetched.logged_at}"
    assert fetched.resolved_at is None, f"Expected None, got {fetched.resolved_at}"

# 4. Verify bind_processor and result_processor on SQLite dialect
dialect = engine.dialect
bp = dt_naive.bind_processor(dialect)
rp = dt_naive.result_processor(dialect, None)

if bp is not None:
    serialized = bp(fixed_naive)
    assert serialized == "2026-09-06 12:30:45.000000", f"Unexpected serialization: {serialized}"

if rp is not None:
    deserialized = rp("2026-09-06 12:30:45.000000")
    assert deserialized == fixed_naive, f"Unexpected deserialization: {deserialized}"
    assert isinstance(deserialized, datetime.datetime), f"Expected datetime.datetime, got {type(deserialized)}"

print("CONTRACT PASS: sqlalchemy.DateTime verified offline")