Main Content

Defining database relations in your Django model

Archive - Originally posted on "The Horse's Mouth" - 2012-03-02 19:08:53 - Graham Ellis

Each database table maps to a class in Django - and the beauty if that be the way you define the tables will work equally well with SQLite, MySQL, and other supported databases (you just have a database configuration block to set when you install your Django project).

Here is my models.py file from the example we have written over the last two days, during a Python and Django training course I have been running in Ireland.

  from django.db import models
 
  class Role(models.Model):
    name = models.CharField(max_length=24)
    required = models.IntegerField()
 
    def __unicode__ (self):
      return self.name + " (" + str(self.required) + ")"
 
  class People(models.Model):
    name = models.CharField(max_length=64)
    role = models.ForeignKey(Role)
    hours = models.FloatField()
    hobbies = models.TextField()
 
    def __unicode__ (self):
      return self.name + " (" + str(self.role) + ")"

You'll note that the field names are all Python methods which create appropriate SQL code internally, tuned for the particular database that's in use. The ForeignKey is particularly noteworthy. In this example, I wanted to join my People table to my Roles table in a one to many mapping, so I defined my Role table first and then included it as a Foreign Key in my People table. The Django methods do all the clever stuff, such as adding in a unique primary key to each table, and linking the foreign key field in the People table to that primary ID in the roles table.

Here's the code that's actually used for the above example when you syncdb:

  munchkin:training grahamellis$ python manage.py sql staff
  BEGIN;
  CREATE TABLE "staff_role" (
    "id" integer NOT NULL PRIMARY KEY,
    "name" varchar(24) NOT NULL,
    "required" integer NOT NULL
  )
  ;
  CREATE TABLE "staff_people" (
    "id" integer NOT NULL PRIMARY KEY,
    "name" varchar(64) NOT NULL,
    "role_id" integer NOT NULL REFERENCES "staff_role" ("id"),
    "hours" real NOT NULL,
    "hobbies" text NOT NULL
    )
    ;
    COMMIT;

I've already added a __unicode method to each of the classes so that I can print out the object / display it in my web page meaningfully, and I could add other business logic / methods to be run non the data here too. In fact, Django provides a very useful database access tool away from the web too, and so you'll want to reuse your code - remember DRY - Don't Repeat Yourself!

Here are some examples of the methods of models.Model in use to select data from the database:

Matching by criteria

  >>> Role.objects.filter(required=3)
  [<Role: Engineer (3)>]

  >>> Role.objects.filter(required=3)
  [<Role: Engineer (3)>]

  >>> Role.objects.filter(required__gt=1)
  [<Role: Engineer (3)>]

  >>> Role.objects.filter(required__gt=0)
  [<Role: Manager (1)>, <Role: Engineer (3)>]

  >>> Role.objects.filter(required__gt=3)
  []

  >>> Role.objects.filter(required__gte=3)
  [<Role: Engineer (3)>]

  >>> Role.objects.filter(required__gte=1)
  [<Role: Manager (1)>, <Role: Engineer (3)>]

  >>> Role.objects.filter(name__contains='ee')
  [<Role: Engineer (3)>]

  >>> Role.objects.filter(name__contains='er')
  [<Role: Manager (1)>, <Role: Engineer (3)>]

  >>> Role.objects.filter(name__regex=r'e.r')
  [<Role: Engineer (3)>]

  >>> Role.objects.filter(name__regex=r'e.r')
  [<Role: Engineer (3)>]

Matching by criteria in joined tables

  >>> People.objects.filter(role__required__gte=2)
  [<People: Fred Smiff (Engineer (3))>, <People: Jenny Jones (Engineer (3))>]

  >>> People.objects.filter(role__required__gte=2)
  [<People: Fred Smiff (Engineer (3))>, <People: Jenny Jones (Engineer (3))>]

Selecting non-matching records

  >>> People.objects.exclude(role__required__gte=2)
  [<People: Ford Prefect (Manager (1))>]

Selecting data by named columns, and returning a list of dictionaries

  >>> People.objects.all().values("name","hours")
  [{'hours': 27.5, 'name': u'Fred Smiff'}, {'hours': 40.0, 'name': u'Jenny Jones'}, {'hours': 48.0, 'name': u'Ford Prefect'}]