Add more data from a get request with FastAPI

user_12043725 0
Viewed 1232

I begun to build an api with FastAPI in python and sqlalchemy : Here is a part of the models :

class Game(Base):
    __tablename__ = "games"

    id = Column(Integer, primary_key=True, index=True)
    league_id = Column(Integer)
    radiant_score = Column(Integer)
    dire_score = Column(Integer)
    duration = Column(Integer)
    is_valid = Column(Boolean, default=True)

    playerstats = relationship("PlayerStat", back_populates="match")


class PlayerStat(Base):
    __tablename__ = "playerstats"

    match_id = Column(Integer, ForeignKey("games.id"), primary_key=True)
    slot = Column(Integer, primary_key=True)
    hero_id = Column(Integer, ForeignKey("heros.id"))
    num_kills = Column(Integer, default=None)
    isRadiant = Column(Boolean, default=None)

    match = relationship("Game", back_populates="playerstats")
    heros = relationship("Hero", back_populates="playerstats")

After this, I create the schemas/models for pydantic (sry for long portion):

    class PlayerStatBase(BaseModel):
    slot: int
    hero_id: int
    num_kills: int
    isRadiant: bool


class PlayerStatCreate(PlayerStatBase):
    pass


class PlayerStat(PlayerStatBase):
    slot: int
    hero_id: int
    num_kills: int
    isRadiant: bool

    class Config:
        orm_mode = True

class GameBase(BaseModel):
    id: int
    league_id: int
    radiant_score: int
    dire_score: int
    duration: int
    is_valid: bool


class GameCreate(GameBase):
    pass


class Game(GameBase):
    id: int
    league_id: int
    radiant_score: int
    dire_score: int
    duration: int
    is_valid: bool
    players: List[PlayerStat] = [{}]

    class Config:
        orm_mode = True

And the crud function I use with the api :

    def get_match(db: Session, match_id: int):
    print(db.query(models.Game).filter(models.Game.id == match_id))
    return db.query(models.Game).filter(models.Game.id == match_id).first()

The api route is :

@app.get("/matches/{match_id}", response_model=schemas.Game)
def read_game(match_id: int, db: Session = Depends(get_db)):
    db_game = crud.get_match(db, match_id=match_id)
    if db_game is None:
        raise HTTPException(status_code=404, detail="Game not found")
    return db_game

The result I've got is the next :

{
  "id": 1,
  "league_id": 10,
  "radiant_score": 41,
  "dire_score": 5,
  "duration": 3541,
  "is_valid": true,
  "players": [
    {}
  ]
}

I would like to populate "players" with the list of playerstats of the corresponding match (sorted by slot) as :

"players" : [
  {
    "slot": 0,
    "hero_id": 14,
    "num_kills": 54,
    "isRadiant": true
  },
  {
    "slot": 1,
    "hero_id": 15,
    "num_kills": 1,
    "isRadiant": false
  }
]

I think I need to chance one of the model/schema or the crud function but don't really know which one ? Also, maybe there is some pydantic schema that are useless or poorly built

PS : I followed the guideline of the FastAPI docs (which I recommend to read).

Thx for your help!

fastapi sqlalchemy python-3.x api database
1 Answers

I believe you problem does not lie within the scope of fastapi, but sqlalchemy. When you query for orm objects with relationships, fastapi's standard is to lazyload relationships whenever they are accessed. Since you never directly access the relationship playerstats, it does not load in. See the docs for information.

The solution to your problem should be to update the crud function to this:

return db.query(models.Game).filter(models.Game.id == match_id)
                            .options(selectinload(models.Game.playerstats)).first()

"Select In Load" is a type of eager load that will load relations as the query is submitted. If you want this behaviour to occur on every query you can update the orm to this:

playerstats = relationship("PlayerStat", back_populates="match", lazy="selectin")

I hope this helped. It is my very first answer on stackoverflow :)

EDIT: There is actually one other thing. In your orm the relationship is called "playerstats" while you name the attribute "players" in your pydantic model. That does not work. Change the pydantic attribute name from "players" to "playerstats" and now everything should work.

EDIT 2: As you can guess no everything will not work yet. I just saw that there is one other thing missing. In pydantic models you can set the orm option. This is very important when working with sqlalchemy. I recommend this for all your pydantic models. This will have to be set on every pydantic model and their attribute models

class OtherModel(BaseModel):
    value: str = None

    class Config:
        orm_mode = True


class SomePydanticModel(BaseModel):
    value: str = None
    some_other_model: OtherModel = None

    class Config:
         orm_mode = True

Now we can also modify the return statement of your crud method again:

return Game.from_orm(db.query(models.Game).options(selectinload(models.Game.playerstats)).filter(models.Game.id == match_id).first())

Now everything should finally work :)

user_145774490
Related