Improve the use of cx-Oracle in Django

Using Django Using the ORM
1

Hello everyone,

For a website project with Django I often need to send requests in an Oracle DB. So far, I use the classic connection for every new request …

con  = cx.connect(...)
cur = conn.cursor()

cur.execute(My_Request)
rows = cur.fetchall()

cur.close()

For some pages, I need to perform up to 5 queries and thus reconnect as many times, which takes time.
So I searched on the Oracle doc and I discovered the “pool connection”, which seems to be much faster and does not close itself after a single request.

My problem is that I’m not sure I understand how it works, nor how to set it up in my code.
Could someone who’s already used it explain it to me because maybe I’m wrong, or maybe there are other more suitable methods ?

Thank you in advance, have a good day

2

First, I would see about setting up a Django-style connection in your settings.

See Multiple databases | Django documentation | Django.

This would get you out of the business of managing your connections entirely, allowing Django to do that for you. You would then also be able to use the ORM for your queries, which may make other operations easier as well.

3

Hello, thank you for your response and sorry for the delay.
Django’s multiple databases function seemed fine to me but by checking I need to have write rights on the Oracle database off I don’t have them, read only. Moreover it is a very large database so I can not afford to make a makemigration/migrate as requested in the documentation.
I think the solution will come rather from cx_Oracle but I wanted to know before if maybe Django could help me simplify the read-only connection.

4

I don’t know about Oracle specifically, but for other databases (PostgreSQL, MariaDB), “write rights” are not required. You can create (effectively) read-only access by creating a database router for those models stored in the Oracle database that prevent all attempts to write to those models.

There is no need to do this with a database that you don’t control.

The multi-data solution does do that. This is a technique we use frequently to integrate data from databases external to our site.

Also see the docs at How to integrate Django with a legacy database | Django documentation | Django

5

It seems to me to be not far from the solution. I followed your method of multiple databases with django which gives:

settings.py

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': BASE_DIR / 'db.sqlite3',
    },
    'oracleDB':{
        'ENGINE': 'django.db.backends.oracle',
        'NAME': 'my_dsn',
        'USER': 'my_user',
        'PASSWORD': 'my_password',
        'HOST': 'my_host',
        'PORT': 'my_port',
        'OPTIONS':{
            'threaded':True
        }
    }

Then i only when to execute raw SQL queries so i use it in views like this (saw on a forum):

views.py

from django.db import connections
def my_function(request):
    with connections['oracleDB'].cursor() as cur:
        cur.execute( "SOME SQL QUERY")
        print(cur.fetcall()) #only to see if it works

and now i have this error

DPI-1047: Cannot locate a 64-bit Oracle Client library: “C:\ProgramData\Oracle12c\product\12.1.0\client_1\bin\oci.dll is not the correct architecture”. See cx_Oracle 8 Installation — cx_Oracle 8.3.0 documentation for help

I know that i need the Oracle instanclient, i used it with the connection script using cx_oracle but now i don’t know where to insert it and specify it in the project.

Thanks again

6

It’s okay I solved my problem by adding

settings.py

import cx_Oracle
cx_Oracle.init_oracle_client(lib_dir="my_path_to_oracle_client")
 
DATABASES = {
        'default': {
            'ENGINE': 'django.db.backends.sqlite3',
            'NAME': BASE_DIR / 'db.sqlite3',
        },
        'oracleDB':{
            'ENGINE':'django.db.backends.oracle',
            'NAME':(
                '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=my_host)(PORT=my_port))'
                '(CONNECT_DATA=(SERVICE_NAME=my_service)))'
            ),
            'USER':'my_user',
            'PASSWORD':'my_password',
        }

So I added client initialization by cx_Oracle directly in Django’s settings and I modified my NAME that didn’t allow recognition of the database before that.

For the query of the base then I do like this:

views.py

from django.db import connections
 
with connections['oracleDB'].cursor() as cur:
        cur.execute("""MY_SQL_QUERY""")

Do not hesitate if you think you have a better solution.

Thanks for the help