In Django, you first write model classes and then generate the migrations.

from django.db import models

class Author(models.Model):
    name = models.CharField(max_length=200)

class Question(models.Model):
    author = models.ForeignKey(Author, on_delete=models.PROTECT)
    text = models.CharField(max_length=200)
    publish_date = models.DateTimeField('date published')

class Choice(models.Model):
    author = models.ForeignKey(Author, on_delete=models.PROTECT)
    question = models.ForeignKey(Question, on_delete=models.CASCADE)
    text = models.CharField(max_length=200)
    votes = models.IntegerField(default=0)

python manage.py makemigrations app_name

Creating Objects

from app_name.models import Author, Question, Choice

q = Question(text='What is the meaning of life?')
q.author = Author.objects.create(name="John")
q.choices.add(Choice.objects.create(text="to eat"))
q.choices.add(Choice.objects.create(text="to code"))
q.save()

# you can also just modify specific fields
q.save(update_fields=('text', 'author',))

Retrieving Objects

MyModel.objects in Django is the Manager of the MyModel model.

To fetch objects of a certain model, you use the Manager to build a QuerySet. QuerySet represents a lazy collection of models in your database.

QuerySet  = SELECT
Filter    = WHERE, LIMIT

get can be used to return a single object.

# these two effectively the same
Blog.objects.get(pk=1)       # might DoesNotExist or MultipleObjectsReturned
Blog.objects.filter(pk=1)[0] # might IndexError

filter and exclude will always return another QuerySet. Even if there is just one match.

Blog.objects.all()                    # all blog posts in the database
Blog.objects.all()[:5]                # first 5 blog posts
Blog.objects.all()[5:10]              # blog posts between 6 and 10
Blog.objects.filter()                 # apply a lookup filter to the set
Blog.objects.exclude()                # don't include the objects that match
Blog.filter().exclude().filter()      # all of these can be chained
Blog.filter(body__startswith="What")  # all blog posts where body starts with "What"

Each QuerySet has internal caching. It is populated the first time the set is evaluated.

queryset = Blog.objects.all()
print([p.headline for p in queryset]) # evaluate the set
print([p.pub_date for p in queryset]) # re-use from cache

# but limiting with an array slice or an index will not populate the cache
queryset = Blog.objects.all()
print(queryset[5]) # queries the database
print(queryset[5]) # queries the database again

# but you can force it to cache by evaluating the whole set
# with bool(queryset), entry in queryset or list(queryset)
queryset = Blog.objects.all()
[blog for blog in queryset]    # queries the database
print(queryset[5])             # uses cache
print(queryset[5])             # uses cache

Deleting Objects

blog.delete()

Blog.objects.filter(publish_date__year=2005).delete()

Django Query Expressions

Lookup API is Django way of defining WHERE clauses of database queries. They usually do relationship lookups, comparisons and transformations.

Entry.objects.filter(publish_date__lte='2006-01-01')
# SELECT * FROM ... WHERE publish_date <= '2006-01-01';

Blog.objects.get(id__exact=14)  # Explicit form
Blog.objects.get(id=14)         # __exact is implied
# SELECT ... WHERE id = 14;

Blog.objects.get(name__iexact="beatles blog")

Entry.objects.get(headline__contains='Lennon')
# SELECT ... WHERE headline LIKE '%Lennon%';

Entry.objects.get(headline__icontains='Lemon')
# SELECT ... WHERE headline ILIKE '%Lemon%';

Entry.objects.filter(blog__name='Beatles Blog')
Blog.objects.filter(entry__headline__contains='Lennon')
Blog.objects.filter(entry__authors__name='Lennon')

# returns objects that have null author or null author name
Blog.objects.filter(entry__authors__name__isnull=True)
# returns objects that have an author but have null author name
Blog.objects.filter(entry__authors__isnull=False, entry__authors__name__isnull=True)

# same entry satisfying both conditions
Blog.objects.filter(entry__headline__contains='Lennon', entry__publish_date__year=2008)
# separate entries can satisfy the conditions
Blog.objects.filter(entry__headline__contains='Lennon').filter(entry__publish_date__year=2008)

F expressions

Instances of F() act as a reference to a model field within a query.

from datetime import timedelta
from django.db.models import F

# the same author name than the blog name
Entry.objects.filter(authors__name=F('blog__name'))

