我无法弄清楚如何使用 fastapi 和 sqlmodel 显示一对多关系。我读过这篇文章question https://stackoverflow.com/questions/72870598/getting-nested-joined-tables-to-display-in-the-openapi-interface-provided-by-f/73372947?noredirect=1#comment131875135_73372947但我的情况似乎略有不同。特别是在函数调用中。
这是我的schemas.py
:
from typing import Optional
from sqlmodel import Field, Relationship, SQLModel
class BinaryBase(SQLModel):
product_id: int
software_install_path: Optional[str] = None
host_id: int = Field(foreign_key="host.id", nullable=False)
class Binary(BinaryBase, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
host_id: int = Field(foreign_key="host.id", nullable=False)
host: "Host" = Relationship(back_populates="binaries")
class HostBase(SQLModel):
name: str
region: Optional[str] = None
os_version: Optional[str] = None
network: Optional[int] = None
class Host(HostBase, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
binaries: list[Binary] = Relationship(back_populates='host')
class HostReadWithBinary(HostBase):
bins: list[HostBase] = []
class BinaryReadWithHost(BinaryBase):
host: BinaryBase
这是我的main.py
:
from fastapi import Depends, FastAPI
from sqlmodel import Session, col, select
...
@app.get(
"/binaries/",
response_model=list[HostReadWithBinary]
)
def get_binary(name: Optional[str] = None, session: Session = Depends(get_session)) -> list[HostReadWithBinary]:
query = select(Host).limit(100)
if name:
query = query.where(col(Host.name).contains(name.lower()))
return session.exec(query).all()
The Host
表代表1
部分和Binary
表代表many
部分。我想得到所有的回应BinaryBase
热切地为所有主机提供属性。但我得到的是这样的:
[
{
"name": "hkl20014889",
"region": "HK",
"os_version": "Red Hat 6.10",
"network": 3,
"bins": []
},
{
"name": "hkl20016283",
"region": "HK",
"os_version": "Red Hat 6.10",
"network": 3,
"bins": []
},
....
理论上bins
应该持有的属性Host
表当id
in Host
joins host_id
在二进制中。