Knowing how to create and update database records is essential, but chances are that the web applications you’ll build will be doing more querying of existing objects than creating new ones. We’ve already seen a way to retrieve every record for a given model:
> Publisher.objects.all() <QuerySet [<Publisher: Apress>, <Publisher: O'Reilly>, <Publisher: GN Independent Publishing>]>
This roughly translates to this SQL:
SELECT id, name, address, city, state_province, country, website FROM books_publisher;
Notice that Django doesn’t use SELECT * when looking up data and instead lists all fields explicitly. This is by design: in certain circumstances SELECT * can be slower, and (more important) listing fields more closely follows one tenet of the Zen of Python: “Explicit is better than implicit.” For more on the Zen of Python, try typing import this at a Python prompt.
Let’s take a close look at each part of this Publisher.objects.all() line:
- First, we have the model we defined, Publisher. No surprise here: when you want to look up data, you use the model for that data.
- Next, we have the objects attribute. This is called a manager. For now, all you need to know is that managers take care of all table-level operations on data including, most important, data lookup. All models automatically get an objects manager; you’ll use it any time you want to look up model instances.
- Finally, we have all(). This is a method on the objects manager that returns all the rows in the database in a QuerySet – an object that represents a specific set of rows from the database.
Any database lookup is going to follow this general pattern – we’ll call methods on the manager attached to the model we want to query against.
Filtering Data
Naturally, it’s rare to want to select everything from a database at once; in most cases, you’ll want to deal with a subset of your data. In the Django API, you can filter your data using the filter() method:
>> Publisher.objects.filter(name='Apress') <QuerySet [<Publisher: Apress>]>
filter() takes keyword arguments that get translated into the appropriate SQL WHERE clauses. The preceding example would get translated into something like this:
SELECT id, name, address, city, state_province, country, website FROM books_publisher WHERE name = 'Apress';
You can pass multiple arguments into filter() to narrow down things further:
>> Publisher.objects.filter(country="U.S.A.", state_province="CA") <QuerySet [<Publisher: Apress>]>
Those multiple arguments get translated into SQL AND clauses. Thus, the example in the code snippet translates into the following:
SELECT id, name, address, city, state_province, country, website FROM books_publisher WHERE country = 'U.S.A.' AND state_province = 'CA';
Notice that by default the lookups use the SQL = operator to do exact match lookups. Other lookup types are available:
>> Publisher.objects.filter(name__contains="press")
<QuerySet [<Publisher: Apress>]>
That’s a double underscore there between name and contains. Like Python itself, Django uses the double underscore to signal that something “magic” is happening – here, the __contains part gets translated by Django into a SQL LIKE statement:
SELECT id, name, address, city, state_province, country, website FROM books_publisher WHERE name LIKE '%press%';
Many other types of lookups are available, including icontains (case-insensitive LIKE), startswith and endswith, and range (SQL BETWEEN queries). Appendix C describes all of these lookup types in detail.
Retrieving Single Objects
The filter() examples above all returned a QuerySet, which you can treat like a list. Sometimes it’s more convenient to fetch only a single object, as opposed to a QuerySet. That’s what the get() method is for:
>>> Publisher.objects.get(name="Apress")<Publisher: Apress>
Instead of a QuerySet, only a single object is returned. Because of that, a query resulting in multiple objects will cause an exception:
>>> Publisher.objects.get(country="U.S.A.") Traceback (most recent call last): ... books.models.MultipleObjectsReturned: get() returned more than one Publisher - it returned 2! !
A query that returns no objects also causes an exception:
>>> Publisher.objects.get(name="Penguin") Traceback (most recent call last): ... books.models.DoesNotExist: Publisher matching query does not exist.
The DoesNotExist exception is an attribute of the model’s class – Publisher.DoesNotExist. In your applications, you’ll want to trap these exceptions, like this:
try:
p = Publisher.objects.get(name='Apress')
except Publisher.DoesNotExist:
print ("Apress isn't in the database yet.")
else:
print ("Apress is in the database.")
Ordering Data
As you play around with the previous examples, you might discover that the objects are being returned in a seemingly random order. You aren’t imagining things; so far we haven’t told the database how to order its results, so we’re simply getting back data in some arbitrary order chosen by the database. In your Django applications, you’ll probably want to order your results according to a certain value – say, alphabetically. To do this, use the order_by() method:
> Publisher.objects.order_by("name")
<QuerySet [<Publisher: Apress>,
<Publisher: GNW Independent Publishing>, <Publisher: O'Reilly>]>
This doesn’t look much different from the earlier all() example, but the SQL now includes a specific ordering:
SELECT id, name, address, city, state_province, country, website FROM books_publisher ORDER BY name;
You can order by any field you like:
>>> Publisher.objects.order_by("address")
<QuerySet [<Publisher: O'Reilly>, <Publisher: GNW Independent Publishing>, <Publisher: Apress>]>
>>> Publisher.objects.order_by("state_province")
<QuerySet [<Publisher: Apress>, <Publisher: O'Reilly>, <Publisher: GNW Independent Publishing>]>
To order by multiple fields (where the second field is used to disambiguate ordering in cases where the first is the same), use multiple arguments:
>>> Publisher.objects.order_by("state_province", "address")
<QuerySet [<Publisher: Apress>, <Publisher: O'Reilly>, <Publisher: GNW Independent Publishing>]>
You can also specify reverse ordering by prefixing the field name with a “-” (that’s a minus character):
>>> Publisher.objects.order_by("-name")
<QuerySet [<Publisher: O'Reilly>, <Publisher: GNW Independent Publishing>, <Publisher: Apress>]>
While this flexibility is useful, using order_by() all the time can be quite repetitive. Most of the time you’ll have a particular field you usually want to order by. In these cases, Django lets you specify a default ordering in the model:
class Publisher(models.Model): name = models.CharField(max_length=30) address = models.CharField(max_length=50) city = models.CharField(max_length=60) state_province = models.CharField(max_length=30) country = models.CharField(max_length=50) website = models.URLField() def __str__(self): return self.name class Meta: ordering = ['name']
Here, I’ve introduced a new concept: the class Meta, which is a class that’s embedded within the Publisher class definition (i.e., it’s indented to be within class Publisher). You can use this Meta class on any model to specify various model-specific options. A full reference of Meta options is available in Appendix B, but for now, we’re concerned with the ordering option. If you specify this, it tells Django that unless an ordering is given explicitly with order_by(), all Publisher objects should be ordered by the name field whenever they’re retrieved with the Django database API.
Chaining Lookups
You’ve seen how you can filter data, and you’ve seen how you can order it. Often, of course, you’ll need to do both. In these cases, you simply “chain” the lookups together:
>> Publisher.objects.filter(country=”U.S.A.”).order_by(“-name”)
<QuerySet [<Publisher: O’Reilly>, <Publisher: Apress>]>
As you might expect, this translates to a SQL query with both a WHERE and an ORDER BY:
SELECT id, name, address, city, state_province, country, website FROM books_publisher WHERE country = 'U.S.A' ORDER BY name DESC;
Slicing Data
Another common need is to look up only a fixed number of rows. Imagine you have thousands of publishers in your database, but you want to display only the first one. Thankfully, a Django QuerySet can be treated just like a Python list. This means you can do this using Python’s standard list slicing syntax:
>> Publisher.objects.order_by('name')[0]
<Publisher: Apress>
This translates roughly to:
SELECT id, name, address, city, state_province, country, website FROM books_publisher ORDER BY name LIMIT 1;
Similarly, you can retrieve a specific subset of data using Python’s range-slicing syntax:
>>> Publisher.objects.order_by('name')[0:2]
This returns two objects, translating roughly to:
SELECT id, name, address, city, state_province, country, website
FROM books_publisher
ORDER BY name
OFFSET 0 LIMIT 2;
Note that negative slicing is not supported:
>> Publisher.objects.order_by('name')[-1]
Traceback (most recent call last):
...
AssertionError: Negative indexing is not supported.
This is easy to get around, though. Just change the order_by() statement, like this:
>>> Publisher.objects.order_by('-name')[0]
Updating Multiple Objects in One Statement
I pointed out in the “Inserting and Updating Data” section that the model save() method updates all columns in a row. Depending on your application, you may want to update only a subset of columns. For example, let’s say we want to update the Apress Publisher to change the name from ‘Apress’ to ‘Apress Publishing’. Using save(), it would look something like this:
>> p = Publisher.objects.get(name='Apress') >> p.name = 'Apress Publishing'
>>> p.save()
This roughly translates to the following SQL:
SELECT id, name, address, city, state_province, country, website FROM books_publisher WHERE name = 'Apress'; UPDATE books_publisher SET name = 'Apress Publishing', address = '2855 Telegraph Ave.', city = 'Berkeley', state_province = 'CA', country = 'U.S.A.', website = 'http://www.apress.com' WHERE id = 1;
(Note that this example assumes Apress has a publisher ID of 1.) You can see in this example that Django’s save() method sets all of the column values, not just the name column. If you’re in an environment where other columns of the database might change due to some other process, it’s smarter to change only the column you need to change. To do this, use the update() method on QuerySet objects. Here’s an example:
>>> Publisher.objects.filter(id=1).update(name='Apress Publishing')
The SQL translation here is much more efficient and has no chance of race conditions:
UPDATE books_publisher SET name = 'Apress Publishing' WHERE id = 1;
The update() method works on any QuerySet, which means you can edit multiple records in bulk. Here’s how you might change the country from ‘U.S.A.’ to USA in each Publisher record:
>>> Publisher.objects.all().update(country='USA') 3
The update() method has a return value – an integer representing how many records changed. In the above example, we got 3.
Deleting Objects
To delete an object from your database, simply call the object’s delete() method:
>>> p = Publisher.objects.get(name="O'Reilly")
>>> p.delete()
(1, {'books.Publisher': 1})
>>> Publisher.objects.all()
<QuerySet [<Publisher: Apress>, <Publisher: GNW Independent Publishing>]>
Note the return value from Django when you delete an object – Django first lists the total number of records that will be affected (in this case one) and a dictionary containing each of the models (tables) affected and how many records were deleted in each table.
You can also delete objects in bulk by calling delete() on the result of any QuerySet. This is similar to the update() method we showed in the last section:
>>> Publisher.objects.filter(country='USA').delete()
(1, {'books.Publisher': 1})
>>> Publisher.objects.all().delete()
(1, {'books.Publisher': 1})
>>> Publisher.objects.all()
<QuerySet []>
Be careful deleting your data! As a precaution against deleting all of the data in a particular table, Django requires you to explicitly use all() if you want to delete everything in your table. For example, this won’t work:
>>> Publisher.objects.delete() Traceback (most recent call last): File "", line 1, in AttributeError: 'Manager' object has no attribute 'delete' But it’ll work if you add the all() method: >>> Publisher.objects.all().delete()
Note, If you’re just deleting a subset of your data, you don’t need to include all(). To repeat a previous example:
>>> Publisher.objects.filter(country='USA').delete()
