async relationship bug

[a410202049]

Privileged issue

• [X] I'm @tiangolo or he asked me directly to create an issue here.

Issue Content

"Why do I get the error 'greenlet_spawn has not been called' when I set sa_relationship_kwargs to 'select' while using async queries, but it works fine when I set it to 'joined'?"

[PookieBuns]

@a410202049 could you include code that can reproduce this issue? I would like to take a look at it

[Trevypants]

In the documentation of SQLAlchemy here, they explain that using 'select' for an async relationship is attempting to use implicit IO and is subsequently not allowed.

Word for word from their example:

Accessing the A.bs collection on newly loaded instances of A when eager loading is not in use will normally use lazy loading, which in order to succeed will usually emit IO to the database, which will fail under asyncio as no implicit IO is allowed.

The link also provides their solution to this which is to use the AsyncAttrs mixin.

When you use the 'joined' loading mechanism, the relationship is pre-loaded with the result instead of on an as-needed basis that comes with the 'select' mechanism.

[maces]

In https://sqlalche.me/e/20/xd2s the following is also mentioned:

When using the ORM this is nearly always due to the use of lazy loading, which is not directly supported under asyncio without additional steps and/or alternate loader patterns in order to use successfully.

I did not check the code yet, but if we implement an additional loader in SQLModel, this could work?

---

For completes sake, here is an example (mainly from the docs) to reproduce the issue:

import asyncio
from typing import Optional, List

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

