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




Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you wonโ€™t want to miss the chance to attend and share knowledge.

If there isnโ€™t a group near you, start one and help create a community that brings people together.

Request a New Group