Sql to Django queryset cheatsheet

by , under django

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