Django with an Oracle Legacy DB

Django with an Oracle Legacy DB

Integrating Django with a legacy database. While Django is best suited for developing new applications, it's quite possible to integrate it into legacy databases...

The problem

I am working on a project that requires connecting an Oracle legacy database to a Django project. I found the steps slowly, and in a variety of different places, so I am bringing them together in case someone else has a similar problem in the future!

The basics

Getting started with a database connection in Django is laid out in the docs. There you will find how to set up the database info in your settings.py file. Remember that your password should NOT go into the settings.py file that you commit to your repo. There are several options for storing passwords, and I use a local_settings.py file. In my settings.py file, my DATABASES look like this:

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

Notice that the password is an empty string. Then my local_settings.pyincludes the password:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.oracle',
        'NAME': 'xe',
        'USER': 'a_user',
        'PASSWORD': 'a_password',
        'HOST': '',
        'PORT': '',
    }
}

The Django docs mention tnsnames.ora, and this is how I connect to my legacy database because it lets you use a service name. However, the docs don't say WHERE in the project structure tnsnames.ora goes. Using the structure that's standard since Django 1.4, I put mine here:

my_project
├── manage.py
├── my_app
│   ├── [...]
└── my_project
    ├── __init__.py
    ├── _batch_settings.py
    ├── settings.py
    ├── urls.py
    └── oracle           <~~~ Create this folder!
        └── tnsnames.ora <~~~ Create this file!

I read that Oracle can be super picky about line endings, so I created my tnsnames.ora file in my vm. I'm spoiled because my org has a standard vm to use, so I am sorry if you can't do this easily and have line-ending trouble.

In your settings.py file, you should set your TNS_ADMIN environment variable to point to the oracle directory created within your project folder:

os.environ['TNS_ADMIN'] = os.path.join(BASE_DIR, '<project_name>', 'oracle')

Recognize the DB

Ok, so the basics are ready! As someone who hadn't started a Django app with a legacy DB ever, I was immediately stuck again. But the next steps are the same as for any database. Since I was not wanting to set up a new database, I wasn't sure that I needed to do these, but it is necessary. Go over to the terminal/command line and:

$ python manage.py makemigrations <appname>
$ python manage.py migrate <appname>

After each of these commands, the terminal window shows you the progress on each step.

Now if you are lucky and you have tables or views you need in your schema, you can use the inspectdb command to give you a good start on your models. In fact, this is the recommended way to integrate with a legacy DB in the docs. Skip ahead to testing if inspectdb works for you. However, if your schema does not contain any tables or views and exists solely to give you access to tables within another schema, our princess is in another castle. Let's go find her.

The next castle

Without the help of inspectdb, you need to write up a little model of your own before you can be sure that you are properly connected. This can be quite short if you want to start with something small for testing. I would recommend including at least two fields: one to query based on, and another to prove you got what you wanted. You will also need to include class Meta in order to tell the Django ORM which schema and table you want to query. A basic model can be quite short:

class Student(models.Model):
    id = models.IntegerField(primary_key=True)
    name = models.CharField(max_length=50, blank=True, null=True)

class Meta:
    managed = False
    db_table = '"&lt;SCHEMA_NAME&gt;"."&lt;TABLE_NAME&gt;"'

Pay attention to all of those single and double quotation marks for the db_table - Oracle is very dumb and can only find the schema and table if you tell it in exactly the right way. Now that you have a new model, re-run your python manage.py makemigrations <appname>. This will make sure Django knows about your new models. Follow it up with python manage.py migrate <appname> as before. Yes, you need to do both.

Testing that our connection can get data

Ok! We have our DATABASES entry, our tnsnames.ora file and something in models.py. Let's go to the shell!

$ python manage.py shell

We are now in the magical django-infused shell environment. You'll need to tell the shell where to find your model, and then retrieve some data within it. In order to be sure to retrieve something, look for a row that you know is in your data.

In[1]: from <appname>.models import *
In[2]: test = Student.objects.get(id=<id that really exists>)
In[3]: test.name
     <you should see the correct related field info here!>

If instead of beautiful data you see NameError: name 'Students' is not defined then you probably either 1) typoed the class name for your model or 2) did not run the migration commands as above.

Go forth and conquer

Celebrate your success! Now that you have proven that your Django project is connected to your Oracle legacy database, go get that data and do whatever you really need to be doing! If you had to write the initial testing model, you will need to go write the rest of the models you need by hand. Not fun, and more error prone, but I haven't found a way around it. I would love to be wrong, though, so let me know if you have a solution!

If this is your first time pulling data through a Django model, take some time to get comfortable with the Django ORM (object-relational mapping layer). It's a fancy way to say "learn how to use that cool model you just generated or wrote." That's where I'm headed next.

Originally published by R Post  at  dev.to

===================================================================

Thanks for reading :heart: If you liked this post, share it with all of your programming buddies! Follow me on Facebook | Twitter

Learn More

☞ Complete Python Bootcamp: Go from zero to hero in Python 3

☞ Python and Django Full Stack Web Developer Bootcamp

☞ Python for Time Series Data Analysis

☞ Python Programming For Beginners From Scratch

☞ Beginner’s guide on Python: Learn python from scratch! (New)

☞ Python for Beginners: Complete Python Programming

☞ Django 2.1 & Python | The Ultimate Web Development Bootcamp

☞ Python eCommerce | Build a Django eCommerce Web Application

☞ Python Django Dev To Deployment

django database oracle

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

5 Steps To Get An Oracle Database Certification

Oracle Online Training & Certification Course; Launch your career by joining India's best Institute to learn 10g 11g and become well-versed oracle professional.

Exploring PL/SQL Nested Tables in Oracle

In this tutorial, you will learn how to declare and initialize Oracle PL/SQL collections (Nested Tables). Nested tables are very similar to the PL/SQL tables, which are known in Oracle as index-by tables.

Main Pros and Cons of Django As A Web Framework for Python Developers

India's best Institute for Django Online Training Course & Certification. Django is a high-level Python Web framework that encourages rapid development and clean, pragmatic design.

Oracle Introduces the MySQL Database Service on Its Cloud Infrastructure

In a recent blog post, Oracle introduced the MySQL Database Service in the Oracle Cloud Infrastructure (OCI). The service is now available as a fully-managed service running on Oracle Generation 2 Cloud Infrastructure.

What is Oracle and Its Benefits?

Oracle Online Training & Certification Course; Launch your career by joining India's best Institute to learn 10g 11g and become well-versed oracle professional.