1. Home
2. Django 1.11 Tutorial
3. Django ORM Basics Part 1

Django ORM Basics Part 1

Last updated on July 27, 2020

Having learned the art of creating models, let's now shift our attention on how to insert and access the data from the database. Django ORM provides an elegant and powerful way to interact with the database. ORM stands for Object Relational Mapper. It is just a fancy word describing how to access the data stored in the database in Object Oriented way instead of executing SQL.

Start the Django shell using the shell command.

1
2
3
4
5

$ ./manage.py shell
Python 3.5.2 (default, Nov 23 2017, 16:37:01) 
[GCC 5.4.0 20160609] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>>

To work with the database inside the shell, first, we have to import necessary models. In this chapter, we will be working on models stored in the djangobin app, so let's start by importing all the models from the djangobin app.

1
2
3

>>>
>>> from djangobin.models import *
>>>

At this point, the tables corresponding to these models are empty.

Let's create an Author object.

1
2
3

>>>
>>> a = Author(name='tom', email='tom@email.com', active=True)
>>>

Try printing the variable a and you will get the following output:

1
2
3
4
5
6
7

>>>
>>> a
<Author: tom : tom@email.com>
>>>
>>> print(a)
tom : tom@email.com
>>>

Note that the string representation of the author is coming from the __str__() method in the Author model. If we had not defined the __str__() method, we would get the output as follows:

<Author: Author object>

You can access the attributes of an object using the dot (.) operator.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

>>>
>>> a.name
'tom'
>>>
>>> a.email
'tom@email.com'
>>>
>>> a.active
True
>>>

Note that at the time of creating Author object we didn't provide any value to created_on and last_logged_in field because these fields have auto_now_add and auto_now set to True. As a result, Django will automatically provide the current date and time when you save the object to the database. However, If we hadn't set auto_now_add and auto_now parameters then we would have to pass values to created_on and last_logged_in fields as follows:

1
2
3
4
5
6
7

>>>
>>> from django.utils import timezone
>>>
>>> r = Author(name="root", email="root@mail.com", active=True, 
... created_on=timezone.now, last_logged_in=timezone.now)
>>>
>>>

Note that here I am using django.utils.timezone module instead of Python's builtin datetime module. This is because django.utils.timezone module generates a timezone aware datetime.datetime objects by default (due to USE_TZ = True in settings.py file).

Another important thing to notice in the preceding snippet is that we are just passing the name of the function (timezone.now) to created_on and last_logged_in arguments instead of actually calling it. This allows Django to call timezone.now() at the time of inserting a new record.

At this point, the object pointed to by variable a exist only inside the Django shell. To save the object to the database call the save() method.

1
2
3

>>>
>>> a.save()
>>>

Recall that every model we define inherits from models.Model class, this is where the save() method comes from.

To view this newly added object open djangobin_author table inside Navicat.

Similarly, models.Model class also provides a delete() method to delete an object from the database.

1
2
3
4

>>>
>>> a.delete()
(1, {'djangobin.Author': 1})
>>>

This code removes author tom from the database. However, it still exists inside the shell.

1
2
3
4

>>>
>>> a
<Author: tom : tom@email.com>
>>>

Let's save it again by calling the save() method.

When you save an object the primary key is assigned automatically. You can refer to the primary key using id or pk attribute.

1
2
3
4
5
6

>>>
>>> a.id
2
>>> a.pk
2
>>>

If you want to alter the attributes of an object just assign new the values and call the save() method again.

1
2
3
4
5
6
7

>>>
>>> a.name = 'tommy'
>>> a.email = 'tommy@example.com'
>>>
>>> a
<Author: tommy : tommy@example.com>
>>>

These changes are not yet saved to the database. To do so, call the save() method.

1
2
3

>>>
>>> a.save()
>>>

Database Access through Managers #

Django by default adds a manager called objects to every model class. The objects manager helps us to query the database in all sorts of ways.

To access the objects manager type model class followed by the (.) dot operator then the objects manager itself. For example:

1
2
3
4
5
6
7

>>>
>>> Author.objects
<django.db.models.manager.Manager object at 0x7f84da83a1d0>
>>>
>>> type(Author.objects)
<class 'django.db.models.manager.Manager'>
>>>

As you can see, the objects is just an instance of django.db.models.manager.Manager class. The objects manager provides a whole range of methods which allows us to interact with the database easily.

Let's discuss some important methods of the objects manager.

The create() method #

The create() method allows us to create and commit object to the database in one go, instead of separately calling the save() method. For example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

>>>
>>>
>>> a2 = Author.objects.create(name='jerry', email='jerry@mail.com')
>>>
>>> a2
<Author: jerry : jerry@mail.com>
>>>
>>> a2.pk
3
>>>
>>>

The bulk_create() method #

The bulk_create() method allows us to create and commit multiple objects. It accepts a list of objects. For example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

