Back to Python
2026-03-255 min read

MySQL CASE (Python Programming)

Learn MySQL CASE (Python Programming) step by step with clear examples and exercises.

Title: MySQL CASE Statement (Python Programming)

Why This Matters

The MySQL CASE statement is a crucial tool for conditional logic in SQL, allowing you to perform complex operations in a single line of code. In Python programming, you can interact with MySQL databases using the mysql-connector-python library. Mastering the CASE statement can help you write more efficient and flexible database queries, making your applications more robust.

Prerequisites

Before diving into the MySQL CASE statement, ensure you have a good understanding of:

  1. Python programming basics (variables, functions, loops, etc.)
  2. SQL syntax and basic operations (SELECT, FROM, WHERE, JOIN, etc.)
  3. Connecting to a MySQL database using Python (mysql-connector-python)
  4. Basic knowledge of tables, columns, and data types in relational databases
  5. Understanding the difference between server-side and client-side processing

Core Concept

The MySQL CASE statement allows you to perform conditional logic within a SQL query. It is similar to the IF-ELSE construct in programming languages but is executed on the server side, which can improve performance for large datasets.

A basic syntax of the MySQL CASE statement is as follows:

CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE else_result
END AS alias;

In this syntax, you define one or more conditions and their corresponding results. If none of the conditions are met, the ELSE clause is executed. The AS alias part assigns an alias to the result, which can be used in further queries.

Here's an example using the CASE statement with a simple dataset:

CREATE TABLE students (id INT PRIMARY KEY, name VARCHAR(20), score DECIMAL(5, 2));
INSERT INTO students (id, name, score) VALUES
(1, 'Alice', 90.7),
(2, 'Bob', 85.3),
(3, 'Charlie', 78.6);

SELECT id, name, CASE
WHEN score > 85 THEN 'Excellent'
WHEN score >= 70 AND score <= 84 THEN 'Good'
ELSE 'Needs Improvement' AS grade
FROM students;

In this example, the query calculates grades for a simple dataset of student scores. The CASE statement checks each row's score and assigns a corresponding grade based on predefined conditions.

Worked Example

Let's create a Python script that connects to a MySQL database, executes a query with the CASE statement, and prints the results:

import mysql.connector

Connect to the MySQL server

cnx = mysql.connector.connect(

user='username', password='password',

host='localhost',

database='test_database'

)

cursor = cnx.cursor()

Create a table for demonstration purposes

query_create_table = '''

CREATE TABLE IF NOT EXISTS students (id INT PRIMARY KEY, name VARCHAR(20), score DECIMAL(5, 2));

'''

cursor.execute(query_create_table)

Insert data into the table

query_insert_data = '''

INSERT INTO students (id, name, score) VALUES

(1, 'Alice', 90.7),

(2, 'Bob', 85.3),

(3, 'Charlie', 78.6);

'''

cursor.execute(query_insert_data)

Execute the query with the CASE statement

query = '''

SELECT id, name, CASE

WHEN score > 85 THEN 'Excellent'

WHEN score >= 70 AND score <= 84 THEN 'Good'

ELSE 'Needs Improvement' AS grade

FROM students;

'''

cursor.execute(query)

Fetch and print the results

results = cursor.fetchall()

for row in results:

print(f"{row[0]}: {row[1]} - Grade: {row[2]}")

Insert a new student with a score of 60 in the 'History' subject

query_insert_new_student = '''

INSERT INTO students (id, name, score) VALUES

(4, 'David', 60.0);

'''

cursor.execute(query_insert_new_student)

Update the CASE statement to categorize scores between 50 and 69 as "Fair"

query_update_case = '''

ALTER TABLE students

CHANGE COLUMN grade grade DECIMAL(20, 2);

UPDATE students SET grade = CASE

WHEN score > 85 THEN 'Excellent'

WHEN score >= 70 AND score <= 84 THEN 'Good'

WHEN score >= 60 AND score <= 69 THEN 'Fair'

ELSE 'Needs Improvement' AS grade;

'''

cursor.execute(query_update_case)

Fetch and print the updated results

query = '''

SELECT id, name, grade FROM students;

'''

cursor.execute(query)

results = cursor.fetchall()

for row in results:

print(f"{row[0]}: {row[1]} - Grade: {row[2]}")

Close the database connection

cnx.close()


In this example, we first create a table and insert some data into it. Then, we execute a query with the CASE statement to calculate grades for the existing students. We also insert a new student with a score of 60 in the 'History' subject and update the CASE statement to categorize scores between 50 and 69 as "Fair."

Common Mistakes

  1. Forgetting to assign an alias: The AS keyword is necessary to give a name to the result of the CASE statement.

Incorrect:

SELECT id, name, CASE score > 85 THEN 'Excellent'
FROM students;

Correct:

SELECT id, name, CASE
WHEN score > 85 THEN 'Excellent'
AS grade
FROM students;
  1. Misusing the syntax: Ensure that you use the WHEN and THEN keywords together, and always end the CASE statement with an END keyword.

Incorrect:

SELECT id, name, CASE score > 85 'Excellent'
FROM students;

Correct:

SELECT id, name, CASE
WHEN score > 85 THEN 'Excellent'
FROM students;
  1. Not handling all possible conditions: If you don't include an ELSE clause, the CASE statement will return NULL for rows that don't match any specified conditions. To avoid this, always include an ELSE clause with a default result or handle these cases separately in your SQL query.
  1. Using incorrect data types: Be aware of the data types of your columns when using arithmetic operations within the CASE statement. For example, if you're comparing decimal numbers, ensure that both columns are defined as DECIMAL or FLOAT to avoid type coercion issues.

Practice Questions

  1. Write a MySQL query using the CASE statement to calculate the average score for each subject in the scores table. If there are fewer than three scores for a given subject, classify it as "Insufficient Data."
  1. Modify the worked example to include a new student with a score of 60 in the 'History' subject. Update the CASE statement to categorize scores between 50 and 69 as "Fair." Also, calculate the total score for each subject and average score overall.
  1. Write a MySQL query using the CASE statement to find all students whose names start with 'A' or have a score greater than 85. Classify students with scores between 70 and 84 as "Above Average."

FAQ

  1. Can I use the MySQL CASE statement with aggregate functions like SUM or AVG?

Yes, you can use the CASE statement with aggregate functions in MySQL. Here's an example:

SELECT subject, SUM(CASE WHEN score > 85 THEN score ELSE 0 END) AS total_excellent, COUNT(*) as num_students
FROM scores
GROUP BY subject;
  1. Is it possible to use the MySQL CASE statement with multiple conditions in a single WHEN clause?

Yes, you can combine multiple conditions in a single WHEN clause using the AND keyword. Here's an example:

SELECT id, name, CASE
WHEN score BETWEEN 60 AND 79 THEN 'Average'
WHEN score >= 80 THEN 'Good'
ELSE 'Poor'
FROM students;
  1. Can I use the MySQL CASE statement with subqueries?

Yes, you can use subqueries within the MySQL CASE statement. Here's an example:

SELECT id, name, CASE
WHEN score > (SELECT AVG(score) FROM students WHERE subject = s.subject) THEN 'Excellent'
ELSE 'Average'
END AS grade
FROM scores s;

In this example, the subquery calculates the average score for each subject and compares it with the current student's score to determine their grade.

MySQL CASE (Python Programming) | Python | XQA Learn