async relationship bug #643
Comments
|
@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:
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. |
|
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: |
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 |
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. |
|
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. |
Thanks. This is my code: |
|
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"},
) |
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'?"
The text was updated successfully, but these errors were encountered: