| name | psycopg2-like-percent-escape |
| description | Fix psycopg2 "IndexError: tuple index out of range" when using LIKE with parameterized queries.
Use when: (1) cursor.execute() fails with IndexError on a query containing LIKE '%pattern%',
(2) SQL LIKE wildcards conflict with psycopg2 %s parameter placeholders, (3) Query works
in psql but fails in Python. The % character has dual meaning: SQL LIKE wildcard AND
psycopg2's parameter substitution marker.
|
| author | Claude Code |
| version | 1.0.0 |
| date | "2026-01-29T00:00:00.000Z" |
psycopg2 LIKE Clause Percent Sign Escaping
Problem
When using psycopg2 with parameterized queries containing SQL LIKE patterns, the % character
causes conflicts. The % is used both as:
- SQL LIKE wildcard (e.g.,
'%pattern%')
- psycopg2's parameter placeholder marker (e.g.,
%s)
This results in confusing errors like IndexError: tuple index out of range because psycopg2
interprets %c in %cdn as a format specifier.
Context / Trigger Conditions
IndexError: tuple index out of range from cursor.execute()- Query contains hardcoded LIKE pattern:
WHERE column LIKE '%something%'
- Query also uses
%s parameters for other values
- Query works in psql or pgAdmin but fails in Python
Example failing code:
cursor.execute("""
SELECT * FROM vines
WHERE url LIKE '%cdn.vine.co%'
LIMIT %s OFFSET %s
""", (1000, 0))
Solution
Option 1: Escape % with %% (for static patterns)
Double the percent signs in hardcoded LIKE patterns:
cursor.execute("""
SELECT * FROM vines
WHERE url LIKE '%%cdn.vine.co%%'
LIMIT %s OFFSET %s
""", (1000, 0))
Option 2: Pass LIKE pattern as parameter (recommended)
The cleaner approach - pass the entire LIKE pattern as a parameter:
pattern = '%cdn.vine.co%'
cursor.execute("""
SELECT * FROM vines
WHERE url LIKE %s
LIMIT %s OFFSET %s
""", (pattern, 1000, 0))
This is the recommended approach because:
- No escaping confusion
- Pattern can be dynamically constructed
- Follows parameterized query best practices
Option 3: Use psycopg2.sql module for complex cases
For dynamic SQL construction:
from psycopg2 import sql
query = sql.SQL("""
SELECT * FROM {table}
WHERE url LIKE %s
""").format(table=sql.Identifier('vines'))
cursor.execute(query, ('%cdn.vine.co%',))
Verification
After applying the fix:
- Query executes without IndexError
- Results correctly match the LIKE pattern
- Other
%s parameters are still substituted correctly
Example
Before (broken):
def get_vines_by_cdn(db, limit, offset):
cursor = db.cursor()
cursor.execute("""
SELECT vine_id, url FROM discovered_vines
WHERE url LIKE '%cdn.vine.co%'
ORDER BY created_at
LIMIT %s OFFSET %s
""", (limit, offset))
return cursor.fetchall()
After (fixed with Option 1):
def get_vines_by_cdn(db, limit, offset):
cursor = db.cursor()
cursor.execute("""
SELECT vine_id, url FROM discovered_vines
WHERE url LIKE '%%cdn.vine.co%%'
ORDER BY created_at
LIMIT %s OFFSET %s
""", (limit, offset))
return cursor.fetchall()
After (fixed with Option 2 - recommended):
def get_vines_by_cdn(db, limit, offset):
cursor = db.cursor()
cdn_pattern = '%cdn.vine.co%'
cursor.execute("""
SELECT vine_id, url FROM discovered_vines
WHERE url LIKE %s
ORDER BY created_at
LIMIT %s OFFSET %s
""", (cdn_pattern, limit, offset))
return cursor.fetchall()
Notes
- This issue only affects parameterized queries with
%s placeholders
- Raw SQL strings without parameters don't have this problem
- The
%% escape only works when the query uses psycopg2's parameter substitution
- Django's ORM handles this automatically; this is a raw SQL issue
- psycopg3 uses
$1, $2 style placeholders, avoiding this conflict entirely
Related Issues
- Searching for literal
% in data requires additional escaping with ESCAPE clause
- Similar issues can occur with
_ (single character wildcard) if using %_ pattern
References
