我已经处理 SQLAlchemy 中关联表的操作几天了，在创建 person 对象时遇到了问题。
我正在尝试创建 person 对象。如果我将career_roles 和genres 字段保留为空列表，则该对象已成功创建。但是，如果我向这些字段添加整数值（主键），则会收到错误。
crud 操作
async def create(*, db_session: AsyncSession, person_in: PersonCreate) -> Person:
"""Creates a new person."""
person = Person(
name=person_in.name,
height=person_in.height,
birthday=person_in.birthday,
)
db_session.add(person)
await db_session.commit()
await db_session.refresh(person)
if person_in.career_roles:
result = await db_session.execute(
select(CareerRole).where(CareerRole.id.in_(person_in.career_roles))
)
career_roles = result.scalars().all()
person.career_roles.extend(career_roles)
if person_in.genres:
result = await db_session.execute(
select(Genre).where(Genre.id.in_(person_in.genres))
)
genres = result.scalars().all()
person.genres.extend(genres)
await db_session.commit()
await db_session.refresh(person)
return person
模型和关联表
person_genre_association = Table(
"person_genre",
Base.metadata,
Column("person_id", ForeignKey("persons.id")),
Column("genre_id", ForeignKey("genres.id")),
)
person_career_role_association = Table(
"person_career_role",
Base.metadata,
Column("person_id", ForeignKey("persons.id")),
Column("career_role_id", ForeignKey("career_roles.id")),
)
class Person(Base):
__tablename__ = "persons"
id: Mapped[int] = mapped_column(primary_key=True)
career_roles: Mapped[list["CareerRole"]] = relationship(
secondary=person_career_role_association
)
genres: Mapped[list["Genre"]] = relationship(secondary=person_genre_association)
pydantic schema
class PersonCreate(PersonBase):
career_roles: list[int] | None = []
genres: list[int] | None = []
json in
{
...
"career_roles": [1],
"genres": [1]
}
错误
ERROR: Exception in ASGI application
Traceback (most recent call last):
File "/home/username/Dev/screen-scout/.venv/lib/python3.10/site-packages/uvicorn/protocols/http/httptools_impl.py", line 399, in run_asgi
result = await app( # type: ignore[func-returns-value]
File "/home/username/Dev/screen-scout/.venv/lib/python3.10/site-packages/uvicorn/middleware/proxy_headers.py", line 70, in __call__
return await self.app(scope, receive, send)
File "/home/username/Dev/screen-scout/.venv/lib/python3.10/site-packages/fastapi/applications.py", line 1054, in __call__
await super().__call__(scope, receive, send)
File "/home/username/Dev/screen-scout/.venv/lib/python3.10/site-packages/starlette/applications.py", line 123, in __call__
await self.middleware_stack(scope, receive, send)
File "/home/username/Dev/screen-scout/.venv/lib/python3.10/site-packages/starlette/middleware/errors.py", line 186, in __call__
raise exc
File "/home/username/Dev/screen-scout/.venv/lib/python3.10/site-packages/starlette/middleware/errors.py", line 164, in __call__
await self.app(scope, receive, _send)
File "/home/username/Dev/screen-scout/.venv/lib/python3.10/site-packages/starlette/middleware/exceptions.py", line 65, in __call__
await wrap_app_handling_exceptions(self.app, conn)(scope, receive, send)
File "/home/username/Dev/screen-scout/.venv/lib/python3.10/site-packages/starlette/_exception_handler.py", line 64, in wrapped_app
raise exc
File "/home/username/Dev/screen-scout/.venv/lib/python3.10/site-packages/starlette/_exception_handler.py", line 53, in wrapped_app
await app(scope, receive, sender)
File "/home/username/Dev/screen-scout/.venv/lib/python3.10/site-packages/starlette/routing.py", line 756, in __call__
await self.middleware_stack(scope, receive, send)
File "/home/username/Dev/screen-scout/.venv/lib/python3.10/site-packages/starlette/routing.py", line 776, in app
await route.handle(scope, receive, send)
File "/home/username/Dev/screen-scout/.venv/lib/python3.10/site-packages/starlette/routing.py", line 297, in handle
await self.app(scope, receive, send)
File "/home/username/Dev/screen-scout/.venv/lib/python3.10/site-packages/starlette/routing.py", line 77, in app
await wrap_app_handling_exceptions(app, request)(scope, receive, send)
File "/home/username/Dev/screen-scout/.venv/lib/python3.10/site-packages/starlette/_exception_handler.py", line 64, in wrapped_app
raise exc
File "/home/username/Dev/screen-scout/.venv/lib/python3.10/site-packages/starlette/_exception_handler.py", line 53, in wrapped_app
await app(scope, receive, sender)
File "/home/username/Dev/screen-scout/.venv/lib/python3.10/site-packages/starlette/routing.py", line 72, in app
response = await func(request)
File "/home/username/Dev/screen-scout/.venv/lib/python3.10/site-packages/fastapi/routing.py", line 278, in app
raw_response = await run_endpoint_function(
File "/home/username/Dev/screen-scout/.venv/lib/python3.10/site-packages/fastapi/routing.py", line 191, in run_endpoint_function
return await dependant.call(**values)
File "/home/username/Dev/screen-scout/screenscout/api/routers/persons.py", line 33, in create_person
person = await create(db_session=db_session, person_in=person_in)
File "/home/username/Dev/screen-scout/screenscout/crud/person.py", line 40, in create
person.career_roles.extend(career_roles)
File "/home/username/Dev/screen-scout/.venv/lib/python3.10/site-packages/sqlalchemy/orm/attributes.py", line 566, in __get__
return self.impl.get(state, dict_) # type: ignore[no-any-return]
File "/home/username/Dev/screen-scout/.venv/lib/python3.10/site-packages/sqlalchemy/orm/attributes.py", line 1086, in get
value = self._fire_loader_callables(state, key, passive)
File "/home/username/Dev/screen-scout/.venv/lib/python3.10/site-packages/sqlalchemy/orm/attributes.py", line 1121, in _fire_loader_callables
return self.callable_(state, passive)
File "/home/username/Dev/screen-scout/.venv/lib/python3.10/site-packages/sqlalchemy/orm/strategies.py", line 967, in _load_for_state
return self._emit_lazyload(
File "/home/username/Dev/screen-scout/.venv/lib/python3.10/site-packages/sqlalchemy/orm/strategies.py", line 1130, in _emit_lazyload
result = session.execute(
File "/home/username/Dev/screen-scout/.venv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2351, in execute
return self._execute_internal(
File "/home/username/Dev/screen-scout/.venv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2236, in _execute_internal
result: Result[Any] = compile_state_cls.orm_execute_statement(
File "/home/username/Dev/screen-scout/.venv/lib/python3.10/site-packages/sqlalchemy/orm/context.py", line 293, in orm_execute_statement
result = conn.execute(
File "/home/username/Dev/screen-scout/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
return meth(
File "/home/username/Dev/screen-scout/.venv/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 515, in _execute_on_connection
return connection._execute_clauseelement(
File "/home/username/Dev/screen-scout/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1640, in _execute_clauseelement
ret = self._execute_context(
File "/home/username/Dev/screen-scout/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
return self._exec_single_context(
File "/home/username/Dev/screen-scout/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
self._handle_dbapi_exception(
File "/home/username/Dev/screen-scout/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2356, in _handle_dbapi_exception
raise exc_info[1].with_traceback(exc_info[2])
File "/home/username/Dev/screen-scout/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
self.dialect.do_execute(
File "/home/username/Dev/screen-scout/.venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
cursor.execute(statement, parameters)
File "/home/username/Dev/screen-scout/.venv/lib/python3.10/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 572, in execute
self._adapt_connection.await_(
File "/home/username/Dev/screen-scout/.venv/lib/python3.10/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 123, in await_only
raise exc.MissingGreenlet(
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)
错误信息
sqlalchemy.exc.MissingGreenlet: greenlet_spawn has not been called
表明你在使用
await
关键字的协程函数中调用了 SQLAlchemy 的同步函数。
具体来说，问题出在你在
create
函数中使用了
extend
方法将查询结果添加到
person.career_roles
和
person.genres
列表中。 虽然你使用了
await db_session.execute
来异步执行查询，但
extend
方法本身是同步的，它试图在没有 greenlet 上下文的情况下执行数据库操作，从而导致错误。
解决方法是使用异步方式将查询结果添加到列表中。你可以使用列表推导式来实现：
async def create(*, db_session: AsyncSession, person_in: PersonCreate) -> Person:
"""Creates a new person."""
person = Person(
name=person_in.name,
height=person_in.height,
birthday=person_in.birthday,
)
db_session.add(person)
await db_session.commit()
await db_session.refresh(person)
if person_in.career_roles:
result = await db_session.execute(
select(CareerRole).where(CareerRole.id.in_(person_in.career_roles))
)
# 使用列表推导式异步添加结果
person.career_roles = [role async for role in result.scalars()]
if person_in.genres:
result = await db_session.execute(
select(Genre).where(Genre.id.in_(person_in.genres))
)
# 使用列表推导式异步添加结果
person.genres = [genre async for genre in result.scalars()]
await db_session.commit()
await db_session.refresh(person)
return person
通过使用异步列表推导式，你可以在不阻塞事件循环的情况下将查询结果添加到列表中，从而避免了
MissingGreenlet
错误。