1

I have the following query:

matches = dal.session.query(Bet.profit_loss)
matches = (
    matches
    .select_from(Bet)
    .join(Schedule, Bet.schedule_id.__eq__(Schedule.id_))
    .join(
        GamesATP,
        and_(
            Schedule.tour_id.__eq__(tour_id),
            Schedule.tournament_id.__eq__(GamesATP.ID_T_G),
            Schedule.round_id.__eq__(GamesATP.ID_R_G),
            Schedule.player_id_p1.__eq__(GamesATP.ID1_G),
            Schedule.player_id_p2.__eq__(GamesATP.ID2_G),
        )
    )
)
matches = matches.filter(
    Bet.complete.__eq__(0),
    or_(Bet.actual_odds.__ne__(None), Bet.actual_odds.__ne__("")),
    or_(Bet.actual_stake.__ne__(None), Bet.actual_stake.__ne__("")),
    or_(Bet.profit_loss.__eq__(None), Bet.profit_loss.__eq__("")),
    (
        Schedule.p1_win - (1 / Bet.p1_max_odds)
        >=
        1 - Schedule.p1_win - (1 / Bet.p2_max_odds)
    )
)

I'm looking to update the result using the following:

for match in matches:
    matches.update(
        {Bet.profit_loss: Bet.actual_stake * (Bet.actual_odds - 1)},
        synchronize_session=False,
    )

However, I get the error message:

InvalidRequestError: Can't call Query.update() or Query.delete() when join(), outerjoin(), select_from(), or from_self() has been called

I have no idea how to get around this as SQL seems fine with update queries that have joins...?

Jossy
  • 589
  • 2
  • 12
  • 36
  • 1
    Having `for match in matches:` there is btw. redundant, and in fact just causes you to emit the same UPDATE as many times as the query up to that point produces results. To your issue at hand, see for example https://stackoverflow.com/questions/42543223/how-do-you-express-a-multi-table-update-update-from-in-sqlalchemy-orm – Ilja Everilä Aug 16 '20 at 07:53
  • 1
    And some history: https://stackoverflow.com/questions/33153823/update-joined-table-via-sqlalchemy-orm-using-session-query – Ilja Everilä Aug 16 '20 at 07:58
  • So put short, to produce the multiple table update you want, you will have to express the query more in terms of relational calculus than algebra, but don't worry, they're equivalent. – Ilja Everilä Aug 16 '20 at 08:06
  • I always worry when I see lines like this: "you will have to express the query more in terms of relational calculus than algebra"!!! So by the looks of it I need to move all the join conditions into the filter statement? – Jossy Aug 16 '20 at 08:26
  • Worked first time. You're a star!!! Oh - and thanks for pointing out my stupid error on ```matches``` – Jossy Aug 16 '20 at 08:29
  • You should consider posting this as an answer and accepting it so others can see that your problem was solved, especially if you solved it yourself :) – Ruben Helsloot Aug 16 '20 at 09:38
  • Hi @RubenHelsloot - I considered it but Ilja has posted links that are really duplicates of my question? – Jossy Aug 16 '20 at 09:57
  • No problem! In that case please consider closing your question as a duplicate, so those are easier to find for others – Ruben Helsloot Aug 16 '20 at 10:30

0 Answers0