Django ORM and SQL

1. Basic Concepts

ORM: Object Relational Mapping. Its job is to map between a relational database and objects. No complex SQL statements are needed — operating on data is as simple as operating on objects.
QuerySet: the list of objects for a given model. A QuerySet lets you read data from the database and filter, sort, and otherwise manipulate it.
Manager: django.db.models.manager.Manager, Django’s class for table-level operations. Every model has a default Manager instance called objects.

2. QuerySet

Django ORM uses three classes: Model, Manager, and QuerySet. Model is the data model. Manager defines table-level methods; when you need customization, you take models.Manager as the parent class, define your own Manager class, and add table-level methods. The queryset discussed mainly here is the QuerySet instance that some of the Manager class’s methods return. A QuerySet is an iterable structure containing one or more elements, each of which is a Model instance, and its methods are also table-level methods.

values_list returns results in tuple form

1
2
3
In [1]: bs = Basket.objects.values_list('weight','create_time')
In [2]: bs
Out[2]: [(2.1, datetime.datetime(2017, 8, 15, 20, 14, 9))]

values returns results in dictionary form

1
2
3
In [1]: b = Basket.objects.values('weight','create_time')
In [2]: b
Out[2]: [{'create_time': datetime.datetime(2017, 8, 15, 20, 14, 9), 'weight': 2.1}]

extra implements aliases, conditions, and sorting

Inside extra you can implement aliases, conditions, sorting, and so on. The latter two can be done with filter and exclude, and sorting with order_by. Here we mainly look at the alias feature:
For example, Basket has a weight that needs to be renamed to w.

1
2
3
In [1]: b =Basket.objects.all().extra(select={'w':'weight'})
Out[1]: b[0].w
2.1

annotate aggregation — count, sum, average

Taking sum as an example:

1
2
3
In [1]:from django.db.models import Sum
In [2]:Basket.objects.values('weight').annotate(sum_weight=Sum('weight'))
Out[2]:[{'sum_weight': 2.1, 'weight': 2.1}]

select_related optimizes one-to-one and many-to-one queries

A single query fetches the foreign-key data.

1
2
3
In [1]:b = Basket.objects.all().select_related('fruit')
In [2]:b[0].fruit.name
Out[2]: u'apple'

prefetch_related optimizes one-to-many and many-to-many queries

prefetch_related is used for one-to-many and many-to-many situations, where select_related cannot be used, because a single current record has several pieces of related content. prefetch_related works by running one extra SQL statement and then using Python to join the contents of the two SQL queries together.

defer excludes fields you do not need

In complex cases, a table may have fields with a great deal of content, and fetching them and converting them into Python objects consumes a lot of resources. defer can exclude part of the fields.

only selects only the fields you need

The opposite of defer, only is used to fetch just the fields you need.

Custom aggregation

django.db.models contains Count, Avg, Sum, and so on. But some are missing, such as GROUP_CONCAT. You can define your own GroupConcat class to implement the relevant functionality.

Cache

When you iterate over a queryset, all matching records are fetched from the database and then converted into Django models. These models are kept in the queryset’s built-in cache, so if you iterate over this queryset again, there is no need to re-run the same query.

1
2
3
4
5
6
7
b_set = Basket.objects.all()
# The query is executed and cached.
for b in b_set :
    print(b.create_time)
# The cache is used for subsequent iteration.
for b in b_set :
    print(b.weight)

3. SQL Statements for Common Django Operations

First, create two models:

1
2
3
4
5
6
7
8
class Fruit(models.Model):
    name = models.CharField(u'名称', default="", max_length=255)
    price = models.FloatField(u"单价", default=0)

class Basket(models.Model):
    create_time = models.DateTimeField(u'新增时间', auto_now_add=True)
    fruit = models.ForeignKey(Fruit)
    weight = models.FloatField(default=0.0)

Django provides a Shell debugging environment. Enter the command:

1
python manage.py shell

and you enter the Console, where you can operate on the Django DB from the command line.

1
2
3
4
5
6
7
8
9
In [1]：from home_application.models import Basket
In [2]：Basket.objects.all()
Out[2]: [<Basket: Basket object>]
In [3]：from django.db import connection
In [4]：connection.queries
Out[4]:
[{u'sql': u'SET SQL_AUTO_IS_NULL = 0', u'time': u'0.001'},
 {u'sql': u'SELECT `home_application_basket`.`id`, `home_application_basket`.`create_time`, `home_application_basket`.`fruit_id`, `home_application_basket`.`weight` FROM `home_application_basket` LIMIT 21',
  u'time': u'0.002'}]

You can use connection.queries to see the historical SQL statements that have been executed. To show only the SQL for the current operation, here we call db.reset_queries() after each SQL inspection to clear connection.queries.

There is another way to get the SQL statement for the current ORM operation: print Basket.objects.all().query. Printing the query attribute of the queryset directly from the console outputs:

1
SELECT `home_application_basket`.`id`, `home_application_basket`.`create_time`, `home_application_basket`.`fruit_id`, `home_application_basket`.`weight` FROM `home_application_basket`

