MERGE SQL in cx_Oracle executemany
I'm looking to insert values into an oracle table (my_table) using the query below. The insert query works when the PROJECT is not NULL/empty (""). However when PROJECT is an empty string(''), the query creates a new duplicate row every time the code is executed (with project value populating as null). I would like to modify my query so a new row is not inserted when all column values are matched (including when project code is null).
I'm guessing I would need to include a "when matched" statement, but not too sure on how to get this going. Would appreciate help with this, thanks.
con = cx_Oracle.connect(connstr)
cur = con.cursor()
rows = [tuple(x) for x in df.values]
cur3.executemany('''merge into my_table
on (YEAR = :1 and QUARTER = :2 and CODE = :3 and AMOUNT = :4 and DATE = :5 and COMMENTS = :6 and PROJECT = :7)
when not matched then insert values (:1, :2, :3, :4, :5, :6, :7)