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.

Iterators and Iterable

Hi ,

here I am going to tell about iterators and iterables in Python. Most of the Python developers are getting confuse what is iterator, iterable and when to use it.

Here  I am explaining in brief which may helpful to get basic knowledge of both.

Iterable

"An object capable of return its members one at a time"

Let me explain more clear.,

If any object is returning its item at a time either by iterating or by returning, it is called Iterable.

for example:: list, str, tuple ----> sequence types

                    dict, file ---------> non-sequence types. For more information about data structures, please click here

One can do this, by implementing __iter__()  method for returning items.

Iterators 

"It is an object which is representation of stream of data."

Confused...!

basically, It can be a user defined or pre-defined which will be having data and can able to send those data by using next() method for taking items. Iterator works till the end of the data. If their is no item, then it will raise stopInteration exception. Once the stopIteration exception came, then the loop will exit.

      When we are talking about Iterators, everyone will think about the iterator's data. but consider a situation, where we require iterator object itself. then, how you will pass object itself?

You can do this, by implementing __iter__() method  which will return iterator itself. As mentioned above, if any object is implementing __iter__() method, it is called  "Iterable". i.e., "every iterator is iterable" :)