SQL Query Parameters for Schema and Table names

[djbpark1]

Hi,

I'm trying to execute a command with parameterized schema and table names. For example, "SELECT * FROM %s.%s" where the parameter values could be ('some_schema_name', 'some_table_name'). With psycopg2 one can do this like:

from psycopg2 import sql
cursor.execute(
    sql.SQL("SELECT * FROM {table} LIMIT 10").format(table=sql.Identifier('my_table'))

Is there similar functionality with redshift_connector?

[Brooke-white]

Hi @djbpark1 , the project README provides examples of how parametrization can be used. Please let me know if you have further questions.

[djbpark1]

Hi Brooke, thanks for the reply. I took a look at the README, and it parameterizes values. I've tried the same thing for parameterizing schema and table names, but I run into an error.

Here's a code example (I've used my_schema and my_table as dummy values for this snippet)

result = None
with conn.cursor() as cursor:
    try:
        sql = " SELECT COUNT(*) FROM `%s`.`%s` LIMIT 10"
        cursor.execute(sql, ('my_schema', 'my_table'))
        result = cursor.fetchone()
    except Exception as e:
        print(str(e))
    try:
        sql = " SELECT COUNT(*) FROM %s.%s LIMIT 10"
        cursor.execute(sql, ('my_schema', 'my_table'))
        result = cursor.fetchone()
    except Exception as e:
        print(str(e))
    
    print(result)

This code fails with the errors:

{'S': 'ERROR', 'C': '42601', 'M': 'syntax error at or near "`"', 'P': '23', 'F': '/home/ec2-user/padb/src/pg/src/backend/parser/parser_scan.l', 'L': '732', 'R': 'yyerror'}
{'S': 'ERROR', 'C': '42601', 'M': 'syntax error at or near "$1"', 'P': '23', 'F': '/home/ec2-user/padb/src/pg/src/backend/parser/parser_scan.l', 'L': '732', 'R': 'yyerror'}

where I would expect result = (10,). Is there a way to correctly parse schema and table names then?

[Brooke-white]

Not sure if you're still hitting this issue, but I notice you're using ` tickback instead of single quote '. Please try to use single quote.

[Brooke-white]

But aside from that, Redshift server does not support using bind parameters in this manner; for the schema or table name.