String table name not working in a query with psycopg2

I would like to generate dynamic queries with variables for column names and table name. This is my code:

query = sql.SQL("select {fields} from {table}").format(fields=sql.SQL(',').join([
    sql.Identifier(country_column_id),
    sql.Identifier(time_column_id),
    sql.Identifier(value_column_id),
    sql.Identifier(type_column_id),
]),
table=sql.Identifier(table_name))

cursor.execute(query)

I get the following error:

psycopg2.errors.UndefinedTable: relation “d1dbforest.interface.v_monitoring_ind34a4” does not exist LINE 1: select “country”,”year”,”data”,”use” from “d1dbforest.interf…

I noticed that in pgAdmin4 I can run

select "country","year","data","use" from d1dbforest.interface.v_monitoring_ind34a4

but not

select "country","year","data","use" from "d1dbforest.interface.v_monitoring_ind34a4"

So, it looks like I cannot use a string table name in the query. Maybe it is also worth to note that I am connecting to a view, not a table. Can anyone suggest a way to inject a table name as a variable to a query?

Thanks.

Answer

when you quote something,it is considered as one object while “d1dbforest.interface.v_monitoring_ind34a4” is referring to 3 objects , database name , schema name and view name :

so you need to quote them separately: "d1dbforest"."interface"."v_monitoring_ind34a4"

so:

select "country","year","data","use" from "d1dbforest"."interface"."v_monitoring_ind34a4"

then you have to specify it in the identifier :

query = sql.SQL("select {fields} from {table}").format(fields=sql.SQL(',').join([
    sql.Identifier(country_column_id),
    sql.Identifier(time_column_id),
    sql.Identifier(value_column_id),
    sql.Identifier(type_column_id),
]),
table=sql.Identifier(db_name,schema_name,table_name))

reference: https://www.psycopg.org/docs/sql.html#module-psycopg2.sql