Using a Database

Introduction

In this example, we'll be creating a simple superheroes database with one table containing the following information:

Superhero Name Secret Identity Sex
Superman Clark Kent Male
Spider-Man Peter Parker Male
Boltie Libby Female
Captain America Steve Rogers Male
Green Lantern Hal Jordan Male
Wonder Woman Diana Prince Female
Wolverine Logan Male
Batgirl Barbara Gordon Female
Hulk Bruce Banner Male
Invisible Woman Susan Storm Richards Female
Thor Donald Blake Male
Black Widow Natasha Romanoff Female
Iron Man Anthony Stark Male
Batman Bruce Wayne Male
Ruby Thursday Thursday Rubinstein Female

Setting Up the Database and the Administration Site

  1. Edit the settings.py file so that we can connect to a sqlite3 database stored a in file called sigcse.db: 

    DATABASES = {
    'default': {
        'ENGINE': 'sqlite3', # Add 'postgresql_psycopg2', 'postgresql', 'mysql', 'sqlite3' or 'oracle'.
        'NAME': 'sigcse.db', # Or path to database file if using sqlite3.
        'USER': '',          # Not used with sqlite3.
        'PASSWORD': '',      # Not used with sqlite3.
        'HOST': '',          # Set to empty string for localhost. Not used with sqlite3.
        'PORT': '',          # Set to empty string for default. Not used with sqlite3.
    }
}

  2. Edit the settings.py file so that we can use the Django administration site and our application's database: 

    INSTALLED_APPS = (
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.sites',
    'django.contrib.messages',    
    # Uncomment the next line to enable the admin:
    'django.contrib.admin',
    # Uncomment the next line to enable admin documentation:
    # 'django.contrib.admindocs',
    'sigcse.examples',
)

  3. Define the application's model. Add the following code to the examples/models.py file: 

    from django.db import models

class Superhero(models.Model):
    name     = models.CharField(max_length=30)
    identity = models.CharField(max_length=30)
    sex      = models.CharField(max_length=6)
    
    def __unicode__(self):
        return u'(%s) %s' % (self.id, self.name)

    The name of the model (Superhero) must be a singular noun, and it must start with an uppercase letter.

  4. Specify the initial data for the database. For every model, Django will look for a file called sql/modelname.sql under your application's directory, where modelname is the model's name in lowercase.

    Create the sql folder under the examples folder. Then create the examples/sql/superhero.sql file with the following content: 

    insert into examples_superhero (name, identity, sex) 
    values ('Superman', 'Clark Kent', 'male');
insert into examples_superhero (name, identity, sex) 
    values ('Spider-Man', 'Peter Parker', 'male');
insert into examples_superhero (name, identity, sex) 
    values ('Boltie', 'Libby', 'female');
insert into examples_superhero (name, identity, sex) 
    values ('Captain America', 'Steve Rogers', 'male');
insert into examples_superhero (name, identity, sex) 
    values ('Green Lantern', 'Hal Jordan', 'male');
insert into examples_superhero (name, identity, sex) 
    values ('Wonder Woman', 'Diana Prince', 'female');
insert into examples_superhero (name, identity, sex) 
    values ('Wolverine', 'Logan', 'male');
insert into examples_superhero (name, identity, sex) 
    values ('Batgirl', 'Barbara Gordon', 'female');
insert into examples_superhero (name, identity, sex) 
    values ('Hulk', 'Bruce Banner', 'male');
insert into examples_superhero (name, identity, sex) 
    values ('Invisible Woman', 'Susan Storm Richards', 'female');
insert into examples_superhero (name, identity, sex) 
    values ('Thor', 'Donald Blake', 'male');
insert into examples_superhero (name, identity, sex) 
    values ('Black Widow', 'Natasha Romanoff', 'female');
insert into examples_superhero (name, identity, sex) 
    values ('Iron Man', 'Anthony Stark', 'male');
insert into examples_superhero (name, identity, sex) 
    values ('Batman', 'Bruce Wayne', 'male');
