After creating your Django web application, you can start using Database models in Python and Django. There are the main steps to get your models and application working with a Database.

Create new models for data processing
Adding your models to the admin interface
Playing with your models to create, edit, filter and delete information
Upgrading your models with new attributes and methods
Integrating data in your application.

In this guide, we continue from the previous post Web Applications using Python and Django.

In this project we created a webapp project, and an example application. This is the main project structure.

webapp/
  > example
  > webapp
  - manage.py
  - db.sqlite3

In the rest of this post we refer to webapp to the project subfolder, and we use example as the application subfolder. The default settings for the database is using a SQLite database created in the root folder (db.sqlite3). This file will be created automatically during this guide, after running the migrations.

Creating a Database Model

You can create database models in Django using Python code. Edit the file models.py inside the application folder (example/models.py):

from django.db import models

# Create your models here.

class Sender(models.Model):
    name = models.CharField(max_length=200)
    email = models.CharField(max_length=200)
    imageUrl = models.CharField(max_length=250)
    def __str__(self):
        return self.name

class Message(models.Model):
    sender = models.CharField(max_length=200)
    recipient = models.CharField(max_length=200)
    message = models.CharField(max_length=200)
    visible = models.IntegerField(default=1)
    timestamp = models.DateTimeField('date created')
    def __str__(self):
        return self.message

This code helps to create 2 database tables: senders and messages. Each one can define their fields using the models object methods:

CharField() for text fields, with a defined max_length
IntegerField() for integer values, with a default value.
DateTimeField() to store date/time

The __str__(self) method is used to get a String representation of the object (like .toString() in Java). In each case, you can use the field or a combinations of values.

Create and run the migrations

Now you can create the tables defined in models.py by creating a migration file (A file to execute the creation of each table):

python manage.py makemigrations

Migrations for 'example':
example/migrations/0001_initial.py
- Create model Message
- Create model Sender

Now, a new file created example/migrations/0001_initial.py contains a set of commands to create your tables. The next step is to run the migration:

python manage.py migrate

Operations to perform:
  Apply all migrations: admin, auth, contenttypes, example, sessions
Running migrations:
  Applying example.0001_initial... OK

After that, your new tables will be created in the SQLite database. If you are insterested in view how it looks the migration code in plain SQL, you can use the sqlmigrate command:

python manage.py sqlmigrate example 0001

It will show a list of SQL commands:

BEGIN;
--
-- Create model Message
--
CREATE TABLE "example_message" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "sender" varchar(200) NOT NULL, "recipient" varchar(200) NOT NULL, "message" varchar(200) NOT NULL, "visible" integer NOT NULL, "timestamp" datetime NOT NULL);
--
-- Create model Sender
--
CREATE TABLE "example_sender" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "name" varchar(200) NOT NULL, "email" varchar(200) NOT NULL, "imageUrl" varchar(250) NOT NULL);
COMMIT;

Note: This output could vary between different versions of the SQLite database.

Using Django Database Operations

Instead of running specific and complicated SQL commands, you can play with the Database functions provided by Django and Python. You can start a special python console ready to work with your application and models:

python manage.py shell

Now you have a console to execute python commands. Some useful commands to execute:

from example.models import Sender, Message from django.utils import timezone

The first line imports the objects Sender and Message associated to the tables created in the migration. The second one imports the timezone object to deal with date/time operations.

Creating models

sender = Sender(email="me@example.com", name="Me Myself", imageUrl = "http://my/image.png")
sender.save()
sender.id

These lines created a new Sender object, filling their values. Then call save() to store this item in the database. After all, the sender will have a new ID (sender.id) assigned by the database.

Updating models

sender.name = "Real Name"
sender.save()

Now, these lines updated theSender object, replacing some values. Calling save() will update this row in the database.

Retrieving and Displaying models

>>> Sender.objects.all()
<QuerySet [<Sender: Real Name [me@example.com]>]>

Calling {ModelName}.objects.all() will retrieve a full list of objects stored in the database for this model. This last command will return a simple description for each object. Each model object must implement the _str_(self) method to help python to show this information in an human-readable way.

Filtering models

# Filter exact match 
Sender.objects.filter(id=1)
# Filter start text
Sender.objects.filter(email__startswith='me@')
# Filter date parts (year)
Message.objects.filter(timestamp__year=2018)
# Filter a related field in an foreign table
Message.objects.filter(sender__email="me@example.com") 
# Get an unique object using the primary key value
Sender.objects.get(pk=1)

Using objects.filter(params) you can do different types of filters:

field=value : Simple filter, field equals to a value
field__startswith=txt : Filter for text starting with a specific text. (Note the double _ _ to separate field and filter)
datefield__year=value : Filter by a part of the date (year, month)

The objects.get(params) method is to retrieve an unique item using the filter.

Deleting models

sender = Sender.objects.get(pk=1)
sender.delete()

Using delete() in an model object will delete this item from the database.

Include models in the admin module

You can easily create an administration page for your models using the django.contrib.admin component. Edit the admin.py file in the application folder (example/admin.py):

from django.contrib import admin

from .models import Sender
from .models import Message

admin.site.register(Sender)
admin.site.register(Message)

Now, when you run your server (python manage.py runserver), you will get additional options in your admin interface:

Upgrading your models

Many times you need to modify or redefine your models adding new fields, adding or modifying relationships. In this case, we want to upgrade the Message model to make the sender and recipient attributes to use a relationship (Foreign key) with the Sender model. The original models looks like this (in example/models.py):

