英文:

Fastapi Sqlalchemy TypeError when returning results from a query that selects columns from two tables

问题

我从一个视频教程中复制了下面的代码，它应该返回以下输出。

代码
```python
@router.get('/', status_code=status.HTTP_200_OK)
def get_notes(db: Session = Depends(get_db)):

    posts = db.query(models.Post, func.count(models.Likes.post_id).label("likes")) \
    .join(models.Likes, models.Likes.post_id == models.Post.post_id, isouter=True) \
    .group_by(models.Post.post_id).all()

    return posts

根据视频教程的预期输出

[
    {
        "Post": {
            "user_id": 1,
            "title": "title1",
            "created_datetime": "2023-02-19T16:39:07.552186+00:00",
            "post_id": 1,
            "content": "content for post 1"
        },
        "likes": 0
    },
    {
        "Post": {
            "user_id": 1,
            "title": "title2",
            "created_datetime": "2023-02-19T16:39:04.426658+00:00",
            "post_id": 2,
            "content": "content for post 2"
        },
        "likes": 0
    },
    ....
]

然而，发送这个请求导致了500 Internal Server Error。

File "C:\Users\apapa\AppData\Local\pypoetry\Cache\virtualenvs\fastapi-backend-curI0rdX-py3.10\lib\site-packages\fastapi\encoders.py", line 160, 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')]

打印posts变量显示它的值是一个元组的列表，而不是字典的列表。

[(<app.db.models.Post object at 0x00000246A03003A0>, 0), (<app.db.models.Post object at 0x00000246A0300400>, 0), (<app.db.models.Post object at 0x00000246A0300430>, 0), (<app.db.models.Post object at 0x00000246A0300460>, 0), (<app.db.models.Post object at 0x00000246A0300490>, 2)]

因此，我用下面的方法绕过了这个问题，但我认为这不是理想的解决方法，而且在第一次就不应该出现这个问题：

posts = [{"Post": {**post.__dict__}, "likes": likes} for post, likes in posts]
return posts

我想知道视频教程是如何使它正常工作的？或者我是否漏掉了任何可以避免这个奇怪问题的步骤？或者只是我自己遇到了这个问题？

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

I have copied the below code from a video tutorial, and it is supposed to return the following output.

Code

@router.get('/', status_code= status.HTTP_200_OK)
def get_notes(db: Session = Depends(get_db)):

posts = db.query(models.Post, func.count(models.Likes.post_id).label("likes")) \

.join(models.Likes, models.Likes.post_id == models.Post.post_id, isouter=True)
.group_by(models.Post.post_id).all()

return posts

Expected output according to the video tutorial

[
{
"Post": {
"user_id": 1,
"title": "title1",
"created_datetime": "2023-02-19T16:39:07.552186+00:00",
"post_id": 1,
"content": "content for post 1"
},
"likes": 0
},
{
"Post": {
"user_id": 1,
"title": "title2",
"created_datetime": "2023-02-19T16:39:04.426658+00:00",
"post_id": 2,
"content": "content for post 2"
},
"likes": 0
},
....
]

However sending this request resulted in a 500 Internal Server Error.

File "C:\Users\apapa\AppData\Local\pypoetry\Cache\virtualenvs\fastapi-backend-curI0rdX-py3.10\lib\site-packages\fastapi\encoders.py", line 160, 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')]

Printing the `posts` variable shows its value to be a list of tuple instead of a list of dicts.

[(<app.db.models.Post object at 0x00000246A03003A0>, 0), (<app.db.models.Post object at 0x00000246A0300400>, 0), (<app.db.models.Post object at 0x00000246A0300430>, 0), (<app.db.models.Post object at 0x00000246A0300460>, 0), (<app.db.models.Post object at 0x00000246A0300490>, 2)]

Therefore I have circumvented the problem with this, which I don't think is ideal, and shouldn't be necessary in the first place:

posts = [{"Post": {**post.dict}, "likes": likes} for post, likes in posts]
return posts

I'm curious how did the video tutorial got it to work in the first place? Or if I have missed any steps that could avoid this weird problem? Or is it just me who is having this problem?


</details>


# 答案1
**得分**: 1

I was also following the same tutorial and faced the same issue.

The issue is with the version of SQLAlchemy==1.4.23 which the tutorial uses.

I was on the latest version of SQLAlchemy==2.0.19.

Surprisingly, the query returns data in the same format for both SQLAlchemy versions.

**Downgrading to SQLAlchemy==1.4.23 fixed the issue.**

[(&lt;app.db.models.Post object at 0x00000246A03003A0&gt;, 0), (&lt;app.db.models.Post object at 0x00000246A0300400&gt;, 0), (&lt;app.db.db.models.Post object at 0x00000246A0300430&gt;, 0), (&lt;app.db.models.Post object at 0x00000246A0300460&gt;, 0), (&lt;app.db.models.Post object at 0x00000246A0300490&gt;, 2)]

The solution you present will not work if we use schemas for output.
The solution that works for me in SQLAlchemy==2.0.19 is basically converting the list of tuples to a list of dictionaries. The Post in dictionary fields should be of object type:

    posts_new = [{"Post": post, "votes": votes}
                 for post, votes in posts]
    
    return posts_new

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

I was also folwing the same tutorial and faced same issue

The issue is with the version of SQLAlchemy==1.4.23 which tutorial uses.


I was on the latest version of SQLAlchemy==2.0.19.

Surprisingly the query returns data in the same format for both SQLAlchemy versions

**Downgrading to SQLAlchemy==1.4.23 fixed the issue**

[(&lt;app.db.models.Post object at 0x00000246A03003A0&gt;, 0), (&lt;app.db.models.Post object at 0x00000246A0300400&gt;, 0), (&lt;app.db.models.Post object at 0x00000246A0300430&gt;, 0), (&lt;app.db.models.Post object at 0x00000246A0300460&gt;, 0), (&lt;app.db.models.Post object at 0x00000246A0300490&gt;, 2)]

The solution you present will not work if we use schemas for output.
The solution that works for me in SQLAlchemy==2.0.19 is basically converting the list of tuples to a list of dictionaries. The Post in dictionary fields should be object type 

    posts_new = [{&quot;Post&quot;: post, &quot;votes&quot;: votes}
                 for post, votes in posts]
    
    return posts_new

</details>