Sep 3, 2018

Python with Oracle Database: Installing cx_Oracle and Oracle Instant Client on macOS

In order to access oracle database with python we need to install
  • cx_Oracle - Python Interface for Oracle Database. It is a python extension module that enables access to Oracle Database.  
  • Oracle Instant Client - Oracle Instant Client enables applications to connect to a local or remote Oracle Database.The Instant Client libraries provide the necessary network connectivity, as well as basic and high end data features, to make full use of Oracle Database.)
Python3 and cx_Oracle : Execute following command to install cx_Oracle python package.
➜  ~ pip3 install cx_Oracle --pre
Collecting cx_Oracle
  Could not fetch URL https://pypi.python.org/simple/cx-oracle/: There was a problem confirming the ssl certificate: [SSL: TLSV1_ALERT_PROTOCOL_VERSION] tlsv1 alert protocol version (_ssl.c:719) - skipping
  Could not find a version that satisfies the requirement cx_Oracle (from versions: )
No matching distribution found for cx_Oracle

Error : Could not find a version that satisfies the requirement cx_Oracle (from versions: )

In order to solve above problem execute following command.

➜  ~ sudo curl https://bootstrap.pypa.io/get-pip.py | python3
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 1604k  100 1604k    0     0   410k      0  0:00:03  0:00:03 --:--:--  410k
Collecting pip
  Using cached https://files.pythonhosted.org/packages/5f/25/e52d3f31441505a5f3af41213346e5b6c221c9e086a166f3703d2ddaf940/pip-18.0-py2.py3-none-any.whl
Collecting wheel
  Using cached https://files.pythonhosted.org/packages/81/30/e935244ca6165187ae8be876b6316ae201b71485538ffac1d718843025a9/wheel-0.31.1-py2.py3-none-any.whl
Installing collected packages: pip, wheel
  Found existing installation: pip 9.0.1
    Uninstalling pip-9.0.1:
      Successfully uninstalled pip-9.0.1
  The script wheel is installed in '/Library/Frameworks/Python.framework/Versions/3.5/bin' which is not on PATH.
  Consider adding this directory to PATH or, if you prefer to suppress this warning, use --no-warn-script-location.
Successfully installed pip-18.0 wheel-0.31.1

Now again run command to install cx_Oracle, it should execute successfully.
➜  ~ pip3 install cx_Oracle --pre                           
Collecting cx_Oracle
Installing collected packages: cx-Oracle
Successfully installed cx-Oracle-6.4.1

Install Oracle Instant Client fdaf: Please refer below link to setup instant client.
https://cx-oracle.readthedocs.io/en/latest/installation.html#installing-cx-oracle-on-macos

Sample python code to connect Database and execute SELECT query & iterate over result set.

import cx_Oracle
import db_constant
from contextlib import closing

def get_oracle_db_config():
    db_url = db_constant.USERNAME+"/"+db_constant.PASSWORD+"@"+\
        db_constant.DATABASE_URL+":"+db_constant.PORT+"/"+db_constant.SERVICE_POINT
    connection = cx_Oracle.connect(db_url)
    return connection

def get_address_details(conn):
    try:
           with closing(conn.cursor()) as cur:
               # Execute the SQL command
               cur.execute(db_constant.SELECT_ADDRESS)
               # Fetch all the rows in a list of lists.
               results = cur.fetchall()
               if len(results) > 0:
                   for row in results:
                      print("------ Customer address -------")
                      address = row[1]
                      landmark = row[2]
                      zip_code = row[3]
                      email = row[4]
                      phone = row[5]
                      print("Address: "+ str(address) +" Landmark: "
                            + str(landmark) + " ZipCode: "+str(zip_code))
                      print("Email: " + str(email) + " Phone: " + str(phone))
                      print("\n")
    except Exception as e:
           print("Exception occured in execution.... : ")
           print(e)
           exit()
    finally:
          conn.close()
#starts here.....
if __name__=="__main__":
    print ("**************Start DB Operation****************")
    conn = get_oracle_db_config()
    get_address_details(conn)
    print ("**************Finished DB Operation****************")

Note: db_constant is constant file where I have stored USERNAME, PASSWORD and database details. I have deliberately not shared that constant file.

Git Source: https://github.com/zytham/Python/tree/master/Python-Oracle
Location: Bengaluru, Karnataka, India

2 comments:

  1. I am glad that I saw this post. It is informative blog for us and we need this type of blog thanks for share this blog, Keep posting such instructional blogs and I am looking forward for your future posts.
    Python Projects for Students

    Data analytics is the study of dissecting crude data so as to make decisions about that data. Data analytics advances and procedures are generally utilized in business ventures to empower associations to settle on progressively Python Training in Chennai educated business choices. In the present worldwide commercial center, it isn't sufficient to assemble data and do the math; you should realize how to apply that data to genuine situations such that will affect conduct. In the program you will initially gain proficiency with the specialized skills, including R and Python dialects most usually utilized in data analytics programming and usage; Python Training in Chennai at that point center around the commonsense application, in view of genuine business issues in a scope of industry segments, for example, wellbeing, promoting and account.

    Project Center in Chennai

    ReplyDelete