# has double amount of pingbacks compared to comments
Entry.objects.filter(n_comments__gt=F('n_pingbacks') * 2)

# were modified more than 3 days after publishing
Entry.objects.filter(modify_date__gt=F('publish_date') + timedelta(days=3))

Q objects

Instances of Q() are standalone WHERE-clauses.

from django.db.models import Q

Q(text__startswith='What')
# WHERE text LIKE 'What%'

Q(text__startswith='What') | Q(text__startswith='Who')
# WHERE text LIKE 'What%' OR question LIKE 'Who%'

Q(text__startswith='what') | ~Q(publish_date__year=2005)
# WHERE text LIKE 'What%' OR NOT pub_date.year = 2005

# Q objects must come before keyword arguments
Blog.objects.get(
    Q(publish_date=date(2005, 5, 2)) | Q(publish_date=date(2005, 5, 6)),
    text__startswith='Who',
)

Custom Lookups

Example custom not-equal lookup; e.g. .filter(name__ne='Jack') that translates to name <> 'Jack' in SQL.

from django.db.models import Lookup

class NotEqual(Lookup):
    lookup_name = 'ne'

    def as_sql(self, compiler, connection):
        lhs, lhs_params = self.process_lhs(compiler, connection)
        rhs, rhs_params = self.process_rhs(compiler, connection)
        params = lhs_params + rhs_params
        return '%s <> %s' % (lhs, rhs), params

# in Django AppConfig:ready()
from django.db.models.fields import Field
Field.register_lookup(NotEqual)
# now e.g. "name__ne='Jack'"  =>  "name <> 'Jack'"

Example custom absolute value transform; e.g. .filter(price__abs=27) that translates to ABS(price).

from django.db.models import Transform

class AbsoluteValue(Transform):
    lookup_name = 'abs'
    function = 'ABS'

# to change the return type:
#    @property
#    def output_field(self):
#        return FloatField()

# in Django AppConfig:ready()
from django.db.models import IntegerField
IntegerField.register_lookup(AbsoluteValue)
# now e.g. "price__abs__lt=27"  =>  "ABS(price) < 27"

Example of more efficient absolute value lookup; e.g. .filter(price__abs__lt=27) that translates to price < 27 AND price > -27.

from django.db.models import Lookup

class AbsoluteValueLessThan(Lookup):
    lookup_name = 'lt'

    def as_sql(self, compiler, connection):
        lhs, lhs_params = compiler.compile(self.lhs.lhs)
        rhs, rhs_params = self.process_rhs(compiler, connection)
        params = lhs_params + rhs_params + lhs_params + rhs_params
        return '%s < %s AND %s > -%s' % (lhs, rhs, lhs, rhs), params
# in Django AppConfig:ready()
from app.transforms import AbsoluteValue
from app.lookups import AbsoluteValueLessThan
AbsoluteValue.register_lookup(AbsoluteValueLessThan)

Example of bilateral transform; e.g. .filter(name__upper='doe') that translates to UPPER(name) = UPPER('doe')

from django.db.models import Transform

class UpperCase(Transform):
    lookup_name = 'upper'
    function = 'UPPER'
    bilateral = True

# in Django AppConfig:ready()
from django.db.models import CharField, TextField
CharField.register_lookup(UpperCase)
TextField.register_lookup(UpperCase)

You can customize which lookup or transform to use per-field with get_lookup and get_transform.

class CoordinatesField(Field):
    def get_lookup(self, lookup_name):
        if lookup_name.startswith('x'):
            try:
                dimension = int(lookup_name[1:])
            except ValueError:
                pass
            else:
                return get_coordinate_lookup(dimension)  # Lookup subclass
        return super().get_lookup(lookup_name)

# .filter(coords__x7=4)

All filtering sequences must end with a lookup. The final lookup is 'exact' if nothing else has been specified.

.filter(myfield__mylookup)
    => myfield.get_lookup('mylookup')

.filter(myfield__mytransform__mylookup)
    => myfield.get_transform('mytransform')
    => mytransform.get_lookup('mylookup')

.filter(myfield__mytransform)
    => myfield.get_lookup('mytransform')
    => mytransform.get_lookup('exact')     # because 'exact' is the default

Sources

• Django Documentation
• Django REST Framework - Filtering
• django-filter - Integration with DRF