web2py insert methods

Question

Web2py docs have two methods for inserting into a database

db.tbl[0] = newRow

and

db.tbl.insert(newRowAsDict)

The documentation implies that they are synonyms, but they appear to be different. For one, the insert method throws an exception if newRow contains fields that are not in the table. Also the .insert method returns the id of the added row, where the assignment doesn't.

Is this the intended behavior
How can I get the id if I use the assignment method?
Is the assignment method depricated?

score 5 · Answer 1 · answered Feb 02 '12 at 02:17

5

There is also

db.tbl.insert(**db.tbl._filter_fields(newRowAsDict))

which will filter the keys in newRowAsDict ignoring unknown fields.

answered Feb 02 '12 at 02:17

Bruno Rocha - rochacbruno

7,890
6
26
31

score 2 · Answer 2 · answered Feb 01 '12 at 21:09

Is this the intended behavior

Based on the code, it appears to be. Using the assignment method, the fields are filtered so it only attempts to insert the fields that belong to the table. This does not happen with the standard insert() method.

How can I get the id if I use the assignment method?

If you need the id, you're probably better off using the insert() method.

Is the assignment method depricated?

I don't think so.

buzypi · Accepted Answer · 2012-02-06T21:03:12.190

A little bit of probing shows the difference between the two:

For: db.tbl[0] = dict(name='something')

File "/var/web2py/gluon/globals.py", line 172, in <lambda>
    self._caller = lambda f: f()
File "/var/web2py/applications/myapp/controllers/default.py", line 114, in test
    db.tbl[0] = dict(name='something')
File "/var/web2py/gluon/dal.py", line 5531, in __setitem__
    self.insert(**self._filter_fields(value))
File "/var/web2py/gluon/dal.py", line 5605, in insert
    return self._db._adapter.insert(self,self._listify(fields))

For: db.tbl.insert(name='something')

File "/var/web2py/gluon/globals.py", line 172, in <lambda>
    self._caller = lambda f: f()
File "/var/web2py/applications/myapp/controllers/default.py", line 115, in test
    db.tbl.insert(name='something')
File "/var/web2py/gluon/dal.py", line 5605, in insert
    return self._db._adapter.insert(self,self._listify(fields))

Both of them end up calling the same code to do the insert, so you will see that they run the same query:

INSERT INTO tbl(name) VALUES ('something');

Since the former does _filter_fields as is apparent from the trace, it does not thrown an exception when there are fields that are not present in the table, while the other does.

Obviously, db.tbl[0] = newRow cannot return a value. You should just consider it a shorthand for insert and its cousin db.tbl[x>0] is extremely useful for updates and has the exact same notation and this helps simplify the code.

web2py insert methods

3 Answers3