SQLAlchemy: Create delete query using self-join on MySQL

Question

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 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.

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()

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')

Are you using the orm or sql layer? Can you include the snippet you tried? — Ian Wilson, Feb 23 '13 at 01:07
ORM, but happy to use whatever helps we achieve the query. I updated my question with failed my attempts so far. — Ole, Feb 23 '13 at 09:23
Related: https://stackoverflow.com/questions/25395756/sqlalchemy-how-to-delete-with-join — Ilja Everilä, Jan 23 '18 at 07:31

zzzeek · Answer 1 · 2013-02-24T18:36:22.760

SQLAlchemy currently supports UPDATE..FROM across Postgresql, MySQL and others, but we haven't as of yet tried to support DELETE..JOIN.

However, it does appear to work (almost?), as far as generating the SQL string:

class Path(Base):
    __tablename__ = "path"

    id = Column(Integer, primary_key=True)
    descendant = Column(Integer)
    ancestor = Column(Integer)

j = join(Path, p1, p1.ancestor == 5)
d = delete(j).where(Path.descendant == p1.descendant)
print d

prints:

DELETE FROM path JOIN path AS p1 ON p1.ancestor = :ancestor_1 
 WHERE path.descendant = p1.descendant

However, my MySQL database isn't accepting this, by default it renders INNER JOIN, which fails, but if I modify the MySQL compiler to not do this, still fails :

s.execute(d)

(ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the
right syntax to use near 'JOIN path AS p1 ON p1.ancestor = 5 WHERE
path.descendant = p1.descendant' at line 1") 'DELETE FROM path JOIN
path AS p1 ON p1.ancestor = %s WHERE path.descendant = p1.descendant'
(5,)

looks like your SQL verbatim (oh, except for 'delete paths FROM paths'? is that right?) ?

In any case, if the built in compiler is not doing it, your options are to use session.execute("some sql") or to build a custom construct with the compiler extension.

Hi, thanks for this. Not sure if this applies to all databases, but yes, MySQL requires "DELETE **paths** FROM paths". Almost there though! — Ole, Feb 25 '13 at 01:05
it doesnt apply to any other database. MySQL has many, many syntaxes that are entirely their own invention. — zzzeek, Feb 25 '13 at 08:16

score 2 · Answer 2 · answered Jan 18 '16 at 12:12

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 keyword just adds one or more expressions following the statement keyword, i.e. SELECT, INSERT, UPDATE, or DELETE.

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: Create delete query using self-join on MySQL

2 Answers2

Linked