web2py check if exist in db insert or update

Question

I'm trying to perform a check to see if a record exist first before inserting the record so I won't get an error. If it exists, i'll update a field.

mydb(mydb.myitems.itemNumber==int(row)).update(oldImageName=fileName) or 
mydb.myitems.insert(itemNumber=int(row),oldImageName=fileName)

If i try to update a record that does not exist, then it should throw a 1 or something aside from 0. But in the case above, it always throws a 0 so the insert keeps happening.

Why is that?

Thanks!

UPDATE: Adding model:

mydb.define_table('myitems', 
                    Field('itemNumber', 'id',notnull=True,unique=True),
                    Field('oldImageName', 'string')

Answer 1

If i try to update a record that does not exist, then it should throw a 1 or something aside from 0.

If you try to update a record that does not exist, .update() will return None, so the insert will then happen. If matching records exist, .update() will return the number of records updated.

In any case, you should instead do:

mydb.myitems.update_or_insert(mydb.myitems.itemNumber == int(row),
                              oldImageName=filename)

or alternatively:

mydb.myitems.update_or_insert(dict(itemNumber == int(row)),
                              oldImageName=filename)

The first argument to update_or_insert is _key, which can either be a DAL query or a dictionary with field names as keys.