How to do create_or_update in sqlobject?

Question

I'm using SQLobject and so far was not able to find a elegant solution for "update row in the db or vreate a new one if it doesn't exist.

Currently I use following somewhat convoluted code:

args = dict(artnr=artnr, name=name, hersteller='?', hersteller_name='?')
if cs.datamart.ArtNrNameHersteller.selectBy(artnr=artnr).count():
    row = cs.datamart.ArtNrNameHersteller.selectBy(artnr=artnr)[0]
    row.set(**args)
else:
    cs.datamart.ArtNrNameHersteller(**args)

Obviously this is far from bening consise, robust, elegant or fast. What is the correct way(TM) to do this?

Answer 1

I don't know of any special way to do it, when you really don't know in advance whether it's a create or update. There's certainly no special SQL syntax that can do this efficiently(*), so any implementation provided would essentially be doing the same as your code.

(*: well, MySQL has the ON DUPLICATE KEY UPDATE and REPLACE mechanisms, but they trigger on any UNIQUE key match not just the primary key, which can be a bit dangerous; and Oracle has the ugly-as-sin MERGE statement. But these are non-standard extensions SQLObject can't really use.)