SQLAlchemy NOT IN clause with a parameter

Question

In using Python with a connection to a PostgreSQL database, we are using SQLAlchemy, but not the ORM. We have a table where one of the columns is an array of strings, and we want to retrieve all the rows for which an input parameter is NOT present in the array column. Please do note that we can retrieve the results of other queries for SQLAlchemy, so the problem has to be in the query creation.

The SQL we need to implement looks like this:

select pk from table where 'paramstring' NOT IN(array_column);
-- returns several rows

The function that we have come up with in Python is like this:

def get_not_matching(param):

    select_statement = select([the_table.c.pk]).where(
        ~data_table.c.array_column.in_([param])
    )

    #... execute and retrieve ResultProxy
    # the result_set comes back empty

Any suggestions would be greatly appreciated.

Nathan Villaescusa · Accepted Answer · 2012-10-22 21:37:43Z

3

Your issue is that you are using the in statement backwards. It is used to find rows that have a filed that is in a list, not to find rows that have a list which contains a string.

In other words, what you have is generating:

select pk from table where array_column NOT IN(paramstring);

According to https://groups.google.com/forum/?fromgroups=#!topic/sqlalchemy/QAfTDrNYaFI SQLAlchemy does not yet support querying for items in a Postgres array column.

They suggest using a raw filter in order to acomplish what you want:

select([the_table.c.pk]).where(text("array_column @> ARRAY[:element_value]"))).params(element_value='paramstring')

edited Oct 22, 2012 at 21:37

answered Oct 22, 2012 at 20:37

Nathan Villaescusa

17.7k4 gold badges55 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Juan Carlos Coto Over a year ago

Thanks. I edited the question to remove unnecessary information and update the code. as you state, negating the in should work, but apparently the parameter is not being passed in properly.

Collectives™ on Stack Overflow

SQLAlchemy NOT IN clause with a parameter

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related