SQLAlchemy:在MySQL上使用自连接创建删除查询 [英] SQLAlchemy: Create delete query using self-join on MySQL
问题描述
我试图在SQLAlchemy + MySQL中复制以下查询,但没有运气...
I'm trying to replicate the following query in SQLAlchemy + MySQL without luck...
delete paths from paths
join paths as p1 on p1.ancestor = <ID>
where paths.descendant = p1.descendant;
SQLAlchemy似乎忽略了我放入删除查询中的所有联接.我也尝试使用子查询而不是联接,但这在MySQL中是不允许的(无法从要删除的表中选择).任何帮助将不胜感激.
SQLAlchemy seems be to ignoring any joins that I put into the delete query. I also tried using a subquery instead of a join, but that's not allowed in MySQL (can't select from same table as you want to delete from). Any help would be much appreciated.
更新: 是的,我正在尝试使用ORM层.这是我尝试过的查询:
Update: Yes, I'm trying to use the ORM layer. Here's the queries that I've tried:
p1 = aliased(Path, name="p1")
db.session.query(Path).join(
p1, p1.ancestor==<ID>
)
.filter(
Path.descendant==p1.Path.descendant
).delete()
还有子查询变体,但这在MySQL上不起作用,因此对我没有用:
And the subquery variant, but this doesn't work on MySQL, so it's of no use to me:
q = db.session.query(Path.descendant).filter(Path.ancestor==<ID>).subquery()
db.session.query(Path).filter(Path.descendant.in_(q)).delete(synchronize_session='fetch')
推荐答案
您可以使用prefixes
关键字参数:
You can use prefixes
keyword argument:
j = join(table1, table2, table1.c.key==table2.c.key)
stmt = delete(j, prefixes=[table1_name])
session.execute(stmt)
prefixes
关键字仅在语句关键字之后添加一个或多个表达式,即SELECT,INSERT,UPDATE或DELETE.
prefixes
keyword just adds one or more expressions following the statement keyword, i.e. SELECT, INSERT, UPDATE, or DELETE.
在这种情况下,delete(j)
语句生成表达式:"DELETE FROM table1 INNER JOIN table2 ON table1.key=table2.key"
.
当我们添加prefixes
时,参数表达式变为:"DELETE table1 FROM table1 INNER JOIN table2 ON table1.key=table2.key"
,这是正确的MySQL查询.
In this case, delete(j)
statement generates expression: "DELETE FROM table1 INNER JOIN table2 ON table1.key=table2.key"
.
When we add prefixes
argument expression becomes: "DELETE table1 FROM table1 INNER JOIN table2 ON table1.key=table2.key"
, that is correct MySQL query.
这篇关于SQLAlchemy:在MySQL上使用自连接创建删除查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!