CODEWITHSUNDEEP
pythonsqlalchemy
JelteF
JelteF

Reputation: 3281

SQLAlchemy get list of ids from a query

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

Answers (6)

Ivan Kvas
Ivan Kvas

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

snakecharmerb
snakecharmerb

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

sscalvo
sscalvo

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

JGFMK
JGFMK

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

Peter Graham
Peter Graham

Reputation: 2575

session.query(Thing.id).all()

usually you query for whole objects but you can query for attributes too

Upvotes: 3

1478963
1478963

Reputation: 1216

SQL 

 SELECT DISTINCT `id` FROM `table`;

Python

 for value in Session.query(Table.id).distinct():
     #Do something Append

Upvotes: 14

Related Questions