In my last article, I continued looking at the Django Web framework, showing how you can create and modify models. As you saw, Django expects you to describe your models using Python code. The model description is then transformed into SQL and compared with any previous version of the model that might have existed. Django then creates a "migration", a file that describes how you can move from one version of the model definition to the next. A migration is a fantastic tool, one that allows developers to move their database forward (and backward) in defined chunks. Migrations make it easier to collaborate with others and upgrade existing applications.

The thing is, migrations have little or nothing to do with the day-to-day application that you want to run. They are useful for the creation and maintenance of your application's models, but in your application, you're going to want to use the models themselves.

So in this article, I look at Django's ORM (object-relational mapper). You'll see how how Django allows you to perform all the traditional CRUD (create-read-update-delete) actions you need and expect within your application, so that you can use a database to power your Web application.

For the purposes of this article, I'll be using the "atfapp" application within the "atfapp" project that I created in last month's article. The model, of an appointment calendar, is defined as follows in atfapp/models.py:

class Appointment(models.Model):
    starts_at = models.DateTimeField()
    ends_at = models.DateTimeField()
    meeting_with = models.TextField()
    notes = models.TextField()
    minutes = models.TextField()
    def __str__(self):
        return "{} - {}: Meeting with {} 
           ↪({})".format(self.starts_at,
                                self.ends_at,
                                self.meeting_with,
                                self.notes)

As you can see, the above model has four fields, indicating when the meeting starts, ends, with whom you are meeting and notes for before the meeting starts. The first two fields are defined to be DateTime fields in Django, which is translated into an SQL TIMESTAMP time in the database.

Creating a New Appointment

The easiest and best way to get your hands dirty with Django models is to use the Django interactive shell—meaning, the Python interactive shell within the Django environment. Within your project, just type:

django-admin shell

and you'll be placed in the interactive Python interpreter—or if you have it installed, in IPython. At this point, you can start to interact with your project and its various applications. In order to work with your Appointment object, you need to import it. Thus, the first thing I do is write:

from atfapp.models import Appointment

This tells Django that I want to go into the "atfapp" package—and since Django applications are Python packages, this means the "atfapp" subdirectory—and then import the "Appointment" class from the models.py module.

The important thing to remember is that a Django model is just a Python class. The ORM magic occurs because your class inherits from models.Model and because of the class attributes that you use to define the columns in the database. The better you understand Python objects, the more comfortable you'll feel with Django models.

If you want to create a new appointment object, you can do what you normally would do with a Python object:

>>> a = Appointment()

Sure enough, if you ask "a" about itself, it'll tell you:

>>> type(a)
atfapp.models.Appointment

The first thing you might try to do is save your new appointment to the database. You can do this with the "save" method:

>>> a.save()

However, as you'll quickly discover if you try to do this, you get an exception—an IntegrityError, as the exception is named, which looks like this:

    IntegrityError: NOT NULL constraint failed:
atfapp_appointment.starts_at

Here, Django is mixing Python and SQL to tell you what went wrong. You defined your model such that it requires a starts_at column, which is translated into a NOT NULL constraint within the database. Because you have not defined a starts_at value for your appointment object, your data cannot be stored in the database.

Indeed, if you simply get the printed representation of your object, you'll see that this is the case:

>>> a
<Appointment: None - None: Meeting with  ()>

The above output comes from the __str__ instance method, which you can see was defined above. The new object has None values for starts_at, ends_at and meeting_with. Note that you don't have None values for meeting_with and notes. That's because the former are defined as DateTimeField, whereas the latter are defined as TextField.

By default, Django models are defined such that their columns in the database are NOT NULL. This is a good thing, I think. NULL values cause all sorts of problems, and it's better to have to name them explicitly. If you want a field to allow NULL values, you need to pass the null=True option, as in:

starts_at = models.DateTimeField(null=True)

However, I'm not interested in NULL values for starting and ending times. Thus, if you want to store your appointment, you'll need to supply some values. You can do that by assigning to the fields in question:

>>> from datetime import datetime
>>> a.starts_at = datetime.now()
>>> a.ends_at = datetime(2015, 4, 28, 6,43)

Once you've done that, you can save it:

>>> a.save()

Another way to create your model would be to pass the parameters at creation time:

>>> b = Appointment(starts_at=datetime.now(),
        ends_at=datetime.now(),
        meeting_with='VIP', notes='Do not be late')

Reading Your Appointment Back

Now that you have two appointments, let's try to read them back and see what you can do with them. Access to the objects you have created in the database is done through the "objects" attribute, known as a "manager" in Django. The "all" method on objects gives you all of your objects back:

>>> len(Appointment.objects.all())
2

You can use your column names as attributes on each object:

>>> for a in Appointment.objects.all():
    print "{}: {}".format(a.starts_at, a.notes)

2015-04-28 05:59:21.316011+00:00:
2015-04-28 07:14:07.872681+00:00: Do not be late

