bulk deleting rows based on query result

[nivya]

I'm trying to run the following sql using sqlalchemy ORM -

"delete from feed_items where feed_id=27 order by published_on asc
limit 10;"

I tried -

session.query(FeedItem).filter_by(feed_id=27).order_by
(FeedItem.published_on.asc()).limit(10).delete()

But this is deleting all rows instead of limiting the deletion to 10
rows. This was unexpected.

I also tried -

connection.execute(feed_items_table.delete().where
(feed_items_table.c.feed_id == 27).order_by
(feed_items_table.c.published_on.asc()).limit(10))

This throws an error saying order_by is not an attribute of Delete.

Is engine.execute(""delete from feed_items where feed_id=27 order by
published_on asc limit 10") the only option?

Thanks.

[Michael Bayer]

nivya wrote:
>
> I'm trying to run the following sql using sqlalchemy ORM -
>
> "delete from feed_items where feed_id=27 order by published_on asc
> limit 10;"
>
> I tried -
>
> session.query(FeedItem).filter_by(feed_id=27).order_by
> (FeedItem.published_on.asc()).limit(10).delete()
>
> But this is deleting all rows instead of limiting the deletion to 10
> rows. This was unexpected.

you can't use LIMIT with delete(). delete() only works with WHERE
criterion. I'm a little surprised its not checking for that so I've
added ticket #1487.

your best bet on this is to say

q =
session.query(FeedItem.id).filter_by(feed_id=27).order_by...).limit(10).subquery()
session.query(FeedItem).filter(FeedItem.id.in_(q)).delete()

> connection.execute(feed_items_table.delete().where
> (feed_items_table.c.feed_id == 27).order_by
> (feed_items_table.c.published_on.asc()).limit(10))
>
> This throws an error saying order_by is not an attribute of Delete.

same answer applies.

>
> Is engine.execute(""delete from feed_items where feed_id=27 order by
> published_on asc limit 10") the only option?

this is a MySQL-only syntax that is a syntactical replacement for the
subquery approach outlined above and it would require strings. If you're
ambitious you can try making your own enhanced delete() construct by
subclassing Delete() and using ext.compiler, described at
http://www.sqlalchemy.org/docs/05/reference/ext/compiler.html .

[thatsanice...@mac.com]

Hello,

I have several objects from a relation that I'd like to delete. To me,
this would be a natural syntax:

session.delete(office.users)

to delete all of the "user" objects. I get this error when doing this:

raise exc.UnmappedInstanceError(instance)
sqlalchemy.orm.exc.UnmappedInstanceError: Class
'sqlalchemy.orm.collections.InstrumentedList' is not mapped

Took me a while to figure out that delete() didn't work like that. I
use this instead:

[session.delete(x) for x in office.users]

Can session.delete() be made to work as above (or at the very least
produce a better error message)?

Cheers,
Demitri

[Mike Conley]

The delete method of query supports bulk deletes.
In your case it might be something like

session.query(Users).filter(User.officeid==office.id).delete()

Any query can be used; there are probably more elegant ways to take advantage of the relation 'users' also.

Make sure you read the documentation for Query.delete() to understand the impact of the 'synchronize_session' keyword


--
Mike Conley

[thatsanice...@mac.com]

On 19 Aug 2009, at 18:32, Mike Conley wrote:

> The delete method of query supports bulk deletes.
> In your case it might be something like
>
> session.query(Users).filter(User.officeid==office.id).delete()
>
> Any query can be used; there are probably more elegant ways to take
> advantage of the relation 'users' also.
>
> Make sure you read the documentation for Query.delete() to
> understand the impact of the 'synchronize_session' keyword

Thanks for the tip - I wasn't aware of query's delete method. Still,
it strikes me as a bit inefficient to look up objects that I already
have at hand. (Even if the engine has to look them up due to lazy
loading, *I* have them at hand.) Ah well.

Demitri

[Michael Bayer]

why not query.filter(MyObject.id.in_(x.id for x in
myobjects)).delete() ? then no lookup.