Sql to Django queryset cheatsheet

Sql QuerySet Notes
SELECT count(*) FROM fruit Fruits.objects.count() table count
SELECT count(*) FROM fruit WHERE name=’Orange’ Fruits.objects.filter(name__exact=’Orange’).count() count with filter
SELECT * FROM fruit WHERE color is NULL Fruits.objects.get(color__isnull=True) filter by null
SELECT * FROM fruit WHERE color is NOT NULL Fruits.objects.get(color__isnull=False) filter by null
SELECT * FROM fruit WHERE name=’Apple’ Fruits.objects.get(name__exact=’Apple’) case sensitive
SELECT * FROM fruit WHERE lower(name)=lower(‘Apple’) Fruits.objects.get(name__iexact=’Apple’) case in-sensitive
SELECT * FROM fruit WHERE name LIKE ‘App%’ Fruits.objects.filter(name__startswith=’App’) case sensitive LIKE
SELECT * FROM fruit WHERE upper(name) LIKE ‘APP%’ Fruits.objects.filter(name__istartswith=’app’) case in-sensitive LIKE
SELECT * FROM fruit WHERE name LIKE ‘%pp%’ Fruits.objects.filter(name__contains=’pp’) case sensitive LIKE
SELECT * FROM fruit WHERE lower(name) LIKE ‘%pp%’ Fruits.objects.filter(name__icontains=’pp’) case in-sensitive LIKE
SELECT * FROM fruit WHERE name LIKE ‘%ple’ Fruits.objects.filter(name__endswith=’ple’) case sensitive LIKE
SELECT * FROM fruit WHERE lower(name) LIKE ‘%ple’ Fruits.objects.filter(name__iendswith=’ple’) case in-sensitive LIKE
SELECT * FROM fruit WHERE weight > 2 Fruits.objects.get(weight__gt=2) greater than
SELECT * FROM fruit WHERE weight >= 2 Fruits.objects.get(weight__gte=2) greater than or equal-to
SELECT * FROM fruit WHERE weight < 2 Fruits.objects.get(weight__lt=2) less than
SELECT * FROM fruit WHERE weight <= 2 Fruits.objects.get(weight__lte=2) less than or equal-to
Sql to Django queryset cheatsheet

How to avoid SELECT sql generated by Django model’s save method?

Django framework is designed for novice users in mind and so they try to keep things simple at the cost of performance. Once such is the Django model’s save method. Every save fires first a SELECT statement to verify if a record exists with the provided “id” and then does update if exists else insert. As a good framework django does provide the flexibility to control this. You can pass the force_update=True to save method if you know for sure that the id exists already and the call to django model save avoids the SELECT sql before insert.
Same goes for insert too. You set the force_insert=True in that case.

How to avoid SELECT sql generated by Django model’s save method?

Django leaks memory in debug mode.

If you find your program is leaking memory when using Django model then it is most probably because you are in running in debug mode. Django keeps track of the sql statement generated by the model save in debug mode which tends to be the reason behind leak. It is not a problem unless you are in tight memory scenario which are normally enforced by hosting providers like webfaction. I learnt of this in a hard way but Django doc does mention this. See here

Django leaks memory in debug mode.