DRCP implementation on oracle 12.1 using python & Django framework

DRCP implementation on oracle 12.1 using python

Read documents:
https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/OOW11/python_django/python_django.htm
https://code.tutsplus.com/articles/python-from-scratch-create-a-dynamic-website--net-22787

Step 1)
Setup oracle test schema [we pick pythonhol by copying several tables emp,dept,sal,bonus from scott schema]

Step 2) verify the version of python you have - expected version for this tutorial is 2.x - max 2.7


****************** STEPS below are mainly targetted @ WEB application setup using Django framework and Python (cx_oracle module).

step 3) Download and install Django [do this as privileged user - root who has write access to /usr/]

wget http://www.djangoproject.com/download/1.3.1/tarball/
tar xzvf Django-1.3.1.tar.gz
cd Django-1.3.1
python setup.py install

step 4) Test weather the installed Django FW works fine.

python
from django import get_version
get_version()

####################
[root@host Django-1.3.1]# python
Python 2.6.6 (r266:84292, Dec 20 2012, 15:53:42)
[GCC 4.4.7 20120313 (Red Hat 4.4.7-3)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> from django import get_version
>>> get_version()
'1.3.1'
>>>
#####################

step 5) download and extract and move the directory to ~ ($HOME) as oracle or app user

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/OOW11/python_django/sample/sample.zip

will extract files directory...

[oracle@host dba]$ cd files
[oracle@host files]$ ls -altr
total 44
-rw-rw-r--.  1 oracle oracle 1286 Jul 22  2011 urls.py
-rw-rw-r--.  1 oracle oracle   70 Jul 22  2011 README.txt
-rw-rw-r--.  1 oracle oracle 2041 Jul 22  2011 python_hol.css
-rw-rw-r--.  1 oracle oracle 1094 Jul 22  2011 employee_list.html
-rw-rw-r--.  1 oracle oracle  622 Jul 22  2011 employee_form.html
-rw-rw-r--.  1 oracle oracle  382 Jul 22  2011 employee_confirm_delete.html
-rw-rw-r--.  1 oracle oracle  390 Jul 22  2011 admin.py.2
-rw-rw-r--.  1 oracle oracle  139 Jul 22  2011 admin.py.1
-rw-rw-r--.  1 oracle oracle 1561 Jul 26  2011 models.py
drwxrwxr-x.  2 oracle oracle 4096 Nov 16  2011 .
drwxrwxr-x. 28 oracle oracle 4096 Jan 20 17:00 ..

step 6) start your project, it should create mysite directory in your PWD.

django-admin.py startproject mysite

drwxrwxr-x.  2 oracle oracle 4096 Jan 20 17:00 mysite

[oracle@host files]$ cd mysite
[oracle@host mysite]$ ls -altr
total 24
drwxrwxr-x. 3 oracle oracle 4096 Jan 20 17:00 ..
-rw-r--r--. 1 oracle oracle  565 Jan 20 17:00 urls.py
-rw-r--r--. 1 oracle oracle  503 Jan 20 17:00 manage.py
-rw-r--r--. 1 oracle oracle    0 Jan 20 17:00 __init__.py
drwxrwxr-x. 2 oracle oracle 4096 Jan 20 17:00 .
-rw-r--r--. 1 oracle oracle 5031 Jan 20 17:00 settings.py


step 7) Run server and try access the webpage from http://127.0.0.1:8000/, this should show "It worked" message.

[oracle@host mysite]$ python manage.py runserver
Validating models...

0 errors found
Django version 1.3.1, using settings 'mysite.settings'
Development server is running at http://127.0.0.1:8000/
Quit the server with CONTROL-C.
[20/Jan/2019 03:03:53] "GET / HTTP/1.1" 200 2051
^C[oracle@host mysite]$ ls -altr
total 36


step 8) preserve settings.py and then modify it by introducing the name,username and password field for the database you acess


