In part 2 you learnt how to use templates and views in Django. In this part I am going to discuss how to integrate database with Django app for pulling and storing data. Before we get into it, there are few things that need to be done before we start playing with data and database.

Setting up database

In order to make our application able to interact with a database, we need to setup the connection of it. Django can connect many RDBMS based db engines. Since Django rely on an ORM, you don’t need to worry about underlying DBMs being used for your app as you can always switch to required DB engine later. For sake of this application I will stick to default SQLite driver. Go to settings.py file and make sure following entry is available:

1 2 3 4 5 6 DATABASES = { 'default' : { 'ENGINE' : 'django.db.backends.sqlite3' , 'NAME' : os.path . join ( BASE_DIR , 'db.sqlite3' ) , } }

Since I am using Sqlite so django.db.backends.sqlite3 will be used. In case of Mysql you will be using django.db.backends.mysql will be used. Since SQLite is a file based database therefore you need to provide the path to your Sqlite file. The default db.sqlite3 is creating when the project is created and it is available in your project root.

Django Models

I mentioned about ORM above, ORM or Object Relation Mapping provides a layer that sits between your database engine and your db queries. ORM treats each table as an object and provide methods to interact with underlying db engine without worrying of compatibility. Thus you pull/store info via objects and objects are responsible to take burden of figuring out whether the data going to be stored in a MySQL database or Postgres, MSSQL or MongoDb. You are supposed to define correct objects and their relationships.

First off, you should know what are main tables and their fields. It’s not necessary that you know every field in the beginning but at least you should be aware the basic fields. For instance if you are making a Contact Management System then name or first name/last name are basic fields. For OhBugz I will be needing two tables:

Projects id – A primary key. title – Name of the project – varchar(100). progress – Showing in percent – float. Timestamps – Created/Updated at fields. Tasks id – Primary Key title – varchar(100) project_id – Foreign Key severity – enum(high, normal, low) open_date – datetime close_date – datetime status – tinyint

I guess it is all good for this project. Now we have schema ready. It’s time to write models.

Open tracker/models.py , here I will write model classes for Projects and Tasks.

from django.db import models # Create your models here. class Project(models.Model): title = models.CharField(max_length=100) progress = models.FloatField(default=0) created_at = models.DateTimeField(auto_now_add=True) updated_at = models.DateTimeField(auto_now=True) class Task(models.Model): project = models.ForeignKey(Project, on_delete=models.CASCADE) title = models.CharField(max_length=100) severity = models.SmallIntegerField() open_date = models.DateTimeField() close_date = models.DateTimeField() status = models.SmallIntegerField(default=0) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 from django . db import models # Create your models here. class Project ( models . Model ) : title = models . CharField ( max_length = 100 ) progress = models . FloatField ( default = 0 ) created_at = models . DateTimeField ( auto_now_add = True ) updated_at = models . DateTimeField ( auto_now = True ) class Task ( models . Model ) : project = models . ForeignKey ( Project , on_delete = models . CASCADE ) title = models . CharField ( max_length = 100 ) severity = models . SmallIntegerField ( ) open_date = models . DateTimeField ( ) close_date = models . DateTimeField ( ) status = models . SmallIntegerField ( default = 0 )

Now that our models are ready, it’s time to run migrations.

Migrations in Django

Django provides two manage.py commands to take care of migrations; first makemigrations which will scan your models.py file and create migration file and then migrate command to run the migration file and apply changes in your db. Before I get into it, allow me to explain the purpose of migration.

Migration or Scheme Migration is a way similar to version of your source code. Just like you document your code changes and can revert to any version, similarly schema migration helps you to manage your schema changes and provides you ability to revert to previous version and document each change. You don’t need to worry about whether your db changes in some (*.sql) file has been applied or not since migration tool will take care of it. Migration concept is available in all modern MVC frameworks in different languages.

OK so our models are ready, it’s time to generate our migration file. Go to terminal and with in your project folder run the command:

1 python manage . py makemigrations tracker

By mentioning tracker you are actually telling which app’s migration should it run. In case you have a single app, you can ignore last part. If it runs successfully you should see something similar:

1 2 3 4 Migrations for 'tracker' : tracker / migrations / 0001_initial.py : - Create model Project - Create model Task

So our first migration is ready. Let’s see what’s there in 0001_initial.py file. The file name is initial because this is the very first migration. Let’s say if you remove a field and generate file again, it should show something like this:

Migrations for 'tracker': tracker/migrations/0002_remove_task_severity.py: - Remove field severity from task 1 2 3 Migrations for 'tracker' : tracker / migrations / 0002_remove_task_severity.py : - Remove field severity from task

Since I removed severity field for demo purpose and ran the command, it generated a new migration file with helpful text like remove_task_severity that gives idea which field removed from which file. Pretty cool, right? Alright, below is what you should see in your initial migration file:

