3

I'm using SQLobject and so far was not able to find a elegant solution for "update row in the db or create 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?

max
  • 29,122
  • 12
  • 52
  • 79

2 Answers2

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.)

bobince
  • 528,062
  • 107
  • 651
  • 834
1

There is no way to do that in SQLObject using high-level API. You can do it using SQLBuilder's Update, just use undocumented Replace instead of Update.

phd
  • 82,685
  • 13
  • 120
  • 165