Python Programming for Oracle Database

Connect to Oracle Database by using Python

 

Let us check the python version and the OS bits:-

 

C:\Users\welcome>python

Python 3.6.2 (v3.6.2:5fd33b5, Jul  8 2017, 04:57:36) [MSC v.1900 64 bit (AMD64)] on win32

Type "help", "copyright", "credits" or "license" for more information.

>>> import sys

>>> print(sys.version)

3.6.2 (v3.6.2:5fd33b5, Jul  8 2017, 04:57:36) [MSC v.1900 64 bit (AMD64)]

 

Next install cx_Oracle from the command line:-

C:\Users\welcome>pip install cx_Oracle

Collecting cx_Oracle

  Downloading https://files.pythonhosted.org/packages/8b/d6/8a3d8d257619ea3a30bf6d73c449fc2d37f3b40f4b8cfe896b486a5f46e0/cx_Oracle-8.0.1-cp36-cp36m-win_amd64.whl (201kB)

    100% |████████████████████████████████| 204kB 930kB/s

Installing collected packages: cx-Oracle

Successfully installed cx-Oracle-8.0.1

You are using pip version 9.0.1, however, version 20.2.3 is available.

You should consider upgrading via the 'python -m pip install --upgrade pip' command.

 

C:\Users\welcome>

 

 

Here we are upgrading pip version into 20.2.3

 

C:\Users\welcome>python -m pip install --upgrade pip

Cache entry deserialization failed, entry ignored

Collecting pip

  Cache entry deserialization failed, entry ignored

  Downloading https://files.pythonhosted.org/packages/4e/5f/528232275f6509b1fff703c9280e58951a81abe24640905de621c9f81839/pip-20.2.3-py2.py3-none-any.whl (1.5MB)

    100% |████████████████████████████████| 1.5MB 550kB/s

Installing collected packages: pip

  Found existing installation: pip 9.0.1

    Uninstalling pip-9.0.1:

      Successfully uninstalled pip-9.0.1

Successfully installed pip-20.2.3

 C:\Users\welcome>

 

Connect to Oracle Database:-

import cx_Oracle

con=cx_Oracle.connect('c##ashok369/*******@ASHOK-cloud/ORCL12C')

ver = con.version.split(".")

for v in ver:

    print (v)

    if v == "12":

        print ("Its12")

    else:

        print ("Not12")

con.close()

SELECT Query :-

import cx_Oracle

con=cx_Oracle.connect('c##ashok369/*******@ASHOK-cloud/ORCL12C')

cur=con.cursor()

cur.execute('select * from BIGTAB where rownum<10')

for result in cur:

    print (result)

cur.close()

con.close()

Fetching Data:- 

Fetchone() Function 

fetchone() method to return just a single row as a tuple . When called multiple times then consecutive rows are returned

import cx_Oracle

con=cx_Oracle.connect('c##ashok369/*******@ASHOK-cloud/ORCL12C')

cur=con.cursor()

cur.execute('select * from BIGTAB order by rownum')

row = cur.fetchone()

print (row)

row = cur.fetchone()

print (row)

row = cur.fetchone()

print (row)

row = cur.fetchone()

print (row)

row = cur.fetchone()

print (row)

row = cur.fetchone()

print (row)

row = cur.fetchone()

print (row)

row = cur.fetchone()

print (row)

row = cur.fetchone()

print (row)

row = cur.fetchone()

print (row)

cur.close()

con.close()

SELECT ROWS MANY:-

import cx_Oracle

con=cx_Oracle.connect('c##ashok369/********@ASHOK-cloud/ORCL12C') 

cur=con.cursor()

cur.execute('select * from BIGTAB order by 1')

res=cur.fetchmany(numRows=3)

print(res)

cur.close()

con.close()

C:\Users\welcome>python SELECTMANY.py

[(0, -353815256, datetime.datetime(2018, 12, 19, 12, 30, 31)), (0, -1689077988, datetime.datetime(2019, 11, 3, 0, 42, 33)), (0, 753485672, datetime.datetime(2020, 1, 13, 15, 16, 52))]

C:\Users\welcome>

The fetchmany() method returns a list of tuples . Here the numRows parameter specifcices that three rows should be returned 

SELECT ALL ROWS :-

import cx_Oracle

con=cx_Oracle.connect('c##ashok369/*******@ASHOK-cloud/ORCL12C') 

cur = con.cursor()

cur.execute('select * from BIGTAB order by 1')

res = cur.fetchall()

for r in res:

    print (r)

cur.close()

con.close()

Query performance improvement using Python method:

Here we will improve the query performance by increasing the number of rows returned in each batch from Oracle to the Python program.

Step1:-

import time

import cx_Oracle

 con=cx_Oracle.connect('c##ashok369/********@ASHOK-cloud/ORCL12C')

start = time.time()

cur = con.cursor()

cur.arraysize = 100      /* we set 100 for arraysize which catches the data of 100 records at time to be retuned from the database to a cache in Python. */

cur.execute('select * from bigtab')

res = cur.fetchall()

 

# print res #

elapsed = (time.time() - start)

print (elapsed, "seconds")

 cur.close()

con.close()

We set the arraysize 100 which causes batches of 100 records at a time to be returned from the database to a cache in python. 

this will reduces the number of roundtrips made to the database often reducing network and reducing the number of context switches on the database server.

Now let us change the arraysize to 1000 then we will see below