# -*- coding: utf-8 -*- # Generated by Django 1.10.5 on 2017-04-27 18:09 from __future__ import unicode_literals from django.db import migrations, models import django.db.models.deletion class Migration(migrations.Migration): initial = True dependencies = [ ] operations = [ migrations.CreateModel( name='Project', fields=[ ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')), ('title', models.CharField(max_length=100)), ('progress', models.FloatField(default=0)), ('created_at', models.DateTimeField(auto_now_add=True)), ('updated_at', models.DateTimeField(auto_now=True)), ], ), migrations.CreateModel( name='Task', fields=[ ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')), ('title', models.CharField(max_length=100)), ('severity', models.SmallIntegerField()), ('open_date', models.DateTimeField()), ('close_date', models.DateTimeField()), ('status', models.SmallIntegerField(default=0)), ('project', models.ForeignKey(on_delete=django.db.models.deletion.CASCADE, to='tracker.Project')), ], ), ] 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 # -*- coding: utf-8 -*- # Generated by Django 1.10.5 on 2017-04-27 18:09 from __future__ import unicode_literals from django . db import migrations , models import django . db . models . deletion class Migration ( migrations . Migration ) : initial = True dependencies = [ ] operations = [ migrations . CreateModel ( name = 'Project' , fields = [ ( 'id' , models . AutoField ( auto_created = True , primary_key = True , serialize = False , verbose_name = 'ID' ) ) , ( 'title' , models . CharField ( max_length = 100 ) ) , ( 'progress' , models . FloatField ( default = 0 ) ) , ( 'created_at' , models . DateTimeField ( auto_now_add = True ) ) , ( 'updated_at' , models . DateTimeField ( auto_now = True ) ) , ] , ) , migrations . CreateModel ( name = 'Task' , fields = [ ( 'id' , models . AutoField ( auto_created = True , primary_key = True , serialize = False , verbose_name = 'ID' ) ) , ( 'title' , models . CharField ( max_length = 100 ) ) , ( 'severity' , models . SmallIntegerField ( ) ) , ( 'open_date' , models . DateTimeField ( ) ) , ( 'close_date' , models . DateTimeField ( ) ) , ( 'status' , models . SmallIntegerField ( default = 0 ) ) , ( 'project' , models . ForeignKey ( on_delete = django . db . models . deletion . CASCADE , to = 'tracker.Project' ) ) , ] , ) , ]

A migration class extended migrations.Migration and a few list variables. If you notice, the code is not much different from what you wrote in models.py . It’s just more elaborative for the core system so that it can generate relevant tables and relationships.

Adnans-MBP:ohbugztracker AdnanAhmad$ python manage.py makemigrations tracker System check identified some issues: WARNINGS: ?: (urls.W001) Your URL pattern '^$' uses include with a regex ending with a '$'. Remove the dollar from the regex to avoid problems including URLs. You are trying to add a non-nullable field 'severity' to task without a default; we can't do that (the database needs something to populate existing rows). Please select a fix: 1) Provide a one-off default now (will be set on all existing rows with a null value for this column) 2) Quit, and let me add a default in models.py Select an option: 1 2 3 4 5 6 7 8 9 10 Adnans - MBP : ohbugztracker AdnanAhmad $ python manage . py makemigrations tracker System check identified some issues : WARNINGS : ? : ( urls . W001 ) Your URL pattern '^$' uses include with a regex ending with a '$' . Remove the dollar from the regex to avoid problems including URLs . You are trying to add a non - nullable field 'severity' to task without a default ; we can ' t do that ( the database needs something to populate existing rows ) . Please select a fix : 1 ) Provide a one - off default now ( will be set on all existing rows with a null value for this column ) 2 ) Quit , and let me add a default in models . py Select an option :

Oops! I did a mistake. Django enforces you to mention default value for your columns. In my case it is severity field. I you scroll up you will find the mistake I did:

severity = models.SmallIntegerField() 1 severity = models . SmallIntegerField ( )

should be as:

severity = models.SmallIntegerField(default=0) 1 severity = models . SmallIntegerField ( default = 0 )

or.. if you want you can pick option number 1 to let the tool set the default field. Since the purpose here to explain how to deal with it manually, I opted for option number 2 and made changes in my migration file. I ran python manage.py makemigrations tracker again and this time it generated another migration file:

Migrations for 'tracker': tracker/migrations/0003_task_severity.py: - Add field severity to task 1 2 3 Migrations for 'tracker' : tracker / migrations / 0003_task_severity.py : - Add field severity to task

Awesome, right? Now you will be grasping the idea of schema migrations and versioning. Now this makemigrations tool is pretty smart. If you run the same command again,it gives the message:

