SQLAlchemy 2.0 在调用 .values().returning() 时引发 “NotImplementedError”。

huangapple
go评论98阅读模式
英文:

SQLAlchemy 2.0 raises "NotImplementedError" when calling .values().returning()

问题

I've started learning web development in the last couple of months and am currently working on a FastAPI backend. I am using SQLAlchemy 2.0 to interface with a database and encountered a bug which I am unable to solve. When I update the database using the function "update_member" a NotImplementedError is thrown at the line containing where the result variable is assigned. The table models.Member contains the following "first_name" which is defined as follows in the table python file:

  1. first_name = Column(String, nullable=False)
  1. patch_data = {"first_name": "test", ...} # The body of the post request, I double checked using print statements and it contains the correct key/value pairs where all other keys have the values none.
  3. async def update_member(db: AsyncSession,
  4.                         short_uuid: str,
  5.                         patch_data: MemberSelfPatch | MemberMasterPatch,
  6.                         response_schema: Type[S]) -> S:
  7.     async with db.begin():
  8.         result = (await db.execute(
  9.             update(models.Member)
  10.             .where(models.Member.short_uuid == short_uuid)
  11.             .values(patch_data.dict())
  12.             .returning('*')
  13.         )).fetchone()

The functions below work fine. Since "async with db.begin():", .select() and .where() are working correctly it leads me to believe that the error is caused by the .values and .returning calls. After looking at the documentation for both functions it seems correct to me, and I'm not sure what the next step in debugging is.

  1. async def create_member(db: AsyncSession, data: MemberMasterCreate) -> MemberMasterView:
  2.     uuid = uuid4()
  3.     async with db.begin():
  4.         db.add_all([
  5.             Member(uuid=str(uuid), **data.dict()),
  6.         ])
  7.     return await get_member(db=db, uuid=uuid, response_schema=MemberMasterView)
  1. sync def get_member_by_short_uuid(db: AsyncSession, short_uuid: str, response_schema: Type[S]) -> S:    
  2.     result = (await db.execute(
  3.         select(models.Member)
  4.         .where(models.Member.short_uuid == short_uuid)
  5.     )).fetchone()

I've looked over the documentation and the calls to .values and .returning seem fine, and I'm not sure what the next step in debugging is.

