psycopg/psycopg2 · error · ValueError

the query contains more than one '%s' placeholder

Error message

the query contains more than one '%s' placeholder

What it means

Raised by _split_sql() (lib/extras.py:1324-1325), invoked from execute_values(), when the query passed to execute_values contains more than one '%s' positional placeholder. execute_values replaces exactly one '%s' with a VALUES list; additional '%s' are ambiguous and rejected. Note that '%%' is correctly handled as a literal percent and does not count.

Source

Thrown at lib/extras.py:1324

def _split_sql(sql):
    """Split *sql* on a single ``%s`` placeholder.

    Split on the %s, perform %% replacement and return pre, post lists of
    snippets.
    """
    curr = pre = []
    post = []
    tokens = _re.split(br'(%.)', sql)
    for token in tokens:
        if len(token) != 2 or token[:1] != b'%':
            curr.append(token)
            continue

        if token[1:] == b's':
            if curr is pre:
                curr = post
            else:
                raise ValueError(
                    "the query contains more than one '%s' placeholder")
        elif token[1:] == b'%':
            curr.append(b'%')
        else:
            raise ValueError("unsupported format character: '%s'"
                % token[1:].decode('ascii', 'replace'))

    if curr is pre:
        raise ValueError("the query doesn't contain any '%s' placeholder")

    return pre, post


# ascii except alnum and underscore
_re_clean = _re.compile(
    '[' + _re.escape(' !"#$%&\'()*+,-./:;<=>?@[\\]^`{|}~') + ']')

View on GitHub (pinned to 3a6d9d6ddc)

Solutions

1. Ensure the execute_values query contains exactly one '%s' (the values placeholder).
2. Move any other parameters into the template via cur.mogrify, or run a separate execute.
3. Use named placeholders (%(name)s) inside the 'template' argument rather than top-level '%s'.
4. If you need a dynamic column or table name, use psycopg2.sql composition instead of a second '%s'.

Example fix

// before
execute_values(cur, "INSERT INTO t VALUES %s RETURNING %s", rows, ['id'])
// after
execute_values(cur, "INSERT INTO t VALUES %s RETURNING id", rows, fetch=True)

assert sql.count('%s') == 1, f'execute_values requires exactly one %s, got {sql.count("%s")}'
# Note: also account for %% escaping if present.

def has_single_values_placeholder(sql: str) -> bool:
    import re
    toks = re.split(r'(%.)', sql)
    return sum(1 for t in toks if t == '%s') == 1

try:
    execute_values(cur, sql, data)
except ValueError as e:
    if 'more than one' in str(e):
        sql = sql.replace(extra_placeholder, ...)  # rewrite
    else: raise

When it happens

Trigger: Calling execute_values(cur, "INSERT INTO t (a,b) VALUES %s ON CONFLICT DO NOTHING RETURNING %s", data) or any execute_values query with two or more '%s'. The tokenizer at lib/extras.py:1314 splits on '(%.)' and the second b's' token flips curr from post back into the error branch.

Common situations: Developers add an extra '%s' expecting to bind another scalar parameter alongside the values list, or they reuse a query template written for cursor.execute (which supports many %s) without removing the extras.

Related errors

• the query doesn't contain any '%s' placeholder
• unsupported format character: '%s'
• bound flags not valid: {bounds!r}
• DictCursorBase can't be instantiated without a row factory.
• Composed elements must be Composable, got {i!r} instead

AI-assisted analysis of psycopg/psycopg2@3a6d9d6ddc (2026-08-04). Data as JSON: /data/errors/2e614f151a16c210.json. Report an issue: GitHub.