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 oracledb
from connect import connect
import logging


def update(billing_no: int, amount: float) -> bool:

    sql = '''update billing_headers 
             set amount = :amount 
             where billing_no = :billing_no'''
    try:
        with connect() as connection:
            with connection.cursor() as cursor:
                params = {
                    'billing_no': billing_no,
                    'amount': amount,                    
                }
                cursor.execute(sql, params)
                connection.commit()
                return True
    except oracledb.Error as e:
        error_message = f"Oracle database error during billing update for billing_no  {billing_no}: {e}"
        logging.error(error_message)
        raise
    except Exception as e:
        error_message = f"An unexpected error occurred during billing update for billing_no {billing_no}: {e}"
        logging.critical(error_message) 
        raise


if __name__ == '__main__':
    update(1, 2000)Code language: Python (python)

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

SELECT * 
FROM billing_headers
WHERE billing_no = 1;Code language: Python (python)

Here is the output:

In this tutorial, you have learned how to use the python-oracledb API to update data in a table.