topic Re: Databricks to Oracle to Delete Rows in Data Engineering

Databricks to Oracle to Delete Rows

QuikPl4y — Thu, 13 Jun 2024 19:35:45 GMT

Hi Community! I’m working in a Fatabricks notebook and using the Oracle JDBC Thin Client connector to query and Oracle table, merge together and select specific rows from my dataframe and write those rows to a table back in Oracle. All of this works well so far, however I need to be able to delete a subset of records from an Oracle table before inserting new ones. I’ve tried using a cursor.execute with a parameterized delete statement, and Databricks appears to successfully run, but no rows are deleted from the target table. Is there an example someone can provide where this does work, as google searches have produced very little to try.
Thanks!

Re: Databricks to Oracle to Delete Rows

delonb2 — Sat, 15 Jun 2024 04:45:40 GMT

This stack overflow post ran into the same issue, would be worth trying How to delete a record from Oracle Table in Python SQLAlchemy - Stack Overflow

Re: Databricks to Oracle to Delete Rows

QuikPl4y — Sat, 15 Jun 2024 13:26:51 GMT

Turns out the issue was my command format. After many re-attempts with changes along the way - I finally had success using the format:

sql_delete = "DELETE FROM owner.table_name WHERE ROW_ID BETWEEN :value1 AND :value2"

value_to_delete1 = variable1

value_to_delete2 = variable2

with conn.cursor() as cursor:

cursor.execute(sql_delete, [value_to_delete1, value_to_delete2])

conn.commit()

print(f"{cursor.rowcount} rows deleted from EIM_LOY_DIST previous run.")