Selecting distinct values from a column in Peewee

Question

I am looking to select all values from one column which are distinct using Peewee.

For example if i had the table

 Organization     Year   
 company_1         2000
 company_1         2001
 company_2         2000
 .... 

To just return unique values in the organization column [i.e.company_1 and company_2]

I had assumed this was possible using the distinct option as documented http://docs.peewee-orm.com/en/latest/peewee/api.html#SelectQuery.distinct

My current code:

   organizations_returned = organization_db.select().distinct(organization_db.organization_column).execute()

    for item in organizations_returned:
         print (item.organization_column)

Does not result in distinct rows returned (it results in e.g. company_1 twice).

The other option i tried:

  organization_db.select().distinct([organization_db.organization_column]).execute()

included [ ] within the disctinct option, which although appearing to be more consistent with the documentation, resulted in the error peewee.OperationalError: near "ON": syntax error:

Am i correct in assume that it is possible to return unique values directly from Peewee - and if so, what am i doing wrong?

---

Model structure:

cd_sql = SqliteDatabase(sql_location, threadlocals=True, pragmas=(("synchronous", "off"),))     

class BaseModel(Model):
    class Meta:
        database = cd_sql

class organization_db(BaseModel):
    organization_column = CharField()
    year_column = CharField()

Workshop

What database are you using? – Guillermo

@Guillermo I'm using Sqlite - it didn't seem to indicate it was database specific in the documentation, but maybe i am missing something – Workshop

Note that the brackets in the documentation are there to show optional arguments, not to be actually passed in. I thought I had done this myself recently but I can't seem to find it - poking around a bit more. EDIT: oops, ignore me, I see now you can also pass in a list of models. – Poleyn

Can you show us what your model looks like? – Poleyn

@Poleyn - sorry - what do you mean? – Workshop

What is organization_db? Generally Peewee revolves around describing your database schema via a Model class, and you'd then do a SelectQuery using that class (and in turn express what you want distinct using that model class as well). – Poleyn

@Poleyn - I have edited into the question... – Workshop

Answer 1

So what coleifer was getting at is that Sqlite doesn't support DISTINCT ON. That's not a big issue though, I think you can accomplish what you want like so:

organization_db.select(organization_db.organization).distinct()

Poleyn