Summary: in this tutorial, you will learn how to use the Python cx_Oracle API to delete data from a table.
To delete data from a table, you use the following steps:
- First, connect to the Oracle Database by creating a new
- Second, create a new
Cursorobject from the
Connectionobject by calling the
- Third, execute a
DELETEstatement by calling the
- Fourth, call the
Connection.commit()method to apply the changes to the database. If you forget to call the
Connection.commit()method, you will see that the change will not take effect.
- Finally, release the
Connectionobjects by calling the
Connection.close()method respectively. You can also use the
withblock to release these objects automatically.
The following code illustrates how to delete a row from the
billing_headers table based on a specific billing number.
import cx_Oracle import config as cfg def delete_billing(billing_no, amount): """ Delete a billing based on a billing no. :param billing_no: :return: """ sql = ('delete from billing_headers ' '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__': delete_billing(1)
Here are the contents of the
billing_headers table after executing the program:
SELECT * FROM billing_headers;
As you can see clearly from the output, the row whose
billing_no is 1 has been removed. It means that the program works as expected.
If you have not following the previous tutorials, you can use the
username = 'OT' password = '<password>' dsn = 'localhost/pdborcl' port = 1512 encoding = 'UTF-8'
And the following script to create the
CREATE TABLE billing_headers( billing_no NUMBER GENERATED BY DEFAULT AS IDENTITY, billing_date DATE NOT NULL, amount NUMBER(19,4) DEFAULT 0 NOT NULL, customer_id NUMBER NOT NULL, note VARCHAR2(100), PRIMARY KEY(billing_no) );
In this tutorial, you have learned how to use the Python cx_Oracle API to delete data in a table.