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` F开发者_Python百科ROM `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?

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

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

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

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]

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

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