async - can't access parent.child item (relationship models) using async

[mnnweb]

First Check

• I added a very descriptive title to this issue.
• I used the GitHub search to find a similar issue and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Dependencies:
aiosqlite
https://pypi.org/project/aiosqlite/

Example Code

import asyncio
from sqlmodel.ext.asyncio.session import AsyncSession
from sqlalchemy.ext.asyncio import create_async_engine
from typing import List, Optional
from sqlmodel import Field, Relationship, SQLModel, select


class Team(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    headquarters: str

    heroes: List["Hero"] = Relationship(back_populates="team")


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: Optional[int] = None

    team_id: Optional[int] = Field(default=None, foreign_key="team.id")
    team: Optional[Team] = Relationship(back_populates="heroes")


class Item(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str


sqlite_file_name = "database.db"
sqlite_url = f"sqlite+aiosqlite:///{sqlite_file_name}"

engine = create_async_engine(sqlite_url, echo=True)


async def create_db_and_tables():
    # SQLModel.metadata.create_all(engine)

    async with engine.begin() as conn:
        await conn.run_sync(SQLModel.metadata.drop_all)
        await conn.run_sync(SQLModel.metadata.create_all)


async def create_heroes():
    async with AsyncSession(engine) as session:
        team_preventers = Team(name="Preventers", headquarters="Sharp Tower")
        hero_deadpond = Hero(
            name="Deadpond", secret_name="Dive Wilson", team=team_preventers
        )

        session.add(hero_deadpond)
        await session.commit()
        await session.refresh(hero_deadpond)

        print(hero_deadpond)


async def select_heroes():
    async with AsyncSession(engine) as session:
        statement = select(Team).where(Team.name == "Preventers")
        result = await session.execute(statement)
        team_preventers = result.scalar()
        print(f"Preventers team: {team_preventers}")
        print(f"Preventers heroes: {team_preventers.heroes}")


async def main():
    await create_db_and_tables()
    await create_heroes()
    await select_heroes()

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

Operating System

macOS

Operating System Details

Using a MacBook with macOS running FastAPI with docker.

SQLModel Version

0.0.4

Python Version

3.8.6

Additional Context

We have two models with a one to many relationship (Team -> Hero).
Using async / await AsyncSession , we are trying to access the parent's child ( {team_preventers.heroes} ) but this is causing the following exception :

sqlalchemy.exc.MissingGreenlet: greenlet_spawn has not been called; can't call await_() here. Was IO attempted in an unexpected place? (Background on this error at: https://sqlalche.me/e/14/xd2s)
sys:1: RuntimeWarning: coroutine 'Connection.cursor' was never awaited

Full stacktrace

Traceback (most recent call last):
  File "async_test.py", line 74, in <module>
    asyncio.run(main())
  File "/usr/local/Cellar/python@3.9/3.9.6/Frameworks/Python.framework/Versions/3.9/lib/python3.9/asyncio/runners.py", line 44, in run
    return loop.run_until_complete(main)
  File "/usr/local/Cellar/python@3.9/3.9.6/Frameworks/Python.framework/Versions/3.9/lib/python3.9/asyncio/base_events.py", line 642, in run_until_complete
    return future.result()
  File "async_test.py", line 71, in main
    await select_heroes()
  File "async_test.py", line 65, in select_heroes
    print(f"Preventers heroes: {team_preventers.heroes}")
  File "/lib/python3.9/site-packages/sqlalchemy/orm/attributes.py", line 481, in __get__
    return self.impl.get(state, dict_)
  File "/lib/python3.9/site-packages/sqlalchemy/orm/attributes.py", line 926, in get
    value = self._fire_loader_callables(state, key, passive)
  File "/lib/python3.9/site-packages/sqlalchemy/orm/attributes.py", line 962, in _fire_loader_callables
    return self.callable_(state, passive)
  File "/lib/python3.9/site-packages/sqlalchemy/orm/strategies.py", line 892, in _load_for_state
    return self._emit_lazyload(
  File "/lib/python3.9/site-packages/sqlalchemy/orm/strategies.py", line 1028, in _emit_lazyload
    result = session.execute(
  File "/lib/python3.9/site-packages/sqlmodel/orm/session.py", line 101, in execute
    return super().execute(  # type: ignore
  File "/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1689, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1583, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1452, in _execute_clauseelement
    ret = self._execute_context(
  File "/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1814, in _execute_context
    self._handle_dbapi_exception(
  File "/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1999, in _handle_dbapi_exception
    util.raise_(exc_info[1], with_traceback=exc_info[2])
  File "/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1771, in _execute_context
    self.dialect.do_execute(
  File "/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
  File "/lib/python3.9/site-packages/sqlalchemy/dialects/sqlite/aiosqlite.py", line 99, in execute
    self._adapt_connection._handle_exception(error)
  File "/lib/python3.9/site-packages/sqlalchemy/dialects/sqlite/aiosqlite.py", line 228, in _handle_exception
    raise error
  File "/lib/python3.9/site-packages/sqlalchemy/dialects/sqlite/aiosqlite.py", line 76, in execute
    _cursor = self.await_(self._connection.cursor())
  File "/lib/python3.9/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 60, in await_only
    raise exc.MissingGreenlet(
sqlalchemy.exc.MissingGreenlet: greenlet_spawn has not been called; can't call await_() here. Was IO attempted in an unexpected place? (Background on this error at: https://sqlalche.me/e/14/xd2s)
sys:1: RuntimeWarning: coroutine 'Connection.cursor' was never awaited

[matthewjcarlson]

I think what you're seeing is a result of SQLAlchemy trying to perform implicit IO when accessing heroes from teams without performing eager loading on heroes. If you eager load heroes, the error message should go away per the SQLAlchemy documentation.

[rscottweekly]

Just to flesh this out a bit, because I struggled to get it sorted and went round in circles for too long.

Import selectinload from the sqlalachemy package
from sqlalchemy.orm import selectinload

and then after the selects, add the selectinload option

statement = select(Team).where(Team.name == "Preventers").options(selectinload(Team.heroes))

[jonra1993]

thanks, @rscottweekly that was the solution. I have created an async sample that can help others implement async sqlmodel https://github.com/jonra1993/fastapi-alembic-sqlmodel-async

[erikwilliamson]

Thanks to @rscottweekly for the solution and @jonra1993 for a sample implementation - it is much appreciated!

[mhentz]

@rscottweekly you're an angel

[hambergerpls]

Hi,

I am also facing this issue and getting the same exception. Adding 'lazy':'selectin' doesn't seem to work for self-referencing model in Many-Many relationship using async.

I've been wrapping my head for days trying to figure out what was the cause. Please let me know where I did wrong.

I expect the results to be something like this. I'm aware that this may yield infinite data/loop (e.g. Parent->Child->Parent), but please ignore that for now.

{
  "name":"Parent",
  "children":[
    {
      "name":"Child",
      "children":[]
    }
  ],
}

Here's an example I modified from the one given by OP

import asyncio
from sqlmodel.ext.asyncio.session import AsyncSession
from sqlalchemy.ext.asyncio import create_async_engine
from typing import List, Optional
from sqlmodel import Field, Relationship, SQLModel, select


class LinkNodes(SQLModel, table=True):
    parent_id: Optional[int] = Field(default=None, nullable=False, foreign_key="node.id", primary_key=True)
    child_id: Optional[int] = Field(default=None, nullable=False, foreign_key="node.id", primary_key=True)

class Node(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    children: List['Node'] = Relationship(
        link_model=LinkNodes,
        sa_relationship_kwargs={
            "lazy": "selectin",
            "primaryjoin":"Node.id==LinkNodes.parent_id",
            "secondaryjoin":"Node.id==LinkNodes.child_id",
        })


sqlite_file_name = "database.db"
sqlite_url = f"sqlite+aiosqlite:///{sqlite_file_name}"

engine = create_async_engine(sqlite_url, echo=True)


async def create_db_and_tables():
    # SQLModel.metadata.create_all(engine)

    async with engine.begin() as conn:
        await conn.run_sync(SQLModel.metadata.drop_all)
        await conn.run_sync(SQLModel.metadata.create_all)


async def create_nodes():
    async with AsyncSession(engine) as session:
        parent_node = Node(name="Parent")
        child_node = Node(name="Child")
        parent_node.children.append(child_node)

        session.add(parent_node)
        await session.commit()
        await session.refresh(parent_node)

        print(parent_node)


async def select_nodes():
    async with AsyncSession(engine) as session:
        statement = select(Node).where(Node.name == "Parent")
        result = await session.execute(statement)
        node = result.scalar()
        print(f"Parent: {node}")
        print(f"Children: {node.children}")


async def main():
    await create_db_and_tables()
    await create_nodes()
    await select_nodes()

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

[juftin]

In my case - I only want to enable eager loading where I know I'm going to ask for the relationship attribute. I'm using joinedload eager loading at the query level rather than enable eager loading everywhere by defining it at the model level:

import logging
from typing import List, Optional

from fastapi import HTTPException
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import joinedload

from app import app, engine, Heroes, Team


@app.get("/team/{team_id}/heroes", response_model=List[Heroes])
async def get_team_heroes(
    team_id: int,
) -> List[Heroes]:
    """
    List Heroes per Team
    """
    async with AsyncSession(engine) as session:
      team: Optional[Team] = await session.get(
          entity=Team,
          ident=team_id,
          options=[
              joinedload(Team.heroes)  # explicit load of relationship supports async session
          ],
      )
      if team is None:
          raise HTTPException(status_code=404, detail="Team does not exist")
      heroes: List[Heroes] = team.heroes
      return heroes

[plsholdmybeer]

for future reference:

selectinload produces 2 queries even if I want to include only a single model attribute. joinedload works for me, I haven't seen it during research so leaving example here

EDIT: CAUTION! don't use joinedload, it breaks everything, took me a while to figure out where the issue is but the query hangs and eats up whole ram, reminds me of serverless horror stories. the following works for now (probably could be optimized)

multi = (await session.exec(
    select(Hero, Cape)
    .join(Cape, Hero.cape)
    .join(...)
    ...
    .options(
        contains_eager(Transaction.round),
        contains_eager(...),
        ...
    )
    .where(...))
).all()
heroes_with_eagerly_loaded_capes = [i[0] for i in multi]

[copdips]

hello,

any clue for the lazy loading solution ?