Django ORM and QuerySets

In this chapter you’ll learn how Django connects to the database and stores data in it. Let’s dive in!

What is a QuerySet?

A QuerySet is, in essence, a list of objects of a given Model. QuerySets allow you to read the data from the database, filter it and order it.

It’s easiest to learn by example. Let’s try this, shall we?

Django shell

Open up your local console (not on PythonAnywhere) and type this command:

{% filename %}command-line{% endfilename %}

  1. (myvenv) ~/djangogirls$ python manage.py shell

The effect should be like this:

{% filename %}command-line{% endfilename %}

  1. (InteractiveConsole)
  2. >>>

You’re now in Django’s interactive console. It’s just like the Python prompt, but with some additional Django magic. :) You can use all the Python commands here too, of course.

All objects

Let’s try to display all of our posts first. You can do that with the following command:

{% filename %}command-line{% endfilename %}

  1. >>> Post.objects.all()
  2. Traceback (most recent call last):
  3. File "<console>", line 1, in <module>
  4. NameError: name 'Post' is not defined

Oops! An error showed up. It tells us that there is no Post. It’s correct – we forgot to import it first!

{% filename %}command-line{% endfilename %}

  1. >>> from blog.models import Post

We import the model Post from blog.models. Let’s try displaying all posts again:

{% filename %}command-line{% endfilename %}

  1. >>> Post.objects.all()
  2. <QuerySet [<Post: my post title>, <Post: another post title>]>

This is a list of the posts we created earlier! We created these posts using the Django admin interface. But now we want to create new posts using Python, so how do we do that?

Create object

This is how you create a new Post object in database:

{% filename %}command-line{% endfilename %}

  1. >>> Post.objects.create(author=me, title='Sample title', text='Test')

But we have one missing ingredient here: me. We need to pass an instance of User model as an author. How do we do that?

Let’s import User model first:

{% filename %}command-line{% endfilename %}

  1. >>> from django.contrib.auth.models import User

What users do we have in our database? Try this:

{% filename %}command-line{% endfilename %}

  1. >>> User.objects.all()
  2. <QuerySet [<User: ola>]>

This is the superuser we created earlier! Let’s get an instance of the user now:

{% filename %}command-line{% endfilename %}

  1. >>> me = User.objects.get(username='ola')

As you can see, we now get a User with a username that equals ‘ola’. Neat! Of course, you have to adjust this line to use your own username.

Now we can finally create our post:

{% filename %}command-line{% endfilename %}

  1. >>> Post.objects.create(author=me, title='Sample title', text='Test')
  2. <Post: Sample title>

Hurray! Wanna check if it worked?

{% filename %}command-line{% endfilename %}

  1. >>> Post.objects.all()
  2. <QuerySet [<Post: my post title>, <Post: another post title>, <Post: Sample title>]>

There it is, one more post in the list!

You can now have a little fun and add more posts to see how it works. Add two or three more and then go ahead to the next part.

Filter objects

A big part of QuerySets is the ability to filter them. Let’s say we want to find all posts that user ola authored. We will use filter instead of all in Post.objects.all(). In parentheses we state what condition(s) a blog post needs to meet to end up in our queryset. In our case, the condition is that author should be equal to me. The way to write it in Django is author=me. Now our piece of code looks like this:

{% filename %}command-line{% endfilename %}

  1. >>> Post.objects.filter(author=me)
  2. <QuerySet [<Post: Sample title>, <Post: Post number 2>, <Post: My 3rd post!>, <Post: 4th title of post>]>

Or maybe we want to see all the posts that contain the word ‘title’ in the title field?

{% filename %}command-line{% endfilename %}

  1. >>> Post.objects.filter(title__contains='title')
  2. <QuerySet [<Post: Sample title>, <Post: 4th title of post>]>

Note There are two underscore characters (_) between title and contains. Django’s ORM uses this rule to separate field names (“title”) and operations or filters (“contains”). If you use only one underscore, you’ll get an error like “FieldError: Cannot resolve keyword title_contains”.

You can also get a list of all published posts. We do this by filtering all the posts that have published_date set in the past:

{% filename %}command-line{% endfilename %}

  1. >>> from django.utils import timezone
  2. >>> Post.objects.filter(published_date__lte=timezone.now())
  3. <QuerySet []>

Unfortunately, the post we added from the Python console is not published yet. But we can change that! First get an instance of a post we want to publish:

{% filename %}command-line{% endfilename %}

  1. >>> post = Post.objects.get(title="Sample title")

And then publish it with our publish method:

{% filename %}command-line{% endfilename %}

  1. >>> post.publish()

Now try to get list of published posts again (press the up arrow key three times and hit enter):

{% filename %}command-line{% endfilename %}

  1. >>> Post.objects.filter(published_date__lte=timezone.now())
  2. <QuerySet [<Post: Sample title>]>

Ordering objects

QuerySets also allow you to order the list of objects. Let’s try to order them by created_date field:

{% filename %}command-line{% endfilename %}

  1. >>> Post.objects.order_by('created_date')
  2. <QuerySet [<Post: Sample title>, <Post: Post number 2>, <Post: My 3rd post!>, <Post: 4th title of post>]>

We can also reverse the ordering by adding - at the beginning:

{% filename %}command-line{% endfilename %}

  1. >>> Post.objects.order_by('-created_date')
  2. <QuerySet [<Post: 4th title of post>, <Post: My 3rd post!>, <Post: Post number 2>, <Post: Sample title>]>

Chaining QuerySets

You can also combine QuerySets by chaining them together:

  1. >>> Post.objects.filter(published_date__lte=timezone.now()).order_by('published_date')
  2. <QuerySet [<Post: Post number 2>, <Post: My 3rd post!>, <Post: 4th title of post>, <Post: Sample title>]>

This is really powerful and lets you write quite complex queries.

Cool! You’re now ready for the next part! To close the shell, type this:

{% filename %}command-line{% endfilename %}

  1. >>> exit()
  2. $