class Team(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    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 = Field(index=True)
    secret_name: str
    age: Optional[int] = Field(default=None, index=True)

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

async def main() -> None:
    engine = create_async_engine("...")

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

    async with AsyncSession(engine) as session:
        session.add(Team(name="Some Team", headquarters="Somewhere"))
        await session.commit()
        session.add(Hero(name="Spider-Boy", secret_name="Pedro Parqueador", team_id=1))
        await session.commit()

    async with AsyncSession(engine) as session:
        statement = select(Hero).where(Hero.name == "Spider-Boy")
        result = await session.exec(statement)
        hero_spider_boy = result.one()

        print("Spider-Boy's team again:", hero_spider_boy.team)

asyncio.run(main())

logs:

$ python -m async_relations
Traceback (most recent call last):
  File "<frozen runpy>", line 198, in _run_module_as_main
  File "<frozen runpy>", line 88, in _run_code
  File "/a/path/async_relations.py", line 47, in <module>
    asyncio.run(main())
  File "~/.pyenv/versions/3.11.7/lib/python3.11/asyncio/runners.py", line 190, in run
    return runner.run(main)
           ^^^^^^^^^^^^^^^^
  File "~/.pyenv/versions/3.11.7/lib/python3.11/asyncio/runners.py", line 118, in run
    return self._loop.run_until_complete(task)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "~/.pyenv/versions/3.11.7/lib/python3.11/asyncio/base_events.py", line 653, in run_until_complete
    return future.result()
           ^^^^^^^^^^^^^^^
  File "/a/path/async_relations.py", line 44, in main
    print("Spider-Boy's team again:", hero_spider_boy.team)
                                      ^^^^^^^^^^^^^^^^^^^^
  File "/a/path/.venv/lib/python3.11/site-packages/sqlalchemy/orm/attributes.py", line 566, in __get__
    return self.impl.get(state, dict_)  # type: ignore[no-any-return]
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/a/path/.venv/lib/python3.11/site-packages/sqlalchemy/orm/attributes.py", line 1086, in get
    value = self._fire_loader_callables(state, key, passive)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/a/path/.venv/lib/python3.11/site-packages/sqlalchemy/orm/attributes.py", line 1121, in _fire_loader_callables
    return self.callable_(state, passive)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/a/path/.venv/lib/python3.11/site-packages/sqlalchemy/orm/strategies.py", line 967, in _load_for_state
    return self._emit_lazyload(
           ^^^^^^^^^^^^^^^^^^^^
  File "/a/path/.venv/lib/python3.11/site-packages/sqlalchemy/orm/strategies.py", line 1068, in _emit_lazyload
    return loading.load_on_pk_identity(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/a/path/.venv/lib/python3.11/site-packages/sqlalchemy/orm/loading.py", line 692, in load_on_pk_identity
    session.execute(
  File "/a/path/.venv/lib/python3.11/site-packages/sqlmodel/orm/session.py", line 129, in execute
    return super().execute(
           ^^^^^^^^^^^^^^^^
  File "/a/path/.venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 2308, in execute
    return self._execute_internal(
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/a/path/.venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 2190, in _execute_internal
    result: Result[Any] = compile_state_cls.orm_execute_statement(
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/a/path/.venv/lib/python3.11/site-packages/sqlalchemy/orm/context.py", line 293, in orm_execute_statement
    result = conn.execute(
             ^^^^^^^^^^^^^
  File "/a/path/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1416, in execute
    return meth(
           ^^^^^
  File "/a/path/.venv/lib/python3.11/site-packages/sqlalchemy/sql/elements.py", line 517, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/a/path/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1639, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/a/path/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1848, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/a/path/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1988, in _exec_single_context
    self._handle_dbapi_exception(
  File "/a/path/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2347, in _handle_dbapi_exception
    raise exc_info[1].with_traceback(exc_info[2])
  File "/a/path/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1969, in _exec_single_context
    self.dialect.do_execute(
  File "/a/path/.venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute
    cursor.execute(statement, parameters)
  File "/a/path/.venv/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 580, in execute
    self._adapt_connection.await_(
  File "/a/path/.venv/lib/python3.11/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 121, in await_only
    raise exc.MissingGreenlet(
sqlalchemy.exc.MissingGreenlet: greenlet_spawn has not been called; can't call await_only() here. Was IO attempted in an unexpected place? (Background on this error at: https://sqlalche.me/e/20/xd2s)

---

And an example using joins like mentioned by @Trevypants :

# ...
    async with AsyncSession(engine) as session:
        statement = select(Hero, Team).join(Team).where(Hero.name == "Spider-Boy")
        result = await session.exec(statement)
        hero_spider_boy, team = result.one()

        print("Spider-Boy's:", hero_spider_boy)
        print("Spider-Boy's team:", hero_spider_boy.team)
        print("Spider-Boy's team again:", team)

results in:

Spider-Boy's: id=2 name='Spider-Boy' team_id=1 age=None secret_name='Pedro Parqueador'
Spider-Boy's team: headquarters='Somewhere' id=1 name='Some Team'
Spider-Boy's team again: headquarters='Somewhere' id=1 name='Some Team'

[copdips]

    async with AsyncSession(engine) as session:
        statement = select(Hero, Team).join(Team).where(Hero.name == "Spider-Boy")
        result = await session.exec(statement)
        hero_spider_boy, team = result.one()

this approach works for many-to-one side when querying a hero with only one team. However, it becomes more complex for one-to-many side when querying a team with many heroes, and even more so for querying many teams with many heroes, since result.all() returns a list where each element is a tuple of one team and one hero. we need extra effort to loop over and format the result.

[ryanrain2016]

from sqlalchemy.ext.asyncio import AsyncAttrs
...
    async with AsyncSession(engine) as session:
        statement = select(Hero).where(Hero.name == "Spider-Boy")
        result = await session.exec(statement)
        hero_spider_boy = result.one()
        team = await hero_spider_boy.awaitable_attrs.team

When creating a new model class, adding AsyncAttrs to the inherited class can achieve this function to a certain extent. However, when obtaining this attribute, the database will be accessed again, so the performance is not very good. I don’t know if there is an operation such as prefetch that can be done in one step.

[copdips]

I believe what you're seeking about prefetch is eager loading, which can be achieved by selectinload, you can find demos on google. And I'm looking forward to learning how to use AsyncAttrs with SQLModel and lazy loading.

[ryanrain2016]

I believe what you're seeking about prefetch is eager loading, which can be achieved by selectinload, you can find demos on google. And I'm looking forward to learning how to use AsyncAttrs with SQLModel and lazy loading.

thanks for reply, I've found this method. This method is very convenient and efficient when obtaining the associated information of a set of data.Instead of accessing the database every time you access an attribute of one item, you only need to access the database once to obtain the associated information for this set of data.

[ChrisNi888]

I believe what you're seeking about prefetch is eager loading, which can be achieved by selectinload, you can find demos on google. And I'm looking forward to learning how to use AsyncAttrs with SQLModel and lazy loading.

Thanks. It seems work.

This is my code:

# select
statement=select(MHero).options(selectinload(MHero.mteam_links)).where(MHero.id==hero_id)
res=await session.exec(statement)
test_hero:MHero=res.one()
print('test_hero',test_hero.mteam_links)

# get
db_hero = await session.get(MHero, hero_id,options=[selectinload(MHero.mteam_links)])

[thyb-zytek]

I've got the same error on Many to Many and I've solved it with defining relationship like this:

class HeroTeamLink(SQLModel, table=True):
    team_id: int | None = Field(default=None, foreign_key="team.id", primary_key=True)
    hero_id: int | None = Field(default=None, foreign_key="hero.id", primary_key=True)

class Team(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    headquarters: str

    heroes: list["Hero"] =  Relationship(
        back_populates="teams",
        link_model=HeroTeamLink,
        sa_relationship_kwargs={"lazy": "selectin"},
    )

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

    teams: list[Team] =  Relationship(
        back_populates="heroes",
        link_model=HeroTeamLink,
        sa_relationship_kwargs={"lazy": "selectin"},
    )

[Pablongo24]

https://github.com/tiangolo/sqlmodel/issues/643#issuecomment-2106196230

thyb-zytek's suggestion worked for me as well.

[khuongtm]

Hello guy Currently I'm having the same problem but I'm not using ORM like the above methods, below is my example code: Please give me advice

[thyb-zytek]

@khuongtm You should add the join instruction for add your sub model to your SQL query, I guess.

[khuongtm]

some things like this

[rvishruth]

I use the same implementation as thyb-zytek's suggestion in my codebase, but this still doesn't work in all cases

For example, the below code would cause a "greenlet_spawn has not been called" error

stmt = select(Team).where(col(Team.id) == id)
results = await db.exec(stmt)
team = results.one()

print(team) # will work
print(team.heros) # will work
print(team.heros[0].teams) # will throw an error

While the above example is synthetic, such situations are common when you are using GraphQL due to nested queries. See an example query below

query GetDetails($getDetailsId: ID!) {
  getDetails(id: $getDetailsId) {
    name
    heros {
      name
      teams {
        name
      }
    }
    }
  }