CODEWITHSUNDEEP
pythonsqlalchemy
Pav Sidhu
Pav Sidhu

Reputation: 6944

Get a list of values of one column from the results of a query

I have a User model where users can have the same name. I want to get the email addresses of all the users with a given name. However, I have to do result[0].email on the result of the query to get just the email for a row. I could do this with a for loop, but is there a way to just get the list of one field without having to do this every time?

my_result = db.session.query(my_table).filter_by(name=name)
emails = []
for r in my_result:
    emails.append(r.email)

Upvotes: 24

Views: 39000

Answers (4)

Martlark
Martlark

Reputation: 14581

There is a way to return specific columns from a filter_by query using the values method. As follows:

 emails = [r[0] for r in db.session.query(my_table).filter_by(name=name).values('email')]

or:

 emails = [r[0] for r in User.query.filter_by(name=name).values('email')]

values() takes any number of field names as parameters and returns a generator that has tuples with each value from each field name. Using a list comprehension to take the first item of that tuple emails will then be a list of plain string email addresses.

Please note that the values method is deprecated and you should use with_only_columns in the future.

Example to get only column b:

s = select(table1.c.a, table1.c.b)
s = s.with_only_columns(table1.c.b)

reference: https://docs.sqlalchemy.org/en/20/core/selectable.html#sqlalchemy.sql.expression.Select.with_only_columns

Upvotes: 8

alttag
alttag

Reputation: 1173

From the SQLAlchemy documentation for Result.scalars():

>>> result = conn.execute(text("select int_id from table"))
>>> result.scalars().all()
[1, 2, 3]

Upvotes: 0

davidism
davidism

Reputation: 127200

No, there's not really a way around the fact that SQLAlchemy queries return a list of dicts. If you want a specific set of fields, you can query for just those fields, but if you want a list of one field, you'll have to extract it from the result. The following example gets a list of the unique emails for every user with the given name.

emails = [r.email for r in db.session.query(my_table.c.email).filter_by(name=name).distinct()]

Upvotes: 30

Isaque Profeta
Isaque Profeta

Reputation: 29

Just to keep a record, I like a wrapper with this function in my common use lib:

def flat_list(l): return ["%s" % v for v in l]

Then:

flat_list(db.session.query(Model.column_attribute).all())

Upvotes: 1

Related Questions