Srikanth Technologies

Oracle + Django ORM

In this blog, we access Oracle database from Django Web Application using Django ORM (Object relational mapping) API.

Connecting to Oracle Database using HR account

In order to connect to Oracle from Python, we need to install cx_Oracle package, which is DB-API 2.0 implementation, using PIP.

If you never used cx_Oracle, use my blog Accessing Oracle From Python to understand how to use cx_Oracle.

At this point I assume you have a working Oracle Database 11g XE (Express Edition), Python 3.7, and cx_Oracle 7.x installed in your system.

Here are some useful links related to Oracle and Python:

Get started with Oracle Database 11g XE and SQL Developer

How to get started with Python and PyCharm

If you are planning to access Oracle that is not in your system and installed on a remote server then you need to install InstantClient software provided by Oracle.

Make sure Oracle's BIN directory or InstantClient folder is in system PATH.

The following command shows how to add Oracle's BIN directory to system PATH: 

c:\>set PATH=%PATH%;C:\oraclexe\app\oracle\product\11.2.0\server\bin

If you are using InstantClient, use folder into which you installed InstantClient (for ex: c:\python\instantclient) as follows: 

c:\>set PATH=%PATH%;C:\python\instantclient

The following program will connect to Oracle Database using username hr and password hr. In case you are trying to use a different account or a different version of Oracle database then feel free to change the details and experiment. 

import os
import cx_Oracle

# Connect to hr account in Oracle Database 11g Express Edition(XE)
con = cx_Oracle.connect("hr", "hr", "localhost/xe")
print("Connected!")
con.close()

Creating Django Project and Application

We need to install Django Framework using PIP as follows: 

pip install django

Installing Django, installs django-admin.exe in Scripts folder of Python installation directory.

Let's create a new Django Project called oracledemo and Application called hr in that project with the following commands: 

django-admin startproject oracledemo

It will create a new folder oracledemo and places manage.py and another folder oracledemo inside that. Get into oracledemo folder and then run manage.py to create a new application called hr inside that project. 

python manage.py startapp hr

Configure Django settings.py

Open settings.py file that is placed in oracledemo folder, which is inside oracledemo (project) folder.

Add application hr as one of the installed applications by adding it to INSTALLED_APPS list. 

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

Modify default database configuration so that Oracle is used as default database (not SQLite). I assume we are using Oracle Database 11g XE running on the current system. If your case is different, do make necessary changes to values.

For more details about Database configuration in Django, refer to Database section in Django documentation. 

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.oracle',
        'NAME': 'XE',
        'USER': 'hr',
        'PASSWORD': 'hr',
        'HOST': 'localhost',
        'PORT': '1521'
    }
}

Creating Model

Create a class that represents JOBS table in Oracle database in oracledemo/hr/models.py file. 

class Job(models.Model):
    job_id = models.CharField(max_length=10,primary_key=True)
    job_title = models.CharField(max_length=50, null = True)
    min_salary = models.IntegerField()
    max_salary = models.IntegerField()
    class Meta:
         db_table = "jobs"

Creating view and template

Create the following function view in oracledemo/hr/views.py to display details of jobs from JOBS table. 

def  list_jobs (request):
    return render(request,'list_jobs.html',{'jobs' : Job.objects.all()})

Here is oracledemo/hr/templates/list_jobs.html to display details of Jobs in HTML table. 

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Jobs</title>
</head>
<body>
<h1>Jobs</h1>
<table width="100%"  border="1">
    <tr style="background-color:lightgray">
        <th>ID</th>
        <th>Title</th>
        <th>Min Salary</th>
        <th>Max Salary</th>
    </tr>
    {% for job in jobs %}
       <tr>
           <td>{{job.job_id}}</td>
           <td>{{job.job_title }}</td>
           <td>{{job.min_salary}}</td>
           <td>{{job.max_salary}}</td>
       </tr>
    {% endfor %}

</table>
</body>
</html>
Finally add a new URL in oracledemo/oracledemo/urls.py that invokes list_jobs(). 

from django.urls import path
import hr.views as hr_views

urlpatterns = [
    ...
    path('jobs', hr_views.list_jobs)
]
Start server after adding Oracle's bin directory or InstantClient directory to system PATH. 

>set PATH=%PATH%;C:\oraclexe\app\oracle\product\11.2.0\server\bin

>python manage.py runserver

Now go to browser and enter the following URL to get list of Jobs. 

http://localhost:8000/jobs