>>>
>>> 
>>> Author.objects.bulk_create([
...     Author(name='spike', email='spike@mail.com'),
...     Author(name='tyke', email='tyke@mail.com'),
...     Author(name='droopy', email='droopy@mail.com'),
... ])
[<Author: spike : spike@mail.com>, <Author: tyke : tyke@mail.com>, <Author: droopy : droopy@mail.com>]
>>> 
>>>

At this point, the djangobin_author table should look like this:

The all() method #

The all() method fetches all the records from the table. For example:

1
2
3
4

>>>
>>> Author.objects.all()
<QuerySet [<Author: tommy : tommy@example.com>, <Author: jerry : jerry@mail.com>, <Author: spike : spike@mail.com>, <Author: tyke : tyke@mail.com>, <Author: droopy : droopy@mail.com>]>
>>>

This query returns all the records from the djangobin_author table.

The all() method returns a QuerySet object. A QuerySet object looks a lot like a list, but it is not an actual list, however, in some ways, it behaves just like a list. For example, you can access individual members in a QuerySet objects using an index number.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

>>>
>>> l = Author.objects.all()
>>>
>>> type(l)
<class 'django.db.models.query.QuerySet'>
>>> 
>>> l
<QuerySet [<Author: tommy : tommy@example.com>, <Author: jerry : jerry@mail.com>, <Author: spike : spike@mail.com>, <Author: tyke : tyke@mail.com>, <Author: droopy : droopy@mail.com>]>
>>> 
>>>
>>> l[0]
<Author: tommy : tommy@example.com>
>>>
>>> l[1]
<Author: jerry : jerry@mail.com>
>>>
>>> l[2]
<Author: spike : spike@mail.com>
>>>

Although, variable l points to an object of type QuerySet but l[0], l[1], l[2] and so on, points to an object of type Author.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

>>>
>>> type(l[0])
<class 'djangobin.models.Author'>
>>>
>>> type(l[1])
<class 'djangobin.models.Author'>
>>>
>>> type(l[3])
<class 'djangobin.models.Author'>
>>>

A QuerySet object is of special importance in Django. We use it to filter, order and slice the results. As we will see, there are many other methods that return a QuerySet object.

The QuerySet object is iterable just like a list. You can use a for loop to iterate through all of the objects in it.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

>>>
>>> l = Author.objects.all()
>>>
>>> for a in l:
...    print("Author: {0}".format(a.name))
...
Author: tommy
Author: jerry
Author: spike
Author: tyke
Author: droopy
>>>

The count() method #

The count() method returns the total number of records returned by the query.

1
2
3
4

>>>
>>> Author.objects.count()
5
>>>

This query isn't qualified in any way that's why it returns the count of all records in the table. The preceding query is functionally equivalent to following:

Author.objects.all().count()

Filtering records using the filter() method #

Most of the time you would only want to work with a subset of data. In Django, that's the job of filter() method. It accepts field names as keyword arguments and returns a QuerySet containing objects that match the given criteria.

1
2
3
4
5
6
7

>>>
>>> Author.objects.filter(name='tommy')
<QuerySet [<Author: tommy : tommy@example.com>]>
>>>
>>> Author.objects.filter(name='johnny')
<QuerySet []>
>>>

The query Author.objects.filter(name='tommy') translates to SQL roughly as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT
    "djangobin_author"."id",
    "djangobin_author"."name",
    "djangobin_author"."email",
    "djangobin_author"."active",
    "djangobin_author"."created_on",
    "djangobin_author"."last_logged_in"
FROM
    "djangobin_author"
WHERE
    "djangobin_author"."name" = tommy

As database has only one record where the name is 'tommy', the QuerySet object contains only a single Author object. If we had two records where the name is 'tommy' then filter() would have returned a QuerySet containing two Author objects.

Similarly, Author.objects.filter(name='johnny') translates to SQL roughly as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT
    "djangobin_author"."id",
    "djangobin_author"."name",
    "djangobin_author"."email",
    "djangobin_author"."active",
    "djangobin_author"."created_on",
    "djangobin_author"."last_logged_in"
FROM
    "djangobin_author"
WHERE
    "djangobin_author"."name" = johnny

As there are no records where the name is 'johnny' an empty QuerySet is returned.

We can also directly print the raw SQL Django uses to query the database using the query attribute of the QuerySet object.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

>>>
>>> print(Author.objects.filter(name='tommy').query)
SELECT
    "djangobin_author"."id",
    "djangobin_author"."name",
    "djangobin_author"."email",
    "djangobin_author"."active",
    "djangobin_author"."created_on",
    "djangobin_author"."last_logged_in"
FROM
    "djangobin_author"
WHERE
    "djangobin_author"."name" = tommy
>>>

Matching performed using keyword arguments are case-sensitive.

1
2
3
4
5
6
7

>>>
>>> Author.objects.filter(email='jerry@mail.com')
<QuerySet [<Author: jerry : jerry@mail.com>]>
>>>
>>> Author.objects.filter(email='JERRY@mail.com')
<QuerySet []>
>>>

