- From Python Program if we want to communicate with any database,some translator must be
- required to translate Python calls into Database specific calls and Database specific calls into
- Python calls.This translator is nothing but Driver/Connector.
- Diagram
- For Oracle database the name of driver needed is cx_Oracle.
- cx_Oracle is a Python extension module that enables access to Oracle Database.It can be used for
- both Python2 and Python3. It can work with any version of Oracle database like 9,10,11 and 12.
Installing cx_Oracle
- From Normal Command Prompt (But not from Python console)execute the following command
- D:\python_classes>pip install cx_Oracle
- Collecting cx_Oracle
- Downloading cx_Oracle-6.0.2-cp36-cp36m-win32.whl (100kB)
- 100% |———–| 102kB 256kB/s
- Installing collected packages: cx-Oracle
- Successfully installed cx-Oracle-6.0.2
How to Test Installation
- From python console execute the following command.
- >>>help(”method”)
- In the output we can see cx_Oracle
- _multiprocessing crypt ntpath timeit
- _opcode csv nturl2path tkinter
- _operator csvr numbers token
- _osx_support csvw opcode tokenize
- _overlapped ctypes operator trace
- _pickle curses optparse traceback
- _pydecimal custexcept os tracemalloc
- _pyio cx_Oracle parser try
- _random data pathlib tty
- _sha1 datetime pdb turtle
- sha256 dbm pick turtledemo
- _sha3 decimal pickle types
- _sha512 demo pickletools typing
- _signal difflib pip unicodedata
- _sitebuiltins dis pipes unittest
- _socket distutils pkg_resources unpick
- _sqlite3 doctest pkgutil update
- _sre dummy_threading platform urllib
- _ssl durgamath plistlib uu
- _stat easy_install polymorph uuid
App1: Program to connect with Oracle database and print its version.
Python
import cx_Oracle
con=cx_Oracle.connect('scott/tiger@localhost')
print(con.version)
con.close()
Output
PowerShell
D:\python_classes>py db1.py
11.2.0.2.0
App2: Write a Program to create employees table in the oracle databaseemployees(eno,ename,esal,eaddr)
Python
import cx_Oracle
try:
con=cx_Oracle.connect('scott/tiger@localhost')
cursor=con.cursor()
cursor.execute("create table employees(eno number,ename varchar2(10),esal number(10,2),eaddr varchar2(10))")
print("Table created successfully")
except cx_Oracle.DatabaseError as e:
if con:
con.rollback()
print("There is a problem with sql",e)
finally:
if cursor:
cursor.close()
if con:
con.close()
App3: Write a program to drop employees table from oracle database?
Python
import cx_Oracle
try:
con=cx_Oracle.connect('scott/tiger@localhost')
cursor=con.cursor()
cursor.execute("drop table employees")
print("Table dropped successfully")
except cx_Oracle.DatabaseError as e:
if con:
con.rollback()
print("There is a problem with sql",e)
finally:
if cursor:
cursor.close()
if con:
con.close()
App3: Write a program to insert a single row in the employees table.
Python
import cx_Oracle
try:
con=cx_Oracle.connect('scott/tiger@localhost')
cursor=con.cursor()
cursor.execute("insert into employees values(100,'Durga',1000,'Hyd')")
con.commit()
print("Record Inserted Successfully")
except cx_Oracle.DatabaseError as e:
if con:
con.rollback()
print("There is a problem with sql",e)
finally:
if cursor:
cursor.close()
if con:
con.close()
Note
- While performing DML Operations (insert|update|delte), compulsory we have to use commit() method,then only the results will be reflected in the database.
App4: Write a program to insert multiple rows in the employees table by using executemany() method.
Python
import cx_Oracle
try:
con=cx_Oracle.connect('scott/tiger@localhost')
cursor=con.cursor()
sql="insert into employees values(:eno,:ename,:esal,:eaddr)"
records=[(200,'Sunny',2000,'Mumbai'),
(300,'Chinny',3000,'Hyd'),
(400,'Bunny',4000,'Hyd')]
cursor.executemany(sql,records)
con.commit()
print("Records Inserted Successfully")
except cx_Oracle.DatabaseError as e:
if con:
con.rollback()
print("There is a problem with sql",e)
finally:
if cursor:
cursor.close()
if con:
con.close()
App5: Write a program to insert multiple rows in the employees table with
dynamic input from the keyboard?
Python
import cx_Oracle
try:
con=cx_Oracle.connect('scott/tiger@localhost')
cursor=con.cursor()
while True:
eno=int(input("Enter Employee Number:"))
ename=input("Enter Employee Name:")
esal=float(input("Enter Employee Salary:"))
eaddr=input("Enter Employee Address:")
sql="insert into employees values(%d,'%s',%f,'%s')"
cursor.execute(sql %(eno,ename,esal,eaddr))
print("Record Inserted Successfully")
option=input("Do you want to insert one more record[Yes|No] :")
if option=="No":
con.commit()
break
except cx_Oracle.DatabaseError as e:
if con:
con.rollback()
print("There is a problem with sql :",e)
finally:
if cursor:
cursor.close()
if con:
con.close()
App6: Write a program to update employee salaries with increment for the
certain range with dynamic input
Example
- Increment all employee salaries by 500 whose salary < 5000
Python
import cx_Oracle
try:
con=cx_Oracle.connect('scott/tiger@localhost')
cursor=con.cursor()
increment=float(input("Enter Increment Salary:"))
salrange=float(input("Enter Salary Range:"))
sql="update employees set esal=esal+%f where esal<%f"
cursor.execute(sql %(increment,salrange))
print("Records Updated Successfully")
con.commit()
except cx_Oracle.DatabaseError as e:
if con:
con.rollback()
print("There is a problem with sql :",e)
finally:
if cursor:
cursor.close()
if con:
con.close()
App7: Write a program to delete employees whose salary greater provided
salary as dynamic input?
Example
- delete all employees whose salary > 5000
Python
import cx_Oracle
try:
con=cx_Oracle.connect('scott/tiger@localhost')
cursor=con.cursor()
cutoffsalary=float(input("Enter CutOff Salary:"))
sql="delete from employees where esal>%f"
cursor.execute(sql %(cutoffsalary))
print("Records Deleted Successfully")
con.commit()
except cx_Oracle.DatabaseError as e:
if con:
con.rollback()
print("There is a problem with sql :",e)
finally:
if cursor:
cursor.close()
if con:
con.close()
App8: Write a program to select all employees info by using fetchone()
method?
Python
import cx_Oracle
try:
con=cx_Oracle.connect('scott/tiger@localhost')
cursor=con.cursor()
cursor.execute("select * from employees")
row=cursor.fetchone()
while row is not None:
print(row)
row=cursor.fetchone()
except cx_Oracle.DatabaseError as e:
if con:
con.rollback()
print("There is a problem with sql :",e)
finally:
if cursor:
cursor.close()
if con:
con.close()
App9: Write a program to select all employees info by using fetchall()
method?
Python
import cx_Oracle
try:
con=cx_Oracle.connect('scott/tiger@localhost')
cursor=con.cursor()
cursor.execute("select * from employees")
data=cursor.fetchall()
for row in data:
print("Employee Number:",row[0])
print("Employee Name:",row[1])
print("Employee Salary:",row[2])
print("Employee Address:",row[3])
print()
print()
except cx_Oracle.DatabaseError as e:
if con:
con.rollback()
print("There is a problem with sql :",e)
finally:
if cursor:
cursor.close()
if con:
con.close()
App10: Write a program to select employees info by using fetchmany()
method and the required number of rows will be provided as dynamic input?
Python
import cx_Oracle
try:
con=cx_Oracle.connect('scott/tiger@localhost')
cursor=con.cursor()
cursor.execute("select * from employees")
n=int(input("Enter the number of required rows:"))
data=cursor.fetchmany(n)
for row in data:
print(row)
except cx_Oracle.DatabaseError as e:
if con:
con.rollback()
print("There is a problem with sql :",e)
finally:
if cursor:
cursor.close()
if con:
con.close()
Output
PowerShell
D:\python_classes>py test.py
Enter the number of required rows:3
(100, 'Durga', 1500.0, 'Hyd')
(200, 'Sunny', 2500.0, 'Mumbai')
(300, 'Chinny', 3500.0, 'Hyd')
D:\python_classes>py test.py
Enter the number of required rows:4
(100, 'Durga', 1500.0, 'Hyd')
(200, 'Sunny', 2500.0, 'Mumbai')
(300, 'Chinny', 3500.0, 'Hyd')
(400, 'Bunny', 4500.0, 'Hyd')