Press "Enter" to skip to content

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/[email protected]/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/[email protected]/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:

Vinish Kapoor

FoxInfotech.in is created, written, and maintained by Vinish Kapoor. It is built on WordPress, and hosted by Bluehost. Connect with Vinish on Facebook, Twitter, and GitHub.

You may also like: