Python sqlite IIF condition checking using Query

We are using our student table. Check how to create database and student table here.

Interactive Companion Jupyter Notebook

Explore interactive code cells, execute IIF conditional queries, test aggregate SUM groupings, and build pivot grid reports directly in Google Colab.

View on GitHub

Read more on MySQL IF query here.

IIf function will check one condition and if it is true then the it will return the Next expression ( 2nd one ) and if False then it will return the 3rd expression.
Here is the syntax

IIF(expression_status, expression_if_true, expression_if_false)

USING IIF query

We will use our student table to display the students who have passed the exam. Mark is checked and if mark is greater than equal to 60 then the result is declared as Pass , otherwise it is Failed.

Here is the query

q = SELECT id, name, mark, IIF(mark >= 60, 'PASS', 'FAIL') AS Result \
FROM `student` LIMIT 0, 10

We have limited number of results to 10 only by adding Limit query, you can go as per your requirement.

Output is here.

id	name	mark	Result
1	John Deo	75	PASS
2	Max Ruin	85	PASS
3	Arnold	55	FAIL
4	Krish Star	60	PASS
5	John Mike	60	PASS
6	Alex John	55	FAIL
7	My John Rob	78	PASS
8	Asruid	85	PASS
9	Tes Qry	78	PASS
10	Big John	55	FAIL

Let us try a more complex query including SUM of columns. We will use our student3 table for this. Copy the code to create the table.

q = SELECT id, name, social + math + science AS total, \
IIF(social + math + science > 200, 'Pass', 'Fail') AS Result \
FROM student3

id	name	total	Result
2	Max Ruin	226	Pass
3	Arnold	170	Fail
4	Krish Star	180	Fail
5	John Mike	230	Pass
6	Alex John	225	Pass
7	My John Rob	208	Pass
8	Asruid	255	Pass
9	Tes Qry	208	Pass
10	Big John	150	Fail

Using aggregate function with GROUP BY

q = SELECT id, name, SUM(social + math + science) AS Total, \
IIF(SUM(social + math + science) > 200, 'Pass', 'Fail') AS Result \
FROM student3 GROUP BY id

You will get same output as above.

Grid view with rows as columns

We used SUM() to add all True conditions.

q = SELECT \
SUM(IIF(class = 'Three', 1, 0)) AS THREE, \
SUM(IIF(class = 'Four', 1, 0)) AS FOUR, \
SUM(IIF(class = 'Five', 1, 0)) AS FIVE, \
SUM(IIF(class = 'Six', 1, 0)) AS SIX, \
SUM(IIF(class = 'Seven', 1, 0)) AS SEVEN, \
SUM(IIF(class = 'Eight', 1, 0)) AS EIGHT, \
SUM(IIF(class = 'Nine', 1, 0)) AS NINIE \
FROM student

Output is here

THREE	FOUR	FIVE	SIX	SEVEN	EIGHT	NINIE
3	9	3	7	10	1	2

Now let us use COUNT, here we will return NULL for False condition of IIF status checking.

q = SELECT \
COUNT(IIF(class = 'Three', 1, NULL)) AS THREE, \
COUNT(IIF(class = 'Four', 1, NULL)) AS FOUR, \
COUNT(IIF(class = 'Five', 1, NULL)) AS FIVE, \
COUNT(IIF(class = 'Six', 1, NULL)) AS SIX, \
COUNT(IIF(class = 'Seven', 1, NULL)) AS SEVEN, \
COUNT(IIF(class = 'Eight', 1, NULL)) AS EIGHT, \
COUNT(IIF(class = 'Nine', 1, NULL)) AS NINIE \
FROM student

Output is here

THREE	FOUR	FIVE	SIX	SEVEN	EIGHT	NINIE
3	9	3	7	10	1	2

Let us crate a report using our student table to display total number of different grades in each class.

q = SELECT class, \
SUM(IIF(mark < 50, 1, 0)) AS grade_C, \
SUM(IIF(mark BETWEEN 50 AND 69, 1, 0)) AS grade_B, \
SUM(IIF(mark >= 70, 1, 0)) AS grade_A \
FROM `student` GROUP BY class

Output is here

class	grade_C	grade_B	grade_A
Eight	0	0	1
Five	0	0	3
Four	0	5	4
Nine	1	1	0
Seven	0	3	7
Six	0	1	6
Three	0	1	2

Using SQLAlchemy (2.0+ Context Manager)

q = text("SELECT id, name, mark, IIF(mark >= 60, 'PASS', 'FAIL') AS Result FROM student LIMIT 10")
try:
  with my_conn.connect() as connection:
    r_set = connection.execute(q)
    for row in r_set:
      print(row)
except SQLAlchemyError as e:
  print(e)

Sqlite INSERT DELETE UPDATE SELECT ORDER By LIMIT

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 IIF Query

Interactive Companion Jupyter Notebook

USING IIF query

Using aggregate function with GROUP BY

Grid view with rows as columns

Using SQLAlchemy (2.0+ Context Manager)

Subhendu Mohapatra