No changes detected in app ‘tracker’

Pretty cool, so go ahead and run this command as much as you can. As far as no change is in your models.py file, you will keep seeing above message. Now I am going to migrate our file. If all goes well you should see something like:

Adnans-MBP:ohbugztracker AdnanAhmad$ python manage.py migrate System check identified some issues: WARNINGS: ?: (urls.W001) Your URL pattern '^$' uses include with a regex ending with a '$'. Remove the dollar from the regex to avoid problems including URLs. Operations to perform: Apply all migrations: admin, auth, contenttypes, sessions, tracker Running migrations: Applying tracker.0001_initial... OK Applying tracker.0002_remove_task_severity... OK Applying tracker.0003_task_severity... OK 1 2 3 4 5 6 7 8 9 10 11 Adnans - MBP : ohbugztracker AdnanAhmad $ python manage . py migrate System check identified some issues : WARNINGS : ? : ( urls . W001 ) Your URL pattern '^$' uses include with a regex ending with a '$' . Remove the dollar from the regex to avoid problems including URLs . Operations to perform : Apply all migrations : admin , auth , contenttypes , sessions , tracker Running migrations : Applying tracker . 0001_initial... OK Applying tracker . 0002_remove_task_severity... OK Applying tracker . 0003_task_severity... OK

like makemigrations you can mention your app name after migrate and it should run the migrations of your app only. Since no app mention here thus it ran application wide migrations. I had already run an application wide migrate command. So in case if you are running first time, you might see something like this beside your app migrations:



If I go to my Db client I will see these two entries:

Did you notice application name as prefix? This is awesome as it will help to avoid any kind of naming conflict within a project.

Now our tables are ready. While you can write ORM related code in views/model files to test your queries, Django provides a neat way to test out your queries, called Django Shell.

Django Shell

Django Shell is an interactive environment where you can run all your python code along with code related to Django. The shell will let you to run queries to insert/pull data. In order to invoke shell, go to your project folder and run the following command:

1 python manage . py shell

It will open an interactive shell like given below:

Once it’s invoked, I am going to pull result from project table. For that I am going to run following statement:

Project.objects.all() 1 Project . objects . all ( )

it should run but.. OOPS!

He is not wrong. The thing is I have not imported the required module:

In [4]: from tracker.models import Project In [5]: Project.objects.all() Out[5]: <QuerySet []> 1 2 3 4 In [ 4 ] : from tracker . models import Project In [ 5 ] : Project . objects . all ( ) Out [ 5 ] : < QuerySet [ ] >

This time it works, returns empty resultset, obviously. Let’s add a record, for that purpose I will use obects.create method.

In [6]: Project.objects.create(title='My First Project') Out[6]: <Project: Project object> 1 2 In [ 6 ] : Project . objects . create ( title = 'My First Project' ) Out [ 6 ] : < Project : Project object >

Here create method will accept the fields correspond to your table. DO REMEMBER that these are not table column name but Model class attributes which defined in Project model. If all goes well you should see something given above. Now run the objects.all() again.

In [7]: Project.objects.all() Out[7]: <QuerySet [<Project: Project object>]> 1 2 In [ 7 ] : Project . objects . all ( ) Out [ 7 ] : < QuerySet [ < Project : Project object > ] >

It returns one result in list [] . Now this is not some helpful output. Go to models.py class and add following method in both Project and Task classes:

def __str__(self): return self.title 1 2 def __str__ ( self ) : return self . title

Now run .objects.all() again and this time you will see title returned:

In [3]: Project.objects.all() Out[3]: <QuerySet [<Project: My First Project>]> 1 2 In [ 3 ] : Project . objects . all ( ) Out [ 3 ] : < QuerySet [ < Project : My First Project > ] >

Pretty cool. You can return anything as long as it is string. For instance:

In [1]: from tracker.models import Project In [2]: Project.objects.all() Out[2]: <QuerySet [<Project: LOL>]> 1 2 3 4 In [ 1 ] : from tracker . models import Project In [ 2 ] : Project . objects . all ( ) Out [ 2 ] : < QuerySet [ < Project : LOL > ] >

__str__ just makes an object string representable in case someone tries to use str() or print() function to print object variable. Read further here.

OK so a record is added in project table. How about add a record in tasks table or Task model class.

Assuming you are still in shell:

In [6]: from django.utils import timezone In [7]: p = Project.objects.get(pk=1) In [8]: p.task_set.create(title='Finishing up this post',severity=1,open_date=timezone.now(),close_date=timezone.now(),status=1) Out[8]: <Task: Task object> 1 2 3 4 5 6 In [ 6 ] : from django . utils import timezone In [ 7 ] : p = Project . objects . get ( pk = 1 ) In [ 8 ] : p . task_set . create ( title = 'Finishing up this post' , severity = 1 , open_date = timezone . now ( ) , close_date = timezone . now ( ) , status = 1 ) Out [ 8 ] : < Task : Task object >

