CODEWITHSUNDEEP
pythondatabaseodo
okorng
okorng

Reputation: 149

How to use python odo to update records in database?

Is the python odo function able to UPDATE records in a database table - or can it only INSERT? I've inherited a python script that uses odo for inserting records into a database table. I need to update a Value column in each record, but as it is when I input the records with just the Value changed, it fails with the report of violating the uniqueness constraint on each record because odo is trying to do an INSERT instead of an UPDATE. Is this a restriction with odo that it can only INSERT into a database, and not UPDATE? In my case I'm trying to update an Oracle database table. I've been searching for any possible optional kwarg for the odo function that would instruct odo to do an UPDATE if the records exists, but I've been unable to locate such an option.

Here's the python code:

from odo import odo, dshape
...
# Reshape data for Oracle upload
idx_cols = ['C_LOAD_PROFILE', 'D_PROFILE', 'H_PROFILE']
pvt_lp = (data
          .reset_index()
          .set_index(idx_cols)
          .loc[:, ['Q_ESTIMATED']]
          .rename(columns={'Q_ESTIMATED': 'q_profile'})
          .reset_index()
          .rename(columns=str.lower)
          .assign(c_load_profile_source=3)
          .assign(c_lst_upd_user_id=un)
          .assign(d_lst_upd=datetime.now()))

dtypes = """\
    var * {c_load_profile: uint16,
           d_profile: datetime,
           h_profile: uint8,
           q_profile: float64,
           c_load_profile_source: uint8,
           c_lst_upd_user_id: string,
           d_lst_upd: datetime}
    """
ds = dshape(dtypes)
meta = MetaData(bind=self.oracle_db.engine, schema=schema)
tbl_name = 'hourly_load_profile'
meta.reflect(only=[tbl_name])
hlp = Table(tbl_name, meta)
tbl = odo(pvt_lp, hlp, dshape=ds)

where the last line that calls the odo function accepts optional arguments. I'm hoping an optional argument exists to allow UPDATE vs INSERT.

Upvotes: 1

Views: 334

Answers (0)

Related Questions