MERGE SQL in cx_Oracle executemany

[Mladen Gogala]

On Sat, 17 Oct 2020 21:23:40 -0600, Jason Friedman wrote:


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

Obviously, the "PROJECT" column is causing the issue. NULL in Oracle 
database is never equal to anything. If :7 is NULL, your "not matched" 
condition is satisfied and your MERGE statement will insert a new and 
exciting row. That has nothing to do with Python. The only solution is
"ALTER TABLE my_table modify(project not null)" or 
"ALTER TABLE my_table add constraint project_nn check(project is not null)"  I

If you already have NULL values in the PROJECT column than use "add constraint" 
with NOVALIDATE option. Other than that, allowing NULL values in key columns is
a sign of bad design. Namely, Oracle cannot index NULL columns, so PROJECT IS NULL
predicate cannot be resolved by an index.

-- 
Mladen Gogala
Database Consultant
http://mgogala.byethost5.com