FastAPI+Psycopg2关联查询返回嵌套JSON问题求助

1. 不使用ORM能否有效解决？ #

可以。核心是手动处理数据库查询结果的结构，将用户字段封装到owner嵌套对象中，再结合FastAPI的Pydantic模型完成序列化。

2. Psycopg2是否可以搭配ORM使用？ #

完全可以。比如常用的SQLAlchemy ORM就支持以Psycopg2（或psycopg2-binary）作为PostgreSQL的底层驱动，只需在SQLAlchemy的连接URL中指定即可：

from sqlalchemy import create_engine
engine = create_engine("postgresql://user:password@localhost/dbname?driver=psycopg2")

3. 实现期望返回格式的具体步骤 #

步骤1：优化SQL查询语句 #

避免使用SELECT *，明确指定字段并给重复字段（如id）起别名，防止字段冲突：

SELECT 
    posts.id AS post_id, posts.title, posts.content, posts.published, 
    posts.created_at AS post_created_at, posts.user_id,
    users.id AS owner_id, users.email, users.created_at AS owner_created_at
FROM posts
JOIN users ON users.id = posts.user_id

步骤2：用DictCursor处理结果并构建嵌套结构 #

使用Psycopg2的DictCursor让查询结果以字典形式返回，更方便字段映射。先配置游标：

import psycopg2.extras
curr = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

然后修改接口函数，手动拆分字段组装成符合模型的结构：

@router.get("/", response_model=list[sh.PostResponse])
def get_posts():  # 修正原代码笔误：ger_posts → get_posts
    curr.execute("""
        SELECT 
            posts.id AS post_id, posts.title, posts.content, posts.published, 
            posts.created_at AS post_created_at, posts.user_id,
            users.id AS owner_id, users.email, users.created_at AS owner_created_at
        FROM posts
        JOIN users ON users.id = posts.user_id
    """)
    posts = curr.fetchall()
    
    result = []
    for post in posts:
        # 组装owner嵌套对象
        owner_data = {
            "id": post["owner_id"],
            "email": post["email"],
            "created_at": post["owner_created_at"]
        }
        # 组装post主对象
        post_data = {
            "id": post["post_id"],
            "title": post["title"],
            "content": post["content"],
            "published": post["published"],
            "created_at": post["post_created_at"],
            "user_id": post["user_id"],
            "owner": owner_data
        }
        result.append(post_data)
    
    # 查询操作无需commit，移除原代码中的conn.commit()
    return result

步骤3：确认Pydantic模型正确性 #

你的模型定义已经符合要求，只要传入的数据结构匹配，FastAPI会自动完成JSON序列化：

from pydantic import BaseModel, EmailStr
from datetime import datetime

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

class PostBase(BaseModel):
    title: str
    content: str
    published: bool = True

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

内容的提问来源于stack exchange，提问作者Lazare Janiashvili