>
> def index():
> form = SQLFORM(db.tr_items)
> if form.process().accepted:
> item_in_out = form.vars.item
> amount_in_out = form.vars.amount
> row = db.products_summary(product=item_in_out)
> if row==None:
> db.products_summary.insert(product=item_in_out,
> cumulative_amount=amount_in_out)
> else:
> db.products_summary.update_or_insert(db.products_summary.
> product==item_in_out, product=item_in_out, cumulative_amount=db(db.
> products_summary.product==item_in_out).select(db.products_summary.
> cumulative_amount)[0]["cumulative_amount"]+form.vars.amount)
>
The point of the update_or_insert method is that you don't have to
explicitly check for the record and then conditionally either do an insert
or an update. Yet in your code, you first attempt to retrieve the record,
and then you conditionally do an insert or an update_or_insert. Of course,
the update_or_insert part of the code will never do an insert, because that
case is already handled by the previous condition -- so it might as well
just be an update.
Also, within your update_or_insert, you are doing a query to once again
retrieve the db.products_summary.product==item_in_out record, even though
you have already retrieved that same record (stored in the "row" variable).
In short, you should either do everything explicitly (i.e., attempt to
retrieve the record and then conditionally insert or update yourself), or
just do an update_or_insert and let that method handle everything. Of
course, if the update_or_insert approach requires you to explicitly select
the record anyway (as you are doing to increment the value), you lose the
benefit of using this method and should instead just handle everything
explicitly.
In this case, you can simplify somewhat to:
if form.process().accepted:
row = db.products_summary(product=item_in_out)
if row:
row.update_record(cumulative_amount=row.cumulative_amount + form
.vars.amount)
else:
db.products_summary.insert(product=form.vars.item,
cumulative_amount=form.vars.amount)
As an aside, when doing a regular update (not and update_or_insert), you *can
*increment a value without first selecting the record:
db(db.mytable).update(myfield=db.mytable.myfield + some_value)
In an update, if you set the value of a field to an expression that
includes the field object itself, the field object gets converted to its
string representation (which is just "tablename.fieldname") in the
generated SQL. So, the above would generate SQL like (assuming some_value
is equal to 1):
UPDATE mytable SET myfield=(mytable.myfield + 1) WHERE (mytable.id IS NOT
NULL);
You can't do this with an update_or_insert, though, because in the case of
an insert, the mytable.myfield + 1 SQL expression would fail.
Anthony
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
