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)
kill_me.delete()
class Migration(migrations.Migration):
dependencies = [
('cheatle', '0001_initial'),
]
operations = [
migrations.RunPython(remove_duplicate_rows)
]
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.
- Could this be made more efficient?
- 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)