Now you see the magic; p.task_set.create is actually inserting a record in task table with the foreign key of the project id = 1. Let me explain:

p = Project.objects.get(pk=1) get the the project object having primary key = 1. Once the reference is obtain in p , you set the associated object ( i:e task here) with data. When it runs, it generates following record in the table:



What happened above you can other way as well; you create a new task object and then associate it with the project having id = 1 but I find this way much cleaner than writing a multiple line code. Read further here if you are interested to go deeper.

Now I want to make sure whether the record really got inserted. I do what I already did with Project object:

In [9]: task.objects.all() --------------------------------------------------------------------------- NameError Traceback (most recent call last) <ipython-input-9-b2dcb0825a95> in <module>() ----> 1 Task.objects.all() NameError: name 'Task' is not defined In [10]: 1 2 3 4 5 6 7 8 9 In [ 9 ] : task . objects . all ( ) -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - NameError Traceback ( most recent call last ) < ipython - input - 9 - b2dcb0825a95 > in < module > ( ) -- -- > 1 Task . objects . all ( ) NameError : name 'Task' is not defined In [ 10 ] :

I hope you got why it happened, yeah I for got to call from tracker.models import Task here. Once done I run it again. And this time it works well:

In [1]: from tracker.models import Task In [2]: Task.objects.all() Out[2]: <QuerySet [<Task: Finishing up this post>]> 1 2 3 4 In [ 1 ] : from tracker . models import Task In [ 2 ] : Task . objects . all ( ) Out [ 2 ] : < QuerySet [ < Task : Finishing up this post > ] >

As you can see the task we added is available here. For instance, you want to return ALL records in which title column contains Finish. All you need to do it:

Task.objects.filter(title__contains='Finish') 1 Task . objects . filter ( title__contains = 'Finish' )

Awesome, No?

Data passing in Django Templates

Now before I go I want to display project name in my html view. Open tracker/views.py and in index() method run same query that we ran in Python shell:

projects = Project.objects.all() 1 projects = Project . objects . all ( )

I am assigning the result in a variable because I want to pass that information in the template file. Your views.py should look like this:

from django.shortcuts import render from django.http import HttpResponse from .models import Project, Task def index(request): projects = Project.objects.all() return render(request, 'index.html', {'projects': projects}) 1 2 3 4 5 6 7 8 from django . shortcuts import render from django . http import HttpResponse from . models import Project , Task def index ( request ) : projects = Project . objects . all ( ) return render ( request , 'index.html' , { 'projects' : projects } )

Notice I am importing both Project and Task class here. Since they are in same app, I am using dot(.) to refer models of current app.

OK, now open tracker/templates/index.html and make following changes:

<div class="row medium-margin-top"> <div class="col-md-12"> {% for project in projects %} <div class="box text-center"> <h3 class="text-center"><a href="project.html">{{project.title}}</a></h3> <div><strong>View Tasks:</strong> <a href="#" class="small">All</a> | <a href="#" class="small">Opened</a></div> <div><strong>Stats:</strong> <span class="small"> 1 Open, 8 Closed</span></div> <div class="progress"> <div class="progress-bar progress-bar-success" role="progressbar" aria-valuenow="40" aria-valuemin="0" aria-valuemax="100" style="width:40%"> 40% Complete (success) </div> </div> </div> {% endfor %} </div> </div> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 < div class = "row medium-margin-top" > < div class = "col-md-12" > { % for project in projects % } < div class = "box text-center" > < h3 class = "text-center" > < a href = "project.html" > { { project . title } } < / a > < / h3 > < div > < strong > View Tasks : < / strong > < a href = "#" class = "small" > All < / a > | < a href = "#" class = "small" > Opened < / a > < / div > < div > < strong > Stats : < / strong > < span class = "small" > 1 Open , 8 Closed < / span > < / div > < div class = "progress" > < div class = "progress-bar progress-bar-success" role = "progressbar" aria - valuenow = "40" aria - valuemin = "0" aria - valuemax = "100" style = "width:40%" > 40 % Complete ( success ) < / div > < / div > < / div > { % endfor % } < / div > < / div >

projects which was passed as dictionary key acting as an array variable here. I am using Jinja template syntax to run for-loop. Notice {{project.title}} . This all going to produce following output:

project.title is calling the data from title table field. Now, if I change it to {{project}} it will STILL print title here. Wonder why? No, I am not gonna tell you, scroll up and figure out and let me know 🙂

That’s it here. In future post I am planning to discuss Django forms, hopefully. Stay tuned.

As usual code is available on Github.





