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