Next, let’s look at the SQL generated by common Django ORM operations:

Batch query - filter

1
2
3
4
5
6
7
In [1]：from django import db
In [2]：db.reset_queries()
In [3]：Basket.objects.filter(weight=2.1)
In [4]：connection.queries
Out[4]:
[{u'sql': u'SELECT `home_application_basket`.`id`, `home_application_basket`.`create_time`, `home_application_basket`.`fruit_id`, `home_application_basket`.`weight` FROM `home_application_basket` WHERE `home_application_basket`.`weight` = 2.1 LIMIT 21',
  u'time': u'0.001'}]

Query a single object - get

1
2
3
4
5
6
In [1]：db.reset_queries()
In [2]：Basket.objects.get(weight=2.1)
In [3]：connection.queries
Out[3]:
[{u'sql': u'SELECT `home_application_basket`.`id`, `home_application_basket`.`create_time`, `home_application_basket`.`fruit_id`, `home_application_basket`.`weight` FROM `home_application_basket` WHERE `home_application_basket`.`weight` = 2.1',
  u'time': u'0.000'}]

Range query - gt, lt

In Django you append __gt or __lt to the field name to implement a range query.

1
2
3
4
5
6
In [1]：db.reset_queries()
In [2]：Basket.objects.filter(create_time__gte='1999-01-01')
In [3]：connection.queries
Out[3]:
[{u'sql': u"SELECT `home_application_basket`.`id`, `home_application_basket`.`create_time`, `home_application_basket`.`fruit_id`, `home_application_basket`.`weight` FROM `home_application_basket` WHERE `home_application_basket`.`create_time` >= '1999-01-01 00:00:00' LIMIT 21",
  u'time': u'0.001'}]

1
2
3
4
5
6
In [1]：db.reset_queries()
In [2]：Basket.objects.exclude(create_time__gte='1999-01-01')
In [3]：connection.queries
Out[3]:
[{u'sql': u"SELECT `home_application_basket`.`id`, `home_application_basket`.`create_time`, `home_application_basket`.`fruit_id`, `home_application_basket`.`weight` FROM `home_application_basket` WHERE NOT (`home_application_basket`.`create_time` >= '1999-01-01 00:00:00') LIMIT 21",
  u'time': u'0.000'}]

Combined query through a foreign key - __

1
2
3
4
5
6
7
In [1]：db.reset_queries()
In [2]：Basket.objects.filter(fruit__name="apple")
In [3]：connection.queries
Out[3]:
[{u'sql': u"SELECT `home_application_basket`.`id`, `home_application_basket`.`create_time`, `home_application_basket`.`fruit_id`, `home_application_basket`.`weight` FROM `home_application_basket` INNER JOIN `home_application_fruit` ON ( `home_application_basket`.`fruit_id` = `home_application_fruit`.`id`
 ) WHERE `home_application_fruit`.`name` = 'apple' LIMIT 21",
  u'time': u'0.001'}]

Multi-condition OR query - Q

1
2
3
4
5
6
7
In [1]：db.reset_queries()
In [2]：from django.db.models import Q
In [3]：Basket.objects.filter(Q(weight=2.1) | Q(weight=2.0))
In [4]：connection.queries
Out[4]:
[{u'sql': u'SELECT `home_application_basket`.`id`, `home_application_basket`.`create_time`, `home_application_basket`.`fruit_id`, `home_application_basket`.`weight` FROM `home_application_basket` WHERE (`home_application_basket`.`weight` = 2.1 OR `home_application_basket`.`weight` = 2) LIMIT 21',
  u'time': u'0.001'}]

in query - in

1
2
3
4
5
6
In [1]：db.reset_queries()
In [2]：Basket.objects.filter(weight__in=[2.1, 2.0])
In [3]：connection.queries
Out[3]:
[{u'sql': u'SELECT `home_application_basket`.`id`, `home_application_basket`.`create_time`, `home_application_basket`.`fruit_id`, `home_application_basket`.`weight` FROM `home_application_basket` WHERE `home_application_basket`.`weight` IN (2.1, 2) LIMIT 21',
  u'time': u'0.001'}]

like query - like

1
2
3
4
5
6
In [1]：db.reset_queries()
In [2]：Basket.objects.filter(weight__contains='2')
In [3]：connection.queries
Out[3]:
[{u'sql': u"SELECT `home_application_basket`.`id`, `home_application_basket`.`create_time`, `home_application_basket`.`fruit_id`, `home_application_basket`.`weight` FROM `home_application_basket` WHERE `home_application_basket`.`weight` LIKE BINARY '%2%' LIMIT 21",
  u'time': u'0.001'}]

Count the number - count

1
2
3
4
5
6
In [1]：db.reset_queries()
In [2]：Basket.objects.filter(weight=2.1).count()
In [3]：connection.queries
Out[3]:
[{u'sql': u"SELECT COUNT('*') AS `__count` FROM `home_application_basket` WHERE `home_application_basket`.`weight` = 2.1",
  u'time': u'0.002'}]

Sort the results - order_by

