@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": testemail@gmail.com,
"created_at": "some timestamp e.g 2025-03-01T10:09:55.174834+04:00"
}
}
]
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