Django ORM

Posted on | In , |

Retrieve all objects

  • MODEL.objects.all() — Return a QuerySet containing all MODEL objects
  • QuerySet is a iterable
1
2
3
>>> from heritagesites.models import HeritageSite
>>> len(HeritageSite.objects.all())
1092

Select from direct foreign-key relationships

  • MODEL.objects.select_related(*fields) — select direct foreign-key relationships
1
2
3
4
>>> from heritagesites.models import CountryArea
>>> ca = CountryArea.objects.select_related('dev_status').get(country_area_id=111)
>>> name = ca.country_area_name
>>> dev_status = ca.dev_status.dev_status_name

** Passing multiple arguments to select_related

1
2
3
4
5
>>> from heritagesites.models import CountryArea
>>> ca.CountryArea.objects.select_related('dev_status', 'location').get(country_area_id = 111)
>>> name = ca.country_area_name
>>> region = ca.location.region.region_name
>>>

Filter

  • AND:
    • objects.filter(<condition_1>, <condition_2)
    • queryset_1 & queryset_2
    • filter(Q(<condition_1) & Q(<condition_2))
  • __startwith(value) — select objects with field start with value
1
2
3
4
5
from heritagesites.models import CountryArea
ca = CountryArea.objects.filter(country_area_name__startwith='China')

# AND
ca = CountryArea.objects.filter(Q(country_area_name__startwith='B') & Q(dev_status = 1))
  • OR:
    • queryset_1 | queryset_2
    • filter(Q(<condition_1>) | Q(<condition_2))
  • NOT:
    • exclude(<condition>)
    • filter(~Q(<condition>))
1
2
3
4
5
6
# OR
ca = CountryArea.objects.filter(Q(country_area_name__startwith='A' | Q(country_area_name__startswith='Z'))

# NOT
ca = CountryArea.objects.filter(~Q(country_area_name__startswith='C'))
ca = CountryArea.objects.exclude(country_area_name__startswith='C')

Show specific fields

  • .values — show specific columns
    1
    2
    3
    # Show specified fields
    ca = CountryArea.objects.filter(country_area_name__startswith='China').values('country_area_name','iso_alpha3_code')
    # ( or use .only)

Subquery

Select all country area with SubRegion in ‘Eastern Asia’

1
2
sr = SubRegion.objects.filter(sub_region_name = 'Eastern Asia')
ca = CountryArea.objects.filter(sub_region_id = Subquery(sr.values('sub_region_id')))

Join

In Django ORM, select joined objects is used as __, i.e: dev_status__dev_status_name

  • select_related — used when one-one foreign keys
1
2
ca3 = CountryArea.objects.filter(dev_status__dev_status_name = 'Developed')
ca = CountryArea.objects.select_related('region','sub_region','intermediate_region','dev_status')

Join across multiple tables

1
2
3
4
5
6
7
8
ca = HeritageSite.objects\
  .select_related('heritage_site_category')\
  .filter(country_area__location__intermediate_region__intermediate_region_name='Southern Africa')

hs = HeritageSite.objects\
  .select_related('heritage_site_category')\
  .filter(country_area__country_area_name__startswith='China')\
  .values_list('country_area__country_area_name','site_name','heritage_site_category__category_name')

Sort Data and imposing limits

Sort the QuerySet by area_hectares(descending), followed by site_name(ascending)

1
2
3
>>> from heritagesites.models import HeritageSite
>>> hs = HeritageSite.objects.values('site_name', 'area_hectares').order_by('-area_hectares', 'site_name')[:5]
>>> hs.count()

Aggregating Data

  • Count
1
2
3
4
5
6
7
from django.db.models import Count
hs = HeritageSite.objects.all().count()
hs = HeritageSite.objects.annotate(site_count=Count('site_name'))
hs.count()

from django.db.models import Count
hs = HeritageSite.objects.all().values('heritage_site_category_id').annotate(count=Count('heritage_site_category_id'))
  • Group by and count

Return count of developed vs developing countries/areas in Sub-Saharan Africa

1
2
3
4
5
6
7
8
9
10
11
12
from django.db.models import Count
from django.db.models import  F
loc = Location.objects
  .values(
    sub_region_name = F('sub_region__sub_region_name'),
    dev_status = F('countryarea___dev_status__dev_status_name'))
  .annotate(count=Count('countryarea__dev_status__dev_status_name'))
  .filter(sub_region__sub_region_name='Sub-Saharan Africa')
  .order_by('countryarea__dev_status__dev_status_name')

for i in loc:
  print(i)
  • Max, Sum
1
2
3
from django.db.models import Max
hs = HeritageSite.objects.all().aggregate(max_hectares=Max('area_hectares'))
print(hs)

Annotation (Equivalent of GROUP BY in SQL)

Group regions by development status

1
2
3
4
5
6
7
8
9
10
11
12
>>> loc = Location.objects\
  .select_related('region')\
  .values(region_name=F('region__region_name'), dev_status_name=F('country_area__dev_status_name'))\
  .filter(region__region_name='Asian')\
  .annotate(count=Count('dev_status_name'))\
  .order_by('dev_status_name')

>>> for l in loc:
...    print(l)
...
{'region_name': 'Asia', 'dev_status_name': 'Developed', 'count': 3}
{'region_name': 'Asia', 'dev_status_name': 'Developing', 'count': 47}