UPSERT (update or insert) in Oracle with Python

Posted by in Software

One of the things I had to do is to build a script to upload data (e.g. the content of a list of files) into a database with a single table. My table has 3 columns:

  1. ID – a 20-character string identifier with PK properties (unique)
  2. ARTICLE– an XMLType element with the article details
  3. CITEDBY – an XMLType element with citations data

The problem is that some of the data is already in the database and it may only need updating. Following this question, the selected strategy is to perform the update and, if the record does not exist, then insert it. The SQL code equivalent is (from here):

If this were only SQL, everything would be OK. However, the requirement is to perform the operation from python, so I can only do one statement at a time. Moreover, I can’t do an IF statement. To be a bit DRY (just in case I need to reuse this somewhere else), I’ve created a simple function:

Update

First attempt is to update a record, if it exists:

My update statement is:

To make usable in the python script, you need to wrap it into UPDATE_STATEMENT=""" ... """.

You can see the statement will have an effect only if the table contains a record with the required identifier. Otherwise, nothing happens.

Insert

Since I can’t perform a composite statement (with an IF) without moving to PL/SQL, I need to emulate this. The simplest way to do it is to perform an INSERT anyway and catch the error if the record is already there. The resulting code is described below:

As you can see, I’m catching the IntegrityError and check if it’s for a duplicate entry (my identifier field has a primary key — unique — constraint). If yes, then I’m just noting it. Otherwise, I’m propagating the error further.

The insert string is very similar to the update string above:

Then, at the end I perform a cursor closing (to avoid the max cursors error):

Function

The full function contents is:

Some notes

  • The function can be further enhanced to allow for flexible arguments, different strings
  • You could perform the PL/SQL statements in a more complex statement

A little experiment: If you find this post and ad below useful, please check the ad out :-)