Python SQLAlchemy DISTINCT Example

Posted on  Jul 12, 2019  in  Python Module Recommendations  by  Amo Chen  ‐ 2 min read

This article records several SQLAlchemy methods that achieve the same effect as SELECT DISTINCT.

Environment

  • Python 3.6.5
  • SQLAlchemy 1.3.4

Example Model

Here is the example model used in this article - User

from sqlalchemy import create_engine
from sqlalchemy import Column, String, Integer, DateTime, ForeignKey
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base


Base = declarative_base()


class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    username = Column(String)
    password = Column(String)


engine = create_engine('sqlite:///:memory:', echo=True)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

Using distinct()

Find unique User.name values:

q = session.query(User.name).distinct()
for r in q.all():
    print(r)

The above SQL statement is:

SELECT DISTINCT user.name AS user_name FROM user

Or you can achieve the same effect with the following code:

from sqlalchemy import distinct


q = session.query(distinct(User.name).label('uniq_username'))
for r in q.all():
    print(r)

The SQL statement is:

SELECT DISTINCT user.name AS uniq_username FROM user

If you want to DISTINCT multiple fields:

q = session.query(User.name, User.id).distinct()
for r in q.all():
    print(r)

The SQL statement is:

SELECT DISTINCT user.name AS user_name, user.id AS user_id FROM user

If you want to count unique values, you can use func.count():

from sqlalchemy import func
from sqlalchemy import distinct

session.query(func.count(distinct(User.name)))

Using group_by()

In addition to using distinct(), you can also achieve the same effect with group_by():

q = session.query(User.name).group_by(User.name)
for r in q.all():
    print(r)

The SQL statement is:

SELECT user.name AS user_name FROM user GROUP BY user.name

Example of GROUP BY multiple fields:

q = session.query(User.name, User.id).group_by(User.name, User.id)
for r in q.all():
    print(r)

The SQL statement is:

SELECT user.name AS user_name, user.id AS user_id FROM user GROUP BY user.name, user.id

References

https://docs.sqlalchemy.org/en/13/orm/query.html#sqlalchemy.orm.query.Query.distinct