Using ILIKE with parameters in Python with pd.read

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

SELECT 

number_column
                
FROM 

number_table

WHERE 

number_column ILIKE ANY (array[     'W244048699%',
                                    '0310003808%',
                                    ')0030480768%',
                                    ')0070002130%',
                                    ')0100059345%',]

I am trying to translate this into Python as follows:


numbers = ['W244048699%',
           '0310003808%',
           ')0030480768%',
           ')0070002130%',
           ')0100059345%',]

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!

CodePudding user response：

Use this:

import psycopg2

#establishing the connection
conn = psycopg2.connect(
   database="mydb", user='postgres', password='password', host='127.0.0.1', port= '5432'
)

#Setting auto commit false
conn.autocommit = True

#Creating a cursor object using the cursor() method
cursor = conn.cursor()
numbers = ['W244048699%',
               '0310003808%',
               ')0030480768%',
               ')0070002130%',
               ')0100059345%',]
number_tuple=tuple(numbers)
sql_query = "SELECT number_column           
FROM 
number_table
WHERE 
number_column ILIKE ANY {}".format(number_tuple)
    
cursor.execute(sql_query )

CodePudding user response：

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}