[oracle@host mysite]$ cp settings.py settings.py_BKP_20jan19
[oracle@host mysite]$ vi settings.py

'ENGINE':
'NAME':
'USER':
'PASSWORD':

step 8) start the application within the webpage. The command below should create a directory in PWD.

python manage.py startapp python_hol

drwxrwxr-x. 2 oracle oracle 4096 Jan 20 17:50 python_hol

[oracle@host python_hol]$ ls -altr
total 20
-rw-r--r--. 1 oracle oracle  383 Jan 20 17:50 tests.py
drwxrwxr-x. 3 oracle oracle 4096 Jan 20 17:50 ..
-rw-r--r--. 1 oracle oracle   26 Jan 20 17:50 views.py
-rw-r--r--. 1 oracle oracle   57 Jan 20 17:50 models.py
-rw-r--r--. 1 oracle oracle    0 Jan 20 17:50 __init__.py
drwxrwxr-x. 2 oracle oracle 4096 Jan 20 17:50 .

step 10) Verify weather cx_oracle is already there by this simple test ot install cx_oracle module.

[oracle@host yum.repos.d]$ python
Python 2.6.6 (r266:84292, Dec 20 2012, 15:53:42)
[GCC 4.4.7 20120313 (Red Hat 4.4.7-3)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import cx_Oracle
>>> db = cx_Oracle.connect("system/****@localhost/<DB_servicename>"
... )
>>> db.version
'12.1.0.1.0'
>>> quit()

if it didnt work.. we need cx_oracle for python version 2.6, which I belive is cx_oracle 5.3

wget https://files.pythonhosted.org/packages/4b/0c/350c738578358ddbb26bc0b3cbef310b677dcca72eb37cd67e29b4e6427c/cx_Oracle-5.3-11g-py26-2.x86_64.rpm --no-check-certificate
--2019-01-20 18:24:08--  https://files.pythonhosted.org/packages/4b/0c/350c738578358ddbb26bc0b3cbef310b677dcca72eb37cd67e29b4e6427c/cx_Oracle-5.3-11g-py26-2.x86_64.rpm
Resolving files.pythonhosted.org... 2a04:4e42:24::319, 151.101.153.63
Connecting to files.pythonhosted.org|2a04:4e42:24::319|:443... connected.
WARNING: certificate common name “r.ssl.fastly.net” doesn’t match requested host name “files.pythonhosted.org”.
HTTP request sent, awaiting response... 200 OK
Length: 167356 (163K) [application/octet-stream]
Saving to: “cx_Oracle-5.3-11g-py26-2.x86_64.rpm”

100%[================================================================================>] 167,356      479K/s   in 0.3s

2019-01-20 18:24:10 (479 KB/s) - “cx_Oracle-5.3-11g-py26-2.x86_64.rpm” saved [167356/167356]

[root@host Sample_App]# ls -altr
total 180
-rw-r--r--.  1 root root 167356 Mar 11  2017 cx_Oracle-5.3-11g-py26-2.x86_64.rpm
-rw-r--r--.  1 root root   4117 Jan 20 16:43 sample.zip
dr-xr-x---. 24 root root   4096 Jan 20 16:45 ..
drwxr-xr-x.  2 root root   4096 Jan 20 18:24 .
[root@host Sample_App]# rpm -ivh cx_Oracle-5.3-11g-py26-2.x86_64.rpm
Preparing...                ########################################### [100%]
   1:cx_Oracle              ########################################### [100%]

************************** Done here with python setups needed  [step 3 to step 8]

step 9) creating the models...

[oracle@host python_hol]$ cat models.py
from django.db import models

# Create your models here.
[oracle@host python_hol]$

../files/mysite

python manage.py inspectdb > python_hol/models.py

