cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

Databricks to Oracle to Delete Rows

QuikPl4y
New Contributor III

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!

1 ACCEPTED SOLUTION

Accepted Solutions

QuikPl4y
New Contributor III

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.")




View solution in original post

2 REPLIES 2

delonb2
New Contributor III

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

QuikPl4y
New Contributor III

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.")




Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!