class Message(models.Model):
    sender = models.CharField(max_length=200)
    recipient = models.CharField(max_length=200)
    message = models.CharField(max_length=200)
    visible = models.IntegerField(default=1)
    timestamp = models.DateTimeField('date created')
    def __str__(self):
        return self.message

Actually, sender and recipient are normal fields not related with any other model. We will change this to create two related fields, both to a Sender model:

class Message(models.Model):
    sender = models.ForeignKey('Sender', on_delete=models.CASCADE, related_name='sender')
    recipient = models.ForeignKey('Sender', on_delete=models.CASCADE, related_name='recipient')
    message = models.CharField(max_length=200)
    visible = models.IntegerField(default=1)
    timestamp = models.DateTimeField('date created')
    def __str__(self):
        return self.message

Now the Message model is related to a Sender model.

To update this models and make new migrations. It’s better for a new project these 3 steps:

Delete the db.sqlite3 file
Remove the previous migration in example/migrations/0001_initial.py
Run again python manage.py makemigrations to create a new migration file.
Execute python manage.py migrate to apply the migration

What happens at database-level

If you are curious, you can execute the sqlmigrate command to see what happens in the database level:

python manage.py sqlmigrate example 0001

BEGIN;
--
-- Create model Message
--
CREATE TABLE "example_message" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "message" varchar(200) NOT NULL, "visible" integer NOT NULL, "timestamp" datetime NOT NULL);

--
-- Create model Sender
--
CREATE TABLE "example_sender" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "name" varchar(200) NOT NULL, "email" varchar(200) NOT NULL, "imageUrl" varchar(250) NOT NULL);

--
-- Add field recipient to message
--
ALTER TABLE "example_message" RENAME TO "example_message__old";
CREATE TABLE "example_message" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "message" varchar(200) NOT NULL, "visible" integer NOT NULL, "timestamp" datetime NOT NULL, "recipient_id" integer NOT NULL REFERENCES "example_sender" ("id") DEFERRABLE INITIALLY DEFERRED);
INSERT INTO "example_message" ("id", "message", "visible", "timestamp", "recipient_id") SELECT "id", "message", "visible", "timestamp", NULL FROM "example_message__old";
DROP TABLE "example_message__old";
CREATE INDEX "example_message_recipient_id_f1469da5" ON "example_message" ("recipient_id");

--
-- Add field sender to message
--
ALTER TABLE "example_message" RENAME TO "example_message__old";
CREATE TABLE "example_message" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "message" varchar(200) NOT NULL, "visible" integer NOT NULL, "timestamp" datetime NOT NULL, "recipient_id" integer NOT NULL REFERENCES "example_sender" ("id") DEFERRABLE INITIALLY DEFERRED, "sender_id" integer NOT NULL REFERENCES "example_sender" ("id") DEFERRABLE INITIALLY DEFERRED);
INSERT INTO "example_message" ("id", "message", "visible", "timestamp", "recipient_id", "sender_id") SELECT "id", "message", "visible", "timestamp", "recipient_id", NULL FROM "example_message__old";
DROP TABLE "example_message__old";
CREATE INDEX "example_message_recipient_id_f1469da5" ON "example_message" ("recipient_id");
CREATE INDEX "example_message_sender_id_043c7729" ON "example_message" ("sender_id");
COMMIT;

Now it’s a lot of SQL code. After creating the base tables, there is a sequence of SQL commands to alter the table (ALTER TABLE), declare the relationships (REFERENCES), and add indexes (CREATE INDEX).

There are more documentation about creating models and relationships in the Django website.

Managing your models using the admin interface

Now we can see how changed the admin interface for Senders and Messages. First of all, we need to create again the super user and start the server:

Run python manage.py createsuperuserto create your admin user
Execute python manage.py runserverto run your web server
Go to http://localhost:8000/admin/ to log in with your admin account.

Now, we can create some Senders in the admin interface

After that, you can create a Message model. Now, for the sender and recipient, we have a selector for existing Senders. The text of each element is obtained from the __str__(self) method (String description).

Using models in your web pages

Now we can crate a more complete web page, with content from the database. Edit the example/views.py to create a custom message view:

from django.shortcuts import render
from django.http import HttpResponse
from example.models import Sender, Message

def index(request):
    messages = Message.objects.all()
    content = []
    for message in messages:
        content.append('<div style="border:1px solid gray">')
        content.append('<div><b>From:</b> %s </div>' % message.sender )
        content.append('<div><b>To:</b> %s </div>' % message.recipient )
        content.append('<div><b>Date:</b> %s </div>' % message.timestamp )
        content.append('<pre>%s</pre>' % message.message )
        content.append('</div>')
    return HttpResponse('<h1>Messages</h1> %s' % ('n'.join(content) ) )

Then, when you go to http://localhost:8000/ you will get :

Messages

From: Me [me@example.com]

To: Other [other@example.com]

Date: 2018-12-10 22:30:00+00:00

Hi! How are you?

Conclusion

To work with databases in a Django project, you can follow these steps:

Edit the file example/models.py to add models
Run python manage.py makemigrations to create a migration file
The, run python manage.py migrate to excute the migration
Run python manage.py shell to start a python console
1. Play with models creating, and updating items
Add your models to the admin interface in example/admin.py.
Run python manage.py runserver to start your web server
Go to http://localhost:8000/admin/ to access the admin interface and create models using the web interface.
You can modify example/views.py to create a more sophisticated index(),and to create other actions (for example delete())
Do not forget to add the new paths and actions into webapp/urls.py

Using Database models in Python and Django