Python Function Example To Insert a Record in Oracle Table

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • Python
  • 2 mins read

Here I am giving an example of Python function to insert a record in Oracle table using the cx_Oracle library.

Python Function Example To Insert A Record in Oracle Table Using CX_Oracle

In the following Python function insert_dept, it will take three parameters:

  1. Department number as n_dept
  2. Department Name as s_dname
  3. Location of Department as s_loc

Then it will insert the record using the cx_Oracle's cursor.execute procedure. The syntax of cursor.execute to add a row in Oracle table using parameters is:

cur.execute("insert into table_name (field1, field2,...) values (:1, :2, ...)", (parameter1, parameter2, ...))

The bind variables in the above statement (:1, :2) will refer to the values from the second parameter (parameter1, parameter2).

Function insert_dept

import cx_Oracle

con = cx_Oracle.connect('scott/tiger@localhost/orcl')

def insert_dept(n_dept, s_dname, s_loc):
    cur = con.cursor()
    cur.execute("insert into dept (deptno, dname, loc) values (:1, :2, :3)", (n_dept, s_dname, s_loc))
    cur.close()
    con.commit()
    con.close()

# call the insert_dept function
try:
    insert_dept(99, 'testdept', 'testloc')
except Exception as e:
    print(e)

Check the Oracle table for the inserted record.

SELECT *
  FROM dept
 WHERE deptno = 99;

Output

    DEPTNO DNAME          LOC          
---------- -------------- -------------
        99 testdept       testloc      
1 row selected.

If you want to save the function in another Python file and want to call it from another, then follow the steps as below.

  1. Create a dept.py file as shown below:
import cx_Oracle

con = cx_Oracle.connect('scott/tiger@localhost/orcl')

def insert_dept(n_dept, s_dname, s_loc):
    cur = con.cursor()
    cur.execute("insert into dept (deptno, dname, loc) values (:1, :2, :3)", (n_dept, s_dname, s_loc))
    cur.close()
    con.commit()
    con.close()
  1. Create another Python file such as call_insert_dept.py with the following code. The difference is, we need to import dept.py in our another Python file to call the insert_dept function.
import dept

# call the insert_dept function
try:
    dept.insert_dept(96, 'testdept', 'testloc')
except Exception as e:
    print(e)

See also: