CODEWITHSUNDEEP
pythonsqloracle-databasesql-deleteexecutemany
Karthik
Karthik

Reputation: 1

Python SQL executemany DELETE statement runs successfully without error for no matching records found

I have a file which has 100 of records to get deleted from a table. I'm using cx_oracle connect connection with executemany statement from Python to Oracle SQL and trying to delete the entire 100 records 1 by 1 with matching primary key. The run completes successful, eventhough some of the records are not present in the table. I want the statement to error out, if any 1 of the records is not present in the table. How to achieve this?

I tried with executemany -

STNSchemaConnection = cx_oracle.connect(self._oracleUserNameSTN, self._oraclePasswordSTN, cx_oracle.makedsn(self._oracleServer, self._oraclePort, self._oracleSID))               
STNSchemaCursor = STNSchemaConnection.cursor()
STNSchemaCursor.prepare(deleteSqlString)
STNSchemaCursor.executemany(None,dataToDump)
STNSchemaConnection.commit()

the deleteSqlString has the delete query and the dataToDump has the 100 records in array.

deleteSqlString =

DELETE FROM SCHEMA.TABLE_NAME
WHERE SOURCE_SYS   = :array_val_1
AND   CONTRACT_NUM = :array_val_2

Upvotes: 0

Views: 61

Answers (2)

MT0
MT0

Reputation: 168470

You can wrap your statement in a PL/SQL block to check whether zero rows were deleted and raise an exception within Oracle:

BEGIN
  DELETE FROM SCHEMA.TABLE_NAME
  WHERE SOURCE_SYS   = :array_val_1
  AND   CONTRACT_NUM = :array_val_2;

  IF SQL%ROWCOUNT = 0 THEN
    RAISE NO_DATA_FOUND;
  END IF;
END;

fiddle

Upvotes: 0

Anubhav Sharma
Anubhav Sharma

Reputation: 543

throw error on your own. Something like this can help.

if STNSchemaCursor.rowcount < len(dataToDump):
    raise RuntimeError(
        f"Error: Expected to affect {len(dataToDump)} rows, but only {STNSchemaCursor.rowcount} rows were affected."
    )
else:
    print(f"Success: All {STNSchemaCursor.rowcount} rows affected as expected.")

Upvotes: 0

Related Questions