this seems super basic but I just CANNOT get it to work after hours, feeling super dumb.
I have a SQLite DB with a table called players that each have an id (int), a name (str) and an is_active (bool) attribute. Making a get endpoint to get all of the entries was no problem. I now want an endpoint that can update a row via put. It does not have to be patch since all attributes to update are provided.
The models
class PlayerBase(BaseModel):
name: str
active: bool
class PlayerModel(PlayerBase):
id: int
class Config:
from_attributes = True
The working endpoints
def get_db():
db = SessionLocal()
try:
yield db
finally:
db.close()
db_dependency = Annotated[Session, Depends(get_db)]
models.Base.metadata.create_all(bind=engine)
@app.post("/players/", response_model=PlayerModel)
async def create_player(player: PlayerBase, db: db_dependency):
db_player = models.Player(**player.dict())
db.add(db_player)
db.commit()
db.refresh(db_player)
return db_player
@app.get("/players/", response_model=List[PlayerModel])
async def read_players(db: db_dependency, skip: int = 0, limit: int = 100):
players = db.query(models.Player).offset(skip).limit(limit).all()
return players
Now, how do I achieve what I want? I have tried 100 different things producing 200 different errors, so I don't think it even makes sense to tell you guys what I've tried since I'm just completely confused now. One thing I wanted was to get a player by it's id as an intermediate step (and because it's just nice to have), but this just super didn't work despite being seemingly very close to the other get that does work.
@app.get("/players/{player_id}", response_model=PlayerModel)
async def read_player(db: db_dependency, player_id: int):
player = db.query(models.Player).filter_by(id = player_id)
return player
For context, in a React app I have a field where one can enter a name and click a checkbox for if the player is active or not. That then gets posted and created a new entry. Works great. I now want to update the active field if an existing player is entered with a different state in is_active. Does not work at all, see above.
I see where the confusion might be stemming from.
Fetching a player by ID:
The issue with your read_player function is that after filtering the query by the player's ID, you never actually execute the query to fetch the data. This is why it doesn't work. You need to add .first() at the end of the query to get the first record that matches the criteria.
@app.get("/players/{player_id}", response_model=PlayerModel)
async def read_player(db: db_dependency, player_id: int):
player = db.query(models.Player).filter_by(id=player_id).first()
if player is None:
raise HTTPException(status_code=404, detail="Player not found")
return player
Updating a player:
To update a player, you need a PUT endpoint that takes in the player's ID and the new data to update. Here's how you can implement this:
@app.put("/players/{player_id}", response_model=PlayerModel)
async def update_player(player_id: int, player_data: PlayerBase, db: db_dependency):
# Fetch the player you want to update
player = db.query(models.Player).filter_by(id=player_id).first()
if player is None:
raise HTTPException(status_code=404, detail="Player not found")
# Update the player's data
for key, value in player_data.dict().items():
setattr(player, key, value)
db.commit()
db.refresh(player)
return player
React Side:
On the React side, when you want to update a player's data, you'll make a PUT request to the /players/{player_id} endpoint with the updated data. If a player with the entered name exists, you'll update the active field using this endpoint.
In the code above, I used the filter_by method for the query, which is just one way to filter the records. You can also use the filter method and provide conditions in a slightly different manner, e.g., filter(models.Player.id == player_id). Both methods are valid and achieve the same result; it's a matter of personal preference.
With these changes, your update functionality should work as expected.