The last query returns an empty QuerySet because there are no records where email is "JERRY@mail.com", however, there is a record where the email is "jerry@mail.com".

You can also pass multiple keyword arguments to the filter() method. When we do so, keyword arguments are ANDed together.

1
2
3
4

>>>
>>> Author.objects.filter(name='spike', email='spike@mail.com')
<QuerySet [<Author: spike : spike@mail.com>]>
>>>

This query translates to SQL roughly as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

SELECT
    "djangobin_author"."id",
    "djangobin_author"."name",
    "djangobin_author"."email",
    "djangobin_author"."active",
    "djangobin_author"."created_on",
    "djangobin_author"."last_logged_in"
FROM
    "djangobin_author"
WHERE
    (
        "djangobin_author"."name" = spike
        AND "djangobin_author"."email" = spike@mail.com
    )

Excluding records with the exclude() method #

The exclude() method is just the opposite of the filter() method. It returns a QuerySet only containing the objects that do not match the given parameters.

1
2
3
4
5

>>> 
>>> Author.objects.exclude(name='spike', email='spike@mail.com')
<QuerySet [<Author: tommy : tommy@example.com>, <Author: jerry : jerry@mail.com>, <Author: tyke : tyke@mail.com>, <Author: droopy : droopy@mail.com>]>
>>> 
>>>

This query translates to SQL roughly as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

>>> print(Author.objects.exclude(name='spike', email='spike@mail.com').query)
SELECT
    "djangobin_author"."id",
    "djangobin_author"."name",
    "djangobin_author"."email",
    "djangobin_author"."active",
    "djangobin_author"."created_on",
    "djangobin_author"."last_logged_in"
FROM
    "djangobin_author"
WHERE
    NOT (
        "djangobin_author"."email" = spike@mail.com
        AND "djangobin_author"."name" = spike
    )

Get Object by Primary Key #

As already discussed, Django automatically adds a primary key field called id to the every model class. You can use id field or its alias pk to access an object by its primary key. For example:

1
2
3
4
5
6
7

>>>
>>> Author.objects.filter(id=2)
<QuerySet [<Author: tommy : tommy@example.com>]>
>>>
>>> Author.objects.filter(pk=2)
<QuerySet [<Author: tommy : tommy@example.com>]>
>>>

Field Lookups #

In addition to passing field names as keyword arguments. You can also use something called lookups to filter to result further. A lookup is composed of a model field followed by two underscores (__), followed by the lookup name. Lookups are also passed as keyword arguments.

contains lookup #

The contains lookup performs a case-sensitive containment test. For example:

1
2
3
4
5

>>>
>>> Author.objects.filter(name__contains="ke")
<QuerySet [<Author: spike : spike@mail.com>, <Author: tyke : tyke@mail.com>]>
>>>
>>>

In this query, the contains lookup finds all the objects where the name field contains the string "ke" at the beginning, or end, or somewhere in between.

The query Author.objects.filter(name__contains="ke") translates to SQL roughly as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT
    "djangobin_author"."id",
    "djangobin_author"."name",
    "djangobin_author"."email",
    "djangobin_author"."active",
    "djangobin_author"."created_on",
    "djangobin_author"."last_logged_in"
FROM
    "djangobin_author"
WHERE
    "djangobin_author"."name" LIKE '%ke%'

Matching performed by contains lookup is case-sensitive.

icontains lookup #

Works just like contains but perform a case-insensitive match.

1
2
3
4

>>>
>>> Author.objects.filter(name__icontains="KE")
<QuerySet [<Author: spike : spike@mail.com>, <Author: tyke : tyke@mail.com>]>
>>>

SQLite database doesn't support case-sensitive LIKE statements. As a result, contains and icontains lookups will return the same result.

startswith lookup #

The startswith lookup performs a case-sensitive start-with. For example:

1
2
3
4

>>>
>>> Author.objects.filter(name__startswith="t")
<QuerySet [<Author: tommy : tommy@email.com>, <Author: tyke : tyke@mail.com>]>
>>>

In this query, the startswith lookup finds all the records whose name field start with the string "t".

The SQL equivalent of the above query is:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT
    "djangobin_author"."id",
    "djangobin_author"."name",
    "djangobin_author"."email",
    "djangobin_author"."active",
    "djangobin_author"."created_on",
    "djangobin_author"."last_logged_in"
FROM
    "djangobin_author"
WHERE
    "djangobin_author"."name" LIKE 't%'

There also exists a complementary lookup called endswith.

endswith lookup #

It performs a case case-sensitive end-with. For example:

1
2
3
4
5
6

>>>
>>> Author.objects.filter(email__endswith="com")
<QuerySet [<Author: tom : tom@email.com>, <Author: jerry : jerry@mail.com>, <Author: spike : spike@mail.com>, <Author: tyke : tyke@mail.com>, <Author: droopy :
droopy@mail.com>]>
>>>
>>>

