Django SQL cheatsheet

SELECT

filtering

# filter students by ID
Student.objects.get(pk=1)

# get array of students
Student.objects.filter(enrolled=True)

# filter by pk
Student.objects.filter(school__pk=1) # school_id = 1

# is null
Student.objects.filter(photo__isnull=True)

order by, distinct

# get distinct last_name
Student.objects.order_by("last_name").distinct()

# order by desc/multiple fields
Student.objects.order_by("-pk", "last_name")

get result as an array

>>> Student.objects.values_list("last_name", flat=True)
["Smith", "Johnson", "Williams"]

• values return a dict
• values_list returns a tuple, but with flat=True option returns an array

see also: https://stackoverflow.com/a/41085356/9644490

count

>>> Student.objects.filter(school__pk=2).count()
120

exists

Student.objects.filter(photo__isnull=True).exists()

CREATE

s = Student(last_name="Smith", first_name="John", school_id=1)
s.save()

UPDATE

s = Student.objects.get(pk=1)
s.last_name="Wilson"
s.save()

DELETE

s = Student.objects.get(pk=1)
s.delete()

all, none

Student.objects.none()
Student.objects.all()