IKH

Menu

Oracle Database

  • 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')

Name
Email
Phone

Report an error

Prev
Next