Basic Data Access

Django provides the four basic database functions—Create, Read, Update and Delete (CRUD)—you would expect from a web framework designed for data-driven websites. Django, however, uses a high-level Python API to communicate with your database, rather than SQL.

To learn how to use Django’s database API, we will use the Django interactive shell. Django’s interactive shell runs just like the regular Python interactive shell, except it loads your project’s settings module and other Django-specific modules so you can work with your Django project. To use the Django interactive shell, you must first be running the virtual environment. Then run the following command from inside your myclub_root folder:

  1. python manage.py shell

Your terminal output should look like this:

  1. (env_myclub) ...\myclub_root> python manage.py shell
  2. Python 3.8.3 (tags/v3.8.3:6f8c832, May 13 2020, 22:20:19) [MSC v.1925 32 bit (Intel)] on win32
  3. Type "help", "copyright", "credits" or "license" for more information.
  4. (InteractiveConsole)
  5. >>>

While this looks the same as the Python interactive shell, you can do things like this:

  1. >>> from events.models import Event

If you try to do this in the standard Python interactive shell, you will get an error because the basic Python shell won’t load Django or your project.

Creating Database Records

To create a new event, you must first create a new event object:

  1. >>> from events.models import Event
  2. >>> event1 = Event(
  3. ...     name="Test Event1",
  4. ...     event_date="2020-05-22",
  5. ...     venue="test venue",
  6. ...     manager="Bob"
  7. ... )
  8. >>>

You can see I have imported the Event model class and have created a new Event object and named it event1. Before we move on, have a look at the database (Figure 4.5). You’ll notice Django hasn’t saved the object to the database yet. This is by design. Accessing a database is expensive time-wise, so Django doesn’t hit the database until you explicitly tell Django to save the object.

Basic Data Access - 图1

Figure 4.5: Django doesn’t hit the database until the record is saved.

To save a record to the database, Django has a model method conveniently called save(). So, let’s save the record to the database:

  1. >>> event1.save()

You will probably get a warning from Django about saving a date without a timezone, but you can ignore it for now; we’re going to fix this warning shortly. Now, when you check the database, you can see the record has been added to the events_event table in the database (Figure 4.6).

Basic Data Access - 图2

Figure 4.6: The record is added when you call the save() method.

As creating and saving objects is so common, Django provides the create() method as a convenient shortcut to create and save a record in one step:

  1. >>> Event.objects.create(
  2. ...     name="Xmas Barbeque",
  3. ...     event_date="2020-12-24 12:00",
  4. ...     venue="Notareal Park",
  5. ...     manager="Bob"
  6. ... )
  7. # Ignore the timezone warning
  8. <Event: Event object (2)>

You can see in the above example, I have used the create() method to create a new event record which Django automatically saves for you.

Django and Naive Datetimes

We’ve ignored the previous timezone warnings, but this is not recommended. As Django and the databases it supports are all timezone aware, your apps shouldn’t use datetimes without timezone information (naive datetimes).

At the moment, Django will accept naive datetimes for backward compatibility, but there is no guarantee support for naive datetimes won’t be removed in later versions.

Let’s create one more event to use in our examples as we explore models in the Django shell. First, create a timezone-aware event date:

  1. >>> from datetime import datetime, timezone
  2. >>> event_date = datetime(2020,12,31,18,0, tzinfo=timezone.utc)

Now, we can create and save the event:

  1. >>> event3 = Event(
  2. ...     name="NYE Party",
  3. ...     event_date=event_date,
  4. ...     venue="McIvor's Bar",
  5. ...     manager="Terry"
  6. ... )
  7. >>> event3.save()

Or you can use the create() shortcut method to achieve the same result:

  1. >>> Event.objects.create(
  2. ...     name="NYE Party",
  3. ...     event_date=event_date,
  4. ...     venue="McIvor's Bar", 
  5. ...     manager="Terry"       
  6. ... )
  7. <Event: Event object (3)>
  8. >>>

Retrieving Records

Retrieve All Records

To retrieve all the records in a database table, you use the all() method:

  1. >>> event_list = Event.objects.all()
  2. >>> event_list
  3. <QuerySet [<Event: Event object (1)>, <Event: Event object (2)>, <Event: Event object (3)>]>

As you can see, this is not a very useful output—you have no way of knowing which event “Event Object” is referring to. Luckily, Python has a special string representation method you can add to Python functions and classes. Let’s go back to the Event model class declaration and add two lines of code (changes in bold):

  1. # \myclub_root\events\models.py
  3. 1  from django.db import models
  4. 2  
  5. 3  class Event(models.Model):
  6. 4      name = models.CharField('Event Name', max_length=120)
  7. 5      event_date = models.DateTimeField('Event Date')
  8. 6      venue = models.CharField(max_length=120)
  9. 7      manager = models.CharField(max_length = 60)
  10. 8      description = models.TextField(blank=True)
  11. 9 
  12. 10    def __str__(self):
  13. 11        return self.name

