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 file. Remember that your password should NOT go into the file that you commit to your repo. There are several options for storing passwords, and I use a file. In my file, my DATABASES look like this:

    'default': {
        'ENGINE': '',
        'NAME': 'xe',
        'USER': 'a_user',
        'PASSWORD': '',
        'HOST': '',
        'PORT': '',

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

    'default': {
        'ENGINE': '',
        '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_app
│   ├── [...]
└── my_project
    └── 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 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 makemigrations <appname>
$ python 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 makemigrations <appname>. This will make sure Django knows about your new models. Follow it up with python 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 Let’s go to the shell!

$ python 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>)
<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


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

Django with an Oracle Legacy DB
4 Likes23.95 GEEK