Search code examples
pythonpsycopg2

psycopg2 sql.Identifier fails executing when query is valid

I have a simple psycoph2 snippet which tries to grant usage on a schema but it displays syntax however the query is perfectly valid when executed on the terminal

import psycopg2
from psycopg2 import Error, sql, extras, extensions

cursor.execute(sql.SQL("GRANT USAGE on SCHEMA public TO {role}".format(role=sql.Identifier("readonly_role"))))

Error while connecting to PostgreSQL syntax error at or near "("
LINE 1: GRANT USAGE on SCHEMA public TO Identifier('readonly_role')
Solution

  • Your parens are in the wrong place.

    Use sql.SQL() on the full raw SQL string, then .format() on the result of that (not on the raw string itself).

    Here it is broken up and indented for clarity:

    cursor.execute(
    sql.SQL(
        "GRANT USAGE on SCHEMA public TO {role}"
    ).format(
        role=sql.Identifier(
            "readonly_role"
        )
    )
)