psycopg/psycopg2 · error · ValueError

no format conversion supported by SQL

Error message

no format conversion supported by SQL

What it means

Raised by SQL.format() (lib/sql.py:232) when a placeholder carries a conversion flag (!r, !s, !a), e.g. {0!r}. Conversions imply a Python-level repr/str/ascii transformation, which is meaningless for Composable objects (Identifier/Literal/SQL render themselves), so the library rejects them up front. Like error [20], it is detected during the single string.Formatter().parse() pass over the template.

Source

Thrown at lib/sql.py:232

            >>> print(sql.SQL("select * from {} where {} = %s")
            ...     .format(sql.Identifier('people'), sql.Identifier('id'))
            ...     .as_string(conn))
            select * from "people" where "id" = %s

            >>> print(sql.SQL("select * from {tbl} where {pkey} = %s")
            ...     .format(tbl=sql.Identifier('people'), pkey=sql.Identifier('id'))
            ...     .as_string(conn))
            select * from "people" where "id" = %s

        """
        rv = []
        autonum = 0
        for pre, name, spec, conv in _formatter.parse(self._wrapped):
            if spec:
                raise ValueError("no format specification supported by SQL")
            if conv:
                raise ValueError("no format conversion supported by SQL")
            if pre:
                rv.append(SQL(pre))

            if name is None:
                continue

            if name.isdigit():
                if autonum:
                    raise ValueError(
                        "cannot switch from automatic field numbering to manual")
                rv.append(args[int(name)])
                autonum = None

            elif not name:
                if autonum is None:
                    raise ValueError(
                        "cannot switch from manual field numbering to automatic")
                rv.append(args[autonum])

View on GitHub (pinned to 3a6d9d6ddc)

Solutions

1. Drop the '!conv' suffix from placeholders (e.g. {0!r} -> {0}, {x!s} -> {x}).
2. If you want a quoted SQL literal of a Python value, use sql.Literal(value) and splice it via the placeholder.
3. Render to a string first and format in plain Python if you truly need repr.

Example fix

// before
q = sql.SQL("values {0!r}").format(val)
// after
q = sql.SQL("values {}").format(sql.Literal(val))

import string

def sql_template_is_clean(template: str) -> bool:
    for _pre, _name, spec, conv in string.Formatter().parse(template):
        if spec or conv:
            return False
    return True

assert sql_template_is_clean(tpl)

When it happens

Trigger: Calling sql.SQL(...).format(...) with a template containing {0!r}, {x!s}, or any {name!conv} / {idx!conv} placeholder. Example: sql.SQL("select {0!r}").format(sql.Identifier('x')).

Common situations: Pasting debug/log strings that used {var!r} for repr-style output into an SQL template. Assuming SQL.format mirrors str.format's full grammar.

Related errors

• no format specification supported by SQL
• cannot switch from automatic field numbering to manual
• cannot switch from manual field numbering to automatic
• invalid name: {name!r}
• Identifier cannot be empty

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