Why isn't my update query committed to the database?

Question

I have the following table:

class Schedule(Base):
    __tablename__ = "schedule_"
    __table_args__ = ({"schema": BELGARATH, "extend_existing": True})

    id_ = Column(Integer, primary_key=True)
    tour_id = Column(Integer, index=True)
    tournament_id = Column(Integer, index=True)
    round_id = Column(Integer, index=True)
    player_id_p1 = Column(Integer, index=True)
    player_id_p2 = Column(Integer, index=True)
    p1_win = Column(Float)

Which I am trying to update with the following query:

    update_schedule = dal.session.query(
        Schedule.tour_id,
        Schedule.tournament_id,
        Schedule.round_id,
        Schedule.player_id_p1,
        Schedule.player_id_p2,
        Schedule.p1_win,
    )
    update_schedule = update_schedule.filter(
        Schedule.tour_id == prediction["tour_id"],
        Schedule.tournament_id == prediction["tournament_id"],
        Schedule.round_id == prediction["round_id"],
        Schedule.player_id_p1 == prediction["player_id_p1"],
        Schedule.player_id_p2 == prediction["player_id_p2"],
    )
    update_schedule.p1_win = prediction["p1_win"]
    breakpoint()
    dal.session.commit()

Where prediction is the following dictionary:

{'tour_id': 0, 'tournament_id': 1, 'round_id': 1, 'player_id_p1': 1, 'player_id_p2': 2, 'p1_win': 2.0}

The change isn't being applied to the database and I can't work out why.

If at the breakpoint I run:

update_schedule.p1_win

Then I get the result as 2.0. However, if I look at update_schedule then this shows (0, 1, 1, 1, 2, None) and the p1_win change doesn't get applied to the database after the commit. What am I missing?

Ilja Everilä · Accepted Answer · 2020-08-15 13:57:32Z

2

update_schedule is a Query object, not a Schedule, so you have not made any changes to an object held in the session, or emitted an UPDATE to the DB. Python allows you to set new attributes on objects (with some exceptions), but having the new attribute set on the Query object has no effect. Either you meant to fetch a Schedule object for update, or you want to perform a bulk update using Query.update(). The former:

update_schedule = dal.session.query(Schedule).filter(
    Schedule.tour_id == prediction["tour_id"],
    Schedule.tournament_id == prediction["tournament_id"],
    Schedule.round_id == prediction["round_id"],
    Schedule.player_id_p1 == prediction["player_id_p1"],
    Schedule.player_id_p2 == prediction["player_id_p2"],
).one() # or first(), or ...
update_schedule.p1_win = prediction["p1_win"]
breakpoint()
dal.session.commit()

and the latter:

dal.session.query(Schedule).filter(
    Schedule.tour_id == prediction["tour_id"],
    Schedule.tournament_id == prediction["tournament_id"],
    Schedule.round_id == prediction["round_id"],
    Schedule.player_id_p1 == prediction["player_id_p1"],
    Schedule.player_id_p2 == prediction["player_id_p2"],
).update({
    Schedule.p1_win: prediction["p1_win"]
}, synchronize_session=False)
breakpoint()
dal.session.commit()

edited Aug 15, 2020 at 13:57

answered Aug 15, 2020 at 13:51

Ilja Everilä

53.4k9 gold badges138 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jossy Over a year ago

Brilliant, thanks! Do you have a preference for the former or the latter?

Ilja Everilä Over a year ago

Well, it depends. If the project is more ORMy, or you just happen to have the object available anyway, why not use the ORM features. But the "bulk" update does save you a SELECT, if you don't need the data for anything and just want to update.

Collectives™ on Stack Overflow

Why isn't my update query committed to the database?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related