Django: Using an Oracle database

This post will show you how to configure a Django application to use an Oracle database.

Demo overview

The goal of the Django project is to store episode information for the crime fiction television series Columbo.

What you will need

To build this demo I used the following:

  • VS Code 1.5.6
  • Django 3.2.3
  • Python 3.9.1
  • Oracle Database XE 18.4
  • Oracle SQL Developer 20.4.1

Create a new Oracle schema

Start Oracle SQL Developer and using an appropriately powerful user, create a new schema called COLUMBO:

-- since Oracle 12.1 and the introduction of Multitenant architecture
-- you need to set the pluggable database you wish to use.
alter session set container = XEPDB1

-- create user sql
create user columbo identified by oracle  
default tablespace users
temporary tablespace temp;
alter user columbo quota unlimited on users;

-- roles and privs
grant resource to columbo;
grant create session to columbo;

SQL Developer Connection

Set up a new SQL Developer connection in order to view and query the objects within the new schema. From the connections window, select new connection and complete the appropriate fields:

After pressing Save followed by Connect you will have something similar to:

Creating the Django project

The following commands create a directory, navigate into it before running the command to create a new Django project called columbo. The last cd command into columbo is not a typo, the folder structure at this point is columbo\columbo.

md columbo
cd columbo
django-admin startproject columbo
cd columbo 

Next create an app that will be used to store details of Columbo episodes.

python manage.py startapp episodes

Open the settings.py file and make the following changes:

Add the episodes app to the list installed apps

INSTALLED_APPS = [    
'django.contrib.admin',    
'django.contrib.auth',    
'django.contrib.contenttypes',    
'django.contrib.sessions',    
'django.contrib.messages',    
'django.contrib.staticfiles',    
'episodes'
]

Change the DATABASES setting to:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.oracle',
        'NAME': 'XEPDB1',
        'USER': 'COLUMBO',
        'PASSWORD': 'oracle',        
        'HOST':'',
        'PORT':''
    }
}

The host and port can be left as the default unless you made any changes when installing Oracle, if so these values will need to be amended.

The final step of configuration is to install cx_Oracle which is a Python extension module that enables access to Oracle Database:

python -m pip install cx_Oracle

Within the episodes models.py I created the following class:

from django.db import models

class Episodes(models.Model):
    no_in_series = models.IntegerField(null=True, blank=True)
    no_in_season = models.IntegerField(null=True, blank=True)
    title = models.CharField(max_length=100)
    directed_by = models.CharField(max_length=100)
    written_by = models.CharField(max_length=100)
    original_air_date = models.DateField(null=True)
    runtime = models.IntegerField(null=True, blank=True)

The final step is to perform the necessary migrations using the following commands:

python manage.py makemigrations
python manage.py migrate

At this point if you return to SQL Developer and refresh your Columbo connection you will now see a number of new tables have been created including one to hold the episode information:

The structure of the episodes_episodes table matches the model with the additional of a ID column

Testing

I have used the built in Django shell to make sure an episode can be persisted to the Oracle database.

python manage.py shell

Then run the following commands to create a new episode.

from datetime import date
from episodes.models import Episodes

e = Episodes(no_in_series=3, no_in_season=1, title="Murder by the book", 
             directed_by="Steven Spielberg", written_by="Steven Bochco",    
             original_air_date=date(1971,9,15), runtime=76)

e.save()

Return to SQL Developer and query the episodes_episodes table to see the newly created row.

Can I help?

Getting Django and Oracle to play nicely with each other is refreshingly straight forward. If there are other Django with Oracle challenges you are facing and would like me to cover in upcoming posts please get in touch.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.