How to pass columns to select as a parameter?

Question

How do I pass as parameters the columns I want to select, especially when I want to select an unknown number of columns?

I'm using psycopg2 in python

This is my query:

columns_to_select = "errors, requests"
sql = "select bucket, %(metrics_to_select)s from metrics"
cursor.execute(sql, {"metrics_to_select": columns_to_select})

I want this to produce this query:

select bucket, errors, requests from metrics
        

Nothing that I have tried works.

Answer 1

Use psycopg2 sql module.

import psycopg2
from psycopg2 import sql
con = psycopg2.connect(dbname="test", host='localhost', user='postgres')

columns_to_select = ["errors", "requests"]

sql_str = sql.SQL("select bucket, {} from metrics").format(sql.SQL(",").join(map(sql.Identifier, columns_to_select)))
sql_str.as_string(con)

'select bucket, "errors","requests" from metrics'