loading...

Django with an Oracle Legacy DB

rpost profile image R Post ・4 min read

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.py includes 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.

Now that you've made the new folder and file, tell Django where it is. 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 = '"<SCHEMA_NAME>"."<TABLE_NAME>"'

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.

Posted on by:

rpost profile

R Post

@rpost

Started out analyzing language data and ended up a programmer.

Discussion

markdown guide
 

I can tell and I did this step: basically a select statement in DB. Did you do any insert/delete a record in the hr table. And what version of django you used?