osdir.com


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

MERGE SQL in cx_Oracle executemany


Hi there,

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()
```