async - can't access parent.child item (relationship models) using async #1519
-
First Check
Commit to Help
Dependencies: Example Codeimport 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 SystemmacOS Operating System DetailsUsing a MacBook with macOS running FastAPI with docker. SQLModel Version0.0.4 Python Version3.8.6 Additional ContextWe have two models with a one to many relationship (Team -> Hero). 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) Full stacktrace |
Beta Was this translation helpful? Give feedback.
Replies: 16 comments
-
|
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. |
Beta Was this translation helpful? Give feedback.
-
|
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 and then after the selects, add the selectinload option
|
Beta Was this translation helpful? Give feedback.
-
|
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 |
Beta Was this translation helpful? Give feedback.
-
|
Thanks to @rscottweekly for the solution and @jonra1993 for a sample implementation - it is much appreciated! |
Beta Was this translation helpful? Give feedback.
-
|
@rscottweekly you're an angel |
Beta Was this translation helpful? Give feedback.
-
|
Hi, I am also facing this issue and getting the same exception. Adding 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()) |
Beta Was this translation helpful? Give feedback.
-
|
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 |
Beta Was this translation helpful? Give feedback.
-
|
for future reference:
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) |
Beta Was this translation helpful? Give feedback.
-
|
hello, any clue for the lazy loading solution ? |
Beta Was this translation helpful? Give feedback.
-
|
I've made an extension module that adds an awaitable field for accessing relationship models using async. from typing import Optional
from collections.abc import Awaitable
from sqlmodel import Field, select
from async_sqlmodel import AsyncSQLModel, AwaitableField
class Team(AsyncSQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
heroes: List["Hero"] = Relationship()
awt_heroes: Awaitable[List["Hero"]] = AwaitableField(field="heroes")
class Hero(AsyncSQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
team_id: Optional[int] = Field(default=None, foreign_key="team.id")
team: Optional[Team] = Relationship(back_populates="heroes")
awt_team: Awaitable[Optional[Team]] = AwaitableField(field="team")
hero = (
await session.exec(select(Hero).where(Hero.id == 1))
).one()
# loading lazy loading attribute will raise MissingGreenlet error
team = hero.team
# E 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)
# it works!
team = await hero.awt_team |
Beta Was this translation helpful? Give feedback.
-
works like a charm 💯 , thanks a lot for this feature ! |
Beta Was this translation helpful? Give feedback.
-
|
@2jun0 say: class Team(AsyncSQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
heroes: List["Hero"] = Relationship()
tags: List["Tag"] = Relationship()
awt_heroes: Awaitable[List["Hero"]] = AwaitableField(field="heroes")
awt_tags: Awaitable[List["Tag"]] = AwaitableField(field="tags")# get awaitable relationship values:
heros = await team.awt_heros
tags = await team.awt_tagsBoth heros and tags got tags values, after tests, it seems that it's always the last declared Awaitable in the Model definition (here |
Beta Was this translation helpful? Give feedback.
-
|
@copdips |
Beta Was this translation helpful? Give feedback.
-
|
I confirm the bug is fixed, and what you did is really awesome to me, take your time to improve it if you want. |
Beta Was this translation helpful? Give feedback.
-
|
Using selectin related methods affects the original lazy loading design. Asynchronous access to Relationship attributes can be achieved by Example: ...
from sqlalchemy.ext.asyncio import AsyncAttrs
class Team(SQLModel, AsyncAttrs, table=True): # <-- AsyncAttrs
...
heroes: List["Hero"] = Relationship(back_populates="team")
async def select_heroes():
async with AsyncSession(engine) as session:
...
heroes = await team_preventers.awaitable_attrs.heroes # <-- awaitable_attrs
print(f"Preventers heroes: {heroes}") |
Beta Was this translation helpful? Give feedback.
-
|
This approach does not work out-of-the-box with FastAPI: from sqlalchemy.ext.asyncio import AsyncAttrs
...
class HeroPublicWithTeam(HeroPublic):
team: TeamPublic | None = None
class TeamPublicWithHeroes(TeamPublic):
heroes: list[HeroPublic] = []
...
@app.get("/heroes/{hero_id}", response_model=HeroPublicWithTeam)
async def read_hero(*, session: AsyncSession = Depends(get_session), hero_id: int):
hero = await session.exec(select(Hero).where(Hero.id == hero_id))
if not hero:
raise HTTPException(status_code=404, detail="Hero not found")
return hero
# Code omitted 👈
@app.get("/teams/{team_id}", response_model=TeamPublicWithHeroes)
async def read_team(*, team_id: int, session: AsyncSession = Depends(get_session)):
team = await session.exec(select(Team).where(Team.id == team_id))
if not team:
raise HTTPException(status_code=404, detail="Team not found")
return teamThe above code is adapted from the SQLModel documentation with modifications for async operations. However, this does not work as expected, and the only workaround I found is: @app.get("/teams/{team_id}", response_model=TeamPublicWithHeroes)
async def read_team(*, team_id: int, session: AsyncSession = Depends(get_session)):
team = await session.exec(select(Team).where(Team.id == team_id))
if not team:
raise HTTPException(status_code=404, detail="Team not found")
await team.awaitable_attrs.heroes # <-- Explicitly populating "heroes"
return teamThis solution feels a bit inelegant and, more importantly, if you forget to manually load the related attribute, you’ll encounter the error: Is there a better approach to handling this in an async context? |
Beta Was this translation helpful? Give feedback.
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 selectinloadand then after the selects, add the selectinload option
statement = select(Team).where(Team.name == "Preventers").options(selectinload(Team.heroes))