django database delete specific number of entries

Question

How to delete specific number of entries from the database? I did something like this

EntriesToDelete=Statusmessages.objects.filter(time__lt=date)[:30000]
EntriesToDelete.delete()

But I get an error which says: AssertionError. Cannot use 'limit' or 'offset' with delete.

How can I specify the number of entries to be deleted.

You can accelerate this process by using: _raw_delete. Here is related answer: https://stackoverflow.com/a/36935536/11280799 . Please read limitations and drawbacks of this method carefully. — Aman, Sep 01 '22 at 12:39

score 43 · Accepted Answer · answered Nov 06 '12 at 12:05

43

You could do it like this:

Statusmessages.objects.filter(pk__in=Statusmessages.objects.filter(time__lt=date).values_list('pk')[:30000]).delete()

answered Nov 06 '12 at 12:05

jpic

32,891
5
112
113

1

I used something similar and looped over it to maintain prevent the memory usage of the DB from going to wild when deleting millions of rows – odedbd Feb 08 '14 at 12:49
Nice. You probably also need 'flat=True' in the values_list call. – Quentin Stafford-Fraser Oct 15 '18 at 10:02
4

MariaDB/Mysql both don't like this - you get ```django.db.utils.NotSupportedError: (1235, "This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'")```. – Danny Staple Jul 02 '19 at 14:22

score 0 · Answer 2 · answered Sep 01 '22 at 12:16

use this:

Statusmessages.objects.filter(pk__in=Statusmessages.objects.filter(time__lt=date).values_list('pk', flat=True)[:30000]).delete()

use flat=True, if not then is a tuple and raise exception:

{NotSupportedError}(1235, "This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'")

score 0 · Answer 3 · answered Aug 30 '23 at 14:14

If you get the following error:

django.db.utils.NotSupportedError: (1235, "This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'")

Force evaluate the list of ids with list(). Here's an example:

sessions_to_delete = LoginSession.objects.filter(user=self.userProfile)[3:]
pks_of_sessions_to_delete = list(sessions_to_delete.values_list("pk", flat=True))
LoginSession.objects.filter(pk__in=pks_of_sessions_to_delete).delete()

django database delete specific number of entries

3 Answers3

Linked