psycopg2-like-percent-escape

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:

1. SQL LIKE wildcard (e.g., '%pattern%')
2. 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))
# IndexError: tuple index out of range

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:

1. Query executes without IndexError
2. Results correctly match the LIKE pattern
3. Other %s parameters are still substituted correctly