pg8000 Can't Parameterize Queries

Question

I've been trying to use pg8000 to interact with my SQL server, but for some reason, it won't accept params properly. It does seem to recognize the paramstyle, and then knows to change those to the PostgreSQL params, but it never seems to pass the params through. I looked at the documentation example here (link) for examples, and they didn't work. Here are some examples:

query = """
        SELECT *
        FROM schema.table_name
        """
conn = pg8000.connect(**credential_dict)
cursor = conn.cursor()
cursor.execute(query)

This example works. The next two do not:

query = """
        SELECT *
        FROM %s
        """
conn = pg8000.connect(**credential_dict)
cursor = conn.cursor()
cursor.execute(query, 'schema.table_name')

query = """
        SELECT *
        FROM %s
        """
conn = pg8000.connect(**credential_dict)
cursor = conn.cursor()
cursor.execute(query, ('schema.table_name',))

These queries fail, but the error I get says the error is at the $1 which pg8000 converts the %s to.

What am I doing wrong?

Answer 1

Try explicitly naming your parameter and passing as a dict:

param_dict = dict(table_name = 'schema.table_name')
query = """SELECT * FROM %(table_name)s"""
# cursor setup code...
cursor.execute(query, param_dict)

Answer 2

With PostgreSQL, some parts of SQL statements can't be parameterized. In your example:

cursor.execute("SELECT * FROM %s", 'schema.table_name')

the table name can't be parameterized, so you'd have to create the string yourself instead:

table_name = 'schema.table_name'
cursor.execute(f"SELECT * FROM {table_name}")