Parameterized Queries with Multiple Values
You'll learn how to safely execute queries with multiple parameter sets—like inserting several rows at once—while keeping your code secure from SQL injection.
The Core Idea
So far, you've used parameterized queries to safely insert or update one record at a time. But what if you need to insert 100 customer records, or update prices for 50 products? Instead of executing 100 separate queries, you can pass a list or tuple of parameter tuples to batch-execute the same query multiple times efficiently.
How It Works
Most database libraries provide an executemany() method (instead of execute()). You supply:
- A parameterized SQL statement (with placeholders)
- A list of tuples, where each tuple contains the values for one execution
Think of it like a mail-merge: you write one template letter, then the system automatically fills in different names and addresses for each recipient.
import sqlite3
conn = sqlite3.connect('sales.db')
cursor = conn.cursor()
# Prepare data as a list of tuples
new_customers = [
('Alice', 'alice@example.com'),
('Bob', 'bob@example.com'),
('Charlie', 'charlie@example.com')
]
# Execute parameterized query for all rows
cursor.executemany(
'INSERT INTO customers (name, email) VALUES (?, ?)',
new_customers
)
conn.commit()
Each tuple in new_customers provides values for the two ? placeholders. The database library handles the looping securely—no string concatenation, no SQL injection risk.
This same pattern works for UPDATE or DELETE operations where you need to modify multiple records based on different criteria.
Key Takeaway:
Use executemany() with a list of tuples to safely batch-execute parameterized queries—maintaining both security and efficiency when working with multiple rows.