Here the endswith lookup finds all the records whose email ends with "com".

The SQL equivalent of the above query is:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT
    "djangobin_author"."id",
    "djangobin_author"."name",
    "djangobin_author"."email",
    "djangobin_author"."active",
    "djangobin_author"."created_on",
    "djangobin_author"."last_logged_in"
FROM
    "djangobin_author"
WHERE
    "djangobin_author"."email" LIKE "%com"

The startswith and endswith are both case-sensitive. Their case-insensitive equivalents are istartswith and iendswith.

gt lookup #

Greater than:

1
2
3
4

>>>
>>> Author.objects.filter(id__gt=3)
<QuerySet [<Author: spike : spike@mail.com>, <Author: tyke : tyke@mail.com>, <Author: droopy : droopy@mail.com>]>
>>>

In this query, the gt lookup finds all the records whose id or primary key (pk) is greater than 3.

The SQL equivalent of the above query is:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT
    "djangobin_author"."id",
    "djangobin_author"."name",
    "djangobin_author"."email",
    "djangobin_author"."active",
    "djangobin_author"."created_on",
    "djangobin_author"."last_logged_in"
FROM
    "djangobin_author"
WHERE
    "djangobin_author"."id" > 3

There also exists a complementary lookup called lt (less than).

lt lookup #

Less than:

1
2
3
4
5

>>>
>>> Author.objects.filter(id__lt=3)
<QuerySet [<Author: tommy : tommy@example.com>]>
>>>
>>>

Here the lt lookups find all the records whose primary key is less than 3.

The SQL equivalent of the above query is:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT
    "djangobin_author"."id",
    "djangobin_author"."name",
    "djangobin_author"."email",
    "djangobin_author"."active",
    "djangobin_author"."created_on",
    "djangobin_author"."last_logged_in"
FROM
    "djangobin_author"
WHERE
    "djangobin_author"."id" < 3

There are two more similar lookups called gte (greater than or equal to) and lte (less than or equal to) which finds the records which are greater than or equal to and less than or equal to, respectively.

You can also pass multiple lookups at once. When we do so, lookups and ANDed together.

1
2
3
4
5

>>>
>>> Author.objects.filter(id__lt=5, email__endswith="com")
<QuerySet [<Author: tommy : tommy@example.com>, <Author: jerry : jerry@mail.com>, <Author: spike : spike@mail.com>]>
>>> 
>>>

The SQL equivalent of the above query is:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

SELECT
    "djangobin_author"."id",
    "djangobin_author"."name",
    "djangobin_author"."email",
    "djangobin_author"."active",
    "djangobin_author"."created_on",
    "djangobin_author"."last_logged_in"
FROM
    "djangobin_author"
WHERE
    (
        "djangobin_author"."id" < 5
        AND "djangobin_author"."email" LIKE "%com"
    )

exact lookup #

The exact lookup performs a case-sensitive exact match: For example:

1
2
3
4

>>>
>>> Author.objects.filter(name__exact="spike")
<QuerySet [<Author: spike : spike@mail.com>]>
>>>

This query returns any object where name is spike. The SQL equivalent of the query is as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

>>> print(Author.objects.filter(name__exact="spike").query)
SELECT
    "djangobin_author"."id",
    "djangobin_author"."name",
    "djangobin_author"."email",
    "djangobin_author"."active",
    "djangobin_author"."created_on",
    "djangobin_author"."last_logged_in"
FROM
    "djangobin_author"
WHERE
    "djangobin_author"."name" = spike
>>>

The important thing to keep in mind is that if you don't specify any lookup in your queries, Django will implicitly apply the exact lookup. This means that the preceding query can also be written as follows:

1
2
3
4

>>>
>>> Author.objects.filter(name="spike")
<QuerySet [<Author: spike : spike@mail.com>]>
>>>

iexact lookup #

Same as exact lookup but performs a case-insensitive match.

1
2
3
4
5
6
7

>>> 
>>> Author.objects.filter(name__exact="SPIKE")
<QuerySet []>
>>> 
>>> Author.objects.filter(name__iexact="SPIKE")
<QuerySet [<Author: spike : spike@mail.com>]>
>>>

isnull lookup #

The isnull lookup takes True or False, and adds IS NULL or IS NOT NULL operators to the query respectively.

1
2
3
4
5
6
7

>>>
>>> Author.objects.filter(name__isnull=True)
<QuerySet []>
>>> 
>>> Author.objects.filter(name__isnull=False)
<QuerySet [<Author: tommy : tommy@example.com>, <Author: jerry : jerry@mail.com>, <Author: spike : spike@mail.com>, <Author: tyke : tyke@mail.com>, <Author: droopy : droopy@mail.com>]>
>>>

The SQL equivalent of the preceding two queries is as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

