Selecting distinct column values in SQLAlchemy/Elixir

Question

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?

score 142 · Accepted Answer · answered Feb 01 '10 at 08:17

142

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

answered Feb 01 '10 at 08:17

Ants Aasma

53,288
15
90
97

3

hm, well I could have sworn I tried that and it gave me an error... but apparently not. It works, thanks! – David Z Feb 01 '10 at 10:34
Trying that, I get a 'Query' object is not callable error... :\ Any idea what I may be doing wrong? – kafuchau Mar 26 '10 at 21:23
3

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 Mar 27 '10 at 11:41
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... – kafuchau Mar 29 '10 at 18:24
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. – kafuchau Mar 29 '10 at 19:00
If you are using a table construct instead of the declarative mapping here, you can do the same thing with: `Session.query(Table.columns.my_column).distinct()...` – Jamie S Oct 16 '19 at 01:50

score 11 · Answer 2 · edited May 03 '16 at 23:02

11

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

edited May 03 '16 at 23:02

Waqar Detho

1,502
18
17

answered Mar 01 '16 at 08:56

Árpád Magosányi

1,394
2
19
35

5

This will only work as intended in Postgresql, which has the DISTINCT ON extension. The accepted answer is far more portable and avoids selecting the extra columns. – Ilja Everilä Sep 04 '19 at 17:06

score 9 · Answer 3 · answered Jul 17 '20 at 17:44

I wanted to count the distinct values, and using .distinct() and .count() would count first, resulting in a single value, then do the distinct. I had to do the following

from sqlalchemy.sql import func
Session.query(func.count(func.distinct(Table.column))

score 5 · Answer 4 · edited Feb 23 '22 at 06:13

For class,

class User(Base):
    name = Column(Text)
    id = Column(Integer, primary_key=True)

Method 1: Using load_only

from sqlalchemy.orm import load_only
records= (db_session.query(User).options(load_only(name)).distinct().all())
values = [record[0] if len(record) == 1 else record for record in records]  # list of distinct values

Method2: without any imports

records = db_session.query(User.name).distinct().all()
l_values = [record.__dict__[l_columns[0]] for record in records]

score 3 · Answer 5 · edited Feb 24 '12 at 00:59

3

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

edited Feb 24 '12 at 00:59

animuson

53,861
28
137
147

answered Feb 15 '12 at 05:10

barryjones

2,149
1
17
21

Paul Rougieux · Answer 6 · 2022-08-17T09:25:37.937

SQL Alchemy version 2 encourages the use of the select() function. You can use an SQL Alchemy table to build a select statement that extracts unique values:

select(distinct(table.c.column_name))

SQL Alchemy 2.0 migration ORM usage:

"The biggest visible change in SQLAlchemy 2.0 is the use of Session.execute() in conjunction with select() to run ORM queries, instead of using Session.query()."

Reproducible example using pandas to collect the unique values.

Define and insert the iris dataset

Define an ORM structure for the iris dataset, then use pandas to insert the data into an SQLite database. Pandas inserts with if_exists="append" argument so that it keeps the structure defined in SQL Alchemy.

import seaborn
import pandas
from sqlalchemy import create_engine
from sqlalchemy import MetaData, Table, Column, Text, Float
from sqlalchemy.orm import Session

Define metadata and create the table

engine = create_engine('sqlite://')
meta = MetaData()
meta.bind = engine
iris_table = Table('iris',
                   meta,
                   Column("sepal_length", Float),
                   Column("sepal_width", Float),
                   Column("petal_length", Float),
                   Column("petal_width", Float),
                   Column("species", Text))
iris_table.create()

Load data into the table

iris = seaborn.load_dataset("iris")
iris.to_sql(name="iris",
            con=engine,
            if_exists="append",
            index=False,
            chunksize=10 ** 6,
            )

Select unique values

Re using the iris_table from above.

from sqlalchemy import distinct, select
stmt = select(distinct(iris_table.c.species))
df = pandas.read_sql_query(stmt, engine)
df
#       species
# 0      setosa
# 1  versicolor
# 2   virginica

score 0 · Answer 7 · answered Feb 08 '23 at 14:24

0

the marked solution showed me an error so I just specified the column and it worked here is the code

for i in (session.query(table_name.c.column_name).distinct()):
    print(i)

answered Feb 08 '23 at 14:24

abhinav srivastava

33
4

Selecting distinct column values in SQLAlchemy/Elixir

7 Answers7

Define and insert the iris dataset

Select unique values

Linked