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

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

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:

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

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.

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

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:

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

<details>
<summary>英文:</summary>

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


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


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.
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


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

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

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

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

发表评论

匿名网友

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

确定