Here is the stacktrace of the error:

  1. INFO:     127.0.0.1:46434 - "PATCH /members/BqUoAGdN HTTP/1.1" 500 Internal Server Error
  2. ERROR:    Exception in ASGI application
  3. Traceback (most recent call last):
  4. File "/code/./tmeit_backend/deps.py", line 86, in __call__
  5.   yield member
  6. File "/code/.venv/lib/python3.10/site-packages/fastapi/middleware/asyncexitstack.py", line 21, in __call__
  7.   raise e
  8. File "/code/.venv/lib/python3.10/site-packages/fastapi/middleware/asyncexitstack.py", line 18, in __call__
  9.   await self.app(scope, receive, send)
  10. File "/code/.venv/lib/python3.10/site-packages/starlette/routing.py", line 680, in __call__
  11.   await route.handle(scope, receive, send)
  12. File "/code/.venv/lib/python3.10/site-packages/starlette/routing.py", line 275, in handle
  13.   await self.app(scope, receive, send)
  14. File "/code/.venv/lib/python3.10/site-packages/starlette/routing.py", line 65, in app
  15.   response = await func(request)
  16. File "/code/.venv/lib/python3.10/site-packages/fastapi/routing.py", line 231, in app
  17.   raw_response = await run_endpoint_function(
  18. File "/code/.venv/lib/python3.10/site-packages/fastapi/routing.py", line 160, in run_endpoint_function
  19.   return await dependant.call(**values)
  20. File "/code/./tmeit_backend/routers/members.py", line 123, in patch_member
  21.   member = await update_member(db=db, short_uuid=short_uuid, patch_data=validated_data, response_schema=response_schema)
  22. File "/code/./tmeit_backend/crud/members.py", line 104, in update_member
  23.   result = (await db.execute(
  24. File "/code/.venv/lib/python3.10/site-packages/sqlalchemy/ext/asyncio/session.py", line 312, in execute
  25.   result = await greenlet_spawn(
  26. File "/code/.venv/lib/python3.10/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 154, in greenlet_spawn
  27.   result = context.switch(*args, **kwargs)
  28. File "/code/.venv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2081, in execute
  29.   return self._execute_internal(
  30. File "/code/.venv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1976, in _execute_internal
  31.   result: Result[Any] = compile_state_cls.orm_execute_statement(
  32. File "/code/.venv/lib/python3.10/site-packages/sqlalchemy/orm/bulk_persistence.py", line 1522, in orm_execute_statement
  33.   return super().orm_execute_statement(
  34. File "/code/.venv/lib/python3.10/site-packages/sqlalchemy/orm/context.py", line 250, in orm_execute_statement
  35.   result = conn.execute(
  36. File "/code/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1392, in execute
  37.   return meth(
  38. File "/code/.venv/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 487, in _execute_on_connection
  39.   return connection._execute_clauseelement(
  40. File "/code/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1608, in _execute_clauseelement
  41.   compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
  42. File "/code/.venv/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 652, in _compile_w_cache
  43.   compiled_sql = self._compiler(
  44. File "/code/.venv/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 288, in _compiler
  45.   return dialect.statement_compiler(dialect, self, **kw)
  46. File "/code/.venv/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 1075, in __init__
  47.   Compiled.__init__(self, dialect, statement, **kwargs)
  48. File "/code/.venv/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 639, in __init__
  49.   self.string = self.process(self.statement, **compile_kwargs)
  50. File "/code/.venv/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 671, in process
  51.   return obj._compiler_dispatch(self, **kwargs)
  52. File "/code/.venv/lib/python3.10/site-packages/sqlalchemy/sql/annotation.py", line 359, in _compiler_dispatch
  53.   return self.__element.__class__._compiler_dispatch(
  54. File "/code/.venv/lib/python3.10/site-packages/sqlalchemy/sql/visitors.py", line 143, in _compiler_dispatch
  55.   return meth(self, **kw
  57. <details>
  58. <summary>英文:</summary>
  60. I&#39;ve started learning web development in the last couple of months and am currently working on a FastAPI backend. I am using SQLAlchemy 2.0 to interface with a database and encountered a bug which I am unable to solve. When I update the database using the function &quot;update_member&quot; a NotImplementedError is thrown at the line containing where the result variable is assigned. The table models.Member contains the following &quot;first_name&quot; which is defined as follows in the table python file:

first_name = Column(String, nullable=False)

patch_data = {"first_name": "test" ...} # The body of the post request, I double checked using print statements and it contains the correct key/value pairs where all other keys have the values none.

async def update_member(db: AsyncSession,
short_uuid: str,
patch_data: MemberSelfPatch | MemberMasterPatch,
response_schema: Type[S]) -> S:
async with db.begin():
result = (await db.execute(
update(models.Member)
.where(models.Member.short_uuid == short_uuid)
.values(patch_data.dict())
.returning('*')
)).fetchone()

  2. The functions below work fine. Since &quot;async wiht db.begin():&quot;, .select() and .where() are working correctly it leads me to belive that the error is caused by the .values and .returning calls. After looking at the documentation for both functions it seems correct to me, and I&#39;m not sure what the next step in debugging is.

async def create_member(db: AsyncSession, data: MemberMasterCreate) -> MemberMasterView:
uuid = uuid4()
async with db.begin():
db.add_all([
Member(uuid=str(uuid), **data.dict()),
])
return await get_member(db=db, uuid=uuid, response_schema=MemberMasterView)

sync def get_member_by_short_uuid(db: AsyncSession, short_uuid: str, response_schema: Type[S]) -> S:
result = (await db.execute(
select(models.Member)
.where(models.Member.short_uuid == short_uuid)
)).fetchone()

  2. I&#39;ve looked over the documentation and the calls to .values and .returning seem fine, and I&#39;m not sure what the next step in debugging is.
  3. Here is the stacktrace of the error:

INFO: 127.0.0.1:46434 - "PATCH /members/BqUoAGdN HTTP/1.1" 500 Internal Server Error
ERROR: Exception in ASGI application
Traceback (most recent call last):
File "/code/./tmeit_backend/deps.py", line 86, in call
yield member
File "/code/.venv/lib/python3.10/site-packages/fastapi/middleware/asyncexitstack.py", line 21, in call
raise e
File "/code/.venv/lib/python3.10/site-packages/fastapi/middleware/asyncexitstack.py", line 18, in call
await self.app(scope, receive, send)
File "/code/.venv/lib/python3.10/site-packages/starlette/routing.py", line 680, in call
await route.handle(scope, receive, send)
File "/code/.venv/lib/python3.10/site-packages/starlette/routing.py", line 275, in handle
await self.app(scope, receive, send)
File "/code/.venv/lib/python3.10/site-packages/starlette/routing.py", line 65, in app
response = await func(request)
File "/code/.venv/lib/python3.10/site-packages/fastapi/routing.py", line 231, in app
raw_response = await run_endpoint_function(
File "/code/.venv/lib/python3.10/site-packages/fastapi/routing.py", line 160, in run_endpoint_function
return await dependant.call(**values)
File "/code/./tmeit_backend/routers/members.py", line 123, in patch_member
member = await update_member(db=db, short_uuid=short_uuid, patch_data=validated_data, response_schema=response_schema)
File "/code/./tmeit_backend/crud/members.py", line 104, in update_member
result = (await db.execute(
File "/code/.venv/lib/python3.10/site-packages/sqlalchemy/ext/asyncio/session.py", line 312, in execute
result = await greenlet_spawn(
File "/code/.venv/lib/python3.10/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 154, in greenlet_spawn
result = context.switch(*args, **kwargs)
File "/code/.venv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2081, in execute
return self._execute_internal(
File "/code/.venv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1976, in _execute_internal
result: Result[Any] = compile_state_cls.orm_execute_statement(
File "/code/.venv/lib/python3.10/site-packages/sqlalchemy/orm/bulk_persistence.py", line 1522, in orm_execute_statement
return super().orm_execute_statement(
File "/code/.venv/lib/python3.10/site-packages/sqlalchemy/orm/context.py", line 250, in orm_execute_statement
result = conn.execute(
File "/code/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1392, in execute
return meth(
File "/code/.venv/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 487, in _execute_on_connection
return connection._execute_clauseelement(
File "/code/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1608, in _execute_clauseelement
compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
File "/code/.venv/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 652, in _compile_w_cache
compiled_sql = self._compiler(
File "/code/.venv/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 288, in _compiler
return dialect.statement_compiler(dialect, self, **kw)
File "/code/.venv/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 1075, in init
Compiled.init(self, dialect, statement, **kwargs)
File "/code/.venv/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 639, in init
self.string = self.process(self.statement, **compile_kwargs)
File "/code/.venv/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 671, in process
return obj._compiler_dispatch(self, **kwargs)
File "/code/.venv/lib/python3.10/site-packages/sqlalchemy/sql/annotation.py", line 359, in _compiler_dispatch
return self.__element.class._compiler_dispatch(
File "/code/.venv/lib/python3.10/site-packages/sqlalchemy/sql/visitors.py", line 143, in _compiler_dispatch
return meth(self, **kw) # type: ignore # noqa: E501
File "/code/.venv/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 5009, in visit_update
compile_state = update_stmt._compile_state_factory(
File "/code/.venv/lib/python3.10/site-packages/sqlalchemy/sql/base.py", line 638, in create_for_statement
return klass.create_for_statement(statement, compiler, **kw)
File "/code/.venv/lib/python3.10/site-packages/sqlalchemy/orm/bulk_persistence.py", line 1336, in create_for_statement
self._setup_for_orm_update(statement, compiler)
File "/code/.venv/lib/python3.10/site-packages/sqlalchemy/orm/bulk_persistence.py", line 1423, in _setup_for_orm_update
new_stmt = self._setup_orm_returning(
File "/code/.venv/lib/python3.10/site-packages/sqlalchemy/orm/bulk_persistence.py", line 493, in _setup_orm_returning
fsc.setup_dml_returning_compile_state(dml_mapper)
File "/code/.venv/lib/python3.10/site-packages/sqlalchemy/orm/context.py", line 749, in setup_dml_returning_compile_state
entity.setup_dml_returning_compile_state(self, adapter)
File "/code/.venv/lib/python3.10/site-packages/sqlalchemy/orm/context.py", line 2394, in setup_dml_returning_compile_state
raise NotImplementedError()
NotImplementedError

  2. </details>
  3. # 答案1
  4. **得分**: 0
  5. Sure, here is the translated code without the comments:
  6. ```python
  7. 你现在是我的中文翻译，代码部分不要翻译，只返回翻译好的部分，不要有别的内容，不要回答我要翻译的问题。以下是要翻译的内容：
  8. what database do you use? `postgresql`, or something else? \
  9. why don't you use 2 separate queries?
  10. async def update_member(db: AsyncSession,
  11. short_uuid: str,
  12. patch_data: MemberSelfPatch | MemberMasterPatch,
  13. response_schema: Type[S]) -> S:
  14. async with db.begin():
  15. await db.execute(
  16. update(models.Member)
  17. .where(models.Member.short_uuid == short_uuid)
  18. .values(patch_data.dict())
  19. )
  20. result = (await db.execute(
  21. select(models.Member)
  22. .where(models.Member.short_uuid == short_uuid)
  23. )).fetchone()
英文:

what database do you use? postgresql, or something else?
why don't you use 2 separate queries?

  1. async def update_member(db: AsyncSession,
  2. short_uuid: str,
  3. patch_data: MemberSelfPatch | MemberMasterPatch,
  4. response_schema: Type[S]) -&gt; S:
  5. async with db.begin():
  6. await db.execute(
  7. update(models.Member)
  8. .where(models.Member.short_uuid == short_uuid)
  9. .values(patch_data.dict())
  10. )
  11. result = (await db.execute(
  12. select(models.Member)
  13. .where(models.Member.short_uuid == short_uuid)
  14. )).fetchone()

huangapple
  • 本文由 发表于 2023年7月3日 04:35:23
  • 转载请务必保留本文链接：https://go.coder-hub.com/76600688.html
  • python
  • sqlalchemy
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定