================================
[oracle@host mysite]$ python manage.py inspectdb > python_hol/models.py
[oracle@host mysite]$ cat python_hol/models.py|more
# This is an auto-generated Django model module.
# You'll have to do the following manually to clean this up:
#     * Rearrange models' order
#     * Make sure each model has one field with primary_key=True
# Feel free to rename the models, but don't rename db_table values or field names.
#
# Also note: You'll have to insert the output of 'django-admin.py sqlcustom [appname]'
# into your database.

from django.db import models

class Emp(models.Model):
    empno = models.IntegerField(null=True, blank=True)
    ename = models.CharField(max_length=30, blank=True)
    job = models.CharField(max_length=27, blank=True)
    mgr = models.IntegerField(null=True, blank=True)
    hiredate = models.DateField(null=True, blank=True)
    sal = models.DecimalField(null=True, max_digits=7, decimal_places=2, blank=True)
    comm = models.DecimalField(null=True, max_digits=7, decimal_places=2, blank=True)
    deptno = models.IntegerField(null=True, blank=True)
    class Meta:
        db_table = u'emp'

class Dept(models.Model):
    deptno = models.IntegerField(null=True, blank=True)
    dname = models.CharField(max_length=42, blank=True)
    loc = models.CharField(max_length=39, blank=True)
    class Meta:
        db_table = u'dept'

class Salgrade(models.Model):
    grade = models.DecimalField(null=True, max_digits=0, decimal_places=-127, blank=True)
    losal = models.DecimalField(null=True, max_digits=0, decimal_places=-127, blank=True)
    hisal = models.DecimalField(null=True, max_digits=0, decimal_places=-127, blank=True)
    class Meta:
        db_table = u'salgrade'

class Bonus(models.Model):
    ename = models.CharField(max_length=30, blank=True)
    job = models.CharField(max_length=27, blank=True)
    sal = models.DecimalField(null=True, max_digits=0, decimal_places=-127, blank=True)
    comm = models.DecimalField(null=True, max_digits=0, decimal_places=-127, blank=True)
    class Meta:
        db_table = u'bonus'

======model is build


step 10) modify python_hol/models.py to include only necessary models and also add def __str__(self):, which will come be uselful later.

i) REmoved sal and bonus tables from the models.py, since they were causing errors @ syncdb step.

ii) Also changed: [when you bring admin page up if the changed below listed arent done, will cause ORA-00904 emp.ID invalid identifier error ]

    empno = models.IntegerField(null=True, blank=True)
changed to
    empno = models.IntegerField(primary_key=True)        <<< DB isnt really a primary key


    deptno = models.IntegerField(null=True, blank=True)
changed to
    deptno = models.IntegerField(primary_key=True)        <<< DB isnt really a primary key

iii) Adding self return strings, if this isnt done, you will get emp object and dept object as fields getting listed.. some sort meaniless messages.

emp:
    def __str__(self):
        return self.ename

dept:
    def __str__(self):
        return self.dname

=====Post changes the model looks like below...

[oracle@host python_hol]$ cat models.py
# This is an auto-generated Django model module.
# You'll have to do the following manually to clean this up:
#     * Rearrange models' order
#     * Make sure each model has one field with primary_key=True
# Feel free to rename the models, but don't rename db_table values or field names.
#
# Also note: You'll have to insert the output of 'django-admin.py sqlcustom [appname]'
# into your database.

from django.db import models

class Emp(models.Model):
    empno = models.IntegerField(primary_key=True)
    ename = models.CharField(max_length=30, blank=True)
    job = models.CharField(max_length=27, blank=True)
    mgr = models.IntegerField(null=True, blank=True)
    hiredate = models.DateField(null=True, blank=True)
    sal = models.DecimalField(null=True, max_digits=7, decimal_places=2, blank=True)
    comm = models.DecimalField(null=True, max_digits=7, decimal_places=2, blank=True)
    deptno = models.IntegerField(null=True, blank=True)
    class Meta:
        db_table = u'emp'
    def __str__(self):
        return self.ename

