Python Program to Update Table in Oracle

The below is an example of Python program to update a table in Oracle. I am using the cx_Oracle library in the program to connect to the Oracle database and to update the SCOTT user's EMP table. If you don't have SCOTT schema installed in your Oracle database, then you can download the script from the following link Download Scott Schema Script to test in your system.

Python Program Example To Update a Table in Oracle

The following Python program will update the employee's salary passed as a second parameter (n_salary) in the EMP table against the Employee number given as the first parameter (n_empno).

import cx_Oracle

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

def update_salary(n_empno, n_salary):
    cur = con.cursor()
    cur.execute("Update EMP set sal = :n_salary where empno = :n_empno",
                {'n_empno': (n_empno), 'n_salary': (n_salary)})
    if cur.rowcount > 0:
        print('Salary updated successfully.')
    else:
        print('Update failed.')
    cur.close()
    con.commit()
    con.close()

# call the update_salary function
try:
    update_salary(7788, 9000)
except Exception as e:
    print(e)

Output

Salary updated successfully.

Now giving another example by saving update salary function in another Python file say update_emp_record.py and will call through another Python program file.

In the below Python program, update_emp_record.py file will update the record but will not print anything on the screen and will return the row count affected by an update. The calling program will check the update status and will display on the screen.

#update_emp_record.py
import cx_Oracle

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

def update_salary(n_empno, n_salary):
    cur = con.cursor()
    cur.execute("Update EMP set sal = :n_salary where empno = :n_empno",
                {'n_empno': (n_empno), 'n_salary': (n_salary)})
    n_count = cur.rowcount
    cur.close()
    con.commit()
    con.close()
    return n_count

Now call it using other Python program file, for example, update_salary.py. The below program will import the update_emp_record.py and will check if the row count is greater than 0 then will print Update success else if it is 0 then will print Update failed.

#udpate_salary.py
import update_emp_record

try:
    if update_emp_record.update_salary(7788, 9000) == 0:
        print('Update failed.')
    else:
        print('Update success.')
except Exception as e:
    print(e)

Output

Update success.

See also:

• Python Program to Insert Record in Oracle Table