How do I format parameters for PyGreSQL?

I am using PyGreSQL to execute queries against my DB. I need to insert the values to be matched at runtime, but the parameters are not being into the query correctly. If my query is:

SELECT *
FROM Database
WHERE job_level = ANY(:job_level)
AND job_family = ANY(:job_family)
AND cost_center = ANY(:cost_center)

And I wish to use parameters

{'cost_center': '*',
 'job_family': 'SDE',
 'job_level': ['5', '6', '4', '7'],
}

How do I correctly insert these values via

cursor.execute(query, parameters)

I have tried: Not using the ANY operator, but just using IN. This fails with

ERROR:  syntax error at or near ":"
LINE 4:                 WHERE job_level IN :job_level

Using %(job_level)s syntax instead of the :job_level. This fails with

ProgrammingError: ERROR:  syntax error at or near "ANY"
LINE 4:                 WHERE job_level IN ANY(ARRAY['5','6','4','7'...

So how do I properly combine the placeholders in the query with the parameters to match all three cases here, a single value, a list of values, and a wildcard matching an entire column?

Solution

Through much trial and error, I have found the correct combination of placeholders and parameter formats to match all my use cases: single values, wildcards for an entire column, and lists of individual values. This may not be the only solution, but it works.

Placeholders in the query should be formatted using %()s syntax, inside the ANY operator:

query = '''
SELECT *
FROM Database
WHERE job_level = ANY(%(job_level)s)
AND job_family = ANY(%(job_family)s)
AND cost_center = ANY(%(cost_center)s)
'''

Single and multi-value parameters need to be formatted as lists (for single values, that's probably a side effect of using ANY). For wildcard matches, you can use the name of the column itself, just as with the IN operator, but this column name must be wrapped in the pgdb.Literal() function. Thus, to match my example selection criteria:

{'cost_center': '*', # All cost centers (wildcard)
 'job_family': 'SDE', # Only this single job family
 'job_level': ['5', '6', '4', '7'], # Any of these multiple job levels
}

The complete parameter dictionary for my example should be formatted as:

parameters = {
'cost_center': [pgdb.Literal('cost_center')],
'job_family': ['SDE'],
'job_level': ['5','6','4','7']
}

Which can be safely executed with cursor.execute(query, parameters)

This will allow matching of single values, multiple values, and wildcard matches.