In shorts, session.Query(User).all() returns list of User object.
I want to session.add_colums() in same level, but it failed.
AS-IS
{
"User": {
"name": "John",
"age": 20
},
"department_name": "Apple"
}
TO-BE
{
"name": "John",
"age": 20
"department_name": "Apple"
}
I'm using FastAPI and SQLAlchemy.
I want to response joined result such as User with its department name.
# SQLAlchemy Models
class User(Base):
id = Column(Intger, primary_key=True)
name = Column(String)
age = Column(Integer)
department_id = Column(Integer, ForeignKey("department.id"), nullable=False)
class Department(Base):
id = Column(Intger, primary_key=True)
name = Column(String)
UserDetail is pydantic model for response model in FastAPI.
# Pydantic Model
class UserDetail(BaseModel):
name: str
age: int
department_name: str
class Config:
orm_mode = True
# FastAPI path operation function
@app.get("/users")
def read_users():
q = session.query(User).join(Department).set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)
# set label style: use column name without its table name
q.add_column(Department.name).label('department_name') # I want to add
return q.all()
Then it returns as below:
[{
"User": {
"name": "John",
"age": 20
},
"department_name": "Apple"
}]
How to change it as pydantic model? If I just add response model, it raises ValidationError.
[{
"name": "John",
"age": 20,
"department_name": "Apple"
}]
I don't want to query User.name and User.age each explictly because pydantic model or SQLAlchemy model can be changed.
I want to load all of them.