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

python oracle update example

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

Was this tutorial helpful?