Updating Data In Oracle Database from Python

Summary: in this tutorial, you will learn how to use the Python cx_Oracle API to update data in a table.

To update data in a table, you follow these steps:

• First, connect to the Oracle Database by creating a new Connection object.
• Second, create a Cursor object from the Connection object.
• Third, execute an UPDATE statement by calling the Cursor.execute() method.
• Fourth, call the Connection.commit() method to apply the changes to the database.
• Finally, release the Cursor and Connection objects. You can use the with block to release these objects automatically.

The following code illustrates how to update the amount for a billing document based on a specific billing number.

import cx_Oracle
import config as cfg


def update_billing(billing_no, amount):
    """
    Update new amount for a billing
    :param billing_no:
    :param amount:
    :return:
    """
    sql = ('update billing_headers '
        'set amount = :amount '
        'where billing_no = :billing_no')

    try:
        # establish a new connection
        with cx_Oracle.connect(cfg.username,
                            cfg.password,
                            cfg.dsn,
                            encoding=cfg.encoding) as connection:
            # create a cursor
            with connection.cursor() as cursor:
                # execute the insert statement
                cursor.execute(sql, [amount, billing_no])
                # commit the change
                connection.commit()
    except cx_Oracle.Error as error:
        print(error)


if __name__ == '__main__':
    update_billing(1, 2000)

After executing the program, you can examine the contents of the billing_headers table:

SELECT * 
FROM billing_headers
WHERE billing_no = 1;

Here is the output:

In this tutorial, you have learned how to use the Python cx_Oracle API to update data in a table.