Python sqlite case with when then to get records matching to condition in a group or range from table

We are using our student table. View and Download the sqlite-case.ipynb file at the end to create your student table and run the sample codes.

Read more on case query here.

CASE A_value
    WHEN B_value THEN B_statement
    [WHEN C_Value THEN C_statement] ...
    [ELSE X_statement]
END CASE

Here A_value is matched with different values given under WHEN. If A_value equals to B_value then B_statement is executed. If it is not matched than next WHEN value C_value is checked. If nothing is matched then statement under X_statement is executed.

If any WHEN statement is matched then rest WHEN statements are not checked and matched statement is returned.

We used escape char \ to add or include breaks when we are continuing our string to next line. Here mostly used for queries

USING case query

We will collect records by using case query by using execute method. We will use case with SELECT command.

We can display error message if there is any error by using try except .

Here we are allotting floor based on the data of class column. Each class value is compared and if none of the WHEN condition matches, then ELSE statement is executed and Ground floor ( in this query ) is allotted. The query returns the data under location column head.

q="SELECT  `id` ,  `name` ,  `class` ,  `mark` ,  `sex` , \
CASE class \
WHEN  'Four' THEN   '1st floor' \
WHEN  'Five' THEN    '2nd floor' \
WHEN  'Three' THEN    '2nd floor' \
WHEN  'Two' THEN    '1st floor' \
ELSE 'Ground floor' \
END AS  location \
FROM  `student` "

try:
    my_cursor=my_conn.execute(q)
    
    for row in my_cursor:
        print(row[0],row[1],row[2],row[3],row[4],'-',row[5])
except sqlite3.Error as my_error:
  print("error: ",my_error)

Output ( first 4 records are displayed here , there are more records, check the downloaded file at the end of this tutorial )

1 John Deo Four 75 female - 1st floor
2 Max Ruin Three 85 male - 2nd floor
3 Arnold Three 55 male - 2nd floor
4 Krish Star Four 60 female - 1st floor
----------------
----------------

Allotting grade based on mark

We can assign grade based on the value under MARK column. You can change the mark range to different value. Note that once one WHEN condition is True then reset of the WHEN conditions are not checked. So the grading has to start from top and grades are assigned based on highest matched grade. ( Example : When mark value is 95, it qualifies first as grade A though it is greater than 80 and also qualifies under grade B )

q="SELECT  `id` ,  `name` ,  `class` ,  `mark` ,  `sex` , \
CASE  \
WHEN mark >= 90 THEN   'A' \
WHEN mark >= 80 THEN    'B' \
WHEN mark >= 70 THEN   'C' \
ELSE 'FAIL' \
END AS  grade \
FROM  `student` "
try:
    my_cursor=my_conn.execute(q)
    
    for row in my_cursor:
        print(row[0],row[1],row[2],row[3],row[4],'-',row[5])
except sqlite3.Error as my_error:
  print("error: ",my_error)

Output ( check the downloaded file at the end for full output )

1 John Deo Four 75 female - C
2 Max Ruin Three 85 male - B
3 Arnold Three 55 male - FAIL
4 Krish Star Four 60 female - FAIL
----------------
----------------

Using Between

We can use BETWEEN query to define a range for the MARK column.

q="SELECT  `id` ,  `name` ,  `class` ,  `mark` ,  `sex` ,\
CASE WHEN mark BETWEEN  90  AND 100 THEN   'A'\
WHEN mark BETWEEN  80  AND 89 THEN    'B'\
WHEN mark BETWEEN  70  AND 79 THEN   'C'\
ELSE 'FAIL' \
END AS  grade FROM  `student`"
try:
    my_cursor=my_conn.execute(q)
    
    for row in my_cursor:
        print(row[0],row[1],row[2],row[3],row[4],row[5])
except sqlite3.Error as my_error:
  print("error: ",my_error)

Output is same as above.

Using Parameters

Always use parameterized query when the data is coming from unknown sources. Use ? as placeholder and a provide a tuple to pass the value to query or execute() method. This is required to prevent injection attack.

We have used placeholders ( ? ) in our query and note that my_data is a tuple used for passing value to execute() method for our query.

my_data=(90,100,80,89,70,79)
q="SELECT  `id` ,  `name` ,  `class` ,  `mark` ,  `sex` ,\
CASE WHEN mark BETWEEN  ?  AND ? THEN   'A'\
WHEN mark BETWEEN  ?  AND ? THEN    'B'\
WHEN mark BETWEEN  ?  AND ? THEN   'C'\
ELSE 'FAIL' \
END AS  grade FROM  `student`"
try:
    my_cursor=my_conn.execute(q,my_data)
    
    for row in my_cursor:
        print(row[0],row[1],row[2],row[3],row[4],row[5])
except sqlite3.Error as my_error:
  print("error: ",my_error)

Output ( same as above )

Using DATE

We can use case query to get records of a range of dates. Our modified student table has one date column dot. Using this dot column we can display range of records.

You can view and download the modified student table with date column at the end of this tutorial.

Distributing financial year wise

We have four quarters in our financial year staring from April and ending at next year March.

Quarter 1 ( Q1) is from April – June
Quarter 2 ( Q2) is from July – Sep
Quarter 3 ( Q3 ) is from Oct – Dec
Quarter 4 ( Q4) is from Jan to March.

Based on the data available under doe column we can group rows on different quarters ( Q1 , Q2, Q3 & Q4 ). Those rows which are not matching to any quarter period, we will return saying Outside FY ( outside the financial year ) .

my_data=('2020-04-01','2020-06-30','2020-07-01','2020-09-30',
         '2020-10-01','2020-12-31','2021-01-01','2021-03-31') 
q="SELECT id,name,mark,class,dot,\
CASE WHEN dot BETWEEN  ?  AND ? THEN   'Q1'\
WHEN dot BETWEEN  ?  AND ? THEN    'Q2'\
WHEN dot BETWEEN  ?  AND ? THEN    'Q3'\
WHEN dot BETWEEN  ?  AND ? THEN    'Q4'\
ELSE 'Outside FY' \
END AS  Date FROM  `student`"

try:
    my_cursor=my_conn.execute(q,my_data)
    
    for row in my_cursor:
        print(row[0],row[1],row[3],row[2],row[4],'-',row[5])
except sqlite3.Error as my_error:
  print("error: ",my_error)

Output ( sample only, all records are included in downloaded page )

1 John Deo Four 75 2020-12-20 - Q3
2 Max Ruin Three 85 2020-12-21 - Q3
3 Arnold Three 55 2019-11-20 - Outside FY
4 Krish Star Four 60 2020-08-16 - Q2
----------------
----------------

View and Download sqlite-case.ipynb file View and Download sqlite-case-between.ipynb file

Sqlite Connection insert Delete Order By

Numpy Pandas Python & MySQL Python- Tutorials

Subscribe to our YouTube Channel here

SQLite case Query