views:

457

answers:

1

In a little script I'm writing using SQLAlchemy and Elixir, I need to get all the distinct values for a particular column. In ordinary SQL it'd be a simple matter of

SELECT DISTINCT `column` FROM `table`;

and I know I could just run that query "manually," but I'd rather stick to the SQLAlchemy declarative syntax (and/or Elixir) if I can. I'm sure it must be possible, I've even seen allusions to this sort of thing in the SQLAlchemy documentation, but I've been hunting through that documentation for hours (as well as that of Elixir) and I just can't seem to actually figure out how it would be done. So what am I missing?

+3  A: 

You can query column properties of mapped classes and the Query class has a generative distinct() method:

for value in Session.query(Table.column).distinct():
     pass
Ants Aasma
hm, well I could have sworn I tried that and it gave me an error... but apparently not. It works, thanks!
David Zaslavsky
Trying that, I get a 'Query' object is not callable error... :\ Any idea what I may be doing wrong?
kchau
kchau: could it be tht you are trying to call the query_property descriptor on a class instead of the query method on Session? So SomeClass.query(...) instead of Session.query(...)?
Ants Aasma
Ants: I have a scoped_session like: `my_session = scoped_session(sessionmaker(bind=my_engine, autoflush=True, autocommit=True))`, and then I'm trying to call: `my_session.query(SysSegCode.sysCode).distinct()` to get the list of unique values...
kchau
Never mind, issue resolved. Was doing a combination of things wrong... was trying to instantiate an instance of an Entity and then calling a property on that entity.
kchau