SQLAlchemy how to add_columns in the same level?

Viewed 26

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.

0 Answers
Related