Python Data Presentation With Django And Legacy Sqlite Database -- Per Erik Strandberg

Background

In the text Python Data Analysis With Sqlite And Pandas I presented one way of setting up an Sqlite database, store many values and then do some Data Analysis With Python, in particular with the tools in SciPy.

If you have not read that text then I could just mention that we simulate a nail factory where different products (nails) are being produced in different production lines. Each line has can create one or more types of products and every produced nail is scanned with one or more sensors. These sensor readings (roughly 600-700 thousand entries is created in the example in Python Data Analysis With Sqlite And Pandas) are central to this text. As you might remember the verdicts given by the sensors could be OK (32), FAIL (33) or UNREADABLE (64).

In this text my goal is to follow up on this database, set up a Django site around it and present the values in the database in some simple way. I also want to add some views so that an operator can manually override the UNREADABLE readings with a comment feature.

Historic Note

Back in 2007 and 2008 I used to work a lot with Django (W: [1], H: [2]) and Plone (W: [3], H: [4]). Some really old texts are Django First Contact and My First Minimal Plone Content Type. Back then Django was a lot less mature and you had to take care of things like sql injection attacks all by yourself, whereas Plone was a more complete (but very large) framework.

Installing Django

Django now uses LTS versions and when I write this text the current LTS is 1.8. For Django this means that "...security and data loss fixes will be applied for at least the next three years...", excellent.

But on the Debian and Ubuntu machines I typically use I don't get this version when I use apt-get install, so I use pip instead:

$ sudo apt-get remove python-django
[...]
After this operation, 27,4 MB disk space will be freed.
Do you want to continue? [Y/n] y
[...]

$ sudo apt-get install python-pip
[...]

$ sudo pip install django==1.8
Downloading/unpacking django==1.8
  Downloading Django-1.8-py2.py3-none-any.whl (6.2MB): 6.2MB downloaded
Installing collected packages: django
Successfully installed django
Cleaning up...

$ django-admin --version
1.8

Start up a site

We setup a new Django project called legacydb. (If you have used the database from Python Data Analysis With Sqlite And Pandas you need to remember to copy it into the site.)

We also create a placeholder app called readings for the database.

$ django-admin startproject legacydb

$ mv -v database.data ~/legacydb/db.sqlite3
‘database.data’ -> ‘/home/per9000/legacydb/db.sqlite3’

$ cd legacydb/

$ python manage.py startapp readings

There are just a few things we need to do now in order to log in and use the classic Django admin interface:

Register the app
Create the models
Register the models
Migrate the database for Django

We register the app with settings.py:

$ grep -A8 INSTALLED legacydb/settings.py
INSTALLED_APPS = (
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    'readings',
)

The inspectdb argument creates some basic models for us that we can use right away:

$ python manage.py inspectdb > readings/models.py

$ grep class readings/models.py | grep -v Meta
class Line(models.Model):
class Product(models.Model):
class Reading(models.Model):
class Sensor(models.Model):

We register these models in readings/admin.py:

from django.contrib import admin

# Register your models here.
from .models import Line, Product, Reading, Sensor

admin.site.register(Line)
admin.site.register(Product)
admin.site.register(Reading)
admin.site.register(Sensor)

We trigger the migration of the database with makemigrations and migrate:

$ python manage.py makemigrations readings
Migrations for 'readings':
  0001_initial.py:
    - Create model Line
    - Create model Product
    - Create model Reading
    - Create model Sensor

# not sure this had any effect
$ python manage.py sqlmigrate readings 0001

$ sqlite3 db.sqlite3 
SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
django_migrations  product            sensor           
line               reading          
[...]

$ python manage.py migrate
Operations to perform:
  Synchronize unmigrated apps: staticfiles, messages
  Apply all migrations: admin, contenttypes, sessions, auth, readings
Synchronizing apps without migrations:
  Creating tables...
    Running deferred SQL...
  Installing custom SQL...
Running migrations:
  Rendering model states... DONE
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying contenttypes.0002_remove_content_type_name... OK
  Applying auth.0002_alter_permission_name_max_length... OK
  Applying auth.0003_alter_user_email_max_length... OK
  Applying auth.0004_alter_user_username_opts... OK
  Applying auth.0005_alter_user_last_login_null... OK
  Applying auth.0006_require_contenttypes_0002... OK
  Applying readings.0001_initial... OK
  Applying sessions.0001_initial... OK

$ sqlite3 db.sqlite3 
SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
auth_group                  django_content_type       
auth_group_permissions      django_migrations         
auth_permission             django_session            
auth_user                   line                      
auth_user_groups            product                   
auth_user_user_permissions  reading                   
django_admin_log            sensor                    
[...]

That should be it. Now we create a superuser and start the site:

$  python manage.py createsuperuser
Username (leave blank to use 'per9000'): 
[...]
Superuser created successfully.

$ python manage.py runserver
Performing system checks...
[...]

Cosmetic Fixes

One thing that is mentioned in the Django tutorial (see [5]), is the boring standard presentation of objects in the admin interface. All readings are listed as "Reading object". We can change this and the other models by adding some representation methods, for example:

    def __str__(self):
        return "Reading %s: %s" % (self.rid, self.verdict)

(Please note that str might have to be replaced with unicode depending on your version of Python and/or Django.)

In order to keep the database consistent we want to better respect some foreign keys, for in he readings table:

    pid = models.ForeignKey(Product, db_column='pid', null=False)
    lid = models.ForeignKey(Line, db_column='lid', null=False)
    sid = models.ForeignKey(Sensor, db_column='sid', null=False)

    #pid = models.IntegerField()
    #lid = models.IntegerField()
    #sid = models.IntegerField()

Model, View, Controller

OK, so far we have done the tricky part. Now let's add a new model and take a look at the views and controllers.

[to be continued]