Repository navigation
async relationship bug #643
Description
Activity
@a410202049 could you include code that can reproduce this issue? I would like to take a look at it
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.
Reacted by Nathan Vieira, hikilyc and macesIn 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' ```Reacted by Fábio Araújo, teodoryantcheff and Xubo Feiasync 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.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.
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.
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.
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)])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"}, )
Reacted by Peter Dresslar, espdev and JimReacted by 朱凌峰, Simon Ryf, Pablo Vega-Behar, Brandon, Vinicius Abade, 陈鼫RWHTYFZ, HinGwenWoong and Jimthyb-zytek's suggestion worked for me as well.
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@khuongtm You should add the join instruction for add your sub model to your SQL query, I guess.
some things like this
Reacted by Alexis THIBAULTI 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 errorWhile 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 } } } }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 errorWhile 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 } } } }I just encountered the same issue. Any workaround?
- locked and limited conversation to collaborators
on Aug 6, 2025
Privileged issue
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'?"