sqlalchemy 2.0.51: Connection

# sqlalchemy Connection API

This sample pins `sqlalchemy` 2.0.51 and verifies `sqlalchemy.Connection` for database execution and transaction lifecycle management.

The contract verifies:
- Connection executes SQL statements returning Result objects, supporting parameter binding and scalar extraction.
- Connection context manager automatically closes the connection upon exiting the block.
- Statements executed on Connection trigger autobegin, and calling begin() while autobegin is active raises InvalidRequestError.
- Connection.begin_nested() creates a savepoint transaction that can be committed or rolled back independently.
- Operations on a closed Connection raise ResourceClosedError.

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

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:a843d007b6fff36dafaaa76ac7a915fd7ca3ab271e5fc460989ce80c9f312953","contract":["Connection executes SQL statements returning Result objects, supporting parameter binding and scalar extraction.","Connection context manager automatically closes the connection upon exiting the block.","Statements executed on Connection trigger autobegin, and calling begin() while autobegin is active raises InvalidRequestError.","Connection.begin_nested() creates a savepoint transaction that can be committed or rolled back independently.","Operations on a closed Connection raise ResourceClosedError."],"goal":"verify sqlalchemy.Connection in pkg:pypi/sqlalchemy@2.0.51","kind":"HOW","packages":["pkg:pypi/sqlalchemy@2.0.51"],"schemaVersion":1,"symbols":["sqlalchemy.Connection"]},"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.Connection"],"verifierAdapter":"python@1"}

sqlalchemy==2.0.51

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

import sqlalchemy
from sqlalchemy import Connection, create_engine, text
from sqlalchemy.exc import InvalidRequestError, ResourceClosedError

# 1. Verify Connection creation, type, and context manager auto-close
engine = create_engine("sqlite:///:memory:")
with engine.connect() as conn:
    assert isinstance(conn, Connection), f"Expected Connection instance, got {type(conn)}"
    assert conn.engine is engine
    assert not conn.closed
    assert not conn.in_transaction()
assert conn.closed

# 2. Verify SQL execution, parameter binding, scalar extraction, and autobegin behavior
conn = engine.connect()
assert not conn.in_transaction()

# Executing a statement initiates autobegin
result = conn.execute(text("SELECT :x AS val"), {"x": 42})
assert result.scalar_one() == 42
assert conn.in_transaction()

# Calling begin() while autobegin is active raises InvalidRequestError
try:
    conn.begin()
    assert False, "Expected InvalidRequestError when calling begin() on an active transaction"
except InvalidRequestError:
    pass

# Commit ends the active transaction
conn.commit()
assert not conn.in_transaction()

# 3. Verify table operations, multiple parameter execution, and scalar queries
with conn.begin():
    conn.execute(text("CREATE TABLE items (id INT, label TEXT)"))
    conn.execute(
        text("INSERT INTO items (id, label) VALUES (:id, :label)"),
        [{"id": 1, "label": "item1"}, {"id": 2, "label": "item2"}, {"id": 3, "label": "item3"}],
    )

assert not conn.in_transaction()
count = conn.scalar(text("SELECT COUNT(*) FROM items"))
assert count == 3
labels = list(conn.scalars(text("SELECT label FROM items ORDER BY id")))
assert labels == ["item1", "item2", "item3"]
conn.commit()

# 4. Verify nested transaction (savepoint) rollback and commit
with conn.begin():
    sp = conn.begin_nested()
    conn.execute(text("INSERT INTO items (id, label) VALUES (4, 'item4')"))
    assert conn.scalar(text("SELECT COUNT(*) FROM items")) == 4
    sp.rollback()
    assert conn.scalar(text("SELECT COUNT(*) FROM items")) == 3

# 5. Verify operations on a closed Connection raise ResourceClosedError
conn.close()
assert conn.closed
try:
    conn.execute(text("SELECT 1"))
    assert False, "Expected ResourceClosedError when executing on a closed Connection"
except ResourceClosedError:
    pass

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