Build Update Statements with Oracle SQL Developer and Python

By default, SQL Developer can only export INSERT statements. This is quite unfortunate when one needs to build UPDATE statements because their syntax is different. I've tried to use regexp to convert the statements but I didn't find it not straight forward at all. So, I cam up with the following strategy:

1. Export the data as JSON
2. Use a python script (below) to write the statements

The python script

We first need to specify things like the DB Schema, table and the primary key:

schema='SCHEMA'
table='TABLE'
pk='primary_key'

Subsequently, I've defined a wrapper function to be able to wrap quotes around string values:

def wrap(data):
  if isinstance(data, int):
    return '%d' % data
  if isinstance(data, float):
    return '%f' % data
  s = '%s' % data  # convert to string
  s = s.replace('\'', '\'\'')  # double-escape single quotes
  return '\'%s\'' % s

Once this preparation is done, I load the JSON data:

import json

with open('statements.json') as f:
  data=json.load(f)

One thing I have noticed about the JSON exported from SQL Developer is that null fields are ignored. Therefore, we first need to build the fill list of fields by scanning the JSON entries:

# get the list of fields
fields = []
for item in data:
  for u in item.keys():
    if not u in fields:
      fields.append(u)

Now we can build the list of UPDATE statements:

for item in data:
  print('update "%s"."%s" SET' %(schema,table))
  first = True
  for u in item.keys():
    if u != pk:
      print('  %s%s = %s' % (',' if not first else '', u, wrap(item[u])))
      first = False

  # append all null fields
  for f in fields:
    if not f in item:
      print ('  %s%s = null' % (',' if not first else '', f))
      first = False

  print('where %s = %s' % (pk, item[pk]))
  print(';')

This is not rocket science by any means, but it saved me a couple of times already when I needed to populate a PROD DB with values tested in acceptance.

The script

The full script is:

import json

def wrap(data):
  if isinstance(data, int):
    return '%d' % data
  if isinstance(data, float):
    return '%f' % data
  s = '%s' % data  # convert to string
  s = s.replace('\'', '\'\'')  # double-escape single quotes
  return '\'%s\'' % s

schema='SCHEMA'
table='TABLE'
pk='primary_key'

with open('statements.json') as f:
  data=json.load(f)

# get the list of fields
fields = []
for item in data:
  for u in item.keys():
    if not u in fields:
      fields.append(u)
for item in data:
  print('update "%s"."%s" SET' %(schema,table))
  first = True
  for u in item.keys():
    if u != pk:
      print('  %s%s = %s' % (',' if not first else '', u, wrap(item[u])))
      first = False

  # append all null fields
  for f in fields:
    if not f in item:
      print ('  %s%s = null' % (',' if not first else '', f))
      first = False

  print('where %s = %s' % (pk, item[pk]))
  print(';')