>>> 
>>> Author.objects.filter(name__isnull=True).query
>>> print(Author.objects.filter(name__isnull=True).query)
SELECT
    "djangobin_author"."id",
    "djangobin_author"."name",
    "djangobin_author"."email",
    "djangobin_author"."active",
    "djangobin_author"."created_on",
    "djangobin_author"."last_logged_in"
FROM
    "djangobin_author"
WHERE
    "djangobin_author"."name" IS NULL
>>> 

>>> print(Author.objects.filter(name__isnull=False).query)
SELECT
    "djangobin_author"."id",
    "djangobin_author"."name",
    "djangobin_author"."email",
    "djangobin_author"."active",
    "djangobin_author"."created_on",
    "djangobin_author"."last_logged_in"
FROM
    "djangobin_author"
WHERE
    "djangobin_author"."name" IS NOT NULL
>>>

in lookup #

The in lookup finds all the values specified in the list. For example:

1
2
3
4

>>> 
>>> Author.objects.filter(name__in=['spike', 'tyke'])
<QuerySet [<Author: spike : spike@mail.com>, <Author: tyke : tyke@mail.com>]>
>>>

The preceding query returns Author objects whose name is either spike or tyke.

The SQL equivalent of the query is:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT
    "djangobin_author"."id",
    "djangobin_author"."name",
    "djangobin_author"."email",
    "djangobin_author"."active",
    "djangobin_author"."created_on",
    "djangobin_author"."last_logged_in"
FROM
    "djangobin_author"
WHERE
    "djangobin_author"."name" IN ('spike', 'tyke')

date, month and year lookups #

The day, month and year lookups perform exact day, month and year match respectively. For example:

1
2
3
4

>>>
>>> Author.objects.filter(created_on__year=2018)
<QuerySet [<Author: tommy : tommy@example.com>, <Author: jerry : jerry@mail.com>, <Author: spike : spike@mail.com>, <Author: tyke : tyke@mail.com>, <Author: droopy : droopy@mail.com>]>
>>>

This query returns all Author objects which are created in the year 2018.

1
2
3
4

>>> 
>>> Author.objects.filter(created_on__month=3, created_on__year=2018)
<QuerySet [<Author: tommy : tommy@example.com>, <Author: jerry : jerry@mail.com>, <Author: spike : spike@mail.com>, <Author: tyke : tyke@mail.com>, <Author: droopy : droopy@mail.com>]>
>>>

This query returns all the objects which are created in the March 2018.

1
2
3
4

>>> 
>>> Author.objects.filter(created_on__day=24, created_on__month=3, created_on__year=2018)
<QuerySet [<Author: jerry : jerry@mail.com>, <Author: spike : spike@mail.com>, <Author: tyke : tyke@mail.com>, <Author: droopy : droopy@mail.com>]>
>>>

This query returns all the objects which are created on the 24th March 2018.

Chaining QuerySet #

It is also possible to chain multiple QuerySet objects to get exactly what you are looking for. For example:

1
2
3
4
5
6

>>> 
>>> Author.objects.filter(id__gt=1).\
...     exclude(name='spike').\
...     filter(name__icontains="o")
<QuerySet [<Author: tommy : tommy@example.com>, <Author: droopy : droopy@mail.com>]>
>>>

In this query, we first create a QuerySet containing objects with the primary key greater than 1. Then we exclude all objects where the name is spike. Finally, we filter only the objects which contain the character 'o' in their name.

Chaining is particularly useful when you are applying some logic while building up the query. For example:

1
2
3

q = Author.objects.filter(created_on__year=2018)
if True:  # some logic
    q = q.filter(active=False)

Getting the first and the last result #

The first() and last() methods returns the first and last result from the QuerySet respectively.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

>>>
>>> Author.objects.filter(created_on__year=2018)
<QuerySet [<Author: tommy : tommy@example.com>, <Author: jerry : jerry@mail.com>, <Author: spike : spike@mail.com>, <Author: tyke : tyke@mail.com>, <Author: droopy : droopy@mail.com>]>
>>> 
>>> 
>>> Author.objects.filter(created_on__year=2018).first()
<Author: tommy : tommy@example.com>
>>> 
>>>
>>> Author.objects.filter(created_on__year=2018).last()
<Author: droopy : droopy@mail.com>
>>> 
>>>

Retrieving a single object using the get() method #

The filter() method described in the above section returns a QuerySet, sometimes we just want to fetch a single record from the table. To handle these situations the objects manager provides a get() method. The get() method accepts same parameters as filter() method but it returns only a single object. If it finds multiple objects it raises a MultipleObjectsReturned exception. If it doesn't find any object it raises DoesNotExist exception.

1
2
3
4
5
6
7

>>>
>>> Author.objects.get(name="tommy")
<Author: tommy : tommy@email.com>
>>>
>>> Author.objects.filter(name="tommy")
<QuerySet [<Author: tommy : tommy@example.com>]>
>>>

Notice the difference between the output of get() and filter() method. For the same parameter, they both return two different results. The get() method returns an instance of Author whereas the filter() methods returns a QuerySet object.

