使用SQLAlchemy异步模式添加多对多关系时触发MissingGreenlet错误（疑似延迟加载问题）

问题描述： #

I'm struggling with SQLAlchemy. I have a model with a many to many relationship set up:

class User(Base):
    roles: Mapped[List["Role"]] = relationship(
        secondary="user_roles", back_populates="users"
    )

I'm setting up some code to populate the database with test data:

user = await register_user(
        email="me@email.com", username="username", password="test1234"
    )
    db_session.add(user)
    await db_session.commit()

    admin_role = Role(name="Admin", owner_id=user.id)
    db_session.add(admin_role)

    user.roles.append(admin_role)
    db_session.add(user)
    await db_session.commit()

But when I do this, I get

sqlalchemy.exc.MissingGreenlet: greenlet_spawn has not been called; can't call await_only() here. Was IO attempted in an unexpected place?

There's a link with that error which suggests that the problem is lazyloading. But in this case, I didn't load anything. I'm confused as to how to establish the cross model relationship.

I'm using the asyncpg driver to run on Postgres and have the sessionmaker set to use autocommit=False, expire_on_commit=False.

解决方案： #

我完全理解你的困惑——明明没主动触发加载操作，怎么会扯到延迟加载的问题？其实问题出在SQLAlchemy关系映射的默认行为上：

当你执行user.roles.append(admin_role)时，看起来只是往列表里添加元素，但SQLAlchemy的默认relationship配置（lazy="select"）会尝试同步加载当前用户的roles集合（哪怕是空的），这就触发了同步的数据库IO操作，而在异步环境下这种操作是不被允许的，所以抛出了MissingGreenlet错误。

解决这个问题有两种常用方式：

1. 修改关系定义，使用异步兼容的加载策略

在定义User.roles时，把lazy参数设置为异步安全的加载策略，比如"selectin"或"joined"：

class User(Base):
    roles: Mapped[List["Role"]] = relationship(
        secondary="user_roles", back_populates="users",
        lazy="selectin"  # 异步安全的批量加载策略
        # 或者用 lazy="joined"，会通过JOIN查询一次性加载关联数据
    )

"selectin"是异步环境下推荐的延迟加载策略，它会批量加载关联数据，不会触发同步IO；"joined"则适合在查询用户时就一次性把角色数据加载出来，避免后续的额外查询。

2. 预加载关联数据再操作

如果不想修改模型的默认配置，也可以在获取用户对象后，主动用异步加载方法预加载roles集合：

from sqlalchemy.future import select
from sqlalchemy.orm import selectinload

# 注册用户并提交
user = await register_user(
    email="me@email.com", username="username", password="test1234"
)
await db_session.commit()

# 重新查询用户并预加载roles
user = await db_session.execute(
    select(User).where(User.id == user.id).options(selectinload(User.roles))
)
user = user.scalar_one()

# 后续操作就不会触发同步加载了
admin_role = Role(name="Admin", owner_id=user.id)
db_session.add(admin_role)
user.roles.append(admin_role)
await db_session.commit()

另外，你设置expire_on_commit=False的做法很正确，这避免了提交后对象过期的问题，继续保持这个配置就好。

备注：内容来源于stack exchange，提问作者Rohit