How do I specify the column that I want in my query using a model (it selects all columns by default)? I know how to do this with the sqlalchmey session: session.query(self.col1), but how do I do it with with models? I can't do SomeModel.query(). Is there a way?
Add a comment
|
9 Answers
You can use the with_entities() method to restrict which columns you'd like to return in the result. (documentation)
result = SomeModel.query.with_entities(SomeModel.col1, SomeModel.col2)
Depending on your requirements, you may also find deferreds useful. They allow you to return the full object but restrict the columns that come over the wire.
9 Comments
kolypto
with_entities() makes all() yield tuples of column values, not objects!
David McKeone
kolypto: It yields whatever you ask it to yield. SomeModel.query.with_entities(SomeModel) would yield the object. Just like session.query(SomeModel.col1, SomeModel.col2) would yield tuples of column values. Deferreds are what you'd use if you don't want columns coming over the wire, but you want the whole object anyway.
Mitul Shah
Thanks it works. But how could we assign alias to the field ? Because in my case, I'm using JOIN and conflict
ID field which is present in both table
Nam G VU
For alias, view this brief answer below ie. use
.label() stackoverflow.com/a/11535992/248616
Mark Mishyn
BTW: at recent versions
with_entities yields Row instances. |
session.query().with_entities(SomeModel.col1)
is the same as
session.query(SomeModel.col1)
for alias, we can use .label()
session.query(SomeModel.col1.label('some alias name'))
2 Comments
fgblomqvist
The second one both sounds more logical and is shorter — win/win
Jay
The first (and third) options are the best, by far, if you want to reuse existing query objects, especially in the case of performing multiple complex subqueries.
You can use load_only function:
from sqlalchemy.orm import load_only
fields = ['name', 'addr', 'phone', 'url']
companies = session.query(SomeModel).options(load_only(*fields)).all()
7 Comments
rborodinov
This solution is the best one as it is still working as an Object not only the list of results.
Damien
Kudos to you for this solution. It has many advantages: - return exactly the same type of object as
.first() and .one() (that will lazy/eager load fields and relations), - can be set as query component
Srikanth Jeeva
the code is clean but the sql query selects all fields from the database. I have used
with_entities as given in the accepted answer and the query selected only that fields/.
alercelik
This returns the whole object.
Lucas Andrade
This is not working, is returning the entire object.
|
You can use Model.query, because the Model (or usually its base class, especially in cases where declarative extension is used) is assigned Sesssion.query_property. In this case the Model.query is equivalent to Session.query(Model).
I am not aware of the way to modify the columns returned by the query (except by adding more using add_columns()).
So your best shot is to use the Session.query(Model.col1, Model.col2, ...) (as already shown by Salil).
1 Comment
JGFMK
I believe there may also be a way to do this with a list of columns for query values() too, docs.sqlalchemy.org/en/latest/orm/… - but the syntactic sugar for the list eludes me at the moment.
You can use Query.values, Query.values
session.query(SomeModel).values('id', 'user')
Comments
I usually use this snippet:
fields = ["col1", "col2", ...]\
session.query(map(lambda x: getattr(SomeModel.c, x), fields))
1 Comment
Nishant
I still had to use
add_columns; not sure what is missing, but the idea is nice.result = ModalName.query.add_columns(ModelName.colname, ModelName.colname)
1 Comment
creyD
Could you please provide a bit more context for your answer? Why and how it works can help other people with similar problems. Thanks!
As session.query(SomeModel.col1) returns an array of tuples like this [('value_1',),('value_2',)] if you want t cast the result to a plain array you can do it by using one of the following statements:
values = [value[0] for value in session.query(SomeModel.col1)]
values = [model.col1 for model in session.query(SomeModel).options(load_only('col1'))]
Result:
['value_1', 'value_2']
Comments
An alternate syntax to fetch entity with specific columns:
any_query.with_entities(Entity).options(load_only(Entity.col))
