Python sqlite distinct query to get unique records of columns from table

We are using our student table for the examples here . You can create or download the SQLite database with sample records here.

Interactive Companion Jupyter Notebook

Explore interactive code cells, execute DISTINCT unique value queries, test multi-column uniqueness, and run SQLAlchemy 2.0+ workflows directly in Google Colab.

View on GitHub

Video Tutorial on SQLite DISTINCT Query

Read more on MySQL distinct query here.

Video Tutorial on Use of DISTINCT
Using student table all the (unique) classes are displayed as options of drop down list
This video is part of a full tutorial about two dependent drop down list

SELECT DISTINCT(Column_name) FROM table

In our sample database my_db.db, we have student table. Each row ( student ) have one class. We will display the unique classes by using DISTINCT query.

q = "SELECT DISTINCT(class) FROM student"
try:
    my_cursor = my_conn.execute(q)
    
    for row in my_cursor:
        print(row[0])
except sqlite3.Error as my_error:
  print("error: ", my_error)

Output

Four
Three
Five
Six
Seven
Nine
Eight

We can add ORDER BY to this to display in alphabetical order. ( the change in query is shown here )

q = "SELECT DISTINCT(class) FROM student ORDER BY class"

Using COUNT()

How many unique classes are there in our student table ?

q = "SELECT COUNT(DISTINCT(class)) FROM student"

Output

Using SQLAlchemy (2.0+ Context Manager) with WHERE condition

from sqlalchemy import create_engine, text
from sqlalchemy.exc import SQLAlchemyError

my_conn = create_engine("sqlite:///my_db.db")
q = text("SELECT DISTINCT(class) FROM student WHERE mark >= 80 ORDER BY class")

try:
  with my_conn.connect() as connection:
    r_set = connection.execute(q)
    data = r_set.fetchall()
    for row in data:
      print(row[0])
    print("No of records displayed : ", len(data))
except SQLAlchemyError as e:
  print(e)

Output

Three
Five
Six
Seven
Four
No of records displayed :  5

Using two columns

We can get distinct or unique values using two columns.

q = "SELECT DISTINCT class, gender FROM student"
try:
    my_cursor = my_conn.execute(q)
    
    for row in my_cursor:
        print(row[0], row[1])
except sqlite3.Error as my_error:
  print("error: ", my_error)

Output

Four female
Three male
Four male
Five male
Six male
Six female
Seven female
Nine male
Nine female
Eight male
Seven male
Three female

Handling null data

Null value in a column is also considered as one DISTINCT value. So our result set will contain one row or record for NULL value. If we want not to included NULL value then we can exclude those records by using NOT NULL.

q = "SELECT DISTINCT(class) FROM student WHERE class IS NOT NULL"
try:
    my_cursor = my_conn.execute(q)
    
    for row in my_cursor:
        print(row[0])
except sqlite3.Error as my_error:
  print("error: my_error)

Sqlite Connection insert Delete Order By

Numpy Pandas Python & MySQL Python- Tutorials

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

SQLite DISTINCT Query

Interactive Companion Jupyter Notebook

Video Tutorial on SQLite DISTINCT Query

Video Tutorial on Use of DISTINCT

Using COUNT()

Using two columns

Handling null data

Subhendu Mohapatra