SQLAlchemy:如何使用连接删除

2021-12-30 00:00:00 python sqlalchemy mysql sql-delete

我在用 SQLAlchemy 做这样的事情时遇到了麻烦:

I have trouble doing such thing bottom with SQLAlchemy:

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

作为这里的帖子:SQLAlchemy:使用创建删除查询MySQL 上的自联接

我很难在 SQLAlchemy 中使用 join 进行删除.

I've got it's hard to do delete in SQLAlchemy with join.

所以我现在是这样做的:

So I'm now doing like this:

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

但它只是让我很恼火:SQL 注入等.

But it just annoy me a lot like about: SQL Injection thing, etc..

有没有什么办法可以使用SQLAlchemy来解决这里的问题?谢谢！

Is there any way using SQLAlchemy to solve the problem here? Thanks!

推荐答案

SQLAlchemy 1.2 及更高版本 支持多表删除某些方言(在编写 Postgresql、MySQL 和 Microsoft SQL Server 时):

SQLAlchemy 1.2 and up support multi table deletes for some dialects (at the time of writing Postgresql, MySQL and 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 和声明式:

and the same using a Session and Declarative:

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

相关文章