Query table for id of distinct value

Question

I have a table with multiple values that repeat sometimes. ex(1,'test', 2, 'test, 3, 'somevalue', 4, 'somevalue' etc)

So basically what I am trying to do is query the table for all the distinct values along with their ids. I'm new to sqlalchemy and can't seem to figure out how.

This is what I have now:

reportTypes = DBSession.query(TDeviceType.sDeviceType).distinct()

where sDeviceType is my string value and ixDeviceType is my id. This query however only returns the distinct value. Any suggestions? I could do a loop for each value and get the id however that just seems like a bad idea

equivalent of what I want to do:

select distinct(sDeviceType), ixDeviceType  FROM TDeviceType

Answer 1

YIf I understand the question correctly, you want a list of ids for each value. While there might be a way to do this in pure SQLAlchemy, it seems simple enough to do this in Python.

reportTypes = {}

for id, value in DBSession.query(TDeviceType.ixDeviceType, TDeviceType.sDeviceType):
    reportTypes.setdefault(value, []).append(id)

print reportTypes  # looks like {'test': [1, 2], 'somevalue': [3, 4]}

Each key is a distinct sDeviceType and each value is a list of ixDeviceType.