Storing rows

[Dave S]

I'm wanting to munge the Rows I get back from the DB when I do my query; kind of a custom group-by.  This involves adding an integer field and concatenating a string field.  So I was wanting to take the first Row, and put it into another object ... dict or Storage or Rows. I was hoping to use the outwardly simple SQLTABLE to display the results (as I do for the un-munged results).

If I use a Storage object (import from gluon.storage), I can copy the row (for row in rows:, and then us row.id for the new key) but then SQLTABLE wants to get back to the DB

 self.attributes = attributes
 self.sqlrows = sqlrows
 (components, row) = (self.components, [])
 if not sqlrows:
 return

 REGEX_TABLE_DOT_FIELD = sqlrows.db._adapter.REGEX_TABLE_DOT_FIELD

sqlhtml.py in __init__ at line 3195
(and print rows doesn't show anything about db or adapter.) 

If I try to use a Rows object (import from pydal.objects), I can't append, add, or assign a row, which means I can't get far enough to see if the DB problem occurs. 

I can munge in place, since a Row object is mutable, but then I have donor rows that I don't want to display.  Can I get rid of them without changing the DB?   [I'm considering updating the DB with the munge results, but for now I just to do it to the display.]  This is perhaps a silly question; it arises from the observation that I don't do the data entry in an optimal way.  I pick up item 1, read the values to fill in the form, and submit the form ... and then find I should have done quantity 2 instead of 1, and appended a flag field appropriate to the second item (it indicates the manufacturing plant).  So the munging does the combining that should have happened at data entry.

Thanks.  
Dave 
/dps

[Anthony]

Hard to say without seeing more code. Would virtual fields help? If you're creating your own Rows object, note that the first argument of the constructor is the db object, so just pass it in.

Also, generating an HTML table from a set of records either via the web2py HTML helpers or the template system isn't too difficult -- so at some point, it may be easier to build something from scratch rather than try to shoehorn your data into SQLTABLE.

Anthony

[Dave S]

On Monday, November 28, 2016 at 4:00:07 AM UTC-8, Anthony wrote:

Hard to say without seeing more code. Would virtual fields help? If you're creating your own Rows object, note that the first argument of the constructor is the db object, so just pass it in.

Also, generating an HTML table from a set of records either via the web2py HTML helpers or the template system isn't too difficult -- so at some point, it may be easier to build something from scratch rather than try to shoehorn your data into SQLTABLE.

Anthony

Building a table by hand was what I did in one of my first web2py projects.  Not hard, but not as convenient as the tools can make it ... for normal cases, at least.

I'll see if I can get back to the "nearly working" Storage example, and using the constructor more knowledgeably..  The rowed = Rows() example foundered on not being able to do rowed.append(row) or rowed.add(row) or rowed[index] = row.  The latter was working for the Storage example.

/dps

[Anthony]

Building a table by hand was what I did in one of my first web2py projects.  Not hard, but not as convenient as the tools can make it ... for normal cases, at least.

I'll see if I can get back to the "nearly working" Storage example, and using the constructor more knowledgeably..  The rowed = Rows() example foundered on not being able to do rowed.append(row) or rowed.add(row) or rowed[index] = row.  The latter was working for the Storage example.

Rows.records is a list of the Row objects, so:

rowed.records.append(row)

Also, if you create a second Rows object (possibly containing just a single new row), you can do:

rowed &= new_rows

The latter method is a bit safer, as it is part of the public API and will therefore remain backward compatible (see http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Combining-rows).

Anthony

[Dave S]

Thank you.  This is good stuff, and no matter how much time I spend in the book, there are parts I lose track of.  No doubt I have a loose part.

/dps