SQLAlchemy:如何使用联接删除 [英] SQLAlchemy: How to Delete with join
本文介绍了SQLAlchemy:如何使用联接删除的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在使用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上自连接
我很难通过join在SQLAlchemy中进行删除.
I've got it's hard to do delete in SQLAlchemy with join.
所以我现在是这样的:
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!
推荐答案
某些方言的多表删除(在编写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
这篇关于SQLAlchemy:如何使用联接删除的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文