Using Django on existing data


In this new entry, we will continue our Simpsons quote search engine. Up to this point, we have been accessing only data created by the application. Now, we are going to access to a pre-existing database. This is a very important step, because it enables us to create an interface to a more complex data collection and processing system that uses other technologies to create the database.

So, let’s suppose we have a MySQL database simpsons_db in our local server holding our Simpsons quotes in table simpsons_quotes. This table will have 4 columns, just like our SimpsonQuote model had. The SQL code for creating this table is:

CREATE TABLE IF NOT EXISTS `simpson_quote` (
  `content` text NOT NULL,
  `character` varchar(100) NOT NULL,
  `episode` int(11) NOT NULL,
  `season` int(11) NOT NULL,

We can then fill this table using INSERT commands, or tools such as HeidiSQL or PHPMyAdmin.

Next step will be to rewrite our SimpsonQuote model so it uses a pre-existing table. For that we will actually replace our file, using a Django command. Before that, we will change the configuration of the database backend in SQProject/ For that, we will add these two lines in te beggining of the file:

import pymysql

This will use the pymysql library; which will save you of a great deal of frustration. You will have to install it before with pip install pymysql. Next, in the same file, we will find the lines that contain:

    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),

and change it by:

    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'simpsons_db',
        'USER': 'your_user',
        'PASSWORD': 'your_password',
        'HOST': '',
        'PORT': '3306',

This configuration will change the original SQLite backend to a MySQL in the same server and database where our data is contained. With this configuration, we can now run the following command to create a model based on the data: python inspectdb > SQapp/ This will crush our old and beloved file. Now it will contain the following:

from __future__ import unicode_literals

from django.db import models

class SimpsonQuote(models.Model):
    content = models.TextField()
    character = models.CharField(max_length=100)
    episode = models.IntegerField()
    season = models.IntegerField()

    class Meta:
        managed = False
        db_table = 'simpson_quote'

Note that, for simplicity, we have used a name for the table and fields so that the resulting model has the same names as the original model. This will prevent us from having to rewrite the rest of the code. If the names are different, we will either have to rewrite our code (in a normal workflow, we will first create this model and afterwards the code to manipulate it) or map the field names to the column names, as shown here. Before using the application, we will have to sync the database:

python migrate
python makemigrations
python migrate

Finally, we can run the server with python runserver, and use curl as shown in the last entry.

This may seem like a minor update, but think of it more deeply; we can now serve exisiting databases; databases that are continuously updated by a third party system. This starts to look more like a serious data source. In the next entry, we will add user authentication to our app, and round some edges.

Leave a Reply

Your email address will not be published. Required fields are marked *