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.

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 and with WHERE condition

from sqlalchemy.exc import SQLAlchemyError
q="SELECT DISTINCT(class) FROM student WHERE mark>=80"
try:
  r_set=my_conn.execute(q)
  data=r_set.fetchall()
  for row in data:
    print(row[0])
except SQLAlchemyError as e:
  error=str(e.__dict__['orig'])
  print(error)
else:
  print("No of records displayed : ",len(data))

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 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)

View and Download sqlite-distinct.ipynb file

Sqlite Connection insert Delete Order By

Numpy Pandas Python & MySQL Python- Tutorials

Subscribe to our YouTube Channel here

SQLite DISTINCT Query

Video Tutorial on SQLite DISTINCT Query

Video Tutorial on Use of DISTINCT

Using COUNT()

Using two columns

Handling null data

Subscribe