Issue DELETE statement with LIMIT

[Grennith]

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():
https://docs.sqlalchemy.org/en/20/search.html?q=with_dialect_options&check_keywords=yes&area=default#
Which points to https://docs.sqlalchemy.org/en/20/core/dml.html#sqlalchemy.sql.expression.UpdateBase.with_dialect_options 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 (https://github.com/sqlalchemy/sqlalchemy/blob/rel_1_4/lib/sqlalchemy/sql/dml.py#L345) already and also in 2.0 (https://github.com/sqlalchemy/sqlalchemy/blob/main/lib/sqlalchemy/sql/dml.py#L438).

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 https://github.com/sqlalchemy/sqlalchemy/blob/main/lib/sqlalchemy/dialects/mysql/base.py#L2454 not listing sql.Delete explicitly. UpdateBase apparently cannot be referenced either given the import (guessing as it's not imported explicitly in https://github.com/sqlalchemy/sqlalchemy/blob/main/lib/sqlalchemy/sql/__init__.py 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]

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 <sqlal...@googlegroups.com> wrote:

Hi everyone,

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
http://www.sqlalchemy.org/
 
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve 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 sqlalchemy+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/f13a8ca6-3e52-4287-a6a4-52b5b4672470n%40googlegroups.com.

[Mike Bayer]

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.