Using ILIKE with parameters in Python with pd.read_sql and psycopg2

Question

This query runs without problem in SQL (PostgreSQL database):

SELECT 

number_column
                
FROM 

number_table

WHERE 

number_column ILIKE ANY (array[     '%57244048699%',
                                    '%150310003808%',
                                    '%290030480768%',
                                    '%290070002130%',
                                    '%290100059345%',]

I am trying to translate this into Python as follows:

numbers = ['%57244048699%',
           '%150310003808%',
           '%290030480768%',
           '%290070002130%',
           '%290100059345%',]

sql_query = """

          SELECT 

number_column
                
FROM 

number_table

WHERE 

number_column ILIKE ANY (array[%(numbers)s]

            """
df = pd.read_sql(sql_query, engine, params = {'numbers':tuple(numbers)})

However, I am getting the following error thrown:

(psycopg2.errors.UndefinedFunction) operator does not exist: character varying ~~* record
LINE 19:                 where number ilike any (array[('%...
                                                 ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

Does anyone know how to achieve the above to make the query run in Python?

Thanks!

Answer 1

Per the psycopg2 docs List adaption a list is adapted to an array. So you are over complicating things.

Try :

number_column ILIKE ANY (%(numbers)s)

and the argument:

{'numbers': numbers}