The magic is in lines 10 and 11. The __str__ method generates a string representation of any Python object. For the Event model, we are simply returning the name of the event. Restart the Django interactive shell for the changes to take effect. After you have added the __str__ method, your terminal output should look like this:

  1. >>> from events.models import Event
  2. >>> event_list = Event.objects.all()
  3. >>> event_list
  4. <QuerySet [<Event: Test Event1>, <Event: Xmas Barbeque>, <Event: NYE Party>]>

Much better.

Retrieve a Single Record

You retrieve single records with the get() method. You can retrieve a record using its primary key:

  1. >>> Event.objects.get(id=1)
  2. <Event: Test Event1>

You can also retrieve a record using one of the field names:

  1. >>> Event.objects.get(name="Xmas Barbeque")
  2. <Event: Xmas Barbeque>

The get() method only works for single objects. If your search term returns multiple records, you will get an error:

  1. >>> Event.objects.get(manager="Bob")
  2. Traceback (most recent call last):
  4. # Lots of traceback info
  6. ... get() returned more than one Event -- it returned 2!
  7. >>>

And if you try to retrieve a record that doesn’t exist, Django will throw a DoesNotExist error:

  1. >>> Event.objects.get(id=999)
  2. Traceback (most recent call last):
  4. # Lots of traceback info
  6. events.models.Event.DoesNotExist: Event matching query does not exist.
  7. >>>

Retrieve Multiple Records

Whereas the get() method can only return a single record, the filter() method allows you to filter your data to return zero or more records.

You can filter with a single search term:

  1. >>> Event.objects.filter(manager="Bob")
  2. <QuerySet [<Event: Test Event1>, <Event: Xmas Barbeque>]>

You can also filter with multiple search terms:

  1. >>> Event.objects.filter(manager="Bob", venue="Notareal Park")
  2. <QuerySet [<Event: Xmas Barbeque>]>

In both the above cases, Django translates the search terms into an SQL WHERE clause.

By default, the filter method uses exact match lookups. For more control, you can search using one of Django’s field lookups:

  1. >>> Event.objects.filter(manager="Bob", name__contains="Xmas")
  2. <QuerySet [<Event: Xmas Barbeque>]>

Note there’s a double underscore between name and contains. If you’re curious, the __contains part gets translated by Django into an SQL LIKE statement. See Chapter 9 for an in-depth review of field lookups.

If the search string doesn’t match, filter() will return an empty QuerySet:

  1. >>> Event.objects.filter(manager="Fred")
  2. <QuerySet []>
  3. >>>

If you want to return all records in the database table with filter, you leave the query string blank:

  1. >>> Event.objects.filter()
  2. <QuerySet [<Event: Test Event1>, <Event: Xmas Barbeque>, <Event: NYE Party>]>
  3. >>>

This is equivalent to using Event.objects.all().

Ordering Data

In the previous examples, records are being retrieved from the database in table order. If you want to sort the records by a field in the table, you use the order_by() method:

  1. >>> Event.objects.order_by("name")
  2. <QuerySet [<Event: NYE Party>, <Event: Test Event1>, <Event: Xmas Barbeque>]>

If you want to sort in descending order, you add a minus (-) sign before the field name:

  1. >>> Event.objects.order_by("-name")
  2. <QuerySet [<Event: Xmas Barbeque>, <Event: Test Event1>, <Event: NYE Party>]>
  3. >>>

Internally, Django translates the order_by() method into an SQL ORDER BY statement.

You can also sort by multiple fields:

  1. >>> Event.objects.order_by("manager", "name")
  2. <QuerySet [<Event: Test Event1>, <Event: Xmas Barbeque>, <Event: NYE Party>]>
  3. >>>

Model Default Ordering

You can see that this would get tedious if you wanted a default ordering for your model and had to use the order_by() method every time you retrieved records from the database.

Django solves this issue with the ordering attribute of Django models’ class Meta method. More about the class Meta method in Chapter 9.

It’s very common to want to set a sort order on a subset of your database records. You achieve this in Django by chaining lookups. This is best illustrated with an example:

  1. >>> Event.objects.filter(manager="Bob").order_by("name")
  2. <QuerySet [<Event: Test Event1>, <Event: Xmas Barbeque>]>
  3. >>>

