How to delete with order_by and limit in core?

[vitaly numenta]

Hi, I am using sqlalchemy core with the mysql dialect. Mysql supports DELETE with ORDER BY and LIMIT:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name

    [PARTITION (partition_name,...)]

    [WHERE where_condition]

    [ORDER BY ...]

    [LIMIT row_count]

However, I can't seem to figure out how to do this via sqlalchemy core. I have a really large table from which I need to garbage-collect old rows now and then. When I try to do it all in one operation, I end up getting "Lock wait timeout exceeded; try restarting transaction", so I wanted to take advantage of order_by and limit to do this incrementally, but mysqlalchemy won't let me.

Please help.

Thank you,

Vitaly

[vitaly numenta]

I also tried to do this via subquery, but MySQL apparently doesn't support subqueries with LIMIT:

NotSupportedError: (NotSupportedError) (1235, "This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'") 'DELETE FROM twitter_tweets WHERE twitter_tweets.uid IN (SELECT twitter_tweets.uid \nFROM twitter_tweets \nWHERE twitter_tweets.created_at < date_sub(UTC_TIMESTAMP, INTERVAL 90 DAY) ORDER BY twitter_tweets.created_at ASC \n LIMIT %s)' (2,)

[Mike Bayer]

On 11/06/2015 03:04 PM, vitaly numenta wrote:
> Hi, I am using sqlalchemy core with the mysql dialect. Mysql supports
> DELETE with ORDER BY and LIMIT:
>
> DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
> [PARTITION (partition_name,...)]
> [WHERE where_condition]
> [ORDER BY ...]
> [LIMIT row_count]
>
>
> However, I can't seem to figure out how to do this via sqlalchemy core.

emit the exact SQL you want as a string:

conn.execute("DELETE .... WHERE ... ORDER BY ... LIMIT")

the query is MySQL specific in any case so you wouldn't need to worry
about portability.

I seem to recall a ticket asking for this feature but at the moment I
can only find one referring to DELETE FROM .. JOIN for MySQL.

> I have a really large table from which I need to garbage-collect old
> rows now and then. When I try to do it all in one operation, I end up
> getting "Lock wait timeout exceeded; try restarting transaction", so I
> wanted to take advantage of order_by and limit to do this incrementally,
> but mysqlalchemy won't let me.
>
> Please help.
>
> Thank you,
> Vitaly
>

> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy-alembic" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy-alem...@googlegroups.com
> <mailto:sqlalchemy-alem...@googlegroups.com>.
> For more options, visit https://groups.google.com/d/optout.

[vitaly numenta]

Thank you Michael. I was hoping to do it the native sqlalchemy way, because my function takes an sqlalchemy-based predicate that needs to be used in this and another query, so I was hoping to be able to do things natively using pure sqlalchemy constructs in order to share this predicate. Thanks, this confirms that what I wanted to do cannot be done at this time.

[Mike Bayer]

we just had nearly the identical problem here where we hit the bizarre
fact that LIMIT won't work in the correlated subquery, and we went with
a temp table.