Issue DELETE statement with LIMIT

Skip to first unread message


Sep 22, 2023, 7:16:42 AM9/22/23
to sqlalchemy
Hi everyone,
I'd like to issue a LIMIT for a DELETE statement.
By default, this is not possible as far as I can see it. The function limit() is available for SELECT in general however.
Searching through documentation, I found a reference to with_dialect_option():
Which points to claiming the function to be available in UpdateBase (although the documentation off given the claimed method is not callable like documented). This was the case in 1.4 ( already and also in 2.0 (

However, trying to call it as per documentation results in an exception being raised:
sqlalchemy.exc.ArgumentError: Argument 'mysql_limit' is not accepted by dialect 'mysql' on behalf of <class 'sqlalchemy.sql.dml.Delete'>
This is caused by not listing sql.Delete explicitly. UpdateBase apparently cannot be referenced either given the import (guessing as it's not imported explicitly in or not referenced another way?).
However, by adding Delete just like Update will have the following line run fine without an error - but not adding the LIMIT either. My best guess right now would be due to the lack of limit clause handling?
stmt = stmt.with_dialect_options(mysql_limit=limit, mariadb_limit=limit)
where `limit` simply is an integer.

Any hints or help is appreciated. I can also raise a ticket on Github :)

Best regards

Warwick Prince

Sep 22, 2023, 7:20:35 AM9/22/23
Hi.  Can you do a sub query with the ones you want to delete selected (with limit) and then delete with an IN clause on the sub query items?

Warwick A. Prince
Mushroom Systems International Pty. Ltd.

On 22 Sep 2023, at 9:16 pm, 'Grennith' via sqlalchemy <> wrote:

Hi everyone,
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See for a full description.
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to
To view this discussion on the web visit

Mike Bayer

Sep 22, 2023, 9:12:19 AM9/22/23
to noreply-spamdigest via sqlalchemy
I thought we have a github for DELETE..LIMIT but we dont.   This would be a construct specific to the MySQL dialect:  from sqlalchemy.dialects.mysql import delete , where it would include order_by() and limit() params.    We don't have internal resources to carry this through so we'd rely upon high quality pull requests with tests, once the issue is created.

for now I would suggest using text() for the case that an application needs this extremely unusual construct once in awhile.
Reply all
Reply to author
0 new messages