使用 SQLAlchemy 删除没有相关记录的行

我们有两个表，分别命名为 table1 和 table2。table2 中有一个外键，指向 table1。我们需要删除 table1 中所有在 table2 中没有子记录的行。以下 SQL 语句可以完成此操作：

DELETE FROM table1
WHERE 0 = (SELECT COUNT(*) FROM table2 WHERE table2.table1_id = table1.table1_id);

但是，我们无法找到一种方法将此查询转换为 SQLAlchemy。尝试使用直接方法：

subquery = session.query(sqlfunc.count(Table2).label('t2_count')).select_from(Table2).filter(Table2.table1_id == Table1.table1_id).subquery()
session.query(Table1).filter(0 == subquery.columns.t2_count).delete()

这会导致错误：

sqlalchemy.exc.ArgumentError: Only deletion via a single table query is currently supported

如何使用 SQLAlchemy 执行此 DELETE 操作？

• 操作环境：
  • Python 2.7
  • PostgreSQL 9.2.4
  • SQLAlchemy 0.7.10
  • GeoAlchemy（由于需要使用 GeoAlchemy，无法升级 SQLAlchemy 版本，但如果新版本可以简化此操作，我们也很感兴趣）

2. 解决方案

答案1：

from sqlalchemy.sql import not_

# 这种方法将行获取到 Python 中，以确定删除了哪些行。
Session.query(Table1).filter(not_(Table1.table2s.any())).delete(
    synchronize_session='fetch')

# 如果您不会在此会话中引用更多 Table1 对象，则可以忽略同步会话。
Session.query(Table1).filter(not_(Table1.table2s.any())).delete(
    synchronize_session=False)

http://docs.sqlalchemy.org/en/rel_0_8/orm/query.html#sqlalchemy.orm.query.Query.delete

答案2：

还有一种非常简单的方法可以实现，你可以使用一个带连接的 select，将这些行加载到内存中，然后逐个删除它们：

subquery = session.query(Table2.table1_id
                        ,sqlalchemy.func.count(Table2.table2_id).label('t1count')
                        ) \
                  .select_from(Table2) \
                  .group_by(Table2.table1_id) \
                  .subquery()
rows = session.query(Table1) \
              .select_from(Table1) \
              .outerjoin(subquery, Table1.table1_id == subquery.c.table1_id) \
              .filter(subquery.c.t1count == None) \
              .all()
for r in rows:
    session.delete(r)

这种方式不仅写起来很麻烦，而且性能也很差。首先，您必须将 table1 行加载到内存中。其次，如果你像我一样在 Table2 的类定义中有这样一行：

table1 = orm.relationship(Table1, backref=orm.backref('table2s'))

那么 SQLAlchemy 实际上会执行一个查询来将相关的 table2 行加载到内存中（即使没有这样的行）。更糟糕的是，因为你现在必须重复遍历列表（我尝试了直接传入列表，但不起作用），它一次只执行一行 table1。所以如果你要删除 10 行，那就是 21 个单独的查询（1 个用于初始选择，1 个用于每个关系拉取，1 个用于每个删除）。也许有办法减轻这种情况；我必须通读文档才能找到答案。所有这些都是为了那些我甚至不想放在数据库中、更不用说放在内存中的东西。我不会将此答案标记为「采纳的答案」。