Oracle connection in Python

This post briefly explains about how python can able to connect Oracle database.

unlike other object oriented programing languages, python has better way to connect Oracle database with simple steps. this includes:

        1. JPype 

        2.JayDeBeApi

Since Oracle connection is much more easy in Java than other OOP languages, direct connection will be little bit expensive. So, for this, JPype is the best third party module which can be available for free to download.

before going into the actual details of the Oracle connection, we will learn what is JPype and Jaydebeapi.

JPype:

         JPype is a third party module, which will be useful for integration of Java to Python. By using JPype, we can access full Java library including odbc and other database connections. 

        Yes, you may have one doubt that why we are interesting in JPype , why not JPython (formally known as Jython) ? 

        As we know that, Jython is always  suffers from a large number of drawbacks, i.e. it always lags behind CPython, it is slow and it does not allow access to most Python extensions. this is the reason, we are moving to JPype.

 JayDeBeApi:

          This is also third party module which is useful to connect database using java JDBC. It provides a Python DB-API v2.0 to that database. By using this module, we can connect to oracle database and it is possible to get connection object for future use.

By using these two modules we can make Oracle database connection very easily. 

After downloading and installing these two modules., 

1. Import modules

2. set path environmental variable for JRE

3. start JVM (Java Virtual Machine) by using JPype

4. make connection using JayDeBeApi

5. Get cursor object and do queries on database.

following is the abstract code in Python which is useful to make connection to Oracle database.

     import jpype, jaydebeapi

     import os

     os.environ['JAVA_HOME'] = "<path to jre>"

     jpype.startJVM("<path to jvm.dll>","<path to ojdbc6.jar>")

     conn = jaydebeapi.connect('oracle.jdbc.driver.OracleDriver','jdbc:oracle:thin:    <username>/<password>@<server IP>:<port>/<SID>')

    cur = conn.cursor()

    cur.execute("<your query to database>")

    conn.commit() #for commiting queries

    

And remenber that ulike SQLIte3 and PostgreSQL, Oracle database connection's cursor object can not be iterate. for this, we need to fetch data by using fetchall() or fetchone() methods, and insert into list and process the data

 Ex:

        data = cur.fetchall()

        for items in data:

             <your operations>

By this, we can make connection to Oracle database with Python.