python - SQLAlchemy: How to Delete with join

Question

I have trouble doing such thing bottom with SQLAlchemy:

DELETE a FROM a INNER JOIN b ON b.`aId` = a.`Id` WHERE `b`.`xxx` = ?;

As the post here: SQLAlchemy: Create delete query using self-join on MySQL

I've got it's hard to do delete in SQLAlchemy with join.

So I'm now doing like this:

session.execute('DELETE a FROM a INNER JOIN b ON b.`aId` = a.`Id` WHERE `b`.`xxx` = %d;'%xxx)

But it just annoy me a lot like about: SQL Injection thing, etc..

Is there any way using SQLAlchemy to solve the problem here? Thanks!

Answer 1

Here is the way I did it:

map_ids = session.query(Table1.map_id). 
    filter(Table1.xxxx == 'xxx_value')

q = session.query(Table2). 
    filter(Table2.map_id.in_(map_ids.subquery()))

q.delete(synchronize_session=False)

The key here are you create subquery by finding ids that has to be deleted. Then you use 'in_' operator to select records to be deleted. 'synchoronize_session=False' will execute query without updating session. This gives the best performance boost.