Background: I am learning web dev and decided on the Django framework as an excuse to learn Python at the same time. (Background is stats with lots of R and C++).

I wrote a Django app to assist with a popular word guessing game. There is a very simple model:

from django.db import models

class FiveLetterWord(models.Model):
    Word = models.CharField(max_length=5,unique=True)

    def __str__(self):
        return self.Word

and I populated this using a fixture. This was generated by loading a large number of five letter words from various sources, deduplicating, etc. (I am now aware that the game has its list available; I used a bunch of free dictionary files instead).

Alls well. Apart from the fact that my deduplication turned out to be case sensitive. That is, it had Aaron and aaron in it. Some few words were in triplicate e.g. AARON as well. I only wanted one of these. (Sorry anyone called Aaron reading this). The views and template use case insensitive logic so which one does not matter.

I wrote the following data migration to identify duplicate entries and remove them.

from django.db import migrations
from django.db.models.functions import Lower

def remove_duplicate_rows(apps, scheme_editor):
    FiveLetterWord = apps.get_model('my_app', 'FiveLetterWord')

    l_qset = FiveLetterWord.objects.annotate(lWord=Lower('Word'))

    distinct_word_list = list(l_qset.values_list('lWord',flat=True).distinct().order_by('lWord'))

    distinct_word_dict = { i : l_qset.filter(lWord__exact = i).count() for i in distinct_word_list }

    dupe_dict = { k:v for k,v in distinct_word_dict.items() if v > 1 }

    dupe_pk = { k : list(l_qset.filter(lWord__exact = k).values_list('id',flat = True)[1:])
                for k in dupe_dict.keys() }

    pk_kill = [item for sublist in dupe_pk.values() for item in sublist]

    for pk in pk_kill:
        kill_me = l_qset.get(pk=pk)

class Migration(migrations.Migration):

    dependencies = [
        ('cheatle', '0001_initial'),

    operations = [

This worked, in that only Aaron was left. But it took of the order of tens of seconds to run (c. 12k records left at the end). From my experience writing SQL I would have expected this to run on the order of a second at most. I mean it's fine as a one off thing for tens of thousands of records, but I feel it won't scale to millions of rows if I ever screw up like that.

  1. Could this be made more efficient?
  2. Could this be made more efficient using only idiomatic ORM functionality? (i.e., not just typing out a SQL string and passing it to the DB)


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.