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!.

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.

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,))

Consider COALESCE as Python's None translates to SQL's NULL. Below method now uses optional args that default to None:

def sql_query(filter1=None, filter2=None, filter3=None):
    with ConnectionPool() as cursor:
        sql = '''SELECT * FROM table 
                 WHERE filter1 = COALESCE(%s, filter1) 
                   AND filter2 = COALESCE(%s, filter2) 
                   AND filter3 = COALESCE(%s, filter3);
              '''
        cursor.execute(sql, (filter1, filter2, filter3))
        cursor.fetchall()

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())