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
On Tuesday, September 16, 2014 7:51:11 PM UTC-4, Yi Liu wrote:
>
> 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)?
>
--
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 [email protected].
For more options, visit https://groups.google.com/d/optout.
