在FastAPI的sqlachemy中连接两个表返回查询对象

Question

我正在通过YouTube学习FastAPI，当我用sqlalchemy连接表的时候，得到的结果和视频里不一样。那个视频已经有两年了，我试着找解决办法，但一直没找到。

我想把POST表和Vote表连接起来，想要每个POST的投票数量，但我得到的只是总投票数，而不是POST对象，只显示了查询对象。

谢谢你的帮助。

模型

class Post(Base):
    __tablename__ = "posts"

    id = Column(Integer, primary_key=True, nullable=False)
    title = Column(String, nullable=False)
    content = Column(String, nullable=False)
    published = Column(Boolean, server_default="TRUE")
    created_at = Column(TIMESTAMP(timezone=True),
                        nullable=False, server_default=text('now()'))
    owner_id = Column(Integer, ForeignKey(
        "users.id", ondelete="CASCADE"), nullable=False)

    owner = relationship("User")


class Vote(Base):
    __tablename__ = "votes"

    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), primary_key=True)
    post_id = Column(Integer, ForeignKey("posts.id", ondelete="CASCADE"), primary_key=True)
    created_at = Column(TIMESTAMP(timezone=True),
                        nullable=False, server_default=text('now()'))

这是我的POST路由函数。给我查询对象的那个查询是results。

@router.get("/posts")
async def get_posts(
        db: Session = Depends(get_db),
        current_user: int = Depends(oauth2.get_current_user),
        search: Optional[str] = '',
):

    posts = db.query(models.Post).filter(models.Post.title.contains(search)).all()


    results = db.query(models.Post, func.count(models.Vote.post_id).label("votes_count")).\
join(models.Vote, models.Vote.post_id == models.Post.id, isouter=True).\
group_by(models.Post.id).all()

print(results)


    return results

"results"变量的打印输出：

[(<app.models.Post object at 0x7f19893fe310>, 0), (<app.models.Post object at 0x7f19893fe390>, 0), (<app.models.Post object at 0x7f19893fe210>, 1), (<app.models.Post object at 0x7f19893fe290>, 0), (<app.models.Post object at 0x7f19893fe410>, 0)]

我希望它能显示实际的POST对象，而不是查询对象。

这是我返回results时出现的错误

INFO:     127.0.0.1:50202 - "GET /posts HTTP/1.1" 500 Internal Server Error
ERROR:    Exception in ASGI application
Traceback (most recent call last):
  File "/home/eminent/Documents/My codes/FastAPI/Tutorials/SocialAPI/venv/lib/python3.11/site-packages/fastapi/encoders.py", line 322, in jsonable_encoder
    data = dict(obj)
           ^^^^^^^^^
TypeError: cannot convert dictionary update sequence element #0 to a sequence

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/eminent/Documents/My codes/FastAPI/Tutorials/SocialAPI/venv/lib/python3.11/site-packages/fastapi/encoders.py", line 327, in jsonable_encoder
    data = vars(obj)
           ^^^^^^^^^
TypeError: vars() argument must have __dict__ attribute

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/eminent/Documents/My codes/FastAPI/Tutorials/SocialAPI/venv/lib/python3.11/site-packages/uvicorn/protocols/http/httptools_impl.py", line 426, in run_asgi
    result = await app(  # type: ignore[func-returns-value]
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/eminent/Documents/My codes/FastAPI/Tutorials/SocialAPI/venv/lib/python3.11/site-packages/uvicorn/middleware/proxy_headers.py", line 84, in __call__
    return await self.app(scope, receive, send)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/eminent/Documents/My codes/FastAPI/Tutorials/SocialAPI/venv/lib/python3.11/site-packages/fastapi/applications.py", line 1106, in __call__
    await super().__call__(scope, receive, send)
  File "/home/eminent/Documents/My codes/FastAPI/Tutorials/SocialAPI/venv/lib/python3.11/site-packages/starlette/applications.py", line 122, in __call__
    await self.middleware_stack(scope, receive, send)
  File "/home/eminent/Documents/My codes/FastAPI/Tutorials/SocialAPI/venv/lib/python3.11/site-packages/starlette/middleware/errors.py", line 184, in __call__
    raise exc
  File "/home/eminent/Documents/My codes/FastAPI/Tutorials/SocialAPI/venv/lib/python3.11/site-packages/starlette/middleware/errors.py", line 162, in __call__
    await self.app(scope, receive, _send)
  File "/home/eminent/Documents/My codes/FastAPI/Tutorials/SocialAPI/venv/lib/python3.11/site-packages/starlette/middleware/exceptions.py", line 79, in __call__
    raise exc
  File "/home/eminent/Documents/My codes/FastAPI/Tutorials/SocialAPI/venv/lib/python3.11/site-packages/starlette/middleware/exceptions.py", line 68, in __call__
    await self.app(scope, receive, sender)
  File "/home/eminent/Documents/My codes/FastAPI/Tutorials/SocialAPI/venv/lib/python3.11/site-packages/fastapi/middleware/asyncexitstack.py", line 20, in __call__
    raise e
  File "/home/eminent/Documents/My codes/FastAPI/Tutorials/SocialAPI/venv/lib/python3.11/site-packages/fastapi/middleware/asyncexitstack.py", line 17, in __call__
    await self.app(scope, receive, send)
  File "/home/eminent/Documents/My codes/FastAPI/Tutorials/SocialAPI/venv/lib/python3.11/site-packages/starlette/routing.py", line 718, in __call__
    await route.handle(scope, receive, send)
  File "/home/eminent/Documents/My codes/FastAPI/Tutorials/SocialAPI/venv/lib/python3.11/site-packages/starlette/routing.py", line 276, in handle
    await self.app(scope, receive, send)
  File "/home/eminent/Documents/My codes/FastAPI/Tutorials/SocialAPI/venv/lib/python3.11/site-packages/starlette/routing.py", line 66, in app
    response = await func(request)
               ^^^^^^^^^^^^^^^^^^^
  File "/home/eminent/Documents/My codes/FastAPI/Tutorials/SocialAPI/venv/lib/python3.11/site-packages/fastapi/routing.py", line 292, in app
    content = await serialize_response(
              ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/eminent/Documents/My codes/FastAPI/Tutorials/SocialAPI/venv/lib/python3.11/site-packages/fastapi/routing.py", line 180, in serialize_response
    return jsonable_encoder(response_content)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/eminent/Documents/My codes/FastAPI/Tutorials/SocialAPI/venv/lib/python3.11/site-packages/fastapi/encoders.py", line 301, in jsonable_encoder
    jsonable_encoder(
  File "/home/eminent/Documents/My codes/FastAPI/Tutorials/SocialAPI/venv/lib/python3.11/site-packages/fastapi/encoders.py", line 330, in jsonable_encoder
    raise ValueError(errors) from e
ValueError: [TypeError('cannot convert dictionary update sequence element #0 to a sequence'), TypeError('vars() argument must have __dict__ attribute')]      
              

Answer 1

这应该能修复你的代码：

@router.get("/posts")
def get_post(db: Session = Depends(get_db), current_user: int = Depends(oauth2.get_current_user), search: Optional[str]=''):

     posts = db.query(models.Post).filter(models.Post.title.contains(search)).all()


    results = db.query(models.Post, func.count(models.Vote.post_id).label("votes_count")).join(
        models.Vote, models.Vote.post_id == models.Post.id, isouter=True).group_by(models.Post.id)

    new_results = db.execute(results).mappings().all()
    

    return new_results