How to write this in SQL Alchemy

Question

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!

Answer 1

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.

Answer 2

With SqlAlchemy 0.8, this can be written as:

mytable.c.myarraycolumn.contains(['something'])

Or, with a declrative class:

query.filter(MyTable.myarraycolumn.contains(['something']))

http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#sqlalchemy.dialects.postgresql.ARRAY