select() - How to fetch specific fields?

[jjg0]

Hello again, I have another problem I've run into maybe someone can help with.

Let's say I have a table of blog posts and all I want to do is print out all the titles.  I'll make up a really simple example:

MODEL:

db.define_table('myBlog',

Field('title'),
Field('body', 'text'),
Field('created_on', 'datetime', default=request.now))

CONTROLLER:

def index():

titles = db(db.myBlog).select()
return locals()

INDEX VIEW:

{{for title in titles:}}
{{=title.title}}
{{pass}}

Now this works, but isn't it a waste to grab all the body and created_on fields if I don't plan on using them? 
I'm looking at the web2py book and it says in the select section of the DAL:

The select command can take arguments. All unnamed arguments are interpreted as the names of the fields that you want to fetch. For example, you can be explicit on fetching field "id" and field "name":

>>> for row in db().select(db.person.id, db.person.name):
        print row.name
Alex
Bob
Carl

So if I am understanding this correctly, I should be able to edit the controller above to say:

def index():

titles = db().select(db.myBlog.title)
return locals()

and titles should now only contain the title fields of myBlog.  So if I add something crazy in the view like this:

VIEW:

{{for title in titles:}}
{{=title.title}}
{{=title.body}}
{{pass}}

I am expecting this to break, but this works fine.  Shouldn't the {{=title.body}} part break the view now that I am only grabbing the titles?  Shouldn't title.body not exist? 
Why am I still able to print out the title.body, title.created_on date, and title.id? 
Does fetching only the title save me anything in the long run, or will grabbing the entire table cost the same as far as performance? 
What have I misinterpreted with the example taken from the book?

Thanks

[Massimo Di Pierro]

It does not break you simply get None.

[Niphlod]

nope.... Rows isn't a Storage() where you can retrieve what you want (fortunately ^_^)

the example posted lands into a <type 'exceptions.AttributeError'>('Row' object has no attribute 'body')
like it's supposed to be.

PS: @jjg0 the only way it doesn't break is if you don't have any rows on the table --> your for loop never gets executed --> the error is not raised.

[Niphlod]

no.no.no.no.
db(query).select(something)
returns either None or a list of Row objects each holding only one key, 'something', with the corresponding value.

your select should return just the detailtip2.amount field.
If it returns the whole detailtip2 table then post the model of that table and the select you're doing (possibly a short model and a short select, just to confirm the issue you're seeing)

[Ricardo Pedroso]

On Sun, Mar 10, 2013 at 9:48 PM, Kenan <ken...@gmail.com> wrote:
> Interesting question, I have a similar problem. Really, why can't we select
> only one field with select() ?
>
> For example, If i have several field in table Detailtip2 and want to select
> only top 10 amounts:
> rows = db((db.Detailtip2.year==session.year) &
> (db.Detailtip2.budget==session.budget)).select(db.Detailtip2.amount,
> orderby=~db.Detailtip2.amount, limitby=(0, 9))
>
> The problem is that rows gets populated with all fields from table.Usually,
> it's not a problem, but in this case i want to populate a list for bar
> chart:
> chart_data_list = rows.as_list()
>
> It doesn't work as expected since I got all fields and can't draw a chart.
> OK, I have another solution for this:
> for row in rows:
> chart_data_list.append(row.amount/)
>
> But I still have a question: How can I select one specific field?

I did a quick test and this works for me with sqlite in web2py shell:

>>> db.define_table('person', Field('first_name'), Field('last_name'))
<Table person (id,first_name,last_name)>

>>> db(db.person)._select(db.person.id)
'SELECT person.id FROM person WHERE (person.id IS NOT NULL);'

>>> db(db.person)._select(db.person.id, db.person.last_name)
'SELECT person.id, person.last_name FROM person WHERE (person.id IS NOT NULL);'

>>> db()._select(db.person.ALL)
'SELECT person.id, person.first_name, person.last_name FROM person;'

>>> db(db.person)._select()
'SELECT person.id, person.first_name, person.last_name FROM person
WHERE (person.id IS NOT NULL);'


Now, for web2py devs, are those below supported, I don't remember see anything
about this in the book.

>>> db(db.person)._select('*')
'SELECT * FROM person WHERE (person.id IS NOT NULL);'

>>> db(db.person)._select('id')
'SELECT id FROM person WHERE (person.id IS NOT NULL);'

>>> db(db.person)._select('id,last_name')
'SELECT id,last_name FROM person WHERE (person.id IS NOT NULL);'


Ricardo

[Niphlod]

expressions are supported (i.e. they don't break most of the times), but the resultset will not be accessible in the "normal" way (try it yourself with, e.g., select('id,last_name')).