SQLAlchemy：如何通过连接删除

Question

我在用SQLAlchemy做一些事情时遇到了麻烦，特别是下面这个：

DELETE a FROM a INNER JOIN b ON b.`aId` = a.`Id` WHERE `b`.`xxx` = ?;

就像这里的帖子提到的：SQLAlchemy：使用自连接在MySQL中创建删除查询

我发现用SQLAlchemy进行删除操作时，结合连接查询真的很难。

所以我现在是这样做的：

session.execute('DELETE a FROM a INNER JOIN b ON b.`aId` = a.`Id` WHERE `b`.`xxx` = %d;'%xxx)

但这让我很烦，因为涉及到SQL注入等问题。

有没有什么办法可以用SQLAlchemy来解决这个问题呢？谢谢！

Answer 1

SQLAlchemy 1.2及以上版本支持对某些数据库类型（比如Postgresql、MySQL和Microsoft SQL Server）进行多表删除操作：

In [18]: a = table('a', column('x'))

In [19]: b = table('b', column('x'))

In [20]: c = table('c', column('x'), column('y'))

In [21]: a.delete().\
    ...:     where(a.c.x == b.c.x).\
    ...:     where(b.c.x == c.c.x).\
    ...:     where(c.c.y == 1)
Out[21]: <sqlalchemy.sql.dml.Delete object at 0x7f3577d89160>

In [22]: print(_.compile(dialect=mysql.dialect()))
DELETE FROM a USING a, b, c WHERE a.x = b.x AND b.x = c.x AND c.y = %s

使用Session和声明式方式也可以做到这一点：

In [2]: class Foo(Base):
   ...:     __tablename__ = 'foo'
   ...:     id = Column(Integer, primary_key=True)

In [3]: class Bar(Base):
   ...:     __tablename__ = 'bar'
   ...:     id = Column(Integer, primary_key=True)
   ...:     foo_id = Column(ForeignKey(Foo.id))
   ...:     

In [4]: class Baz(Base):
   ...:     __tablename__ = 'baz'
   ...:     id = Column(Integer, primary_key=True)
   ...:     bar_id = Column(ForeignKey(Bar.id))
   ...:     val = Column(Integer)
   ...:     

In [5]: session.query(Foo).\
   ...:     filter(Foo.id == Bar.foo_id,
   ...:            Bar.id == Baz.bar_id,
   ...:            Baz.val == 1).\
   ...:     delete(synchronize_session=False)
   ...:            

这样会产生以下结果：

DELETE FROM foo USING foo, bar, baz
WHERE foo.id = bar.foo_id AND bar.id = baz.bar_id AND baz.val = %(val_1)s

Answer 2

这是我做到的方式：

map_ids = session.query(Table1.map_id). \
    filter(Table1.xxxx == 'xxx_value')

q = session.query(Table2). \
    filter(Table2.map_id.in_(map_ids.subquery()))

q.delete(synchronize_session=False)

这里的关键是，你需要先找出那些要删除的记录的ID，创建一个子查询。然后你可以用'in_'操作符来选择要删除的记录。'synchoronize_session=False'这个设置会让查询执行时不更新会话，这样可以提高性能。