What is the Sqlalchemy syntax for having ORDER BY and LIMIT on DELETE

[Moshe C.]

Hi,
I am using Sqlalchemy 0.6.5 .

How do I generate the following statement usin Sqlalchemy expressions
(not ORM).

DELETE FROM table ORDER BY timestamp LIMIT 10;

TIA

[Michael Bayer]

That's a MySQL specific syntax you might be better off not using, perhaps you could say "delete from table where id in (select id from table order by timestamp limit 10)".

To get the exact statement, it's probably easiest just to emit the string SQL. If you wanted the sqlalchemy.sql.delete() construct to do it you'd need to subclass Delete, add order_by() and limit() to it, and augment its compilation as described in http://www.sqlalchemy.org/docs/core/compiler.html

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>

[Moshe C.]

What is the syntax for the "where id in (select ... ) " ?

On Jun 13, 5:17 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> That's a MySQL specific syntax you might be better off not using, perhaps you could say "delete from table where id in (select id from table order by timestamp limit 10)".
>

> To get the exact statement, it's probably easiest just to emit the string SQL.  If you wanted the sqlalchemy.sql.delete() construct to do it you'd need to subclass Delete, add order_by() and limit() to it, and augment its compilation as described inhttp://www.sqlalchemy.org/docs/core/compiler.html

[Rami Chowdhury]

On Mon, Jun 13, 2011 at 15:42, Moshe C. <mos...@gmail.com> wrote:
> What is the syntax for the "where id in (select ... ) " ?

Does the sqlalchemy.sql.where() function and the in_() operator fit your needs?

Can I also ask: why do you want to ORDER BY on a DELETE?

--
Rami Chowdhury
"Never assume malice when stupidity will suffice." -- Hanlon's Razor
+44-7581-430-517 / +1-408-597-7068 / +88-0189-245544

[Moshe C.]

Thanks, I'll check it out.

I need the LIMIT in order to delete a lot of old rows in a loop
without locking concurrent transactions out for too long (and getting
the "ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting
transaction") error. Can make the timeout longer but that will not let
other processes get a chance at querying.

The ORDER BY is just for aesthetics, so that I always delete the
oldest ones first and not create holes, but I can live without it.

> > For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en.