strange async sqlalchemy error with fastapi-pagination...

[wu-clan]

when i use async sqlalchemy with fastapi-pagination, I have a very strange mistake.
these are my code:

# This code can return data normally
@role.get('', summary='get all roles', response_model=Page[RoleAll], dependencies=[Depends(get_current_user)])
async def get_all_roles(db: AsyncSession = Depends(get_db)):
    roles = select(Role).order_by(Role.id.desc()).options(joinedload(Role.menus))
    return await paginate(db, roles)

# schema
class RoleAll(RoleBase):
    id: int
    menus: List[MenuAll]

    class Config:
        orm_mode = True

# This code doesn't return data correctly
@user.get('', summary='get all users', response_model=Page[GetUserInfo], dependencies=[Depends(get_current_user)])
async def get_all_users(db: AsyncSession = Depends(get_db)) -> Any:
    users = select(User).order_by(User.time_joined.desc()).options(joinedload(User.roles))
    return await paginate(db, users)

# schema
class GetUserInfo(UpdateUser):
    id: int
    user_uid: str
    avatar: Optional[str]
    time_joined: Optional[datetime.datetime]
    last_login: Optional[datetime.datetime]
    is_superuser: bool
    is_active: bool
    roles: List[RoleAll]

    class Config:
        orm_mode = True

And a strange mistake happened:
sqlalchemy.exc.StatementError: (sqlalchemy.exc.MissingGreenlet) greenlet_spawn has not been called; can't call await_() here. Was IO attempted in an unexpected place?

I've been thinking for a long time, but I can't understand why.

[uriyyo]

Hi @wu-clan,

I think I know where the problem is.
Could you please try changing get_all_users implementation to:

# This code doesn't return data correctly
@user.get('', summary='get all users', response_model=Page[GetUserInfo], dependencies=[Depends(get_current_user)])
async def get_all_users(db: AsyncSession = Depends(get_db)) -> Any:
    users = select(User).order_by(User.time_joined.desc()).options(selectinload(User.roles).joinedload(Role.menus))
    return await paginate(db, users)

Basically, you need to also load Role.menus relationship because it's required by RoleAll schema.

P.S. Little advice from me - when you have one-to-many or many-to-many relationships it's better to load them using selectinload in order to have correct pagination info (number of rows, number of pages, etc.).

[wu-clan]

Thank you very much for your quick recovery, i am glad it works.
I guess I don't know enough about sqlalchemy, it's hard for me to understand some of it docs.

fastapi-paginationis really great.