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. On Jun 14, 2:32 pm, Rami Chowdhury <[email protected]> wrote: > On Mon, Jun 13, 2011 at 15:42, Moshe C. <[email protected]> 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? > > > > > > > > > > > > > On Jun 13, 5:17 pm, Michael Bayer <[email protected]> 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 > > >> On Jun 13, 2011, at 10:10 AM, Moshe C. wrote: > > >> > 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 > > >> > -- > >> > You received this message because you are subscribed to the Google > >> > Groups "sqlalchemy" group. > >> > To post to this group, send email to [email protected]. > >> > To unsubscribe from this group, send email to > >> > [email protected]. > >> > For more options, visit this group > >> > athttp://groups.google.com/group/sqlalchemy?hl=en. > > > -- > > You received this message because you are subscribed to the Google Groups > > "sqlalchemy" group. > > To post to this group, send email to [email protected]. > > To unsubscribe from this group, send email to > > [email protected]. > > For more options, visit this group > > athttp://groups.google.com/group/sqlalchemy?hl=en. > > -- > Rami Chowdhury > "Never assume malice when stupidity will suffice." -- Hanlon's Razor > +44-7581-430-517 / +1-408-597-7068 / +88-0189-245544 -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
