Delete Duplicate Rows in Django DB

Question

I have a model where because of a code bug, there are duplicate rows. I now need to delete any duplicates from the database.

Every row should have a unique photo_id. Is there a simple way to remove them? Or do I need to do something like this:

rows = MyModel.objects.all()
for row in rows:
    try:
        MyModel.objects.get(photo_id=row.photo_id)
    except:
        row.delete()

Answer 1

This may be faster because it avoids the inner filter for each row in MyModel.

Since the ids are unique, if the models are sorted by them in increasing order, we can keep track of the last id we saw and as we walk over the rows if we see a model with the same id, it must be a duplicate, so we can delete it.

lastSeenId = float('-Inf')
rows = MyModel.objects.all().order_by('photo_id')

for row in rows:
  if row.photo_id == lastSeenId:
    row.delete() # We've seen this id in a previous row
  else: # New id found, save it and check future rows for duplicates.
    lastSeenId = row.photo_id 

Answer 2

Here is a fast solution:

from django.db import connection

query = "SELECT id FROM table_name GROUP BY unique_column HAVING COUNT(unique_column)>1"
cursor = connection.cursor()
cursor.execute(query)
ids_list = [item[0] for item in cursor.fetchall()]

now you can do:

Some_Model.objects.filter(id__in=ids_list).delete()

or if ids_list was too huge to be handled by your dbms

you can segment it to chunks that can be handled by it:

seg_length = 100
ids_lists = [ids_list[x:x+seg_length] for x in range(0,len(ids_list),seg_length)]
for ids_list in ids_lists:
    SomeModel.objects.filter(id__in=ids_list).delete()

Answer 3

from Django 1.11, you can use

MyModel.objects.annotate(
    count=Subquery(
        MyModel.objects.filter(
            photo_id=OuterRef('photo_id')
        ).values(
            'photo_id'
        ).annotate(
            count=Count('pk')
        ).values('count')
    )
).filter(
    count__gt=1
)

This query will give you the rows that don't have a unique photo_id, you can then keep one row of each photo id and delete the rest

Answer 4

I know it's an old question, but if you really need performance and your table has +500k rows, this might help:

last_used_id = 0
your_ids = list(MyModel.objects
                .order_by("your_id")
                .values_list('your_id',flat=True)
                .annotate(name_count=Count('your_id'))
                .filter(name_count__gt=1)[:1000] # for example
               )
duplicates = MyModel.objects
               .filter(your_id__in=your_ids)
               .order_by("your_id")

for my_model in duplicates:
  if my_model.your_id == last_used_id:
    my_model.delete()
  else:
    last_used_id = my_model.your_id

Answer 5

Instead of iterating the whole table you can just do

count = MyModel.objects.filter(photo_id='some_photo_id').count()
while count >=1:
    MyModel.objects.filter(photo_id='some_photo_id')[0].delete()
    count -= 1

Answer 6

A general and optimized method in case large number of objects need to be deleted -

qs = Model.objects.all()
key_set = set()
delete_ids_list = []
for object in qs:
    object_key = object.unique_key    # photo_id here
    if object_key in key_set:
        delete_ids_list.append(object.id)
    else:
        key_set.add(object_key)
Model.objects.filter(id__in=delete_ids_list).delete()

Answer 7

Combining some of the other answers along with a Window function allows you to annotate the row number. It's important to use a partition otherwise the row numbers will just be sequential. Iterate through the queryset and add any object where the row number is not the first occurrence to a list of ids that can be later deleted in one query.

from django.db.models import Count, F, IntegerField, OuterRef, Subquery, Window
from django.db.models.functions import RowNumber

from group.models import BuyingGroupTemplate

templates = (
    BuyingGroupTemplate.objects
    .order_by('group', 'product')
    .annotate(
        count=Subquery(
            BuyingGroupTemplate.objects
            .filter(
                group=OuterRef('group'),
                product=OuterRef('product'),
            )
            .values('group', 'product')
            .annotate(count=Count('*'))
            .values('count'),
            output_field=IntegerField(),
        ),
        row_number=Window(
            expression=RowNumber(),
            partition_by=[F('group'), F('product')],
            order_by=F('id').asc()
        ),
    )
    .filter(count__gt=1)
)
ids_to_delete = list()
for template in templates:
    if template.row_number != 1:
        ids_to_delete.append(template.id)

BuyingGroupTemplate.objects.filter(id__in=ids_to_delete).delete()

Answer 8

If you have lots of records, efficient way:

import itertools

pks_to_delete = []
rows = MyModel.objects.values_list('pk', 'photo_id').order_by('photo_id')
filter_func = lambda x: x[1]
for key, group in itertools.groupby(rows.iterator(), filter_func):
    pks_to_delete.extend((i[0] for i in list(group)[1:]))

MyModel.objects.filter(pk__in=pks_to_delete).delete()