MERGE SQL in cx_Oracle executemany

[Jason Friedman]

>
> 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
> using dual
> 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)
> ''',rows)
> con.commit()
> cur.close()
> con.close()
>

Perhaps the issue is that NULL is not equal to anything. Oracle provides
the IS NULL function to determine if a value is NULL.

Note also you define "cur" but executemany with "cur3".

And is "rows = [tuple(x) for x in df.values]" what you want? Print it.