psycopg2 table name as a parameter fails with double quotes

Question

I use this python function to get column names from different tables in postGres.

def get_table_columns(conn, table):
"""
Retrieve a list of column names in a table
:param conn:
:param table:
:return: List of column names
"""
try:
    query = sql.SQL('SELECT * FROM {} LIMIT 0').format(sql.Identifier(table))
    print(query.as_string(conn))
    with conn as c:
        with c.cursor() as cur:
            cur.execute(query)
            return [desc[0] for desc in cur.description]

except psycopg2.DatabaseError as de:
    logger.error("DatabaseError in get_table_columns: {0}".format(str(de)))
    raise de
except Exception as ex:
    logger.error("Exception in get_table_columns: {0}".format(str(ex)))
    raise ex

I'm receiving error, "relation "v43fs.evt_event_cycle" does not exist.

The print statement looks like this: SELECT * FROM "v43fs.evt_event_cycle" LIMIT 0

The double quotes are causing the query to fail. How can I make them go away?

score 3 · Accepted Answer · edited Jun 08 '20 at 22:21

3

I changed the query to be formatted as follows and it works much better now:

query = sql.SQL('SELECT * FROM {}.{} LIMIT 0').format(sql.Identifier(schema), sql.Identifier(table))

Thanks!

edited Jun 08 '20 at 22:21

Maurice Meyer

17,279
4
30
47

answered Jun 08 '20 at 22:19

wasabi

31
6

1

Thank you so much. I wonder if this should be made more explicit in the [documentation](https://www.psycopg.org/docs/sql.html#module-psycopg2.sql). – wireman Jul 28 '20 at 15:43

psycopg2 table name as a parameter fails with double quotes

1 Answers1

Linked