MissingGreenlet: greenlet_spawn has not been called

Question

I am trying to get the number of rows matched in a one to many relationship. When I try parent.children_count I get :

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/14/xd2s)

I added expire_on_commit=False but still get the same error. How can I fix this?

import asyncio
from uuid import UUID, uuid4
from sqlmodel import SQLModel, Relationship, Field
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession

class Parent(SQLModel, table=True):
    id: UUID = Field(default_factory=uuid4, primary_key=True)
    children: list["Child"] = Relationship(back_populates="parent")
    @property
    def children_count(self):
        return len(self.children)

class Child(SQLModel, table=True):
    id: UUID = Field(default_factory=uuid4, primary_key=True)
    parent_id: UUID = Field(default=None, foreign_key=Parent.id)
    parent: "Parent" = Relationship(back_populates="children")

async def main():
    engine = create_async_engine("sqlite+aiosqlite://")
    async with engine.begin() as conn:
        await conn.run_sync(SQLModel.metadata.create_all)

    async with AsyncSession(engine) as session:
        parent = Parent()
        session.add(parent)
        await session.commit()
        await session.refresh(parent)
        print(parent.children_count)  # I expect 0 here, as of now this parent has no children

asyncio.run(main())

Answer 1

I think the problem here is that by default SQLAlchemy lazy-loads relationships, so accessing parent.children_count implicitly triggers a database query leading to the reported error.

One way around this would be to specify a load strategy other than "lazy" in the relationship definition. Using SQLModel, this would look like:

children: list['Child'] = Relationship(
    back_populates='parent', sa_relationship_kwargs={'lazy': 'selectin'}
)

This will cause SQLAlchemy to issue an additional query to fetch the relationship while still in "async mode". Another option would be to pass {'lazy': 'joined'}, which would cause SQLAlchemy to fetch the all the results in a single JOIN query.

If configuring the relationship is undesirable, you could issue a query specifying the option:

from sqlalchemy.orm import selectinload
from sqlmodel import select

    ...
    async with AsyncSession(engine) as session:
        parent = Parent()
        session.add(parent)
        await session.commit()
        result = await session.scalars(
            select(Parent).options(selectinload(Parent.children))
        )
        parent = result.first()
        print(
            parent.children_count
        )  # I need 0 here, as of now this parent has no children