1
2
3
4
5
6
In [1]：db.reset_queries()
In [2]：Basket.objects.all().order_by('create_time')
In [3]：connection.queries
Out[3]:
[{u'sql': u'SELECT `home_application_basket`.`id`, `home_application_basket`.`create_time`, `home_application_basket`.`fruit_id`, `home_application_basket`.`weight` FROM `home_application_basket` ORDER BY `home_application_basket`.`create_time` ASC LIMIT 21',
  u'time': u'0.001'}]

1
2
3
4
5
6
In [1]：db.reset_queries()
In [2]：Basket.objects.all().order_by('create_time', '-weight')
In [3]：connection.queries
Out[3]:
[{u'sql': u'SELECT `home_application_basket`.`id`, `home_application_basket`.`create_time`, `home_application_basket`.`fruit_id`, `home_application_basket`.`weight` FROM `home_application_basket` ORDER BY `home_application_basket`.`create_time` ASC, `home_application_basket`.`weight` DESC LIMIT 21',
  u'time': u'0.001'}]

Modify data - save

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
In [1]：db.reset_queries()
In [2]：b =  Basket.objects.get(pk=1)
In [3]：b.weight = 2.0
In [4]：b.save()
In [5]：connection.queries
Out[5]:
[{u'sql': u'SELECT `home_application_basket`.`id`, `home_application_basket`.`create_time`, `home_application_basket`.`fruit_id`, `home_application_basket`.`weight` FROM `home_application_basket` WHERE `home_application_basket`.`id` = 1',
  u'time': u'0.001'},
 {u'sql': u"UPDATE `home_application_basket` SET `create_time` = '2017-08-08 18:00:59', `fruit_id` = 1, `weight` = 2 WHERE `home_application_basket`.`id` = 1",
  u'time': u'0.003'}]

Batch update - update

1
2
3
4
5
6
In [1]：db.reset_queries()
In [2]：Basket.objects.filter(weight=2.0).update(weight=2.1)
In [3]：connection.queries
Out[3]:
[{u'sql': u'UPDATE `home_application_basket` SET `weight` = 2.1 WHERE `home_application_basket`.`weight` = 2',
  u'time': u'0.003'}]

Batch delete - delete

1
2
3
4
5
6
In [1]：db.reset_queries()
In [2]：Basket.objects.filter(weight=2.1).delete()
In [3]：connection.queries
Out[3]:
[{u'sql': u'DELETE FROM `home_application_basket` WHERE `home_application_basket`.`weight` = 2.1',
  u'time': u'0.003'}]

Filtering the objects to be processed in advance does not reduce SQL queries

As you can see, if the queryset is not used, no SQL query is generated. SQL only starts querying when you operate on the queryset. Saving the queryset is about making use of the built-in Cache.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
In [1]：db.reset_queries()
In [2]：all = Basket.objects.all()
In [3]：connection.queries
Out [3]: []
In [4]: all
In [5]：connection.queries
Out [5]:
[{u'sql': u'SELECT `home_application_basket`.`id`, `home_application_basket`.`create_time`, `home_application_basket`.`fruit_id`, `home_application_basket`.`weight` FROM `home_application_basket` LIMIT 21',
  u'time': u'0.000'}]
In [6]: all.filter(weight=2.1)
Out [6]:
[{u'sql': u'SELECT `home_application_basket`.`id`, `home_application_basket`.`create_time`, `home_application_basket`.`fruit_id`, `home_application_basket`.`weight` FROM `home_application_basket` LIMIT 21',
  u'time': u'0.000'},
 {u'sql': u'SELECT `home_application_basket`.`id`, `home_application_basket`.`create_time`, `home_application_basket`.`fruit_id`, `home_application_basket`.`weight` FROM `home_application_basket` WHERE `home_application_basket`.`weight` = 2.1 LIMIT 21',
  u'time': u'0.000'}]

4. SQL Execution Performance

Add indexes sensibly

Apart from the ID field, no index is created for other fields by default. By setting the db_index attribute you can add an index yourself, which gives a significant performance improvement for filter(), exclude(), and order_by() operations, for example: models.DateField(db_index=True).

Take advantage of the QuerySet Lazy behavior

The following seven cases query the database and generate a cache, so there is no need to reconnect to the database to query again

Iteration, i.e. performing a for loop over the Queryset.
slicing, e.g. Entry.objects.all()[:5], which fetches the first five objects in the queryset, equivalent to
LIMIT 5 in SQL
picling/caching
repr/str
len (Note: if all you want to know is the length of this queryset result, the most efficient way is still to call the count() method at the database level, that is, COUNT() in SQL.)
list()
bool()
For example:

1
2
3
>>> queryset = Entry.objects.all()
>>> print([p.headline for p in queryset]) # Evaluate the query set.
>>> print([p.pub_date for p in queryset]) # Re-use the cache from the evaluation.

Fetch all the data at once, and do not retrieve data you do not need

Use the select_related(), prefetch_related(), values_list(), and values() methods

If the queryset you fetch is used only once, you can use iterator() to prevent it from occupying too much memory
bulk (batch) insert, update, and delete data
Use count() instead of len(queryset), and exists() instead of if queryset