CODEWITHSUNDEEP
sqlalchemy
Sergio Ayestarán
Sergio Ayestarán

Reputation: 5910

How to write this in SQL Alchemy

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!

Upvotes: 3

Views: 1808

Answers (3)

glyphobet
glyphobet

Reputation: 1562

Here is how we got this to work, in SQLAlchemy 0.9.

SQLAlchemy doesn't know how to convert a Python type into the array element type in these queries. Since our array elements were VARCHAR(256) instead of TEXT, we had to add a cast expression inside the array literal. 

session.query.filter(
    models.TableClass.arraycolumn.contains(   
        # contains() generates @>, and @> always takes an array, 
        # it's more like has-subset
        array([
            # the array() literal constructor needs an iterable
            cast(
                'array-element-to-find', 
                # SQLAlchemy does not know how to convert a Python string 
                # to an SQL VARCHAR type here
                String(256),
            )
        ])
    )
).all()

Upvotes: 2

Taha Jahangir
Taha Jahangir

Reputation: 4902

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

Upvotes: 3

zzzeek
zzzeek

Reputation: 75167

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.

Upvotes: 4

Related Questions