CODEWITHSUNDEEP
pythonsqlalchemy
Paul
Paul

Reputation: 243

SQLAlchemy Return All Distinct Column Values

I am creating a website using Flask and SQLAlchemy. This website keeps track of classes that a student has taken. I would like to find a way to search my database using SQLAlchemy to find all unique classes that have been entered. Here is code from my models.py for Class:

class Class(db.Model):
    __tablename__ = 'classes'
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100))
    body = db.Column(db.Text)
    created = db.Column(db.DateTime, default=datetime.datetime.now)
    user_email = db.Column(db.String(100), db.ForeignKey(User.email))
    user = db.relationship(User)

In other words, I would like to get all unique values from the title column and pass that to my views.py.

Upvotes: 22

Views: 44295

Answers (4)

Samuel Nde
Samuel Nde

Reputation: 2743

As @van has pointed out, what you are looking for is:

session.query(your_table.column1.distinct()).all(); #SELECT DISTINCT(column1) FROM your_table

but I will add that in most cases, you are also looking to add another filter on the results. In which case you can do

session.query(your_table.column1.distinct()).filter_by(column2 = 'some_column2_value').all();

which translates to sql

SELECT DISTINCT(column1) FROM your_table WHERE column2 = 'some_column2_value';

Upvotes: 4

Thomas Morrison
Thomas Morrison

Reputation: 647

Using the model query structure you could do this

Class.query.with_entities(Class.title).distinct()

Upvotes: 36

cheickmec
cheickmec

Reputation: 183

titles = [r.title for r in session.query(Class.title).distinct()]

Upvotes: 7

van
van

Reputation: 76962

query = session.query(Class.title.distinct().label("title"))
titles = [row.title for row in query.all()]

Upvotes: 26

Related Questions