Srikanth Technologies

Accessing Oracle From Python

Python supports access to different databases. It provides a standard API called DB-API to access different databases in a uniform way.

In this blog, We learn how to connect to Oracle using cx_Oracle package, which is an implementation of DB-API, and perform some operations with Oracle Database.

Getting Oracle Database ready

We work with Oracle Database 11g Express Edition (XE). If you have not yet installed it, go to Oracle Database 11g Express Edition and download the version relevant to your platform.

For detailed instructions regarding installation and other required steps, watch my video on Get Started With Oracle Database 11g XE.

Installing Python and cx_Oracle

We work with Python 3.6. Download and install Python 3.6 for your platform. Any other version of Python should do.

The python package that is used to connect to Oracle from Python is cx_Oracle

Go to directory where pip.exe (or pip3.6.exe) is present and give the following command. Pip.exe is present in /Scripts folder in Windows installation. Check your platform for details. 

pip  install cx_Oracle
For more information about cx_oracle, go to cx_Oracle - Python Interface for Oracle Database

Installing Instant Client

In order to access Oracle from Python, you need to install Instant Client that is specific to your platform.

Go to Instant Client and select download for your platform.

You need to download Instant Client 64 bit if your Python is 64 bit otherwise download 32 bit version. Python edition and Instant Client edition must be same.

It is enough to download Instant Client Basic Light version as we need support for only English.

You can check which version of Python you are using when you start REPL. 

C:\python>python
Python 3.6.4 (v3.6.4:d48eceb, Dec 19 2017, 06:04:45) [MSC v.1900 32 bit  (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>>

Just extract .zip file in a folder and remember that folder as we have to include that folder in system PATH for Python to use .DLLs in that folder to connect to Oracle.

Connecting to Oracle Database

Once Oracle, Python, cx_Oracle package and Instant Client are ready, we can connect to Oracle from Python.

The following program will connect to Oracle Database using username hr and password hr. In case you are trying to use a different account or a different version of Oracle database then feel free to change the details and experiment. 

import os
import cx_Oracle

# Set folder in which Instant Client is installed in system path
os.environ['PATH'] = 'c:\\oraclexe\\client'

# Connect to hr account in Oracle Database 11g Express Edition
con = cx_Oracle.connect("hr", "hr", "localhost/xe")
print("Connected!")
con.close()

Performing DML operation

The following programs demonstrate how to use cx_Oracle to insert a row into JOBS table.

In order to pass parameters, we need to use bind variable that are prefixed with : (colon).

We can use either numbers or meaningful names for bind variables. If we use names then those names can be passed at the time of providing values as shown in the first example. 

import os
import cx_Oracle

os.environ['PATH'] = 'c:\\oraclexe\\client'
con = cx_Oracle.connect("hr/hr@localhost")

cur = con.cursor()
cur.execute("insert into jobs values(:id,:title,:min,:max)", id='PYP', title='Python Programmer', min=5000, max=10000);
print("Inserted Job Successfully!")
cur.close()

con.commit()
con.close()
The following program is same as above, except that it uses numbers for bind variables and sends data to execute method using a tuple where each element corresponds to bind variable in the command in the order given. First element in tuple replaces bind variable :1 and second value :2 and so on. 

import os
import cx_Oracle

os.environ['PATH'] = 'c:\\oraclexe\\client'
con = cx_Oracle.connect("hr/hr@localhost")

cur = con.cursor()
cur.execute("insert into jobs values(:1,:2,:3,:4)", ('PYP', 'Python Programmer', 5000, 10000));
print("Inserted Job Successfully!")
cur.close()

con.commit()
con.close()

Executing Query

It is possible to execute SELECT command and retrieve data from database in the form of list of tuples.

Method execute() of cursor object should use query to be executed and then use fetchall() (or some other fetch methods) to retrieve data in the form of tuples.

The following program displays all jobs titles in ascending order of title. 

import os
import cx_Oracle
    
os.environ['PATH'] = 'c:\\oraclexe\\client'
con = cx_Oracle.connect("hr/hr@localhost")

cur = con.cursor()
cur.execute("select * from jobs order by job_title")

for job in cur:
   print(job[1])

cur.close()
con.close()

Calling Stored Procedures

A stored procedure is a program written in PL/SQL and stored in database. It encapsulates business logic and business rules related to a single task. We create a stored procedure called LOW_HIGH_SALARIES, which sends lowest and highest salaries being paid to employees in the given department in the form of output parameters. 

create or replace procedure low_high_salaries(p_dept number, p_low out number, p_high out number)
is
begin
   select min(salary), max(salary) into p_low, p_high
   from employees
   where department_id = p_dept;
end;

Now, lets call this procedure by passing three parameters - first one is department id, second and third parameters are variables created by using var() method of cursor object.

We need to use callproc() method of cursor object to call a stored procedure. Parameters are passed through a tuple. 

import os
import cx_Oracle

os.environ['PATH'] = 'c:\\oraclexe\\client'
con = cx_Oracle.connect("hr/hr@localhost")

cur = con.cursor()

# Create output variables

high = cur.var(int)
low = cur.var(int)

# Call stored procedure
cur.callproc('low_high_salaries', (80, low, high)) 

# Print values of out variables - getvalue() returns value of variable 
print("Lowest is %d and highest is %d" % (low.getvalue(), high.getvalue())) 

cur.close()
con.close()

Well, that's all for now.

For more information about how to use cx_Oracle package, refer to their official documentation and samples.