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

Question

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?

Answer 1

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

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

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 = [{"Post": post, "votes": votes}
             for post, votes in posts]

return posts_new