> > 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. -- https://mail.python.org/mailman/listinfo/python-list