On Sunday, November 15, 2015 at 11:58:01 AM UTC-8, RedBeard wrote:
>
> Hi,
>
>
Hi. See below (inline)
> I have only just started learning python and web2py so please bear with me.
>
> When a user inserts an item and amount in tr_items, I am trying to have
> the system check whether the item exists in products_summary, if it does,
> update the amount by the amount input by the user into tr_items - if it
> doesn't exist, the product should be added to products_summary and the
> amount set to the amount input by the user.
>
> Below I have put in three variations that most make sense, but I have
> tried a whole load of different possibilities. Any ideas on how I could go
> about solving this problem would be great!
>
> Model:
>
> db.define_table('products_summary',
> Field('product'),
> Field('cumulative_amount', default='0', type='double'))
>
>
> db.define_table('tr_items',
> Field('item'),
> Field('amount', type='double'))
>
>
>
Are products_summary.product and tr_items supposed to be the same
identification? If so, you may want to change one to a reference to the
other.
See
<URL:http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Field-types>,
where "reference" is one of the field types.
More at
<URL:http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer?search=reference+type#One-to-many-relation>
>
> Controller:
>
> def index():
> form = SQLFORM(db.tr_items)
> if form.process().accepted:
> item_in_out = form.vars.item
> amount_in_out = form.vars.amount
> #1
>
> #db.products_summary.update_or_insert(db.products_summary.product==item_in_out,
>
> product=item_in_out,
> cumulative_amount=db.products_summary.cumulative_amount+form.vars.amount)
> #2
>
> #db.products_summary.update_or_insert(db.products_summary.product==item_in_out,
>
> product=item_in_out,
> cumulative_amount=db(db.products_summary.product).select(db.products_summary.cumulative_amount)+form.vars.amount)
> #3
> db.products_summary.update_or_insert(db.products_summary.product==
> item_in_out, product=item_in_out, cumulative_amount=+form.vars.amount)
> response.flash = T("Product: %s Amount: %d") % (item_in_out,
> amount_in_out)
> return locals()
>
>
>
> Error message #1
>
> <class 'sqlite3.OperationalError'> no such column:
> products_summary.cumulative_amount
>
> Error message #2
>
> <type 'exceptions.TypeError'> unsupported operand type(s) for +: 'Rows'
> and 'float'
>
>
I believe that #2 is what you want, but you have to extract the field of
interest from the Rows object. A Rows object is (roughly) an array of Row
objects, and a Row object is (roughly) a dictionary of fields. Assuming
you only get 1 match to your query, you'd want
db.products_summary.update_or_insert(db.products_summary.product==
item_in_out, product=item_in_out,
cumulative_amount=db(db.products_summary.product).select(db.
products_summary.cumulative_amount)[0]["cumulative_amount"]
+ form.vars.amount)
Error message #3
>
> No error message - this overwrites cumulative amount to the amount entered
> by the user, no memory.
>
Python doesn't have the increment assignment operator, so number 3 is being
taken as
cumulative_amount = NULL + form.vars.amount
>
>
>
> Thanks for any help!!
>
If this was of any help, you're welcome!
/dps
--
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 web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