insert into examples_superhero (name, identity, sex) 
    values ('Ruby Thursday', 'Thursday Rubinstein', 'female');

    Note that the data for the model called Superhero is stored in a table called examples_superhero (the name of the application, followed by an underscore, followed by the name of the model in lowercase).

    Synchronize the model definitions with the database. Type at the command line: 

    python manage.py syncdb

    You'll be asked to create a superuser definition. Type yes, and provide the requested information.

  5. Add new information or inspect the database using the Django shell. Type at the command line: 

    python manage.py shell

    Type the following commands in the interactive shell in order to see how to use models from code written in Python: 

    from sigcse.examples.models import Superhero

# Create a new superhero instance
a = Superhero(name='Mr. Fantastic', identity='Reed Richards', sex='male')
a.save()

# Get a collection containing all the superheroes
b = Superhero.objects.all()

# Get a superhero with ID 7
c = Superhero.objects.get(id=7)

# Get the superhero's field values
d = c.id
e = c.name
f = c.sex
g = c.identity

# Delete the superhero
c.delete()

# Update Black Widow's identity 
h = Superhero.objects.get(name='Black Widow')
h.identity = 'Natalia Romanova'
h.save()

# Obtain all female superheroes
i = Superhero.objects.filter(sex='female')

# Get all superheroes sorted by name
j = Superhero.objects.order_by('name')

# Select all superheroes that contain "man" in their name
k = Superhero.objects.filter(name__contains='man')

# Select all male superheroes, sort result by identity in descending order
l = Superhero.objects.filter(sex='male').order_by('-identity')

  6. Register the model so that it can be managed using the administration site. Create a file named admin.py in the examples folder with the following content: 

    from django.contrib.admin import site
from sigcse.examples.models import Superhero

site.register(Superhero)

  7. Edit the urls.py file. Uncomment the highlighted lines in order to define a URL for accessing the Django administration site: 

    from django.conf.urls.defaults import patterns, include, url

# Uncomment the next two lines to enable the admin:
from django.contrib import admin
admin.autodiscover()

urlpatterns = patterns('',
    # Examples:
    # url(r'^$', 'sigcse.views.home', name='home'),
    # url(r'^sigcse/', include('sigcse.foo.urls')),
    (r'^foo/', 'sigcse.examples.views.foo'),
    (r'^game/(rock|paper|scissors)/', 'sigcse.game.views.play'),

    # Uncomment the admin/doc line below to enable admin documentation:
    # url(r'^admin/doc/', include('django.contrib.admindocs.urls')),

    # Uncomment the next line to enable the admin:
    url(r'^admin/', include(admin.site.urls)),
    
)

  8. Open the administration site. Using your web browser, visit this following URL: http://localhost:8000/admin/ (open link in a new tab)

    The administration site allows you to do basic CRUD (Create, Read, Update, Delete) operations. Try several of these operations to see how they work.

  9. Check the database contents using the SQLite 3 shell. Type at the command line: 

    python manage.py dbshell

    Type the following commands in the database shell and interpret the results: 

    .schema

select * from examples_superhero;

Exercises

  1. Write a view/template that displays, in ascending order, all the names of the superheroes contained in the database. Use the <ul> and <li> tags to produce an unordered (bulleted) list. The corresponding URL should be: http://localhost:8000/exercise_1/ (open link in a new tab)

    The expected output should look something like this:

  2. Write a view/template that displays the secret identity, in descending order, of all the female superheroes contained in the database. Use the <ol> and <li> tags to produce an ordered (numbered) list. The corresponding URL should be: http://localhost:8000/exercise_2/ (open link in a new tab)

    Expected output:

  3. Write a view/template that displays the name, in descending order, and the secret identity of all the male superheroes contained in the database. Use the <dl>, <dt>, and <dd> tags to produce a definition list. The corresponding URL should be: http://localhost:8000/exercise_3/ (open link in a new tab)

    The expected output should be something like this:

  4. Write a view/template that displays in an HTML table all the information about the superheroes contained in the database. Use the <table>, <tr>, <th>, and <dt> tags to produce the table. The corresponding URL should be: http://localhost:8000/exercise_4/ (open link in a new tab)

    The expected output: