Flask SQLAlchemy query, specify column names

Question

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?

score 326 · Accepted Answer · edited Jun 16 '16 at 09:45

326

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.

edited Jun 16 '16 at 09:45

Adversus

2,166
20
23

answered Aug 21 '12 at 12:29

David McKeone

3,493
1
15
8

37

with_entities() makes all() yield tuples of column values, not objects! – kolypto Jan 15 '14 at 16:20
12

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. – David McKeone Feb 06 '14 at 17:29
2

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 – Mitul Shah Jun 06 '15 at 10:46
Yeah I have same question with @MitulShah, how to set alias? – Nam G VU Mar 31 '18 at 03:30
1

For alias, view this brief answer below ie. use `.label()` https://stackoverflow.com/a/11535992/248616 – Nam G VU Mar 31 '18 at 03:38
1

Note that result will just be a query, i.e. `flask_sqlalchemy.BaseQuery object`. You still need to execute the query - https://flask-sqlalchemy.palletsprojects.com/en/2.x/queries/#querying-records. For example, you can call `result.all()` or `result.first()`. – user5305519 May 28 '20 at 03:22
2

BTW: at recent versions `with_entities` yields `Row` instances. – Mark Mishyn Jun 13 '21 at 09:11
on sqlalchemy [1.3.19](https://github.com/sqlalchemy/sqlalchemy/releases/tag/rel_1_3_19) (almost 3 years ago), `.query.with_entities(...)` returns `sqlalchemy.util._collections.result` – manus Jun 16 '23 at 19:45

score 107 · Answer 2 · edited Sep 03 '19 at 09:49

107

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'))

edited Sep 03 '19 at 09:49

SuperShoot

9,880
2
38
55

answered Jul 18 '12 at 07:07

Salil

9,534
9
42
56

2

The second one both sounds more logical and is shorter — win/win – fgblomqvist Jan 24 '18 at 19:06
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. – Jamie S Nov 28 '19 at 09:39

score 81 · Answer 3 · edited Sep 03 '19 at 09:51

81

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()

edited Sep 03 '19 at 09:51

SuperShoot

9,880
2
38
55

answered Aug 27 '17 at 13:55

Vlad Bezden

83,883
25
248
179

5

This solution is the best one as it is still working as an Object not only the list of results. – rborodinov Jul 02 '19 at 13:09
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 – Damien Apr 21 '20 at 05:28
1

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/. – Srikanth Jeeva May 05 '20 at 00:34
2

This returns the whole object. – alercelik Sep 29 '20 at 12:50
1

This is not working, is returning the entire object. – Lucas Andrade Jun 03 '21 at 18:58
It is returning the id value which is not required for me – Sathiamoorthy Jun 27 '22 at 18:15

score 11 · Answer 4 · answered Jul 19 '12 at 08:54

11

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).

answered Jul 19 '12 at 08:54

van

74,297
13
168
171

I believe there may also be a way to do this with a list of columns for query values() too, http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.values - but the syntactic sugar for the list eludes me at the moment. – JGFMK Feb 08 '18 at 17:37

score 5 · Answer 5 · answered Oct 15 '19 at 07:47

5

You can use Query.values, Query.values

session.query(SomeModel).values('id', 'user')

answered Oct 15 '19 at 07:47

gaozhidf

2,621
1
22
17

score 3 · Answer 6 · edited Apr 29 '21 at 11:26

3

I usually use this snippet:

fields = ["col1", "col2", ...]\
session.query(map(lambda x: getattr(SomeModel.c, x), fields))

edited Apr 29 '21 at 11:26

Tomerikoo

18,379
16
47
61

answered Apr 29 '21 at 10:34

Carlos RC

31
4

I still had to use `add_columns`; not sure what is missing, but the idea is nice. – Nishant Dec 12 '21 at 11:37

score 2 · Answer 7 · answered Jun 21 '21 at 08:03

2

result = ModalName.query.add_columns(ModelName.colname, ModelName.colname)

answered Jun 21 '21 at 08:03

Basurp Patil

121
1
2
5

6

Could you please provide a bit more context for your answer? Why and how it works can help other people with similar problems. Thanks! – creyD Jun 21 '21 at 09:00

score 0 · Answer 8 · answered Jan 06 '21 at 18:05

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']

score 0 · Answer 9 · answered Apr 27 '22 at 10:22

0

An alternate syntax to fetch entity with specific columns:

any_query.with_entities(Entity).options(load_only(Entity.col))

answered Apr 27 '22 at 10:22

user2599052

1,056
3
12
27

Flask SQLAlchemy query, specify column names

9 Answers9

Linked

Related