class Dept(models.Model):
    deptno = models.IntegerField(primary_key=True)
    dname = models.CharField(max_length=42, blank=True)
    loc = models.CharField(max_length=39, blank=True)
    class Meta:
        db_table = u'dept'
    def __str__(self):
        return self.dname
[oracle@host python_hol]$

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

step 11) modify settings.py to include the new app.

mysite.python_hol

example:

INSTALLED_APPS = (
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.sites',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    # Uncomment the next line to enable the admin:
    # 'django.contrib.admin',
    # Uncomment the next line to enable admin documentation:
    # 'django.contrib.admindocs',
    'mysite.python_hol'            <<< newly added entry
)


step 12) modify settings.py to uncomment the admin app

INSTALLED_APPS = (
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.sites',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    # Uncomment the next line to enable the admin:
    'django.contrib.admin',                    <<<< uncommented here
    # Uncomment the next line to enable admin documentation:
    # 'django.contrib.admindocs',
    'mysite.python_hol'
)

step 13) synchronize with the db [here in case you have bonus and sal table you will face errors. Hence please remove them @ step 10]

dir: files/mysite
python manage.py syncdb

=========
[oracle@host mysite]$ python manage.py syncdb
Creating tables ...
Creating table auth_permission
Creating table auth_group_permissions
Creating table auth_group
Creating table auth_user_user_permissions
Creating table auth_user_groups
Creating table auth_user
Creating table auth_message
Creating table django_content_type
Creating table django_session
Creating table django_site
Creating table django_admin_log

You just installed Django's auth system, which means you don't have any superusers defined.
Would you like to create one now? (yes/no): yes
Username (Leave blank to use 'oracle'): pythonhol
E-mail address: pythonhol@example.com
Password:
Password (again):
Superuser created successfully.
Installing custom SQL ...
Installing indexes ...
No fixtures found.
==========


SQL> select table_name from dba_tables where owner='PYTHONHOL';

TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
DJANGO_ADMIN_LOG                <<< START HERE - newly created table
DJANGO_SITE
DJANGO_SESSION
DJANGO_CONTENT_TYPE
AUTH_MESSAGE
AUTH_USER
AUTH_USER_GROUPS
AUTH_USER_USER_PERMISSIONS
AUTH_GROUP
AUTH_GROUP_PERMISSIONS
AUTH_PERMISSION                <<< END HERE - newly created table
BONUS
SALGRADE
EMP
DEPT


You can see above all the tables that django framework created on the table, please note this tables didnt exist before.

step 14) Update the URL mappings. Edit ../mysite/urls.py. Uncomment the three lines that reference the admin application. This file maps URLs to application pages.

the file to look like below..


[oracle@host mysite]$ cat urls.py
from django.conf.urls.defaults import patterns, include, url

# Uncomment the next two lines to enable the admin:
from django.contrib import admin                <<uncommented
admin.autodiscover()                        <<uncommented

urlpatterns = patterns('',
    # Examples:
    # url(r'^$', 'mysite.views.home', name='home'),
    # url(r'^mysite/', include('mysite.foo.urls')),

    # Uncomment the admin/doc line below to enable admin documentation:
    # url(r'^admin/doc/', include('django.contrib.admindocs.urls')),

    # Uncomment the next line to enable the admin:
    url(r'^admin/', include(admin.site.urls)),            <<uncommented
)

step 15) To enable admin interface for emp and dept table

create file called admin.py under python_hol directory with the below contents

[oracle@host python_hol]$ cat admin.py
from mysite.python_hol.models import Emp, Dept
from django.contrib import admin
admin.site.register(Emp)
admin.site.register(Dept)

step 16) start the webserver

python manage.py runserver

Validating models...

0 errors found
Django version 1.3.1, using settings 'mysite.settings'
Development server is running at http://127.0.0.1:8000/
Quit the server with CONTROL-C.

..it stays here...


step 17) open web browser and go to http://localhost:8000/admin/

