Build Update Statements with Oracle SQL Developer and Python
![Build Update Statements with Oracle SQL Developer and Python](/content/images/size/w960/2022/07/python-1.jpg)
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:
- Export the data as JSON
- 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(';')