CODEWITHSUNDEEP
mysqlinsertweb2py
user3211229
user3211229

Reputation: 111

web2py check if exist in db insert or update

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

Upvotes: 4

Views: 5793

Answers (2)

Tim 333
Tim 333

Reputation: 952

This is basically a correction of Anthony's answer which did not give quite the desired result when I tried it. If you do:

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

then the code should insert a record with the itemNumber and filename if there is not an item with that number there already, otherwise update it.

If you miss the itemNumber = int(row), bit then web2py makes up an itemNumber which is probably not what is wanted.

See http://www.web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#update_or_insert

Upvotes: 1

Anthony
Anthony

Reputation: 25536

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.

Upvotes: 8

Related Questions