Python SQLModel FastAPI Get Unique Values From Database

To get unique values for a specific column in SQLModel, the key is to select only that column before applying .distinct(). If you select the entire model (e.g., select(Item)), the database will evaluate uniqueness across all columns (including the unique primary key), so duplicate values in the target column will not be filtered out.

Recommended Approach: Select a Single Column and Extract the Values

from fastapi import FastAPI, Depends
from sqlmodel import Session, select

app = FastAPI()

@app.get("/unique-titles")
def get_unique_titles(session: Session = Depends(get_session)):
   statement = select(Item.title).distinct()
   results = session.exec(statement).all()
   # results is a list of strings (the unique titles)
   return results

Why this works: select(Item.title).distinct() generates SQL similar to SELECT DISTINCT item.title FROM item, and the database only compares the title column, thereby returning unique values.

If You Need to Return a Structured Response

If you want to return a list of objects in the API (e.g., [{"title": "..."}, ...]), you can manually construct the dictionaries after querying:

@app.get("/unique-titles")
def get_unique_titles(session: Session = Depends(get_session)):
   statement = select(Item.title).distinct()
   titles = session.exec(statement).all()
   return [{"title": title} for title in titles]

Important Notes

1.Do not use select(Item).distinct(): This will include the primary key column, causing the DISTINCT to fail to filter duplicate values in the target column.

2.If you need to retain the complete object and deduplicate based on a certain column, PostgreSQL supports distinct(Item.title), but this is database-specific and not portable to other databases (such as MySQL or SQLite).

3.To add filtering conditions, you can chain .where() before .distinct(), for example:

statement = select(Item.title).where(Item.title.ilike("%keyword%")).distinct()

8 Views