In this example, the filter() method first retrieves all events managed by Bob, and then the order_by() method sorts them in alphabetical order by event name.

Slicing Data

You can also return a fixed number of rows from the database using Python’s list slicing syntax. This is handy for when you are paginating data or want to return the Top 10, say, of a QuerySet.

For example, you can retrieve the earliest event in the database:

  1. >>> Event.objects.all().order_by("event_date")[0]
  2. <Event: Test Event1>

Or the latest:

  1. >>> Event.objects.all().order_by("-event_date")[0]
  2. <Event: NYE Party>

You can even specify a subset of records to retrieve:

  1. >>> Event.objects.all()[1:3]
  2. <QuerySet [<Event: Xmas Barbeque>, <Event: NYE Party>]>
  3. >>>

What you can’t do is use negative indexing. So, to retrieve Bob’s last event, this won’t work:

  1. >>> Event.objects.filter(manager="Bob")[-1]
  2. Traceback (most recent call last):
  4. # More traceback info
  6. AssertionError: Negative indexing is not supported.

To get around this limitation, you use a reverse sort:

  1. >>> Event.objects.filter(manager="Bob").order_by("-event_date")[0]
  2. <Event: Xmas Barbeque>
  3. >>>

Updating Records

Earlier in the chapter, we used Django’s database API to create a new record. Let’s follow the same process and add another record:

  1. >>> from datetime import datetime, timezone
  2. >>> event_date = datetime(2020,9,8,15,0, tzinfo=timezone.utc)
  3. >>> event4 = Event(
  4. ...     name="Bob's Birthday",
  5. ...     event_date=event_date,
  6. ...     venue="McIvor's Bar",
  7. ...     manager="Terry"
  8. ... )
  9. >>> event4.save()

You may have noticed our Event model class doesn’t have a primary (unique) key field defined. This is because Django creates a primary key automatically when it adds a record to the database. Django generates the primary key using the AUTOINCREMENT function of your database. In SQLite, AUTOINCREMENT returns an integer which you can access via the object’s id field:

  1. >>> event4.id
  2. 4

You can see in Figure 4.7 that this is the same key used by the database.

Basic Data Access - 图3

Figure 4.7: The Event object primary key is the same as the database record unique ID.

To update records, you change the instance data and call the save() method again. Each subsequent call to the save() method will update the record:

  1. >>> event_date = datetime(2020,9,8,17,0, tzinfo=timezone.utc)
  2. >>> event4.event_date = event_date
  3. >>> event4.save()

If you check out the database table, you can see the updated event data (Figure 4.8).

Basic Data Access - 图4

Figure 4.8: Changing instance data and calling the save() method updates the current record.

While this method of updating a database record is simple, it’s inefficient as it will save all the field values, not just the event date. To ensure records are updated in the most efficient manner possible, use the update() method. Rewriting the above process to use the update() method, you get:

  1. >>> Event.objects.filter(id=4).update(event_date=event_date)
  2. 1
  3. >>>

update() has an integer return value—the number of records updated.

You can also use update() to modify multiple records. For example, say you want to move all the events at McIvor’s Bar to Rippemoff Casino, you can use the following code:

  1. >>> Event.objects.filter(venue="McIvor's Bar").update(venue="Ripemoff Casino")
  2. 2
  3. >>>

The return value tells you that update() has changed two records in the database. If you check the database, you will also see that all events at the bar have moved to the casino (Figure 4.9).

Basic Data Access - 图5

Figure 4.9: The update() method makes updating multiple records easy.

Deleting Records

To delete a record from the database, you use the delete() method:

  1. >>> Event.objects.filter(name__contains="Test").delete()
  2. (1, {'events.Event': 1})
  3. >>>

The return value for the delete() method lists the total number of records affected (one in this example), a dictionary listing the tables affected by the delete operation, and the number of records deleted in each table.

You can delete multiple objects by using a filter that returns more than one record:

  1. >>> Event.objects.filter(manager="Terry").delete()
  2. (2, {'events.Event': 2})
  3. >>>

And you can delete all the records in a table with the all() method:

  1. >>> Event.objects.all().delete()
  2. (1, {'events.Event': 1})
  3. >>> Event.objects.all()
  4. <QuerySet []>
  5. >>>

To prevent accidental deletion of all the data in a table, Django requires you to use the all() method explicitly to delete everything in a table. For example, this code doesn’t work:

  1. >>> Event.objects.delete()
  2. Traceback (most recent call last):
  3. File "<console>", line 1, in <module>
  4. AttributeError: 'Manager' object has no attribute 'delete'
  5. >>>