Using a Database

Introduction

We will develop an application that allows us to take online quizzes. The database will be used to store several multiple choice questions.

Each question consists of a stem and a set of options. The stem is the question to be asked. The options are the possible answers to choose from, with the correct answer called the key and the incorrect answers called distractors.

The following figure depicts our application's models:

Setting Up the Database and the Administration Site

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

DATABASES = {
    'default': {
        'ENGINE': 'sqlite3', # Add 'postgresql_psycopg2', 'postgresql', 'mysql', 'sqlite3' or 'oracle'.
        'NAME': 'quiz.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.
    }
}

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',
)

Define the application models. Add the following code to the examples/models.py file:

from django.db import models

class Stem(models.Model):
    text        = models.CharField(max_length=200)

    def __unicode__(self):
        return self.text

class Option(models.Model):
    stem        = models.ForeignKey(Stem, related_name='options')
    text        = models.CharField(max_length=50)
    answer_key  = models.BooleanField()

    def __unicode__(self):
        return self.text

Note that the ForeignKey field type allows us to establish a many-to-one relationship between the Option and the Stem models. The related_name argument will indicate the name to use for the relation from the related model back to this one.

Specify some 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/stem.sql file with the following content:

insert into examples_stem (text) 
    values ('What was the first computer to defeat a world champion chess player?');
insert into examples_stem (text) 
    values ('What was the first commercial computer produced in the United States?');
insert into examples_stem (text) 
    values ('Who created the social networking site Facebook?');

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

Now create the examples/sql/option.sql file with the following content:

insert into examples_option (stem_id, text, answer_key) 
    values (1, 'Deep Blue', 1);
insert into examples_option (stem_id, text, answer_key) 
    values (1, 'Colossus', 0);
insert into examples_option (stem_id, text, answer_key) 
    values (1, 'WOPR', 0);
insert into examples_option (stem_id, text, answer_key) 
    values (1, 'Skynet', 0);
insert into examples_option (stem_id, text, answer_key) 
    values (2, 'UNIVAC I', 1);
insert into examples_option (stem_id, text, answer_key) 
    values (2, 'ENIAC', 0);
insert into examples_option (stem_id, text, answer_key) 
    values (2, 'Zuse Z3', 0);
insert into examples_option (stem_id, text, answer_key) 
    values (2, 'Difference Engine', 0);
insert into examples_option (stem_id, text, answer_key) 
    values (3, 'Mark Zuckerberg', 1);
insert into examples_option (stem_id, text, answer_key) 
    values (3, 'Steve Jobs', 0);
insert into examples_option (stem_id, text, answer_key) 
    values (3, 'Bill Gates', 0);
insert into examples_option (stem_id, text, answer_key) 
    values (3, 'Sergey Brin', 0);

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.

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 add a new option to the stem with ID = 3.
```
from sigcse.examples.models import Stem, Option
s = Stem.objects.get(id=3)
o = Option(stem=s, text='Lary Page', answer_key=False)
o.save()
```
The following command retrieves all the options associated to the previously selected stem:
```
s.options.all()
```
The expected output is:
```
[<Option: Mark Zuckerberg>, <Option: Steve Jobs>, 
 <Option: Bill Gates>, <Option: Sergey Brin>, 
 <Option: Lary Page>]
```
Register the models that are to 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 Stem, Option

site.register(Stem)
site.register(Option)
```

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 *

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

urlpatterns = patterns('',
    # Example:
    # (r'^sigcse/', include('sigcse.foo.urls')),
    (r'^foo/', 'sigcse.examples.views.foo'),    

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

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

Open the administration site. Using your web browser, visit this following URL: http://localhost:8000/admin/

The administration site allows you to do basic CRUD (Create, Read, Update, Delete) operations. Try several of these operations to see how they work.
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_stem;

select * from examples_option;
```

Exercises

Write a view/template that lists the text of all the quiz stems using an HTML unordered list. The corresponding URL should be: http://localhost:8000/stems/

The expected output would look something like this:
Write a view/template that lists the text of all the quiz stems with their corresponding answer option using an HTML unordered list. The URL should be: http://localhost:8000/stems_answers/

Expected output:

Web Development with Python and Django

Using a Database

Introduction

Setting Up the Database and the Administration Site

Exercises