SQLAlchemy delete limit rows

Question

How can I delete last 10 rows from my query?

I have query:

query = session.query(MyTableClass) \ 
.filter(MyTableClass.country=='USA') \ 
.order_by(MyTableClass.id.asc()) \ 
.limit(10)

How can I delete 10 rows only?

I tried: query.delete() - error: Can't call Query.update() or Query.delete() when limit() has been called

session.delete(query) - error: Class 'sqlalchemy.orm.query.Query' is not mapped

Answer 1

You can't delete a query that has a limit() on it, but you can delete one with a filter(). So you:

• make your query into a subquery with the limit(10) on it. Just put a .subquery() on the end
• make a second query that filters for your subquery and you can delete that.

If this seems silly to you, you're not alone. I believe this was reported as a bug a few years ago and never got fixed. It has to do with the object type limit() returns.