Selecting distinct column values in SQLAlchemy/Eli

问题:

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?

回答1:

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

回答2:

For this class:

class Assurance(db.Model):
    name = Column(String)

you can do this:

assurances = []
for assurance in Assurance.query.distinct(Assurance.name):
    assurances.append(assurance.name)

and you will have the list of distinct values

回答3:

for user in session.query(users_table).distinct():
    print user.posting_id