0

I am trying to map data from sql join of two tables to pydantic model, but getting null value

models.py

class Countries(BaseModel):
    country_name: str = Field(None, alias="country_name")

class Tools(BaseModel):
    tool_name:str = Field(None, alias="tool_name")
    tool_id: int = Field(None, alias="tool_id")

class ShowData(BaseModel):
    countries:Countries = Field(None, alias="countries")
    tools:Tools = Field(None, alias="tools")

    class Config:
        orm_mode = True

repository.py

def test(db: Session = Depends(get_db)):
    statement = select(Tool.tool_name, UserCountryToolAccess.tool_id, Country.country_name).where(Tool.tool_id == UserCountryToolAccess.tool_id, Country.country_id == UserCountryToolAccess.country_id)
    results = db.exec(statement).fetchall()
    print(results)
    return results

This is the data I am receiving in repository.py [('pdf compressor', 1, 'United States of America'), ('image resizer', 4, 'United Kingdom'), ('cost budgeting', 2, 'Russia'), ('scenario planner', 5, 'Germany'), ('cropping image', 1, 'United States of America'), ('leave dashboard', 3, 'Russia')]

test.py

@router.get("/test",tags=['test'],response_model=schemas.ShowData)
def get_user(db: Session = Depends(get_db)):
    result = repository.test(db)
    return result

I need data in the below json structure

{
  "countries": {
    "country_name": "India"
  },
  "tools": {
    "tool_id": 1,
    "tool_name": "pdf compressor"
  }
}
code_10
  • 155
  • 1
  • 2
  • 10
  • It's possible that there is an option to configure the return structure from SQLAlchemy (which SQLModel is a thin wrapper around), but I would say the way easier option is just to structure your data yourself in a list/dict comprehension. I don't quite understand how your JSON structure is supposed to work when `test` returns more than on row? – M.O. Nov 11 '22 at 12:01

0 Answers0