Appointment.objects.all() returns an object known in Django as a QuerySet. A QuerySet, as you can see above, is iterable. And, if you call len() on it, or even if you ask for its representation (for example, in the Python shell), you'll see it displayed as a list. So you might think that you're talking about a list here, which potentially means using a great deal of memory.

But, the Django development folks have been quite clever about things, and a QuerySet is actually an iterator—meaning that it tries as hard as possible not to retrieve a large number of records into memory at once, but to use "lazy loading" to wait until the information is truly needed. Indeed, just creating a QuerySet has no effect on the database; only when you actually try to use the QuerySet's objects does the query run.

It's nice to be able to get all of the records back, but what's even more useful and important is to be able to select individual records and then to order them.

For this, you can apply the "filter" method to your manager:

>>> for a in Appointment.objects.filter(meeting_with='VIP'):
        print a.starts_at

Now you know when your appointments with a VIP will be starting. But, what if you want to search for a range of things, such as all of the appointments since January 1st, 2015?

Django provides a number of special methods that perform such comparisons. For each field that you have defined in your model, Django defines __lt, __lte, __gt and __gte methods that you can use to filter query sets. For example, to find all of the appointments since January 1st, 2015, you can say:

>>> Appointment.objects.filter(starts_at__gte=datetime(2015,1,1))

As you can see, because you have a starts_at field name, Django accepts a starts_at__gte keyword, which is turned into the appropriate operator. If you pass more than one keyword, Django will combine them with AND in the underlying SQL.

QuerySets can be filtered in more sophisticated ways too. For example, you might want to compare a field with NULL. In that case, you cannot use the = operator in SQL, but rather, you must use the IS operator. Thus, you might want to use something like this:

>>> Appointment.objects.filter(notes__exact=None)

Notice that __exact knows to apply the appropriate comparison, based on whether it was given None (which is turned into SQL's NULL) or another value.

You can ask whether a field contains a string:

>>> Appointment.objects.filter(meeting_with__contains='VIP')

If you don't care about case sensitivity, you can use icontains instead:

>>> Appointment.objects.filter(meeting_with__icontains='VIP')

Don't make the mistake of adding % characters to the front and back of the string for which you're searching. Django will do that for you, turning the icontains filter parameter into an SQL ILIKE query.

You even can use slice notation on a QuerySet in order to get the effects of OFFSET and LIMIT. However, it's important to remember that in many databases, the uses of OFFSET and LIMIT can lead to performance issues.

Django, by default, defines an "id" field that represents a numeric primary key for each record stored. If you know the ID, you can search based on that, using the get method:

>>> Appointment.objects.get(pk=2)

If there is a record with this primary key, it'll be returned. If not, you'll get a DoesNotExist exception.

Finally, you also can sort the records that are returned using the order_by method. For example:

>>> Appointment.objects.filter
↪(starts_at__gte=datetime(2015,1,1)).order_by('id')

What if you want to reverse the ordering? Just preface the name of the column with a - sign:

>>> Appointment.objects.filter
↪(starts_at__gte=datetime(2015,1,1)).order_by('-id')

You can pass multiple arguments to order_by if you want to order (ascending or descending) by a combination of columns.

One nice feature of Django's QuerySets is that every call to filter or order_by returns a new QuerySet object. In this way, you can make your calls to filter all at once or incrementally. Moreover, you can create one QuerySet and then use that as the basis for further QuerySets, each of which will execute (when necessary) its query independently.

A big problem with creating dynamic queries is that of SQL injection—that users can, through the use of manipulation, force their own SQL to be executed, rather than what you intended. Using Django's QuerySets basically removes this threat, because it checks and appropriately quotes any parameters it receives before passing their values along to SQL. Really, there's no excuse nowadays for SQL injection to be a problem—please think twice (or three times) before trying to work around Django's safeguards.

Updating and Deleting

Updating the fields of a Django model is trivially easy. Modify one or more attributes, as you would with any other Python object and then save the updated object. Here, I load the first (unordered) record from the database before updating it:

>>> a = Appointment.objects.first()
>>> a.notes = 'blah blah'
>>> a.save()

Note that if you change the "id" attribute and then save your object, you'll end up creating a new record in the database! Of course, you shouldn't be changing the "id" of an object in any event, but now you can consider yourself warned as well.

To delete an object, just use the delete method on the instance. For example:

>>> len(Appointment.objects.all())
2

>>> a = Appointment.objects.first()
>>> a.delete()

>>> len(Appointment.objects.all())
>>> 1

As you can see, in the above example, I found that there is a total of two records in my database. I load the first and then delete it. Following that call—no need for saving or otherwise approving this action—you can see that the record is removed.

Conclusion

In my next article, I'll finish this series on Django with a discussion of the different types of relationships you can have across different models. I'll look at one-to-one, one-to-many and many-to-many relationships, and how Django lets you express and work with each of them.

Resources

The main site for Django is http://DjangoProject.com, and it has a great deal of excellent documentation, including a tutorial. Several pages are dedicated to QuerySets and how you can create and manipulate them.

Information about Python, in which Django is implemented, is at http://python.org.