Python Function Example To Insert a Record in Oracle Table

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()

2. 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:

• How to Oracle Function in Python?