Let's see what happens, if get() method encounters multiple records.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

>>>
>>> Author.objects.filter(name__contains="ke")
<QuerySet [<Author: spike : spike@mail.com>, <Author: tyke : tyke@mail.com>]>
>>>
>>>
>>> Author.objects.get(name__contains="ke")
Traceback (most recent call last):
...
djangobin.models.MultipleObjectsReturned: get() returned more than one Author -- it returned 2!
>>>

Here the get() method raises a MultipleObjectsReturned exception because there are multiple objects in the database that matches the given parameter.

Similarly, if you try to access an object which does not exist then the get() method will raise a DoesNotExist exception.

1
2
3
4
5
6

>>>
>>> Author.objects.get(name__contains="captain planet")
Traceback (most recent call last):
...
djangobin.models.DoesNotExist: Author matching query does not exist.
>>>

Ordering Results #

To order result we use order_by() method, just like filter() it also returns a QuerySetobject. It accepts field names that you want to sort by as positional arguments.

1
2
3
4

>>>
>>> Author.objects.order_by("id")
<QuerySet [<Author: tommy : tommy@example.com>, <Author: jerry : jerry@mail.com>, <Author: spike : spike@mail.com>, <Author: tyke : tyke@mail.com>, <Author: droopy : droopy@mail.com>]>
>>>

This code retrieves all the Author objects ordered by id in ascending order. The SQL equivalent of the query is:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT
    "djangobin_author"."id",
    "djangobin_author"."name",
    "djangobin_author"."email",
    "djangobin_author"."active",
    "djangobin_author"."created_on",
    "djangobin_author"."last_logged_in"
FROM
    "djangobin_author"
ORDER BY
    "djangobin_author"."id" ASC

Here is another example, in which we first filter the result and sort it by name in ascending order.

1
2
3
4
5

>>>
>>> Author.objects.filter(id__gt=3).order_by("name")
<QuerySet [<Author: droopy : droopy@mail.com>, <Author: spike : spike@mail.com>,
 <Author: tyke : tyke@mail.com>]>
>>>

The SQL equivalent of the query is:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

SELECT
    "djangobin_author"."id",
    "djangobin_author"."name",
    "djangobin_author"."email",
    "djangobin_author"."active",
    "djangobin_author"."created_on",
    "djangobin_author"."last_logged_in"
FROM
    "djangobin_author"
WHERE
    "djangobin_author"."id" > 3
ORDER BY
    "djangobin_author"."name" ASC

To reverse the sorting ordering add minus(-) sign before the field name like this:

1
2
3
4
5

>>>
>>> Author.objects.filter(id__gt=3).order_by("-name")
<QuerySet [<Author: tyke : tyke@mail.com>, <Author: spike : spike@mail.com>, <Au
thor: droopy : droopy@mail.com>]>
>>>

The SQL equivalent of the query is:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

SELECT
    "djangobin_author"."id",
    "djangobin_author"."name",
    "djangobin_author"."email",
    "djangobin_author"."active",
    "djangobin_author"."created_on",
    "djangobin_author"."last_logged_in"
FROM
    "djangobin_author"
WHERE
    "djangobin_author"."id" > 3
ORDER BY
    "djangobin_author"."name" DESC

You can also sort the result by multiple fields like this.

1
2
3
4
5

>>>
>>> Author.objects.filter(id__gt=3).order_by("name", "-email")
<QuerySet [<Author: droopy : droopy@mail.com>, <Author: spike : spike@mail.com>,
 <Author: tyke : tyke@mail.com>]>
>>>

This query sorts the result first by name in ascending and then by email in descending order

Selecting the Fields #

When you query a database like this:

1
2
3

>>>
>>> Author.objects.filter(name__contains='foo').order_by("name")
>>>

It returns data from all the fields (columns). What if we want data only from one or two fields ? The objects manager provides a values_list() method for this job. The values_list() method accepts one or more field names from where we want the data and returns a QuerySet. If you don't pass any values to values_list() method, then the data from all the fields will be returned. For example:

1
2
3
4

>>>
>>> Author.objects.values_list("id", "name")
<QuerySet [(2, 'tommy'), (3, 'jerry'), (4, 'spike'), (5, 'tyke'), (6, 'droopy')]>
>>>

Notice that the values_list() method returns a QuerySet where each element is a tuple. And the tuple only contains data from the fields which we have specified in the values_list() method.

Here is another example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

>>>
>>> Author.objects.filter(id__gt=3).values_list("id", "name")
<QuerySet [(4, 'spike'), (5, 'tyke'), (6, 'droopy')]>
>>>
>>> r = Author.objects.filter(id__gt=3).values_list("id", "name")
>>>
>>> r
<QuerySet [(4, 'spike'), (5, 'tyke'), (6, 'droopy')]>
>>>
>>> r[0]
(4, 'spike')
>>>
>>> r[0][0]
4
>>>
>>> r[0][1]
'spike'
>>>

