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` ( `id` int NOT NULL AUTO_INCREMENT, `content` text NOT NULL, `character` varchar(100) NOT NULL, `episode` int(11) NOT NULL, `season` int(11) NOT NULL, PRIMARY KEY (ID) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
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 models.py file, using a Django command. Before that, we will change the configuration of the database backend in SQProject/settings.py. For that, we will add these two lines in te beggining of the file:
import pymysql pymysql.install_as_MySQLdb()
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:
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.sqlite3',
'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
}
}
and change it by:
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.mysql',
'NAME': 'simpsons_db',
'USER': 'your_user',
'PASSWORD': 'your_password',
'HOST': '127.0.0.1',
'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 manage.py inspectdb > SQapp/models.py. This will crush our old and beloved models.py 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 manage.py makemigrations
python manage.py migrate
Finally, we can run the server with python manage.py 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.