About cx_Oracle

cx_Oracle is a Python extension module that enables access to Oracle Database. It conforms to the Python database API 2.0 specification with a considerable number of additions and a couple of exclusions.

Overview

To use cx_Oracle 7 with Python and Oracle Database you need:

Python 2.7 or 3.5 and higher. Older versions of cx_Oracle may work with older versions of Python.

Oracle client libraries. These can be from the free Oracle Instant Client, or those included in Oracle Database if Python is on the same machine as the database. Oracle client libraries versions 18, 12, and 11.2 are supported on Linux, Windows and macOS. Users have also reported success with other platforms.

An Oracle Database. Oracle’s standard client-server version interoperability allows cx_Oracle to connect to both older and newer databases.

Quick Start cx_Oracle Installation

An installation of Python is needed. Python 2.7 and Python 3.5 and higher are supported by cx_Oracle 7.

Install cx_Oracle from PyPI with:

python -m pip install cx_Oracle --upgrade

Pre-requisites

1) Oracle data base
http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/Windows_DB_Install_OBE/Installing_Oracle_Db12c_Windows.html

2) Oracle instant client
https://www.oracle.com/technetwork/topics/winsoft-085727.html

3) cx_Oracle though command prompt
Command should execute in command prompt: pip install cx-Oracle

4) cx_Oracle in Pycharm
Select project-->File-->Settings-->Project interpreter--> Click on + -->cx_Oracle-->select-->install package.

Database operations using Pyhton(cx_Oracle Module)

1) Connect to database
2) How to execute queries(insert, update, delete)
3) How to select data from database
4) Data driven testing

Connect to database

import cx_Oracle
import os
os.environ['PATH']='E:\\app\\OracleHomeUser1\\instantclient_18_3'

#Establish connection to the database
con=cx_Oracle.connect("hr","hr","localhost/pdborcl")
print("Connected!!!")
con.close()

How to execute queries(insert, update, delete)

import cx_Oracle
import os
os.environ['PATH']='E:\\app\\OracleHomeUser1\\instantclient_18_3'

#Establish connection to the database
con=cx_Oracle.connect("hr","hr","localhost/pdborcl")

cur=con.cursor()

query1="insert into student values(102,'JOHN')"
query2="update student set sname='XYZ' where sid=102"
query3="delete student where sid=102"

cur.execute(query3)

cur.close()
con.commit()
con.close()

print("Completed!!!")

How to select data from database 

import cx_Oracle
import os
os.environ['PATH']='E:\\app\\OracleHomeUser1\\instantclient_18_3'

#Establish connection to the database
con=cx_Oracle.connect("hr","hr","localhost/pdborcl")

cur=con.cursor()

query="select * From employees"

cur.execute(query)

for cols in cur:
    print(cols[0],"     ",cols[1],"     ",cols[2])

cur.close()
con.close()

print("Completed!!!")

Data Driven testing

from selenium import webdriver
import time
import cx_Oracle
import os
os.environ['PATH']='E:\\app\\OracleHomeUser1\\instantclient_18_3'

driver=webdriver.Chrome(executable_path="C:\Drivers\chromedriver_win32\chromedriver.exe")

driver.get("http://newtours.demoaut.com/")
driver.maximize_window()

#Establish connection to the database
con=cx_Oracle.connect("hr","hr","localhost:1521/pdborcl")

cur=con.cursor()
query="select * From users"
cur.execute(query)

for cols in cur:
    driver.find_element_by_name("userName").send_keys(cols[0])
    driver.find_element_by_name("password").send_keys(cols[1])
    driver.find_element_by_name("login").click()
    time.sleep(5)

    # validation started
    if driver.title == "Find a Flight: Mercury Tours:":
        print("Test passed")
    else:
        print("Test failed")
    driver.find_element_by_link_text("Home").click()

cur.close()
con.close()

print("Data Driven test Completed!!!")