Oracle Database Connection in Python

Steps to Connect Python to Oracle using cx_Oracle connect

For communicating any database with our Python program, then we required some connector which is nothing but the cx_Oracle module.

1. Install the cx_Oracle package

   For installing cx_Oracle :

   pip install cx_Oracle

2. Connect Python to Oracle

   • Import database specific module

     import cx_Oracle

   • connect():

     Now Establish a connection between Python program and Oracle database by using connect() function.

     con = cx_Oracle.connect('username/password@localhost')

   • cursor():

     To execute sql query and to provide result some special object required is nothing but cursor() object

     cursor = cx_Oracle.cursor()

   • execute method :

     cursor.execute(sqlquery) -- -- -> to execute single query.

     cursor.execute(sqlqueries) -- -- -> to execute a group of multiple sqlquery seperated by ";"

   • commit():

     For DML(Data Manuplate Language) operation we need to commit() to reflect in database.

   • Fetch():

     This retrieves the next row of a query result set and returns a single sequence, or None if no more rows are available.

   • close():

     After all done mendentory to close all operation

     cursor.close()
     con.close()

   Connection using HOST and SERVICE NAME

   import cx_Oracle
   
   try: 
   
   dsn_tns = cx_Oracle.makedsn('Host Name', 'Port Number', service_name='Service Name') 
   conn = cx_Oracle.connect(user=r'User Name', password='Personal Password', dsn=dsn_tns) 
   
   c = conn.cursor()
   
   c.execute('select * from database.table') 
   
   for row in c:
       print (row[0], '-', row[1])
   
   except cx_Oracle.DatabaseError as e: 
       print("There is a problem with Oracle", e)
   
   #conn.close()
   
   finally: 
       if cursor: 
           cursor.close() 
       if con: 
           con.close() 

   Note -if needed, place an 'r' before any parameter in order to address any special character such as '\'.