Web2Py Book inconsistency and Reference to auth_user table has no recursive attributes

[Yi Liu]

web2py Shell 2.9.5-stable+timestamp.2014.03.16.02.35.39
In [1] : tos = db(db.auth_criteria.toSend ==1).select()

In [2] : tos
<Rows (1)>

In [3] : for row in tos:
    print row.user_id.email

Traceback (most recent call last):
  File "/Users/LaViez/Documents/Python/web2py/gluon/contrib/shell.py", line 234, in run
    exec compiled in statement_module.__dict__
  File "<string>", line 2, in <module>
AttributeError: 'long' object has no attribute 'email'

Hi, the above is my error. Spent a few hours and couldn't figure out...

my db.py:

db.define_table('auth_criteria',
   Field('user_id', 'reference auth_user',requires = IS_IN_DB(db, 'auth_user.id'), readable=False, writable=False),
   Field('salePrice', 'integer', widget=SQLFORM.widgets.radio.widget, requires = IS_IN_SET(salePrice)),
   Field('toSend','integer', readable=False, writable=False))
db.auth_criteria.id.readable=False

In my appadmin interface, I can tell the "user_id" field IS indeed linked to auth_user, since I can click the auth_criteria "user_id" field, it will direct me to auth_user table.

But I cannot get any recursive attribute in the run as the error shows.

Btw, I find some inconsistency in the book 6th edition.

In the Overview section:

db.post.image_id.requires = IS_IN_DB(db, db.image.id, '%(title)s')

But in DAL section:

db.thing.owner_id.requires = IS_IN_DB(db,'person.uuid','%(name)s')

It seems both will link the tables. But none gave me the "email" or "first_name" attributes in recursive selection.

Thanks for any help.

[Niphlod]

the requires attribute just sets a validator that is called upon form validation, so it's correct that by itself doesn't produce a "link", as you call it.


Did you try with a simpler model to pinpoint the cause ?

i.e.

db.define_table('auth_criteria',
   Field('user_id', 'reference auth_user'),
)

[Yi Liu]

Thanks a lot, ... Niphlod.

When I do a simpler version, the web shell will give me "an error occured, please reload." when I try: print row.user_id.email. Then I basically have to restart web2py.py again to get the shell back. Reload won't work.

I am wondering if auth tables has to be logged in to use? Since I was either querying the tables in scheduler (in actual deployment) or in web admin shell.

I can tell the reference is working, since this code work fine showing the user's first name and last name in the user_id section.

    if request.args(0)=='profile':
        db.auth_criteria.user_id.default = auth.user.id
        record = db.auth_criteria(db.auth_criteria.user_id.default)
        formFil=SQLFORM(db.auth_criteria,
            record=record,
            labels = {'salePrice':XML('By Sale Price')},
            buttons = [TAG.button('Set Mine', _class='btn-primary')])

But I cannot get recursive attributes in either web shell or scheduler tasks.

[Anthony]

On Tuesday, September 16, 2014 4:05:12 PM UTC-4, Yi Liu wrote:

web2py Shell 2.9.5-stable+timestamp.2014.03.16.02.35.39
In [1] : tos = db(db.auth_criteria.toSend ==1).select()

In [2] : tos
<Rows (1)>

In [3] : for row in tos:
    print row.user_id.email

Traceback (most recent call last):
  File "/Users/LaViez/Documents/Python/web2py/gluon/contrib/shell.py", line 234, in run
    exec compiled in statement_module.__dict__
  File "<string>", line 2, in <module>
AttributeError: 'long' object has no attribute 'email'

I think this is a quirk of the web-based shell. You should be able to do:

db(db.auth_criteria.toSend ==1).select().first().user_id.email
 
But if you first store the Rows object and then access attributes of a row, the dal.Reference objects will have been converted to simple Long objects, and the recursive select will not work. It should work fine in a console based shell as well as standard app code.

Btw, I find some inconsistency in the book 6th edition.

In the Overview section:

db.post.image_id.requires = IS_IN_DB(db, db.image.id, '%(title)s')

But in DAL section:

db.thing.owner_id.requires = IS_IN_DB(db,'person.uuid','%(name)s')

It's not an inconsistency -- just two different allowed argument types for the second argument (either a field object, or its string representation).

Anthony

[Yi Liu]

Following your suggestion, I looked up how to use ipython console shell mode. The key is to load models by -M. Ahah! I hope this -M option could be documented better in the book. Now I have my friend iPython with me.

Recursive indeed works:

In [5]: rowtosend = db(db.auth_criteria.toSend==1).select()

In [7]: rowtosend
Out[7]: <Rows (1)>

In [8]: for row in rowtosend:
   ...:     print row.user_id.email
   ...:
xxxxxx@gmail.com

Then I have found my stupid real problem:

myList = []
for row in rowsToSend:
    email = row.user_id.email
    myList = myList.append(email)

after the for loop, myList became a Nonetype.

I should simply use myList.append(email)

Well, thanks everyone. I learned how to use iPython with web2py today. It will help me a lot in future develpment.


[Yi Liu]

New function suggestion:

Is it possible to return a list of field (column-wise) values without iterating through the rows? Currently, to get a column of selected rows, you have to do a for loop to collect them.

It is very convenient to select column in numpy arrays or pandas dataframes. I guess not so easy for SQL (I am beginner)?

[Anthony]

The easiest way to extract a column into a list is using a Python list comprehension:

rowstosend = db(db.auth_criteria.toSend == 1).select()
mylist = [row.user_id for row in rowstosend]

However, that is not a good option if using recursive selects, because each row will result in a separate database query. So, don't do:

mylist = [row.user_id.email for row in rowstosend]

Instead, do a join:

rowstosend = db((db.auth_criteria.toSend == 1) &
                (db.auth_criteria.user_id == db.auth_user.id)).select()
mylist = [row.auth_user.email for row in rowstosend]

Also, if you know you only need a single column from the database, it will be more efficient to explicitly specify just that column:

rowstosend = db((db.auth_criteria.toSend == 1) &
                (db.auth_criteria.user_id == db.auth_user.id)).select(db.auth_user.email)

Finally, if you actually want a Pandas DataFrame, you can pass a custom processor function to .select() that will take the raw results set from the database driver and convert it directly to a DataFrame rather than creating a DAL Rows object:

import pandas as pd
def pandas_df(rows, fields, columns, cacheable):
    return pd.DataFrame.from_records(rows, columns=columns)

df = db((db.auth_criteria.toSend == 1) &
        (db.auth_criteria.user_id == db.auth_user.id)).select(processor=pandas_df)

mylist = df['email'] # now you have a Pandas Series

Anthony

[Yi Liu]

Great. This is a really thorough answer. I appreciate it, Anthony.

 Let web2py stay focused, and let others (pandas etc.) do what they are good at :)