Re: [sqlalchemy] Issue DELETE statement with LIMIT

[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.

On Fri, Sep 22, 2023, at 7:16 AM, 'Grennith' via sqlalchemy wrote:
> 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_keywords=yes=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 
> ```
> 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
> 
> 
> 
> 
> -- 
> 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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/f13a8ca6-3e52-4287-a6a4-52b5b4672470n%40googlegroups.com
>  
> .

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/ae24d467-9750-496a-a3b7-1da85afc70f2%40app.fastmail.com.

Re: [sqlalchemy] Issue DELETE statement with LIMIT

[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. PrinceMushroom Systems International Pty. Ltd.On 22 Sep 2023, at 9:16 pm, 'Grennith' via sqlalchemy  wrote: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_keywords=yes=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 ```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



-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/f13a8ca6-3e52-4287-a6a4-52b5b4672470n%40googlegroups.com.




-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/978052A5-E996-4839-8DA2-50009A6384AE%40mushroomsys.com.

[sqlalchemy] Issue DELETE statement with LIMIT

['Grennith' via 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():
https://docs.sqlalchemy.org/en/20/search.html?q=with_dialect_options_keywords=yes=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 
```
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


-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/f13a8ca6-3e52-4287-a6a4-52b5b4672470n%40googlegroups.com.