On Mon, Jan 24, 2011 at 8:38 AM, Markus Schmitz <[email protected]>wrote:
> Hi everybody,
>
> I have the requirement to treat a range of table fields like an array.
> Example:
>
> I have a table x with fields y1, y2, ...., y100. In normal SQL I could
> create the table by issuing an 'alter table x add field y...'
> statement in a loop and I would be able to access the fields by
> constructing the appropriate SQL command in a string.
>
> So instead of using an index i and writing in theory: select y[i]
> from x
> I would write: ExecuteSQL("select y" + i + " from x)"
>
> But in web2py:
>
> Question 1: How can I create such a table definition in web2py without
> listing all fields manually?
>
I think list comprehension could help here
You can get all the fields of your model x like this
fields_from_x = [ request.args(0)+'.%s'%f for f in
db[request.args(0)].fields ] # request.args(0) should be your table x in the
URL or just replace it...
Then you build your query like this
db().select(*fields_from_x)
You could have a look to regular expression also.
Richard
> Question 2: How can I use the DAL to access these fields using an
> index and avoid using the ExecuteSQL method?
>
> Thanks in advance,
>
> Markus
>
> P.S.: I know I could simply create a small table, having two fields,
> basically "i" and "y", where "i" is part of the index. But in my case
> I end up with millions of records and quite an overhead for each data
> point. I tried it and performance was not satisfactory. But I am open
> for alternative suggestions.
