How to write this in SQL Alchemy
sqlalchemy
Solution
you need to use the `and_()` and `or_()` methods, or alternatively the `&&` and `||` operators, not the Python `and` and `or` keywords.
Also, the operations you're doing with array indexing and "<@" are easier to do (in 0.8) like this:
mytable.c.array[:"acity"].op('<@')(mytable.c.city)
see ARRAY.
Problem
I need to write a query in SQL Alchemy to check some string parameters against a field that contains an array of string (Postgre) city state address_line_1 zip_code phone_numbers are all of type text[] ``` select_statement = bdb.get_select_statement(business_schema)\ .where(((text('array[:acity] <@ city') and text('array[:astate] <@ state') and text('array[:aaddress] <@ address_line_1')) or (text('array[:aaddress] <@ address_line_1') and text('array[:azip_code] <@ zip_code'))) and (text('array[:aphone] <@ phone_numbers')))\ .params(aaddress = address_line_1, acity = city, astate = state, azip_code = zip_code, aphone = phone_number) ``` The problem is that i receive an exception when I do this, "Boolean value of this clause is not defined". The plain SQL to be written is: ``` select * from business where ('address_line1' = ANY (address_line_1) and 'acity' = ANY (city) and 'state' = ANY (state) or ('adress_line1' = ANY (address_line_1) and 'zip' = ANY (zip_code)) and 'phone' = ANY (phone_numbers) ``` Any ideas on how to do it?, Thanks in advance!