Reputation: 3281
How do I get a list of ids instead of a list of SQLAlchemy objects? Currently I'm doing this:
[x.id for x in random_query.all()]
Is there a better way of doing this, preferably by only using SQLAlchemy statements.
Upvotes: 17
Views: 40375
Reputation: 456
SQLAlchemy Session
has method scalars for getting list of values without mapping.
SQLAlchemy 1.4+ style:
results = Session.scalars(select(Table.id)).all()
SQLAlchemy old styles:
results = Session.scalars(Session.query(Table.id)).all()
results = Session.scalars(Table.query.with_entities(Table.id)).all()
Actual results
:
[1, 2, 3, 4, 5]
Upvotes: 8
Reputation: 55963
Using SQLAlchemy 1.4+, you can you use the core-style select syntax:
ids = session.execute(select(MyModel.spam).distinct()).all()
Upvotes: 2
Reputation: 495
Trying to be as accurate as possible to the original question, you get a list of ids instead of a list of SQLAlchemy objects by uing with_entities()
User.query.with_entities(User.id).all()
Think of it as, instead of doing a full select..
SELECT * FROM user
you are selecting only whatever field(s) you are interested in:
SELECT id FROM user
To be honest, what you get is a list of 1 element tuples:
[(7,), (11,), (15,), (21,), (37,)]
So the final answer is:
ids = [id[0] for id in User.query.with_entities(User.id).all()]
Upvotes: 8
Reputation: 8914
FYI, the list I got back, was in the form of a tuple, so I found it useful to do something like this. I used the classes that came straight out of SQLACODEGEN and unfortunately it uses some sort of generic Numeric that returns floats too, hence the casting.
ids = list(map(lambda x: int(x[0]), \
session.query(Trgmst.trgmst_id).filter(Trgmst.trgmst_trigger==trigger).all()))
I was originally thinking of doing something like this in my case..
ids = list(map(lambda x: int(x[0]), \
session.query(Trgmst.trgmst_id).filter(Trgmst.trgmst_trigger==trigger).all()))
return session.query(Trgjob).filter( \
and_(Trgjob.jobmst_id==jobmst_id, Trgjob.trgmst_id.in_(ids)) \
).count() > 0
But in the end went with this instead:
return session.query(Trgjob).join(Trgjob.trgmst). \
filter(Trgjob.jobmst_id==jobmst_id). \
filter(Trgmst.trgmst_trigger==trigger). \
count() > 0
This was because I was filtering on an attribute of a joined file.
trgmst
is the relationship to Trgmst
from Trgjob
.
Upvotes: 0
Reputation: 2575
session.query(Thing.id).all()
usually you query for whole objects but you can query for attributes too
Upvotes: 3
Reputation: 1216
SQL
SELECT DISTINCT `id` FROM `table`;
Python
for value in Session.query(Table.id).distinct():
#Do something Append
Upvotes: 14