CODEWITHSUNDEEP
pythonsqlsqlalchemypython-elixir
David Z
David Z

Reputation: 131550

Selecting distinct column values in SQLAlchemy/Elixir

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?

Upvotes: 87

Views: 157974

Answers (7)

abhinav srivastava
abhinav srivastava

Reputation: 73

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)

Upvotes: 1

Paul Rougieux
Paul Rougieux

Reputation: 11399

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

Upvotes: 4

sree_pk
sree_pk

Reputation: 86

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]

Upvotes: 5

Charles L.
Charles L.

Reputation: 6275

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))

Upvotes: 9

Árpád Magosányi
Árpád Magosányi

Reputation: 1432

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

Upvotes: 11

barryjones
barryjones

Reputation: 2309

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

Upvotes: 3

Ants Aasma
Ants Aasma

Reputation: 54882

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

Upvotes: 151

Related Questions