Python Oracle DB Tutorial

---

    

Import oracledb Module

	      		import oracledb
	      

If module not available in your System. Then install using pip

	      		$python -m pip install python-oracledb
	      

Python Oracle DB connection

     oracledb module has method called connect, It takes various parameters like user,password,data source name(dsn),port,portocol, service_name,sid, server_type etc., which establishes connection to Oracle Database.

Steps Required to Connect to Oracle Database

1. Get Connection Object
2. Get Cursor Object
3. Execute SQL Statement
4. Commit the transaction
5. Close the Connection

Here We are going to use HR sample schema to Query, INSERT ,UPDATE and DELETE Operations

Connecting to Oracle DB using DSN(Data Source Name)

      	
      	conn=oracledb.connect(dsn="hr@//localhost:1521/xe",user="hr",password="ora123")
      	

Connecting to Oracle DB passing Individual Fields,
    User,password,service Identifier(sid),host where Oracle DB is running and Port on which Database can be accessed.

	
	conn=oracledb.connect(user="hr",password="ora123",port=1521,sid="XE",host="localhost")

      	

HR Schema in Oracle Database has various Tables called

DEPARTMENTS,
EMPLOYEES,
JOBS,
JOB_HISTORY
etc.,

QUERY Data

     Retrieve Data from Employees Table. Employees Table has following columns

SQL> desc Employees
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID				   NOT NULL NUMBER(6)
 FIRST_NAME					    VARCHAR2(20)
 LAST_NAME				   NOT NULL VARCHAR2(25)
 EMAIL					   NOT NULL VARCHAR2(25)
 PHONE_NUMBER					    VARCHAR2(20)
 HIRE_DATE				   NOT NULL DATE
 JOB_ID 				   NOT NULL VARCHAR2(10)
 SALARY 					    NUMBER(8,2)
 COMMISSION_PCT 				    NUMBER(2,2)
 MANAGER_ID					    NUMBER(6)
 DEPARTMENT_ID					    NUMBER(4)
      	

To Query Data from Oracle Table, User must open database Cursor. Cursor Object has method called execute, Once SQL statement successfully executed on the Oracle Server,ResultSet stored in memory, cursor is a pointer to that memorty.

Cursor Object has various methods to fetch Rows from the cursor Object

These Methods are

• fetachall --> fetches all records
• fetchone --> fetch first record
• fetchmany --> fetch specified number of records

All fetch methods in the cursor Object, returns rows as a list Object, in list Object each element is a tuple, contains all columns/specified columns data
[(col1,col2,...colN),(col1,col2,...colN)...(col1,col2,...colN)] Where each element represents a Row in the Database table.

Query Employees Table Data using Python oracledb module

	
		SQL = "SELECT * FROM EMPLOYEES";
		cur = conn.cursor()
		
		cur.execute ( SQL )
	
		print(cur.fetchall())
	

above fetchall methods prints all records with all columns in the Employees Table.

Note: Cursor object is iteratable , so it can be directly used in for-loop as a source of data. as shown below

		SQL = "SELECT * FROM EMPLOYEES";
		cur = conn.cursor()
		
		cur.execute ( SQL )
		
		
		for row in cur:
		 print(row)
	
		#Prints all rows in the Employees Table.
	

Extract Only Specified fields from list tuple

		cur.execute(SQL)

		#row is a tuple, it has (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE, ...etc.,) as values
		#EMPLOYEE_ID indexed at 0
		#FIRST_NAME indexed at 1  
		# ....
		
		
		for row in cur:
		  print(row[0],row[1],row[5])
	
		#Displays Employee_ID, FIRST_NAME and HIRE_DATE values
	

INSERT Data

    INSERT DML Operation ,Using python user can insert one row or many rows at a time.

Cursor Object has 2 types execute methods

• execute --> Inserts One Row
• executemany -->Inserts many rows

INSERT Record using Execute Method

		cur = conn.cursor()
		
		cur.execute("""
		INSERT INTO EMPLOYEES(EMPLOYEE_ID,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID) 
			VALUES(300,'JHON','jhon@yahoo.com',TO_DATE('2000-12-12','YYYY-MM-dd'),'SA_MAN')
		""");
		
		#Close the resources
		conn.commit();
		cur.close();
		conn.close()
	

The above example inserts a Record into Employees Table,Once INSERT statement executes successfully on the Oracle Server, You need to commit the DML statement using connection.commit() method.

UPDATE Data

     DML UPDATE statements is used to update a record or records from a Table

		cur = conn.cursor()
		
		cur.execute("UPDATE EMPLOYEES  SET SALARY = NVL(SALARY,0) + 3000 WHERE EMPLOYEE_ID=300")
		
		conn.commit();
		
		conn.close()
	
	

DELETE Data

     DML DELETE statement is used to delete arecord or records from a Table

		cur = conn.cursor()
		
		cur.execute("DELETE EMPLOYEES  WHERE EMPLOYEE_ID=300")
		
		conn.commit();
	
		conn.close()
	
	

Bind Parameters

     Passing python data to Oracle DB, Otherwise Passing python strucured data to Oracle SQL Query using Binding Parameter Concept.

oracledb.exceptions.OperationalError: DPY-6003: cannot connect to database. SID "XE" is not registered with the listener at host "localhost" port 1521. (Similar to ORA-12505)
oracledb.exceptions.InterfaceError: DPY-1004: no statement executed
oracledb.exceptions.ProgrammingError: DPY-2006: positional and named binds cannot be intermixed
oracledb.exceptions.DatabaseError: ORA-00904: "DATETIME"."DATETIME"."NOW": invalid identifier
oracledb.exceptions.DatabaseError: ORA-00984: column not allowed here
oracledb.exceptions.ProgrammingError: DPY-2003: expecting a dictionary, list or tuple, or keyword args
oracledb.exceptions.DatabaseError: DPY-4009: 0 positional bind values are required but 1 were provided