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())

Cotidal

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

Trigraph

thanks for the reply @snakecharmerb, this works, if I add sa_relationship_kwargs={'lazy': 'selectin'} to my model, do I have to run alembic and create a migration script, the current table is configured without the sa_relationship_kwargs? – Cotidal

Relationship exist entirely in the Python layer, so there should be no need for a migration. – Trigraph

I have the same problem.i add "sa_relationship_kwargs={'lazy':'selectin'} in my model,use select(model).where(model.id==id).options(selectinload(model.children).i got same error,greenlet_spawn has not been called! how to fix it? this my problem:#74835591 – Petree

Answer 2

The main reason for this error is that in the synchronous sqlalchemy driver you can use the session for the lazy loading queries properly. Assume you have two relational models to each other, fetch the parent by a query then you are going to reach the child leading to another query (i.e. lazy loading). However, when you are using the async driver on top of sqlalchemy (e.g. asyncpg) the session will be automatically closed each time after a query leading to the raising error for getting the child info.

Therefore, generally, to solve this, you can adopt the different loading strategies (eager loading) whether in the relationship or in the query:

Relationship

You should add lazy= "joined" or "selectin" in the relationship

class Parent(Base):
    ...
    children = relationship("Child", back_populates="parent", lazy="selectin")

In this way, you can now perform the query in your crud method as follows:

from sqlalchemy.ext.asyncio import AsyncSession

async def create_parent(db: AsyncSession) -> Parent:
    parent = Parent()
    db.add(parent)
    await db.commit()
    await db.refresh(parent)  # you need this
    print(parent.children_count)  # works properly now
    return parent

_{Note: the async db session has been injected as a parameter to the crud method.}

---

Query

Now, let's say we haven't changed the relationship lazy value, so we would need those same changes into the query as follows:

Using joinedload:

from sqlalchemy.orm import joinedload, selectinload

async def create_parent(db: AsyncSession) -> Parent:
    parent = Parent()
    db.add(parent)
    await db.commit()

    result = await db.execute(
        select(Parent)
        .options(joinedload(Parent.children))
        .where(Parent.id == parent.id)
    )
    parent = result.scalars().unique().one()

    print(parent.children_count)  # works properly now

    return parent

Or using selectin:

from sqlalchemy.orm import joinedload, selectinload

async def create_parent(db: AsyncSession) -> Parent:
    parent = Parent()
    db.add(parent)
    await db.commit()

    result = await db.scalars(
        select(Parent)
        .options(selectinload(Parent.children))
        .where(Parent.id == parent.id)
    )
    parent = result.first()

    print(parent.children_count)  # works properly now

    return parent

---

[Loading Strategy Differences]:

joinedload is used which performs an SQL JOIN to load the related Parent.children objects. This means all data is loaded in one go. It results in fewer database round-trips, but the initial load might be slower due to the join operation.

selectinload strategy breaks up the loading into two separate queries — one for the parent and one for the child objects. This can sometimes be faster than joinedload as it avoids complex joins.

---

_{PS: I used sqlalchemy form instead of sqlmodel}

Eury

This solution worked for me, except I had to add lazy='selectin' both ways: both in the relation defined in parent to child, and in relation defined in child to parent. – Pellerin

Answer 3

For me the issue was due to a misleading error message. To me this error had nothing to do with a failed to spawn greenlet worker and that I had an invalid query where my relationships were misconfigured.

How I was able to debug this was first adding this line to my main method:

import logging
logging.basicConfig()    
logging.getLogger("sqlalchemy.engine").setLevel(logging.DEBUG)

By adding this line sqlalchemy will output your generated database query logs making it easier to see which table failed.

Then I checked every single nested relationship I had using this line and cross referencing it with https://docs.sqlalchemy.org/en/20/orm/basic_relationships.html:

await session.execute(select(<TableName>))

For many to many relationships I also needed to add:

relationship(secondary="junction_table_name", lazy="subquery")

I chose subquery more for performance reasons but you can make a more informed choice here https://docs.sqlalchemy.org/en/20/orm/relationship_api.html#sqlalchemy.orm.relationship

I went through the rabbit hole of joins and using selectinload, joinedload, contains_eager all of which did not solve the problem. This is my conjecture but what caused the problem is that a failed query tried to write an error the the IO stream which caused an error in the greenlet worker.

Cabal

Answer 4

In my scenario it caused by using asyncpg in db uri: postgresql+asyncpg://postgres:postgres@localhost:5432/postgres, but wuthout async code (def run_migrations_online() -> None:...), so I just removed +asyncpg in uri

Victualler

If you remove +asyncpg from the uri, you are likely to get an error: raise exc.InvalidRequestError( sqlalchemy.exc.InvalidRequestError: The asyncio extension requires an async driver to be used. The loaded 'psycopg2' is not async. – Valerivaleria

more likely you are calling async engine or session, in my case i was calling sync engine – Victualler