First of all it's \d <table_name> (note the backslash \d not /d), but that's only available in the psql interactive terminal

My [objective] with this program is to get the table structure

You can use the SQL table information_schema.columns which carries the information you want

SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = '<table_name>';

    column_name     |     data_type     | is_nullable 
--------------------+-------------------+-------------
 column_a           | integer           | YES
 column_b           | boolean           | NO

Here's a list of the columns available for use: https://www.postgresql.org/docs/9.4/static/infoschema-columns.html

Snippet

import psycopg2
conn = psycopg2.connect(database='carto', user=..., password=...)

q = """                              
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = %s;
"""

cur = conn.cursor()
cur.execute(q, ('BADGES_SFR',))  # (table_name,) passed as tuple
cur.fetchall()

# Example Output 
[('column_a', 'integer', 'YES'),
 ('column_b', 'boolean', 'NO'),
 ...,
]

Tip for Jinja2/Flask integration:

Consider using psycopg2.extras.DictCursor, it'll make it easier for you to pull the information based on the column name (dict key) in your Flask template, because your'll a have a dict that you can access e.g. using row['data_type'], row['column_name'] etc.

This did the trick for me:

cursor.execute("""SELECT table_name FROM information_schema.tables
       WHERE table_schema = 'public'""")
for table in cursor.fetchall():
    print(table)

When we were working on ThreadConnectionPool which is in psycopg2 and creating connection pool, this is how we did it.

from psycopg2.pool import ThreadedConnectionPool

db_conn = ThreadedConnectionPool(
    minconn=1, maxconn=5,
    user="postgres", password="password", database="dbname", host="localhost", port=5432,
    options="-c search_path=dbo,public"
)

You see that options key there in params. That's how we did it.

When you execute a query using the cursor from that connection, it will search across those schemas mentioned in options i.e., dbo,public in sequence from left to right.

You may try something like this:

psycopg2.connect(host="localhost", 
                 port="5432", 
                 user="postgres", 
                 password="password", 
                 database="database", 
                 options="-c search_path=dbo,public")

Hope this might help you.

you want to join it against pg_namespace:

t=# select nspname||'.'||relname from pg_class join pg_namespace on relnamespace = pg_namespace.oid where relkind='r' and relname !~ '^(pg_|sql_)' limit 5;
     ?column?
-------------------
 public.s141
 public.events
 public.tg_rep_que
 public.t4
 public.menupoint
(5 rows)

The schema name is in uppercase, you should put it in double quotes:

cursor.execute("""select * from "DB_FZ".d_results;""")

See also Are PostgreSQL column names case-sensitive?

The construction

sql.Identifier('myschema.mytable')

is treated as a single quoted identifier, as can be seen from the produced query. Please see the answer by @gg for how to handle qualified identifiers in Psycopg since version 2.8.

(For older versions) You should pass the schema and table name as separate identifiers to format:

cursor.execute(sql.SQL("SELECT * FROM {}.{}").format(
    sql.Identifier('myschema'),
    sql.Identifier('mytable'))

Note that the schema and table name must match exactly, case and all, since psycopg2's SQL string composition tools produce quoted identifiers, and quoted identifiers are case-sensitive.

Please try the below code,

  Query = """select * from dl."products";"""
   cursor.execute(Query)
   print("Selecting rows from test table using cursor.fetchall")
   row = cursor.fetchone()
   while row is not None:
        print(row)
        row = cursor.fetchone()
   print("Print each row and it's columns values")