The objects manager also provides an identical method called values() which works exactly like values_list() but it returns a QuerySet where each element is a dictionary instead of a tuple.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

>>>
>>> r = Author.objects.filter(id__gt=3).values("id", "name")
>>>
>>> r
<QuerySet [{'name': 'spike', 'id': 4}, {'name': 'tyke', 'id': 5}, {'name': 'droo
py', 'id': 6}]>
>>>
>>> type(r[0])
<class 'dict'>
>>>
>>> r[0]
{'name': 'spike', 'id': 4}
>>>
>>> r[0]['name']
'spike'
>>>
>>> r[0]['id']
4
>>>

Slicing Results #

You can use Python list slicing syntax ([start:end]) to limit your QuerySet object to a certain number of results.

Example 1:

1
2
3
4

>>>
>>> Author.objects.order_by("id")[1]  
<Author: tyke : tyke@mail.com>
>>>

This query sorts the results by id in ascending order and then returns only the first object from the QuerySet. The SQL equivalent of the query is:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

SELECT
    "djangobin_author"."id",
    "djangobin_author"."name",
    "djangobin_author"."email",
    "djangobin_author"."active",
    "djangobin_author"."created_on",
    "djangobin_author"."last_logged_in"
FROM
    "djangobin_author"
ORDER BY
    "djangobin_author"."id" DESC
LIMIT 1 OFFSET 1

Example 2:

1
2
3
4
5
6

>>>
>>> Author.objects.order_by("-id")[:3]
<QuerySet [<Author: droopy : droopy@mail.com>, <Author: tyke : tyke@mail.com>, <
Author: spike : spike@mail.com>]>
>>>
>>>

This query sorts the results by id in descending order and returns the first three objects from the QuerySet. This code roughly translates to SQL as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

SELECT
    "djangobin_author"."id",
    "djangobin_author"."name",
    "djangobin_author"."email",
    "djangobin_author"."active",
    "djangobin_author"."created_on",
    "djangobin_author"."last_logged_in"
FROM
    "djangobin_author"
ORDER BY
    "djangobin_author"."id" DESC
LIMIT 3

Example 3:

1
2
3
4
5
6
7

>>>
>>> # returns objects from 3rd index to 5th index after sorting the result
>>>
>>> Author.objects.filter(id__gt=1).order_by("-id")[2:5]
<QuerySet [<Author: spike : spike@mail.com>, <Author: jerry : jerry@mail.com>, <Author: tommy : tommy@example.com>]>
>>> 
>>>

This query returns objects from index 2 to 5. The SQL equivalent of the query is as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

SELECT
    "djangobin_author"."id",
    "djangobin_author"."name",
    "djangobin_author"."email",
    "djangobin_author"."active",
    "djangobin_author"."created_on",
    "djangobin_author"."last_logged_in"
FROM
    "djangobin_author"
ORDER BY
    "djangobin_author"."id" DESC
LIMIT 3 OFFSET 2

Unfortunately, we can't use negative indexes to slice the queryset.

1
2
3
4
5
6
7

>>>
>>> Author.objects.order_by("-id")[-1]
Traceback (most recent call last):
#...
AssertionError: Negative indexing is not supported.
>>>
>>>

Complex Query with Q objects #

We have already seen that when we pass multiple keyword arguments to look up functions (i.e. filter(), exclude() or get() ), they are ANDed together. There will be times when you want to combine two or more conditions using OR operator instead of AND operator. This is where Q objects come into play.

A Q object is just a collection of keyword arguments, that you would normally pass to look up function (filter(), exclude() or get() method). For example:

1
2

from django.db.models import Q
Q(name__contains="tom")

This Q object encapsulates a single keyword argument. But we can pass as many keyword arguments as we want.

1
2

from django.db.models import Q
Q(name__icontains="tom", email__icontains="example", created_on__year=2018)

Once we have created a condition using Q object, we can reuse it multiple times in our code without recreating it over and over again.

Note that in the preceding example multiple keyword arguments passed to Q() constructor are ANDed together. That means the following two queries are equivalent.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

>>> 
>>> from django.db.models import Q
>>>
>>> Author.objects.filter(Q(name__icontains="tom", email__icontains="example", created_on__year=2018))
<QuerySet [<Author: tommy : tommy@example.com>]>
>>> 
>>>
>>> Author.objects.filter(name__icontains="tom", email__icontains="example", created_on__year=2018)
<QuerySet [<Author: tommy : tommy@example.com>]>
>>> 
>>>

The SQL equivalent of the query is:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

SELECT
    "djangobin_author"."id",
    "djangobin_author"."name",
    "djangobin_author"."email",
    "djangobin_author"."active",
    "djangobin_author"."created_on",
    "djangobin_author"."last_logged_in"
FROM
    "djangobin_author"
WHERE
    (
        "djangobin_author"."created_on" BETWEEN 2018 - 01 - 01 00 : 00 : 00
        AND 2018 - 12 - 31 23 : 59 : 59.999999
        AND "djangobin_author"."name" LIKE "%tom%"
        AND "djangobin_author"."email" LIKE "%example%"
    )

We can combine Q objects using & (bitwise AND) and | (bitwise OR) operators. When we do so, a new Q object is created. The & and | operators allows us to create SQL queries with AND or OR condition respectively. For example:

1
2
3
4
5

>>>
>>> Author.objects.filter(Q(name__iexact="tommy") | Q(name__iexact="jerry"))
<QuerySet [<Author: tommy : tommy@example.com>, <Author: jerry : jerry@mail.com>]>
>>> 
>>>

This query returns all the objects whose name field is either tom or jerry. The SQL equivalent of the query is:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

SELECT
    "djangobin_author"."id",
    "djangobin_author"."name",
    "djangobin_author"."email",
    "djangobin_author"."active",
    "djangobin_author"."created_on",
    "djangobin_author"."last_logged_in"
FROM
    "djangobin_author"
WHERE
    (
        "djangobin_author"."name" = 'tommy'
        OR "djangobin_author"."name" = 'jerry'
    )

We can pass multiple Q objects to the lookup functions (e.g. filter(), exclude(), get() etc) as position arguments. If we do so, the Q objects will be ANDed together. For example:

1
2
3
4
5
6
7

>>> 
>>> Author.objects.filter(
...     Q(created_on__year=2018),  
...     Q(name__iexact="tommy")| Q(name__iexact="jerry")
... )
<QuerySet [<Author: tommy : tommy@example.com>, <Author: jerry : jerry@mail.com>]>
>>>

The SQL equivalent of the query is:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

SELECT
    "djangobin_author"."id",
    "djangobin_author"."name",
    "djangobin_author"."email",
    "djangobin_author"."active",
    "djangobin_author"."created_on",
    "djangobin_author"."last_logged_in"
FROM
    "djangobin_author"
WHERE
    (
        "djangobin_author"."created_on" BETWEEN 2018 - 01 - 01 00 : 00 : 00
        AND 2018 - 12 - 31 23 : 59 : 59.999999
        AND (
            "djangobin_author"."name" LIKE "tommy"
            OR "djangobin_author"."name" LIKE "jerry"
        )
    )

Finally, we can also mix keyword arguments with Q objects while calling the lookup function. In doing so, remember that all the arguments to the lookup function will be ANDed together and all the Q objects must appear before any keyword arguments. That means the following is a valid query:

1
2
3
4
5
6
7

>>> 
>>> Author.objects.filter(
...    Q(name__iexact="tommy") | Q(name__iexact="jerry"), 
...    active=True
... )
<QuerySet [<Author: tommy : tommy@example.com>]>
>>>

But the next one is not.

1
2
3
4
5
6
7
8

>>>
>>> Author.objects.filter(
...    active=True,
...    Q(name__iexact="tommy") | Q(name__iexact="jerry"), 
... )
  File "<console>", line 3
SyntaxError: positional argument follows keyword argument
>>>

Updating Multiple Objects #

Recall that one way to update an object is to call save() method after updating it's attributes. For example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

>>>
>>>
>>> a = Author.objects.get(pk=2)
>>> a
<Author: tommy : tommy@email.com>
>>>
>>> a.name = 'tom'
>>> a.email = 'tom@mail.com'
>>>
>>> a.save()
>>>
>>> a
<Author: tom : tom@mail.com>
>>>
>>>

The objects manager provides a method called update() to update one or multiple records in one step. Just like filter() method it accepts one or more keyword arguments. If the update was successful it returns the number of rows updated.

1
2
3
4

>>>
>>> Author.objects.filter(id__gt=3).update(active=True, name='x')
3
>>>

This query modifies the active and name fields of all the authors whose primary key is greater than 3.

This SQL equivalent of the query is:

1
2
3

UPDATE djangobin_author 
SET active=1, name='x'
WHERE id > 3;

Here is another example which modifies the active attribute of all objects to False.

1
2
3
4
5
6

>>>
>>>
>>> Author.objects.update(active=False)
5
>>>
>>>

The query is equivalent to the following:

Author.objects.all().update(active=False)

The SQL equivalent of the above query is:

1
2

UPDATE djangobin_author SET
active=0

Deleting records #

As discussed in earlier in this lesson, we can use model's delete() method to delete an object from the database. For example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

>>>
>>> a = Author.objects.get(pk=2)
>>>
>>> a
<Author: tom : tom@mail.com>
>>>
>>> a.delete()
(1, {'djangobin.Author': 1})
>>>
>>>

To delete multiple records at once use delete() method provided by the QuerySet object. For example:

1
2
3
4
5

>>>
>>> Author.objects.all().delete()
(4, {'djangobin.Author': 4})
>>>
>>>

You should now have a solid understanding of Django ORM. In the next lesson, we will discuss how to access data from related tables using Django ORM.