Now we are able to browse the employee by their names and dept by their names as well.

step 18) More enhancement to admin page

class EmpAdmin(admin.ModelAdmin):
    list_display = ('ename', 'job', 'hiredate', 'sal', 'comm')
    list_filter = ['hiredate']
    search_fields = ['ename']
    date_hierarchy = 'hiredate'

admin.site.register(Emp, EmpAdmin)


step 19) run the server and check the admin browser, you will see the changes.

************************** Done here with admin build  [step 9 to step 19]

step 20) build a custom page

the new url looks like below..

[oracle@host files]$ cat mysite/urls.py
from django.conf.urls.defaults import *
from mysite.python_hol.models import Emp

# Uncomment the next two lines to enable the admin:
from django.contrib import admin
admin.autodiscover()

info_dict = {
     'queryset': Emp.objects.all(),
}
employee_info = {'model' : Emp}

urlpatterns = patterns('',
     (r'^employees/$', 'django.views.generic.list_detail.object_list',
          dict(info_dict, template_name='employees/employee_list.html')),
     (r'^employees/create/$', 'django.views.generic.create_update.create_object', dict(employee_info,
          template_name='employees/employee_form.html', post_save_redirect='/employees/')),
     (r'^employees/update/(?P<object_id>\d+)/$', 'django.views.generic.create_update.update_object',
          dict(employee_info, template_name='employees/employee_form.html', post_save_redirect='/employees/')),
     (r'^employees/delete/(?P<object_id>\d+)/$', 'django.views.generic.create_update.delete_object',
     dict(employee_info, template_name='employees/employee_confirm_delete.html',
          post_delete_redirect='/employees/')),

     (r'^admin/', include(admin.site.urls)),

     (r'^site_media/(?P<path>.*)$', 'django.views.static.serve',
          {'document_root': '/home/pythonhol/mysite/python_hol/static'}),
)
[oracle@host files]


step 20.a) setup the templates referenced in the above urls.py

mkdir ../files/mysite/python_hol/templates
mkdir ../files/mysite/python_hol/templates/employees

step 21) introduce the template directory in settings.py file

..
TEMPLATE_DIRS = (
"/home/oracle/dba/files/mysite/python_hol/templates"
)
..

step 22) Create a Django template file that lists all employees:

cp ../files/employee_list.html ./files/mysite/python_hol/templates/employees/

edit the template to match the datamodel.column names as per models.py and also change object search from employee to emp

step 23) setup cascading style sheet...

mkdir ../files/mysite/python_hol/static
mkdir ../files/mysite/python_hol/static/css
cp ../files/python_hol.css ../files/mysite/python_hol/static/css


step 24) start the python server

python manage.py runserver

************************** Done here with custom build [step 20 to step 24]


************************** To create/update/delete records on emp table [step 25 to 26]

step 25) now to create/update record place the next 2 tamplate files.
from ../files/
cp employee_form.html mysite/python_hol/templates/employees

Replace all Employee reference by Emp & then change the edit field as appropriate.

step 26) now to delete record place employee_confirm_delete.html to templates directory

No need to edit this page, since it was having the right config.

************************** To start the webserver on different IP and port. [step 27]

step 27) now to start the web on a different ip and port...

python manage.py runserver 192.168.x.x:8000

Validating models...

0 errors found
Django version 1.3.1, using settings 'mysite.settings'
Development server is running at http://192.168.x.x:8000/
Quit the server with CONTROL-C.
[20/Jan/2019 07:46:46] "GET /employees HTTP/1.1" 301 0
[20/Jan/2019 07:46:46] "GET /employees/ HTTP/1.1" 200 4090
[20/Jan/2019 07:46:46] "GET /site_media/css/python_hol.css HTTP/1.1" 404 1796

====Image references

************************** connection pool config below... [step 29 to step 32]
Step 28) Connection Pool configuration

