sqlalchemy 2.0.51: ext.asyncio.create_async_engine

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.ext.asyncio.create_async_engine 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.ext.asyncio.create_async_engine

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:ad4f886aee72a4bd1ec6769fe49220b6a8b9a5d3464d004bd5bc773a9b231093","contract":["create_async_engine initializes an AsyncEngine with custom connection parameters and dialect configuration","create_async_engine provides AsyncConnection via connect context manager for executing asynchronous SQL queries","create_async_engine provides transactional execution via begin context manager that auto-commits on success and rolls back on failure","create_async_engine supports run_sync on AsyncConnection for schema creation and synchronous DDL operations","create_async_engine supports execution options such as isolation_level configuration","create_async_engine properly disposes connection pool asynchronously via dispose"],"goal":"verify sqlalchemy.ext.asyncio.create_async_engine in pkg:pypi/sqlalchemy@2.0.51","kind":"HOW","packages":["pkg:pypi/sqlalchemy@2.0.51"],"schemaVersion":1,"symbols":["sqlalchemy.ext.asyncio.create_async_engine"]},"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.ext.asyncio.create_async_engine"],"verifierAdapter":"python@1"}

[project]
name = "sample-sqlalchemy-create-async-engine"
version = "0.1.0"
requires-python = ">=3.9"
dependencies = [
    "aiosqlite==0.20.0",
    "greenlet==3.1.1",
    "sqlalchemy==2.0.51",
    "typing-extensions==4.12.2",
]

aiosqlite==0.20.0
greenlet==3.1.1
sqlalchemy==2.0.51
typing-extensions==4.12.2

"""Sample demonstrating asynchronous database engine creation and management with create_async_engine."""

from typing import Any, Callable, Dict, List, Optional, Sequence, TypeVar
from sqlalchemy import Integer, String, text
from sqlalchemy.ext.asyncio import (
    AsyncConnection,
    AsyncEngine,
    create_async_engine,
)
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from sqlalchemy.pool import NullPool, StaticPool

T = TypeVar("T")


class Base(DeclarativeBase):
    pass


class Item(Base):
    __tablename__ = "items"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    name: Mapped[str] = mapped_column(String(50), unique=True, nullable=False)
    value: Mapped[int] = mapped_column(Integer, nullable=False)


def create_in_memory_async_engine(
    url: str = "sqlite+aiosqlite:///:memory:",
    echo: bool = False,
    **kwargs: Any,
) -> AsyncEngine:
    """Create an SQLite async database engine with StaticPool for in-memory persistence across connections."""
    return create_async_engine(
        url,
        echo=echo,
        poolclass=StaticPool,
        **kwargs,
    )


def create_custom_async_engine(
    url: str,
    isolation_level: Optional[str] = None,
    **kwargs: Any,
) -> AsyncEngine:
    """Create an AsyncEngine with custom parameters and execution options."""
    if isolation_level is not None:
        kwargs["isolation_level"] = isolation_level
    return create_async_engine(url, **kwargs)


async def init_tables(engine: AsyncEngine) -> None:
    """Initialize database tables asynchronously using run_sync on an engine connection."""
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)


async def execute_scalar_query(
    engine: AsyncEngine,
    sql: str,
    params: Optional[Dict[str, Any]] = None,
) -> Any:
    """Execute a query asynchronously using engine.connect() and return a single scalar value."""
    async with engine.connect() as conn:
        result = await conn.execute(text(sql), params or {})
        return result.scalar()


async def execute_transactional_insert(
    engine: AsyncEngine,
    records: Sequence[Dict[str, Any]],
) -> None:
    """Insert records within an async transactional begin context manager."""
    async with engine.begin() as conn:
        for record in records:
            await conn.execute(
                text("INSERT INTO items (name, value) VALUES (:name, :value)"),
                record,
            )


async def run_sync_ddl(
    conn: AsyncConnection,
    fn: Callable[..., T],
    *args: Any,
    **kwargs: Any,
) -> T:
    """Execute a synchronous DDL or schema operation via AsyncConnection.run_sync."""
    return await conn.run_sync(fn, *args, **kwargs)


async def dispose_engine(engine: AsyncEngine) -> None:
    """Dispose of the connection pool underlying the AsyncEngine."""
    await engine.dispose()

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

"""Contract verification tests for sqlalchemy.ext.asyncio.create_async_engine."""

import asyncio
import os
import sys

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

from sqlalchemy import select, text
from sqlalchemy.exc import DBAPIError, IntegrityError
from sqlalchemy.ext.asyncio import (
    AsyncConnection,
    AsyncEngine,
    create_async_engine,
)
from sqlalchemy.pool import NullPool, StaticPool

from sample import (
    Base,
    Item,
    create_custom_async_engine,
    create_in_memory_async_engine,
    dispose_engine,
    execute_scalar_query,
    execute_transactional_insert,
    init_tables,
    run_sync_ddl,
)


async def test_create_async_engine_initialization() -> None:
    """Assertion 1: create_async_engine initializes an AsyncEngine with custom connection parameters and dialect configuration."""
    engine = create_async_engine(
        "sqlite+aiosqlite:///:memory:",
        echo=False,
        poolclass=StaticPool,
    )
    try:
        assert isinstance(engine, AsyncEngine)
        assert engine.dialect.name == "sqlite"
        assert engine.dialect.driver == "aiosqlite"
        assert engine.echo is False
        assert engine.sync_engine is not None
        assert str(engine.url) == "sqlite+aiosqlite:///:memory:"
    finally:
        await engine.dispose()

    engine_echo = create_async_engine(
        "sqlite+aiosqlite:///:memory:",
        echo=True,
        poolclass=StaticPool,
    )
    try:
        assert engine_echo.echo is True
    finally:
        await engine_echo.dispose()


