async relationship bug #1455
-
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'?" |
Beta Was this translation helpful? Give feedback.
Replies: 15 comments
-
|
@a410202049 could you include code that can reproduce this issue? I would like to take a look at it |
Beta Was this translation helpful? Give feedback.
-
|
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:
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. |
Beta Was this translation helpful? Give feedback.
-
|
In https://sqlalche.me/e/20/xd2s the following is also mentioned:
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: 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: |
Beta Was this translation helpful? Give feedback.
-
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 |
Beta Was this translation helpful? Give feedback.
-
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.teamWhen 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. |
Beta Was this translation helpful? Give feedback.
-
|
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. |
Beta Was this translation helpful? Give feedback.
-
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. |
Beta Was this translation helpful? Give feedback.
-
Thanks. This is my code: |
Beta Was this translation helpful? Give feedback.
-
|
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"},
) |
Beta Was this translation helpful? Give feedback.
-
|
thyb-zytek's suggestion worked for me as well. |
Beta Was this translation helpful? Give feedback.
-
|
Hello guy |
Beta Was this translation helpful? Give feedback.
-
|
@khuongtm You should add the join instruction for add your sub model to your SQL query, I guess. |
Beta Was this translation helpful? Give feedback.
-
|
some things like this |
Beta Was this translation helpful? Give feedback.
-
|
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 While the above example is synthetic, such situations are common when you are using GraphQL due to nested queries. See an example query below |
Beta Was this translation helpful? Give feedback.
-
I just encountered the same issue. Any workaround? |
Beta Was this translation helpful? Give feedback.
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:
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.