Python MySQL Dynamic Search and Filtering Engine
Interactive Google Colab Practice Notebook
Practice building dynamic search and filtering queries with conditional WHERE clauses, wildcard matching, and SQLAlchemy 2.0+ parameters directly inside Google Colab.
In real-world web applications (such as student portals or e-commerce directories), users frequently search and filter data using multiple optional criteria—such as searching by name keyword, filtering by class, or specifying a minimum mark threshold. In this tutorial, we will build a dynamic search engine in Python using SQLAlchemy 2.0+ [cite: 21].
1. Building Conditional WHERE Clauses Dynamically
When filter parameters are optional, hardcoding an SQL query fails. Instead, we can assemble query conditions and parameter dictionaries dynamically based on user input [cite: 21].
from sqlalchemy import create_engine, text
# Initialize SQLAlchemy engine
engine = create_engine("mysql+pymysql://demo_user:demo_password@localhost/my_tutorial")
def search_students(class_name=None, min_mark=None, name_keyword=None):
# Base query
sql = "SELECT id, name, class, mark, gender FROM student WHERE 1=1"
params = {}
# Conditionally append filters
if class_name:
sql += " AND class = :class_name"
params["class_name"] = class_name
if min_mark is not None:
sql += " AND mark >= :min_mark"
params["min_mark"] = min_mark
if name_keyword:
sql += " AND name LIKE :name_keyword"
params["name_keyword"] = f"%{name_keyword}%"
# Execute dynamic query
with engine.connect() as conn:
result = conn.execute(text(sql), params)
rows = result.fetchall()
print(f"Found {len(rows)} matching record(s):")
for row in rows:
print(row)
# Test search combinations
print("--- Search 1: Class 'Four' and Mark >= 60 ---")
search_students(class_name="Four", min_mark=60)
print("--- Search 2: Name Keyword 'John' ---")
search_students(name_keyword="John")Frequently Asked Questions & Common Errors
WHERE 1=1 is a convenient placeholder condition that evaluates to true. It allows us to safely append subsequent filter conditions using AND without needing to check whether the current condition is the first filter in the query.
LIKE, you should append wildcards (%) directly to the parameter value in Python (e.g., f"%{keyword}%") rather than trying to embed wildcards inside the SQL string format.
params) rather than direct string formatting or concatenation, your queries remain 100% safe against SQL injection attacks.
ORDER BY column_name ASC/DESC, but you must validate column names against an explicit whitelist of allowed database columns to prevent malicious input tampering.
Related Tutorials & Next Steps
-
Python MySQL Fetch Records Guide
Learn how to consume query results using fetchall() and fetchone() [cite: 21].
-
Python MySQL SQLAlchemy Guide
Master SQLAlchemy connection engines and execution patterns [cite: 21].
-
Python MySQL Connection Guide
Explore connection configuration and database drivers [cite: 21].
-
Student Table SQL Dump File
Download sample database schemas and data dumps [cite: 21].
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.
Subscribe to our YouTube Channel here
Python Video Tutorials