Search code examples
web2py

Why does updating a record retrieved using the db.table[id] syntax not work?

I have tried these four ways of updating a record. The first two are described in the book. The third one looks the same as the second one but it doesn't work. And the fourth one looks the same as the first one. But it doesnt work either. What are the differences here that I am missing?

    DeckID = 1

    db(db.Deck.id == DeckID).update(CardCriteria='This works')

    db.Deck[DeckID] = dict(CardCriteria='This works too')

    rec = db.Deck[DeckID]
    rec = dict(CardCriteria='This DOESNT work')

    db.Deck[DeckID].update(CardCriteria='This DOESNT work either')
Solution

  • Using update

    db(db.Deck.id == DeckID).update(CardCriteria='This works')

    In the above, db(db.Deck.id == DeckID) is a DAL Set object -- it's update method executes a database update. However, db.Deck[DeckID] is a DAL Row object -- it acts like a dictionary, so it's update method simply updates the Row object itself (it does not execute a database update). To execute a database update, the Row object has the update_record method:

    db.Deck[DeckID].update_record(CardCriteria='This works')

    You can also update the Row object locally and then call update_record with no arguments, and it will send the local updates to the database:

    rec = db.Deck[DeckID]
rec.update(CardCriteria='This works')
rec.update_record()

    Using __setitem__

    db.Deck[DeckID] = dict(CardCriteria='This works too')

    In the above, the assignment to db.Deck[DeckID] triggers the __setitem__ magic method of the Row object, which results a database update rather than a DeckId key being added to the db.Deck table object. However, in this line:

    rec = dict(CardCriteria='This DOESNT work')

    you are simply re-assigning the rec variable to be a dictionary (there is no triggering of the __setitem__ magic method because you are not using object[key] = value syntax).

    Note, this same issue applies outside of the context of the DAL:

    >>> mydict = dict(mykey=1)
>>> myvalue = mydict['mykey']
>>> mydict['mykey'] = 2 # Here we update the "mykey" key of `mydict`.
>>> myvalue = 3 # Here we assign a new value to `myvalue` (no effect on `mydict`)
>>> mydict # Notice the `myvalue` assignment above did not affect `mydict`
{'mykey': 2}