As per psycopg2 documentation the execute function takes variables as an extra parameter.

cursor.execute("""select * from table where type = %(value)s """, {"value": variable_value})

More examples in psycopg2 user manual..

Also please read carefully the section about SQL injection - the gist is, you should not quote parameters in your query, the execute function will take care of that to prevent the injection of harmful SQL.

Also to explain the error you are getting - the query you're sending is comparing two identifiers (type and variable_value). The table does not contain variable_value column, hence the error.

I believe, you intended to use string interpolation to construct the query, but you forgot the {}. It would work like this:

cursor.execute(f"""select * from table where type = '{variable_value}'""")

⚠️ but because of previously mentioned SQL injection, it is not a recommended way!.

For the IN operator, you want a tuple instead of list, and remove parentheses from the SQL string.

# using psycopg2
data=('UK','France')

sql='SELECT * from countries WHERE country IN %s'
cur.execute(sql,(data,))

During debugging you can check that the SQL is built correctly with

cur.mogrify(sql, (data,))

You can use the Postgres function COALESCE()

query = """
SELECT * 
FROM my_table 
WHERE column = %(value)s 
AND otherColumn = COALESCE(%(otherValue)s, '')

"""

When otherValue is None then will be converted to Postgres NULL and COALESCE() return an empty string.

Check out the psycopg2 documentation on Lists adaptation

You are getting that error because psycopg2 is trying to substitute the two parameters, but you only gave it one parameter. Try changing to this:

from app.commons.database import conn

from psycopg2 import sql
from psycopg2.extras import DictCursor

query = '''
    SELECT
        *
    FROM
        {}.{}
    WHERE
        {} =ANY(%s)
    '''.format(
        sql.Identifier('information_schema'),
        sql.Identifier('tables'),
        sql.Identifier('table_schema')
    )

data = [
    'information_schema',
    'pg_catalog'
] # A list now, instead of a tuple

with conn.cursor(cursor_factory=DictCursor) as cursor:
    cursor.execute(query, (data, )) # A tuple, containing your list
    print(cursor.fetchall())