async def test_connect_and_execute_queries() -> None:
    """Assertion 2: create_async_engine provides AsyncConnection via connect context manager for executing asynchronous SQL queries."""
    engine = create_in_memory_async_engine()
    try:
        async with engine.connect() as conn:
            assert isinstance(conn, AsyncConnection)
            assert conn.closed is False

            result = await conn.execute(text("SELECT 42 AS answer"))
            row = result.fetchone()
            assert row is not None
            assert row[0] == 42

        val = await execute_scalar_query(engine, "SELECT 'test_str'")
        assert val == "test_str"
    finally:
        await engine.dispose()


async def test_transactional_begin_commit_and_rollback() -> None:
    """Assertion 3: create_async_engine provides transactional execution via begin context manager that auto-commits on success and rolls back on failure."""
    engine = create_in_memory_async_engine()
    try:
        # Setup table
        async with engine.begin() as conn:
            await conn.execute(
                text("CREATE TABLE tx_test (id INTEGER PRIMARY KEY, name TEXT UNIQUE)")
            )

        # Successful commit block
        async with engine.begin() as conn:
            await conn.execute(
                text("INSERT INTO tx_test (id, name) VALUES (:id, :name)"),
                {"id": 1, "name": "first"},
            )

        # Verify commit succeeded
        async with engine.connect() as conn:
            res = await conn.execute(text("SELECT name FROM tx_test WHERE id = 1"))
            assert res.scalar_one() == "first"

        # Failed block triggering rollback
        rollback_caught = False
        try:
            async with engine.begin() as conn:
                await conn.execute(
                    text("INSERT INTO tx_test (id, name) VALUES (:id, :name)"),
                    {"id": 2, "name": "second"},
                )
                # Duplicate primary key/unique constraint violation
                await conn.execute(
                    text("INSERT INTO tx_test (id, name) VALUES (:id, :name)"),
                    {"id": 1, "name": "first_duplicate"},
                )
        except (IntegrityError, DBAPIError):
            rollback_caught = True

        assert rollback_caught, "Expected IntegrityError on duplicate insert"

        # Verify rollback: record with id=2 must not exist
        async with engine.connect() as conn:
            res = await conn.execute(text("SELECT count(*) FROM tx_test WHERE id = 2"))
            count = res.scalar_one()
            assert count == 0
    finally:
        await engine.dispose()


async def test_run_sync_schema_creation() -> None:
    """Assertion 4: create_async_engine supports run_sync on AsyncConnection for schema creation and synchronous DDL operations."""
    engine = create_in_memory_async_engine()
    try:
        await init_tables(engine)

        records = [
            {"name": "alpha", "value": 100},
            {"name": "beta", "value": 200},
        ]
        await execute_transactional_insert(engine, records)

        async with engine.connect() as conn:
            result = await conn.execute(
                text("SELECT name, value FROM items ORDER BY value ASC")
            )
            rows = result.fetchall()
            assert len(rows) == 2
            assert rows[0] == ("alpha", 100)
            assert rows[1] == ("beta", 200)

            # Test run_sync helper directly
            def sync_inspector(sync_conn):
                from sqlalchemy import inspect
                return inspect(sync_conn).has_table("items")

            has_table = await run_sync_ddl(conn, sync_inspector)
            assert has_table is True
    finally:
        await engine.dispose()


async def test_execution_options_and_isolation_level() -> None:
    """Assertion 5: create_async_engine supports execution options such as isolation_level configuration."""
    engine = create_custom_async_engine(
        "sqlite+aiosqlite:///:memory:",
        isolation_level="AUTOCOMMIT",
        poolclass=StaticPool,
    )
    try:
        assert isinstance(engine, AsyncEngine)
        assert engine.sync_engine.pool.__class__ is StaticPool

        async with engine.connect() as conn:
            # Under AUTOCOMMIT, statements execute outside explicit transaction blocks
            await conn.execute(text("CREATE TABLE auto_table (val INT)"))
            await conn.execute(text("INSERT INTO auto_table VALUES (99)"))

            result = await conn.execute(text("SELECT val FROM auto_table"))
            assert result.scalar_one() == 99

        # Test execution_options configured per-engine
        engine_opt = create_async_engine(
            "sqlite+aiosqlite:///:memory:",
            poolclass=StaticPool,
            execution_options={"compiled_cache": None},
        )
        try:
            assert engine_opt.get_execution_options().get("compiled_cache") is None
            engine_with_opt = engine_opt.execution_options(stream_results=True)
            assert engine_with_opt.get_execution_options().get("stream_results") is True
        finally:
            await engine_opt.dispose()
    finally:
        await engine.dispose()


async def test_engine_dispose() -> None:
    """Assertion 6: create_async_engine properly disposes connection pool asynchronously via dispose."""
    engine = create_in_memory_async_engine()
    async with engine.connect() as conn:
        res = await conn.execute(text("SELECT 1"))
        assert res.scalar() == 1

    await dispose_engine(engine)
    # Re-disposing an already disposed engine should safely succeed (idempotent)
    await engine.dispose()


async def main_async() -> None:
    await test_create_async_engine_initialization()
    await test_connect_and_execute_queries()
    await test_transactional_begin_commit_and_rollback()
    await test_run_sync_schema_creation()
    await test_execution_options_and_isolation_level()
    await test_engine_dispose()
    print("All sqlalchemy.ext.asyncio.create_async_engine contract tests passed.")


def main() -> None:
    asyncio.run(main_async())


if __name__ == "__main__":
    main()