import asyncio

import sqlalchemy
import sqlalchemy.exc
from sqlalchemy.orm import (
    declarative_base,
    mapped_column,
    sessionmaker,
    Mapped,
)
from sqlalchemy.ext.asyncio import AsyncAttrs, create_async_engine, AsyncSession


Base = declarative_base()


class Item(AsyncAttrs, Base):
    __tablename__ = "item"

    id: Mapped[str] = mapped_column(primary_key=True)
    item: Mapped[str] = mapped_column(deferred=True, deferred_raiseload=True)


async_engine = create_async_engine("sqlite+aiosqlite://", pool_pre_ping=True)

AsyncSessionFactory = sessionmaker(
    bind=async_engine, class_=AsyncSession, expire_on_commit=True, autoflush=False
)


async def create_tables() -> None:
    async with async_engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)


async def test():
    # create 2 related records
    async with AsyncSessionFactory() as sess:
        item = Item(id="1", item="an item")
        sess.add_all([item])
        await sess.commit()

    # load relations using session.get
    async with AsyncSessionFactory() as sess:
        sess: AsyncSession
        top = await sess.get(Item, "1")

        # load right-relation using session.get
        deferred_item = await sess.get_one(
            Item,
            "1",
        )

        try:
            deferred_item.item
            raise AssertionError("unexpected, this should have raised")
        except sqlalchemy.exc.InvalidRequestError as error:
            print(f"as expected: {error}")

        value = await deferred_item.awaitable_attrs.item
        print(value)


async def main():
    await create_tables()
    await test()


if __name__ == "__main__":
    asyncio.run(main())

as expected: 'Item.item' is not available due to raiseload=True
Traceback (most recent call last):
  File "sqlalchemy-test/sa-defer.py", line 70, in <module>
    asyncio.run(main())
    ~~~~~~~~~~~^^^^^^^^
  File ".local/share/uv/python/cpython-3.13.5-macos-x86_64-none/lib/python3.13/asyncio/runners.py", line 195, in run
    return runner.run(main)
           ~~~~~~~~~~^^^^^^
  File ".local/share/uv/python/cpython-3.13.5-macos-x86_64-none/lib/python3.13/asyncio/runners.py", line 118, in run
    return self._loop.run_until_complete(task)
           ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^
  File ".local/share/uv/python/cpython-3.13.5-macos-x86_64-none/lib/python3.13/asyncio/base_events.py", line 725, in run_until_complete
    return future.result()
           ~~~~~~~~~~~~~^^
  File "sqlalchemy-test/sa-defer.py", line 66, in main
    await test()
  File "sqlalchemy-test/sa-defer.py", line 60, in test
    value = await deferred_item.awaitable_attrs.item
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "sqlalchemy-test/.venv/lib/python3.13/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 190, in greenlet_spawn
    result = context.switch(*args, **kwargs)
  File "sqlalchemy-test/.venv/lib/python3.13/site-packages/sqlalchemy/orm/attributes.py", line 569, in __get__
    return self.impl.get(state, dict_)  # type: ignore[no-any-return]
           ~~~~~~~~~~~~~^^^^^^^^^^^^^^
  File "sqlalchemy-test/.venv/lib/python3.13/site-packages/sqlalchemy/orm/attributes.py", line 1096, in get
    value = self._fire_loader_callables(state, key, passive)
  File "sqlalchemy-test/.venv/lib/python3.13/site-packages/sqlalchemy/orm/attributes.py", line 1131, in _fire_loader_callables
    return self.callable_(state, passive)
           ~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^
  File "sqlalchemy-test/.venv/lib/python3.13/site-packages/sqlalchemy/orm/strategies.py", line 543, in _load_for_state
    self._invoke_raise_load(state, passive, "raise")
    ~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^
  File "sqlalchemy-test/.venv/lib/python3.13/site-packages/sqlalchemy/orm/strategies.py", line 552, in _invoke_raise_load
    raise sa_exc.InvalidRequestError(
        "'%s' is not available due to raiseload=True" % (self,)
    )
sqlalchemy.exc.InvalidRequestError: 'Item.item' is not available due to raiseload=True