sql to django queryset cheatsheet by pythonnewbie

gistfile1.py

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