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
– Ian Wilson, Commented Feb 23, 2013 at 1:07
ORM, but happy to use whatever helps we achieve the query. I updated my question with failed my attempts so far. — Ole
– Ole, Commented Feb 23, 2013 at 9:23

zzzeek · Accepted Answer · 2013-02-24 18:36:22Z

2

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.

edited Feb 24, 2013 at 18:36

answered Feb 24, 2013 at 18:29

zzzeek

75.8k24 gold badges204 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ole Over a year ago

Hi, thanks for this. Not sure if this applies to all databases, but yes, MySQL requires "DELETE paths FROM paths". Almost there though!

zzzeek Over a year ago

it doesnt apply to any other database. MySQL has many, many syntaxes that are entirely their own invention.

romsuhov · Accepted Answer · 2016-01-18 12:12:13Z

2

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.

answered Jan 18, 2016 at 12:12

romsuhov

1732 silver badges8 bronze badges

Collectives™ on Stack Overflow

SQLAlchemy: Create delete query using self-join on MySQL

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related