如何在SQLAlchemy 2.0语法中使用包含连接到别名表的查询删除记录?

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",
)

值得一提的是，这些表格是由第三方数据库填充的，该数据库包含两次网球巡回赛的锦标赛，球员和比赛;ATP和WTA。在这个数据库中，每个巡回赛都有单独的锦标赛、球员和比赛表。我将它们导入到数据库中的组合表中，并使用tour_id字段来标识它们最初来自哪个游/表。我需要能够根据原始锦标赛和球员id从Match表中删除记录。

我第一次尝试这个查询:

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)

但是，我得到了错误:

'Delete' object has no attribute 'join'

这个相关的答案说明在1。然后SA将从filter内获取表，并在SQL中转换为USING。由此，我用2.0语法构建了以下查询:

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)

然而，我得到了错误:

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.

我不确定推荐的操作会有什么效果，所以我也调整了解决方案如下:

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)

但是我现在得到错误:

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.

值得一提的是，在本例中没有符合查询条件的记录。

实现我想要做的事情的最好方法是什么?

Answer 1

当然，SQLAlchemy中的where子句是一个单语句操作。要考虑多个条件，您要么需要使用and_操作符，要么将多个.where调用链接起来。

换句话说，我相信这个可以解决你的问题-

1. 使用and_操作符:

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. 链接多个.where调用:

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)