syntax to guard against SQL-injection of named identifiers

Question

I'm reading the psycopg2 documentation & wondering how to parametrize SQL identifiers of tables with a name? Here is an example:

import psycopg2

conn = psycopg2.connect()
cursor = conn.cursor()
cursor.execute(
   "SELECT * FROM %(my_table)s LIMIT %(my_limit)s;"
   vars={
       "my_limit": 42,                    # parametrizing literals works fine.
       "my_table": sql.Identifier("foo"), # how to do same with named identifiers?
   }
)

psycopg2.ProgrammingError: can't adapt type 'Identifier'

I know I could use positional parameters %s or {} but I would like the query to mix and match identifiers with literals with a named mapping.

Answer 1

This did it for me:

import psycopg2
from psycopg2 import sql

conn = psycopg2.connect()
cursor = conn.cursor()
cursor.execute(sql.SQL(
    "SELECT * FROM {my_table} LIMIT {my_limit};"
  ).format(
    my_limit = sql.Literal(42),
    my_table = sql.Identifier("foo"),
  ).as_string(conn)
)