Parameter Placeholders: Named Parameters
You'll learn how to write SQL queries with named placeholders for better readability and maintainability.
In the previous lesson, you used question marks (?) as parameter placeholders—simple but anonymous. Named parameters give each placeholder a meaningful name using the :name syntax, making your queries self-documenting and easier to modify.
Why Named Parameters Matter
Imagine ordering a sandwich by saying "I want option 1, option 3, and option 2" versus "I want turkey, lettuce, and tomato." Named parameters work the same way—they tell you what each value represents, not just where it goes.
With question marks, you must pass parameters in exact order. With named parameters, the dictionary keys make the intent crystal clear.
Using Named Parameters in SQLite
Instead of passing a tuple of values, you pass a dictionary where keys match the placeholder names:
import sqlite3
conn = sqlite3.connect('company.db')
cursor = conn.cursor()
# Named parameters make the query self-explanatory
query = """
SELECT name, salary
FROM employees
WHERE department = :dept AND salary > :min_salary
"""
# Pass values as a dictionary
params = {
'dept': 'Engineering',
'min_salary': 75000
}
cursor.execute(query, params)
results = cursor.fetchall()
Notice how :dept and :min_salary clearly indicate what each value represents. If you need to reorder query logic or add parameters, you won't accidentally swap values.
Benefits Over Positional Placeholders
- Readability: Anyone reading your code immediately understands what each parameter does
- Maintainability: Adding or reordering parameters won't break your code
- Reusability: The same parameter name can appear multiple times in one query
Key Takeaway: Named parameters (:name syntax) make parameterized queries more readable and maintainable by giving each placeholder a descriptive name, reducing errors from parameter ordering issues.