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