How to delete records using a query that includes joins to aliased tables in SQLAlchemy 2.0 syntax?

Question

I'm trying to delete records based on a query that includes joins to a couple of aliased tables.

Here are the tables in question:

class Match(Base):
    
    id_ = Column(Integer, primary_key=True)
    tournament_id = Column(Integer, ForeignKey("myschema.tournament.id_"))
    round_id = Column(TINYINT, index=True)
    player_id_p1 = Column(Integer, ForeignKey("myschema.player.id_"))
    player_id_p2 = Column(Integer, ForeignKey("myschema.player.id_"))

    p1 = relationship("Player", foreign_keys=[player_id_p1])
    p2 = relationship("Player", foreign_keys=[player_id_p2])


class Tournament(Base):

    id_ = Column(Integer, primary_key=True)
    original_id = Column(Integer, index=True)
    tour_id = Column(TINYINT, index=True)

    match = relationship("Match", backref="tournament")


class Player(Base):

    id_ = Column(Integer, primary_key=True)
    original_id = Column(Integer, index=True)
    tour_id = Column(TINYINT, index=True)

    match = relationship(
        'Match',
        primaryjoin=("or_(Player.id_ == Match.player_id_p1, Player.id_ == Match.player_id_p2)"),
        overlaps="p1, p2",
    )

Worth mentioning that these tables are populated from a third party database which has tournaments, players and matches from two tennis tours; the ATP and WTA. In that database each tour has separate tournament, player and match tables. I've imported them into combined tables in my database and used a tour_id field to identify which tour/table they originally came from. I need the ability to delete records from the Match table based upon the original tournament and player ids.

I first tried this query:

p1 = sa.orm.aliased(Player)
p2 = sa.orm.aliased(Player)
stmt = sa.delete(Match)
stmt = stmt.join(Tournament)
stmt = stmt.join(p1, p1.id_ == Match.player_id_p1)
stmt = stmt.join(p2, p2.id_ == Match.player_id_p2)
stmt = stmt.where(
    Tournament.tour_id == tour_id,
    Tournament.original_id == 16907,
    p1.tour_id == tour_id,
    p1.original_id == 79810,
    p2.tour_id == tour_id,
    p2.original_id == 37136,
    Match.round_id == 5,
)
session.execute(stmt)

However, I got the error:

'Delete' object has no attribute 'join'

This related answer states that in the 1.x syntax then SA will take the tables from within filter and convert to USING in SQL. From this I built the following query in 2.0 syntax:

p1 = sa.orm.aliased(Player)
p2 = sa.orm.aliased(Player)
stmt = sa.delete(Match)
stmt = stmt.where(
    Tournament.tour_id == 0,
    Tournament.original_id == 16907,
    p1.tour_id == 0,
    p1.original_id == 79810
    p2.tour_id == 0,
    p2.original_id == 37136,
    Match.round_id == 5,
)
session.execute(stmt)

However, I then get the error:

Exception has occurred: InvalidRequestError       (note: full exception trace is shown but execution is paused at: <module>)
Could not evaluate current criteria in Python: "Can't evaluate criteria against alternate class <class 'Tournament'>". Specify 'fetch' or False for the synchronize_session execution option.

I wasn't sure what effect the recommended action would have so I also adapted the solution here as follows:

p1 = sa.orm.aliased(Player)
p2 = sa.orm.aliased(Player)
s_qry = sa.select(Match.id_)
s_qry = s_qry.join(Tournament)
s_qry = s_qry.join(p1, p1.id_ == Match.player_id_p1)
s_qry = s_qry.join(p2, p2.id_ == Match.player_id_p2)
s_qry = s_qry.where(
    Tournament.tour_id == tour_id,
    Tournament.original_id == 16907,
    p1.tour_id == tour_id,
    p1.original_id == 79810,
    p2.tour_id == tour_id,
    p2.original_id == 37136,
    Match.round_id == 5,
)
s_qry = s_qry.subquery()
stmt = sa.delete(Match).where(Match.id_.in_(s_qry))
session.execute(stmt)

But I now get the error:

Exception has occurred: InvalidRequestError       (note: full exception trace is shown but execution is paused at: <module>)
Could not evaluate current criteria in Python: "Cannot evaluate Select". Specify 'fetch' or False for the synchronize_session execution option.

It might be worth mentioning that in this instance there is no record that corresponds to the query criteria.

What would be the best way to achieve what I'm looking to do?

Answer 1

AFAIK, the where clause in SQLAlchemy is a single statement operation. To consider multiple conditions, you either need to use the and_ operator, or chain multiple .where calls.

In other words, I believe this might solve your issues -

1. Use the and_ operator:

stmt = stmt.where(and_(Tournament.tour_id == tour_id,
                       Tournament.original_id == 16907,
                       p1.tour_id == tour_id,
                       p1.original_id == 79810,
                       p2.tour_id == tour_id,
                       p2.original_id == 37136,
                       Match.round_id == 5))

2. Chain multiple .where calls:

stmt = stmt.where(Tournament.tour_id == tour_id).\
            where(Tournament.original_id == 16907).\
            where(p1.tour_id == tour_id).\
            where(p1.original_id == 79810).\
            where(p2.tour_id == tour_id).\
            where(p2.original_id == 37136).\
            where(Match.round_id == 5)