we need to configure django to accept the drcp connections... so wemake changes to settings.py to include the cclass.

[oracle@host mysite]$ more settings.py
# Django settings for mysite project.
import cx_Oracle                <<< import cx_Oracle
DEBUG = True
TEMPLATE_DEBUG = DEBUG

ADMINS = (
    # ('Your Name', 'your_email@example.com'),
)

MANAGERS = ADMINS

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.oracle', # Add 'postgresql_psycopg2', 'postgresql', 'mysql', 'sqlite3' or 'oracle'.
        'NAME': '<DBNAME>_drcp',                      # Or path to database file if using sqlite3.
        'USER': 'PYTHONHOL',                      # Not used with sqlite3.
        'PASSWORD': 'XXXXXX',                  # Not used with sqlite3.
        'HOST': '',                      # Set to empty string for localhost. Not used with sqlite3.
        'PORT': '',                      # Set to empty string for default. Not used with sqlite3.
        'OPTIONS': {
            'purity': cx_Oracle.ATTR_PURITY_SELF,
            'cclass': 'myappname',
        },
  }
}

---no change rest.

step 29) Start the connection pool @ the DB

SQL> execute dbms_connection_pool.start_pool();

PL/SQL procedure successfully completed.

step 30) create the tne entry rmn01tst_drcp

<DBNAME>_drcp=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <host>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = POOLED)
      (SERVICE_NAME = <DBNAME>)
    )
  )


step 31) restart the server

python manage.py runserver 192.168.x.x:8000

step 32) verify the connection pool info.


select * from V$CPOOL_CC_INFO;
select * from V$CPOOL_CC_STATS;
select * from V$CPOOL_STATS;
select server,sid,serial#,username,status,state,sql_id,event from v$session order by server;

SQL> select * from V$CPOOL_CC_INFO;

POOL_NAME                                     CCLASS_NAME                        CON_ID
--------------------------------------------- ------------------------------ ----------
SYS_DEFAULT_CONNECTION_POOL                   PYTHONHOL.myappname                     0
SYS_DEFAULT_CONNECTION_POOL                   SYSTEM.SHARED                           0

SQL> select * from V$CPOOL_CC_STATS;

CCLASS_NAME                    NUM_REQUESTS   NUM_HITS NUM_MISSES  NUM_WAITS  WAIT_TIME CLIENT_REQ_TIMEOUTS NUM_AUTHENTICATIONS     CON_ID
------------------------------ ------------ ---------- ---------- ---------- ---------- ------------------- ------------------- ----------
PYTHONHOL.myappname                       7          3          4          0          0                   0                   7    0        << 3 browser pages, 3 hits.
SYSTEM.SHARED                             2          0          2          0          0                   0                   2    0

SQL> select * from V$CPOOL_STATS;

POOL_NAME                                     NUM_OPEN_SERVERS NUM_BUSY_SERVERS NUM_AUTH_SERVERS NUM_REQUESTS   NUM_HITS NUM_MISSES  NUM_WAITS    WAIT_TIME CLIENT_REQ_TIMEOUTS NUM_AUTHENTICATIONS NUM_PURGED HISTORIC_MAX     CON_ID
--------------------------------------------- ---------------- ---------------- ---------------- ------------ ---------- ---------- ---------- ---------- ------------------- ------------------- ---------- ------------ ----------
SYS_DEFAULT_CONNECTION_POOL                                  4                0                0            9          3   6           0          0                   0                   9          0            4          0

SQL> select server,sid,serial#,username,status,state,sql_id,event from v$session order by server;

SERVER           SID    SERIAL# USERNAME   STATUS   STATE               SQL_ID        EVENT
--------- ---------- ---------- ---------- -------- ------------------- ------------- ----------------------------------------------------------------
...
POOLED           280        389 PYTHONHOL  INACTIVE WAITING                           pool server timer

So we see pooled connections.

Thanks for paying the visit.