CODEWITHSUNDEEP
pythonmysqlsqlsqlalchemy
Ole
Ole

Reputation: 11

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

Upvotes: 1

Views: 3279

Answers (2)

romsuhov
romsuhov

Reputation: 173

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.

Upvotes: 2

zzzeek
zzzeek

Reputation: 75137

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.

Upvotes: 2

Related Questions