Previous Page Next Page

Performing Queries in the Database

The Django database-abstraction API provides a rich set of objects and functions that allow you to perform complex queries on the database. We touched on this earlier in this hour when we discussed the all() and get() functions. This section discusses how to use the filter() and exclude() functions to retrieve QuerySets from the database.

Using the filter() Function

The filter(**kwargs) function accepts any number of lookup parameters to use when querying the database. The lookups are field-based. Only entries whose fields match those in the lookups are returned in the QuerySet. For example, the following code snippet shows using one filter to query for Blog objects by the name field and another filter to query for Blog objects by the name and text fields:

from iFriends.People.models import Blog
blogs = Blog.objects.filter(title='MyBlog')
blogs = Blog.objects.filter(title='MyBlog', text='Some Text')

Using the exclude() Function

The exclude(**kwargs) function accepts any number of lookup parameters to use when querying the database. The lookups are field-based. All entries whose fields do not match those in the lookups are returned in the QuerySet. For example, the following code snippet shows using one filter to query for Blog objects by the name field and another filter to query for Blog objects by the name and text fields:

from iFriends.People.models import Blog
blogs = Blog.objects.exclude(title='MyBlog')
blogs = Blog.objects.exclude(title='MyBlog', text='Some Text')

Understanding Field Lookups

So far, we have done only simple field lookups that look for exact matches using the syntax field=value. However, the database-abstraction API provides numerous types of field lookups that allow you to create complex queries.

These field lookup types are applied by attaching them to the end of the field name using a double underscore. For example, the following line of code performs a query using the iexact field lookup type on the title field of the Blog object:

blogs = Blog.objects.filter(title__iexact='MyBlog')

The information in the field lookup is translated behind the scenes into an SQL query by the Django database-abstraction layer so that the database can be queried. For instance, in the preceding example, the case-insensitive iexact search would be translated into the following SQL statement:

SELECT ... WHERE title ILIKE 'MyBlog';

The following is a list of the different types of field lookups that you can use in filter(), exclude(), and get() functions:

Chaining QuerySets

Now it's time to put together all the pieces we have discussed in this section. The all(), get(), filter(), and exclude() functions are all part of the QuerySet class of objects that is attached to each object in the model. The all(), filter(), and exclude() functions all return QuerySet objects. You can also use the all(), get(), filter(), and exclude() functions on each new QuerySet object that is returned. For example, consider the following code snippet:

qs1 = Blog.objects.filter(title__icontains='test')
testBlogs = qs1.exclude(postDate__year=2008)

The first QuerySet object, qs1, contains all Blog entries whose title field contains the text test. The second QuerySet object, testBlogs, refines the initial QuerySet to exclude any Blog entries posted in the year 2008. By joining the QuerySets in this manner, you can create complex lookups in your code to handle the most complex types of database queries.

Django also provides another object, django.db.models.Q, that helps immensely with difficult queries. The Q object allows you to encapsulate a collection of field lookup arguments. For example, the following code snippet encapsulates a contains field lookup in a Q object and assigns it to variable q1:

from django.db.models import Q
q1 = Q(title__contains='test')

The filter(), exclude(), and get() functions accept the Q objects as their arguments. You can also use the | and & operators to define SQL OR and AND behavior in the queries. For example, the following code snippet shows how to define two Q objects and then use them with OR and AND logic in the filter() function:

from django.db.models import Q
q1 = Q(title__contains='test')
q2 = Q(title__contains='obsolete')
oldBlogs = Blog.objects.filter(q1 | q2)
oldTestBlogs = Blog.objects.filter(q1 & q2)

To illustrate this further, let's look at a slightly more complex query that involves using Q objects and QuerySets:

from django.db.models import Q
qs1 = Blog.objects.filter(uName__exact='Admin')
qs2 = qs1.exclude(postDate__year=2008)
q1 = Q(title__contains='SPAM')
q2 = Q(title__contains='obsolete')
oldBlogs = qs2.filter(q1 | q2 )

Ordering Objects in a QuerySet

Another valuable function that is available through QuerySet objects is the order_by() function. The order_by() function accepts one or more field names as its arguments and then returns a new QuerySet that is ordered based on the field name you specify.

For example, the following code creates two QuerySets, qs1 and qs2. qs1 is ordered by the title, and qs2 is ordered by the postDate:

qs1 = Blog.objects.all().orderby('title')
qs2 = Blog.objects.all().orderby('postDate')

Watch Out!

Django does not have case sensitivity built into the order_by() function. Therefore, the order is determined by the database backend.


Django orders the list by default in ascending order. You can also order the objects in descending order by specifying a – before the field name:

qs1 = Blog.objects.all().orderby('-postDate')

Did you Know?

Django provides a handy tool to randomize QuerySets. If you specify a ? as the only argument to order_by(), as in the following code, order_by() returns a randomly ordered QuerySet.

qs1 = Blog.objects.all().orderby('?')


Try It Yourself: Perform Queries on the Database

In this section, you will use the Django shell to add some Blog entries to the database. Then you will perform queries on them to get a feel for how the queries work and how to include them in your code later.

Follow these steps from the Django shell to create some Blog entries, and then perform queries on them:

1.
Import the Blog object using the following command at the shell prompt:

from iFriends.People.models import Blog

2.
Add some Blog entries by entering the following commands at the shell prompt, as shown in Figure 5.4:

b = Blog(title='BlogA', text='Text in BlogA')
b.save()
b = Blog(title='BlogB', text='More text in BlogB')
b.save()
b = Blog(title='BlogC', text='Even more text in BlogC')
b.save()

Figure 5.4. Using the save() function in the Python shell to add Blog objects to the database.

[View full size image]


3.
Get the entry with the title BlogC, and print the text field using the following commands at the shell prompt, as shown in Figure 5.5:

e = Blog.objects.get(title='BlogC')
print e.text

Figure 5.5. Using the filter() function in the Python shell to query the database.

[View full size image]


4.
Retrieve all entries whose title field begins with Blog, and print the text fields using the following commands at the shell prompt, as shown in Figure 5.5:

blogs = Blog.objects.filter(title__startswith='Blog')
for e in blogs:
    print e.text

5.
Retrieve all entries whose title field begins with Blog and whose text fields contain the word "more" (case-insensitive) but not the word "even" (case-insensitive). Then print the text field using the following commands at the shell prompt, as shown in Figure 5.6.

from django.db.models import Q
q1 = Q(text__icontains='more')
q2 = Q(text__icontains='even')
qs1 = Blog.objects.filter(title__startswith='Blog')
qs2 = qs1.filter(q1)
blogs = qs2.exclude(q2)
for e in blogs:
    print e.text

Figure 5.6. Using the Q object and the filter() and exclude() functions in the Python shell to query the database.

[View full size image]



Previous Page Next Page