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') ```

Accepted Answer

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.

web2py check if exist in db insert or update

Solution

Problem