Filtering Data in MySQL
-
Filter MySQL records using WHERE conditions in Python queries.
WHERE Clause
The WHERE clause is used to filter records based on a condition.
Syntax:
SELECT column1, column2 FROM table_name
WHERE condition;
Example in plain language:
Fetch students whose course is "MCA".
Filter Students Using WHERE
import mysql.connector
conn = mysql.connector.connect(
host="localhost",
user="root",
password="root",
database="college_db"
)
cursor = conn.cursor()
# Fetch students enrolled in MCA
cursor.execute("SELECT * FROM students WHERE course='MCA'")
mca_students = cursor.fetchall()
for student in mca_students:
print(student)
conn.close()
Explanation:
Only rows matching course='MCA' are returned
ORDER BY Clause
The ORDER BY clause is used to sort results.
Default → ascending (ASC)
Use DESC → descending
Syntax:
SELECT column1, column2 FROM table_name
ORDER BY column1 ASC|DESC;
Sort Students by Age
import mysql.connector
conn = mysql.connector.connect(
host="localhost",
user="root",
password="root",
database="college_db"
)
cursor = conn.cursor()
# Fetch all students sorted by age descending
cursor.execute("SELECT * FROM students ORDER BY age DESC")
students_sorted = cursor.fetchall()
for student in students_sorted:
print(student)
conn.close() Explanation:
ORDER BY age DESC → Oldest student first
Use ASC → Youngest first
LIMIT Clause
The LIMIT clause is used to restrict the number of rows returned.
Syntax:
SELECT column1, column2 FROM table_name
LIMIT number_of_rows;
Limit Number of Records
import mysql.connector
conn = mysql.connector.connect(
host="localhost",
user="root",
password="root",
database="college_db"
)
cursor = conn.cursor()
# Fetch only 2 students
cursor.execute("SELECT * FROM students LIMIT 2")
limited_students = cursor.fetchall()
for student in limited_students:
print(student)
conn.close()Explanation:
Returns only 2 records from the table
Combining WHERE, ORDER BY, LIMIT
You can use all together:
SELECT * FROM students
WHERE course='MCA'
ORDER BY age ASC
LIMIT 3;
Filter, Sort & Limit Students
import mysql.connector
conn = mysql.connector.connect(
host="localhost",
user="root",
password="root",
database="college_db"
)
cursor = conn.cursor()
cursor.execute("SELECT * FROM students WHERE course='MCA' ORDER BY age ASC LIMIT 3")
filtered_students = cursor.fetchall()
for student in filtered_students:
print(student)
conn.close()
Explanation:
Filters MCA students
Sorts by age ascending
Returns first 3 students only