Pydantic如何获得关系价值

Question

我需要在pydanticBaseModel中从关系多对一模型中获得一个值。我该怎么做呢?

My children class

class Picnic(Base):
__tablename__ = 'picnic'
id = Column(Integer, primary_key=True, autoincrement=True)
city_id = Column(Integer, ForeignKey('city.id'), nullable=False)
city = relationship('City', backref='picnics')

class City(Base):
__tablename__ = 'city'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String, unique=True, nullable=False, index=True)

我需要得到城市名称值:

class Picnics(BaseModel):
id: int
# city: str[CityBaseInDB.name] not working
# city: str = Field(source='city.name') not working 
# city_name: str not working

class Config:
orm_mode: bool = True

Answer 1

from sqlalchemy import Column, ForeignKey, Integer, String, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session, relationship, sessionmaker, Query
from pydantic import BaseModel
Base = declarative_base()
engine = create_engine("sqlite://", echo=True)

class Picnic(Base):
__tablename__ = 'picnic'
id = Column(Integer, primary_key=True, autoincrement=True)
city_id = Column(Integer, ForeignKey('city.id'), nullable=False)
city = relationship('City', backref='picnics')

class City(Base):
__tablename__ = 'city'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String, unique=True, nullable=False, index=True)

class PicnicModel(BaseModel):
id: int
# city: str[CityBaseInDB.name] not working
# city: str = Field(source='city.name') not working
city_name: str
class Config:
orm_mode: bool = True

picnic = Picnic(city=City(name='Shenzhen'))
Base.metadata.create_all(engine)
LocalSession = sessionmaker(bind=engine)
db: Session = LocalSession()
db.add(picnic)
db.commit()
q: Query = db.query(Picnic.id, City.name.label('city_name'))
q = q.select_from(Picnic).join(City)
row = q.one_or_none()
model = PicnicModel.from_orm(row)
print(model)

Answer 2

您还需要为父模型创建一个pydantic模型，并将其作为关系字段中的类型。

from pydantic import BaseModel
class City(BaseModel):
id: int
name: str
class Config:
orm_mode: bool = True

class Picnics(BaseModel):
id: int
city: City
class Config:
orm_mode: bool = True