Python 如何使用 pydantic 模式从 sqlalchemy 关系中单独获取列

我有4张桌子:Hardware、SoftwareName、SoftwareVersion和Software.

Software表与SoftwareName表和SoftwareVersion表有one-to-many关系.最后，Hardware模型与Software表有one-to-many关系.

我试图使用Pydantic Schema从模型关系中获得一个特定的列.

现在我得到了以下输出:

[
  {
    "id": 1,
    "hostname": "hostname2",
    "softwares": [
      {
        "id": 1,
        "software_name": {
          "id": 1,
          "name": "nginx"
        },
        "software_version": {
          "id": 1,
          "version": "2.9"
        }
      },
      {
        "id": 2,
        "software_name": {
          "id": 2,
          "name": "vim"
        },
        "software_version": {
          "id": 2,
          "version": "0.3"
        }
      },
      {
        "id": 3,
        "software_name": {
          "id": 3,
          "name": "apache"
        },
        "software_version": {
          "id": 3,
          "version": "1.0"
        }
      }
    ]
  }
]

但我期望的是这种输出:

[
  {
    "id": 1,
    "hostname": "hostname2",
    "softwares": [
      {
        "id": 1,
        "name": "nginx",
        "version": "2.9"
      },
      {
        "id": 2,
        "name": "vim",
        "version": "0.3"
      },
      {
        "id": 3,
        "name": "apache",
        "version": "1.0"
      }
    ]
  }
]

我有文件main.py:

   
import uvicorn
from typing import Any, Iterator, List, Optional
from faker import Faker
from fastapi import Depends, FastAPI
from pydantic import BaseModel
from sqlalchemy import Column, ForeignKey, Integer, String, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session, sessionmaker, relationship
from faker.providers import DynamicProvider

software_name = DynamicProvider(
     provider_name="software_name",
     elements=["bash", "vim", "vscode", "nginx", "apache"],
)


software_version = DynamicProvider(
     provider_name="software_version",
     elements=["1.0", "2.9", "1.1", "0.3", "2.0"],
)


hardware = DynamicProvider(
     provider_name="hardware",
     elements=["hostname1", "hostname2", "hostname3", "hostname4", "hostname5"],
)

fake = Faker()

# then add new provider to faker instance
fake.add_provider(software_name)
fake.add_provider(software_version)
fake.add_provider(hardware)


engine = create_engine("sqlite:///.db", connect_args={"check_same_thread": False})
SessionLocal = sessionmaker(autocommit=True, autoflush=True, bind=engine)

Base = declarative_base(bind=engine)


class Software(Base):
    __tablename__ = 'software'

    id = Column(Integer, primary_key=True)
    hardware_id = Column(Integer, ForeignKey('hardware.id'))
    name_id = Column(Integer, ForeignKey('software_name.id'))
    version_id = Column(Integer, ForeignKey('software_version.id'))

    software_name = relationship('SoftwareName', backref='software_name')
    software_version = relationship('SoftwareVersion',
                                    backref='software_version')


class SoftwareName(Base):
    __tablename__ = 'software_name'

    id = Column(Integer, primary_key=True)
    name = Column(String)


class SoftwareVersion(Base):
    __tablename__ = 'software_version'

    id = Column(Integer, primary_key=True)
    version = Column(String)


class Hardware(Base):
    __tablename__ = "hardware"

    id = Column(Integer, primary_key=True, autoincrement=True)
    hostname = Column(String, nullable=False)

    softwares = relationship(Software)


Base.metadata.drop_all()
Base.metadata.create_all()

class BaseSchema(BaseModel):
    id: int

    class Config:
        orm_mode = True


class SoftwareNameSchema(BaseSchema):
    name: str


class SoftwareVersionSchema(BaseSchema):
    version: str


class SoftwareSchema(BaseSchema):
    software_name: SoftwareNameSchema
    software_version:  SoftwareVersionSchema


class HardwareOut(BaseSchema):
    hostname: str
    softwares: List[SoftwareSchema]


app = FastAPI()


@app.on_event("startup")
def on_startup() -> None:
    session = SessionLocal()

    for _ in range(10):
        software_list = []
        for _ in range(3):
            sn = SoftwareName(name=fake.software_name())
            sv = SoftwareVersion(version=fake.software_version())
            s = Software(software_name=sn, software_version=sv)
            software_list.append(s)

        h = Hardware(hostname=fake.hardware(), softwares=software_list)
        session.add(h)
        session.flush()

    session.close()


def get_db() -> Iterator[Session]:
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()


@app.get("/hardwares", response_model=List[HardwareOut])
def get_hardwares(db: Session = Depends(get_db)) -> Any:
    return [HardwareOut.from_orm(hardware) for hardware in db.query(Hardware).all()]

如何更改HardwareOut模式以返回预期的结果？