cx_oracle MERGE USING when matched, when not matched and a Pandas dataframe

  Kiến thức lập trình

I have searched stackoverflow, ‘ASK TOM’, and oracle forums, but can’t find a solution to my problem.
I am trying to either update or insert into a table based on whether or not the keys exist in the table. I keep getting ‘right parenthesis missing’ even after removing all parenthesis.
Below is the code:
I read in a csv file to a pandas data frame and then change the np.nan to ”.

”’

import pandas as pd
import numpy as np
import cx_Oracle

CONNECT_DEV = '/@HISTORICAL_DEV'

master_airtempclimo = pd.read_csv('/home/milstedl/JMCLIM/master_airtempclimo_FINAL_PYTHON.csv')
master_airtempclimo.replace(np.nan, '', regex=True, inplace=True)

dev_connect = cx_Oracle.connect(CONNECT_DEV)
dev_cursor = dev_connect.cursor()

sql = '''merge into jm_admin.jmm_airtempclimo a 
using (select LOCATIONID, PERIODID, MONTH, NAME, ORGANIZATIONID, VERSION
   from dual) d
on (a.LOCATIONID = d.LOCATIONID, a.PERIODID = d.PERIODID, a.MONTH = d.MONTH, a.NAME = d.NAME, 
a.ORGANIZATIONID = a.ORGANIZATIONID, a.VERSION = d.VERSION)
when matched then update set a.TEMPERATURESAMPLESIZE = :TEMPERATURESAMPLESIZE, a.DENSITYMEAN = :DENSITYMEAN, 
 a.TEMPERATUREDEWPOINTSAMPLESIZE = :TEMPERATUREDEWPOINTSAMPLESIZE, a.HUMIDITYSAMPLESIZE = :HUMIDITYSAMPLESIZE,
 a.TEMPERATUREMEAN = :TEMPERATUREMEAN, a.TEMPERATUREDEWPOINTMEAN = :TEMPERATUREDEWPOINTMEAN,
 a.PRESSUREVAPORMEAN = :PRESSUREVAPORMEAN, a.TEMPERATURESTANDARDDEVIATION = :TEMPERATURESTANDARDDEVIATION,
 a.TEMPERATUREDEWPOINTSTDDEV = :TEMPERATUREDEWPOINTSTDDEV, a.DENSITYSTANDARDDEVIATION = :DENSITYSTANDARDDEVIATION,
 a.TEMPERATUREMAXIMUM = :TEMPERATUREMAXIMUM, a.TEMPERATUREDEWPOINTMAXIMUM = :TEMPERATUREDEWPOINTMAXIMUM,
 a.HUMIDITYRELATIVEMAXIMUM = :HUMIDITYRELATIVEMAXIMUM, a.HUMIDITYABSOLUTEMAXIMUM = :HUMIDITYABSOLUTEMAXIMUM,
 a.HUMIDITYSPECIFICMAXIMUM = :HUMIDITYSPECIFICMAXIMUM, a.TEMPERATUREMINIMUM = :TEMPERATUREMINIMUM,
 a.TEMPERATUREDEWPOINTMINIMUM = :TEMPERATUREDEWPOINTMINIMUM, a.HUMIDITYRELATIVEMINIMUM = :HUMIDITYRELATIVEMINIMUM,
 a.HUMIDITYABSOLUTEMINIMUM = :HUMIDITYABSOLUTEMINIMUM, a.HUMIDITYSPECIFICMINIMUM = :HUMIDITYSPECIFICMINIMUM,
 a.TEMPERATUREMAXIMUMMEAN = :TEMPERATUREMAXIMUMMEAN, a.TEMPERATUREMINIMUMMEAN = :TEMPERATUREMINIMUMMEAN,
 a.HUMIDITYRELATIVEMAXIMUMMEAN = :HUMIDITYRELATIVEMAXIMUMMEAN, a.HUMIDITYRELATIVEMINIMUMMEAN = :HUMIDITYRELATIVEMINIMUMMEAN,
 a.TEMPERATUREMAXIMUMRANGE = :TEMPERATUREMAXIMUMRANGE, a.TEMPERATUREMINIMUMRANGE = :TEMPERATUREMINIMUMRANGE
when not matched then insert (LOCATIONID, PERIODID, MONTH, NAME, ORGANIZATIONID, VERSION, TEMPERATURESAMPLESIZE, DENSITYMEAN, TEMPERATUREDEWPOINTSAMPLESIZE, HUMIDITYSAMPLESIZE, TEMPERATUREMEAN, TEMPERATUREDEWPOINTMEAN, PRESSUREVAPORMEAN, TEMPERATURESTANDARDDEVIATION, TEMPERATUREDEWPOINTSTDDEV, DENSITYSTANDARDDEVIATION, TEMPERATUREMAXIMUM, TEMPERATUREDEWPOINTMAXIMUM, HUMIDITYRELATIVEMAXIMUM, HUMIDITYABSOLUTEMAXIMUM, HUMIDITYSPECIFICMAXIMUM, TEMPERATUREMINIMUM, TEMPERATUREDEWPOINTMINIMUM, HUMIDITYRELATIVEMINIMUM, HUMIDITYABSOLUTEMINIMUM, HUMIDITYSPECIFICMINIMUM, TEMPERATUREMAXIMUMMEAN, TEMPERATUREMINIMUMMEAN, HUMIDITYRELATIVEMAXIMUMMEAN, HUMIDITYRELATIVEMINIMUMMEAN, TEMPERATUREMAXIMUMRANGE, TEMPERATUREMINIMUMRANGE)
values (:LOCATIONID, :PERIODID, :MONTH, :NAME, :ORGANIZATIONID, :VERSION, :TEMPERATURESAMPLESIZE, :DENSITYMEAN, :TEMPERATUREDEWPOINTSAMPLESIZE, :HUMIDITYSAMPLESIZE, :TEMPERATUREMEAN, :TEMPERATUREDEWPOINTMEAN, :PRESSUREVAPORMEAN, :TEMPERATURESTANDARDDEVIATION, :TEMPERATUREDEWPOINTSTDDEV, :DENSITYSTANDARDDEVIATION, :TEMPERATUREMAXIMUM, :TEMPERATUREDEWPOINTMAXIMUM, :HUMIDITYRELATIVEMAXIMUM, :HUMIDITYABSOLUTEMAXIMUM, :HUMIDITYSPECIFICMAXIMUM, :TEMPERATUREMINIMUM, :TEMPERATUREDEWPOINTMINIMUM, :HUMIDITYRELATIVEMINIMUM, :HUMIDITYABSOLUTEMINIMUM, :HUMIDITYSPECIFICMINIMUM, :TEMPERATUREMAXIMUMMEAN, :TEMPERATUREMINIMUMMEAN, :HUMIDITYRELATIVEMAXIMUMMEAN, :HUMIDITYRELATIVEMINIMUMMEAN, :TEMPERATUREMAXIMUMRANGE, :TEMPERATUREMINIMUMRANGE)

”’

try:
    for index, row in master_airtempclimo.iterrows():
        dev_cursor.execute(sql, {"LOCATIONID": row['LOCATIONID'], "PERIODID": row['PERIODID'],
                                 "MONTH": row['MONTH'], "NAME": row['NAME'], 'ORGANIZATIONID': row['ORGANIZATIONID'],
                             'VERSION': row['VERSION'], 'TEMPERATURESAMPLESIZE': row['TEMPERATURESAMPLESIZE'],
                             'DENSITYMEAN': row['DENSITYMEAN'], 'TEMPERATUREDEWPOINTSAMPLESIZE': row['TEMPERATUREDEWPOINTSAMPLESIZE'],
                             'HUMIDITYSAMPLESIZE': row['HUMIDITYSAMPLESIZE'], 'TEMPERATUREMEAN': row['TEMPERATUREMEAN'],
                             'TEMPERATUREDEWPOINTMEAN': row['TEMPERATUREDEWPOINTMEAN'], 'PRESSUREVAPORMEAN': row['PRESSUREVAPORMEAN'],
                             'TEMPERATURESTANDARDDEVIATION': row['TEMPERATURESTANDARDDEVIATION'],
                             'TEMPERATUREDEWPOINTSTDDEV': row['TEMPERATUREDEWPOINTSTDDEV'],
                             'DENSITYSTANDARDDEVIATION': row['DENSITYSTANDARDDEVIATION'], 'TEMPERATUREMAXIMUM': row['TEMPERATUREMAXIMUM'],
                             'TEMPERATUREDEWPOINTMAXIMUM': row['TEMPERATUREDEWPOINTMAXIMUM'],
                             'HUMIDITYRELATIVEMAXIMUM': row['HUMIDITYRELATIVEMAXIMUM'],
                             'HUMIDITYABSOLUTEMAXIMUM': row['HUMIDITYABSOLUTEMAXIMUM'],
                             'HUMIDITYSPECIFICMAXIMUM': row['HUMIDITYSPECIFICMAXIMUM'], 'TEMPERATUREMINIMUM': row['TEMPERATUREMINIMUM'],
                             'TEMPERATUREDEWPOINTMINIMUM': row['TEMPERATUREDEWPOINTMINIMUM'],
                             'HUMIDITYRELATIVEMINIMUM': row['HUMIDITYRELATIVEMINIMUM'],
                             'HUMIDITYABSOLUTEMINIMUM': row['HUMIDITYABSOLUTEMINIMUM'],
                             'HUMIDITYSPECIFICMINIMUM': row['HUMIDITYSPECIFICMINIMUM'],
                             'TEMPERATUREMAXIMUMMEAN': row['TEMPERATUREMAXIMUMMEAN'], 'TEMPERATUREMINIMUMMEAN': row['TEMPERATUREMINIMUMMEAN'],
                             'HUMIDITYRELATIVEMAXIMUMMEAN': row['HUMIDITYRELATIVEMAXIMUMMEAN'],
                             'HUMIDITYRELATIVEMINIMUMMEAN': row['HUMIDITYRELATIVEMINIMUMMEAN'],
                             'TEMPERATUREMAXIMUMRANGE': row['TEMPERATUREMAXIMUMRANGE'],
                             'TEMPERATUREMINIMUMRANGE': row['TEMPERATUREMINIMUMRANGE']
                             })
except Exception as exception:
    print('Error, ', exception)
    exit()
else:
    dev_connect.commit()

”’

I am sure that I did something wrong in the sql but don’t know what.
Any help would be greatly appreciated.

LEAVE A COMMENT