i can not find the solution with this task (fastapi and sql)

Question

@router.get("/", response_model=list[sh.PostResponse]) def ger_posts():
    curr.execute("""
        SELECT *
        FROM posts
        JOIN users ON users.id = posts.user_id

    """)
    posts = curr.fetchall()
    conn.commit()
    return posts 


class UserResponse(BaseModel):
    id: int
    email: EmailStr
    created_at: datetime  

class PostResponse(PostBase):
    id: int
    created_at: datetime
    user_id: int
    owner: UserResponse

can you help me to solve the error. there i want it to return proper json where will be information about post and user (owner).

for better understanding what i want to do, my friend used orm and added this to his database from python to get the exact same result i want:

owner = relationship("user")

but i don't use orm because i use psycopg2. is it possible to solve this problem effectively without orm, or am i better start using it soon? is it okay to work with psycopg2 with orm? how to solve this specific task?

kind of result i need:

[
    {
        "title": "test 1",
        "content": "testing",
        "published": true,
        "id": 1,
        "created_at": "2025-03-01T10:09:55.174834+04:00",
        "user_id": 2,
        "owner":{
            "id": 2,
            "email": [email protected],
            "created_at": "some timestamp e.g 2025-03-01T10:09:55.174834+04:00"
        }
    }
]

Coco Q. · Accepted Answer · 2025-03-01 09:26:06Z

1

If you want to return proper JSON, you need to pre-set a JSON structure in your return. You need to manually construct a dictionary that fits JSON structure.

Here is the example of what you can add to your code:

@router.get("/", response_model=list[sh.PostResponse]) 
def ger_posts():
    with conn.cursor() as curr:
        curr.execute("""
            SELECT *
            FROM posts
            JOIN users ON users.id = posts.user_id
        """)
        rows = curr.fetchall()
    # posts = curr.fetchall() - Here you fetch all raw data as posts which has not been structured
    # conn.commit()
    # return posts 

    posts = [] # create an empty json basic structure for structuring raw data
    # structure your raw data
    for row in rows:
        # assume first 5 columns belong to posts, next 3 belong to users
        post_data = row[:5] 
        user_data = row[5:]  

        # assume fields in your json 
        post = {
            "id": post_data[0],
            "title": post_data[1],
            "content": post_data[2],
            "created_at": post_data[3],
            "user_id": post_data[4],
            "owner": {
                "id": user_data[0],
                "email": user_data[1],
                "created_at": user_data[2],
            },
        }
        posts.append(post)
    
    conn.commit()
    return posts


class UserResponse(BaseModel):
    id: int
    email: EmailStr
    created_at: datetime  

class PostResponse(PostBase):
    id: int
    created_at: datetime
    user_id: int
    owner: UserResponse

answered Mar 1 at 9:26

Coco Q.

1,0564 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Lazare Janiashvili Mar 1 at 9:30

yes i have seen that solution but i thought it was too manual and thought there would be more effective solution since it would be hard for big data to manually set the area.

Coco Q. Mar 1 at 9:54

you can use RealDictCursor, although it still needs manual work, it returns row as dic-like objects. Then you can convert it using a list comprehension, which works efficiently even with a large number of rows.

Lazare Janiashvili Mar 1 at 9:57

you mean curr.execute and what is inside it sql code?

Coco Q. Mar 1 at 10:10

When you create a cursor, select RealDictCursor; then you get rows from fetchall; use dict() convert rows to a dictionary; then use json.dumps to convert dictionary into a JSON structure

Lazare Janiashvili Mar 1 at 10:21

oh thanks you gave me great hint. i used json_build_object and it worked. thanks

Collectives™ on Stack Overflow

i can not find the solution with this task (fastapi and sql)

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related