SQLAlchemy NOT IN clause with parameter

Question

When using Python with a connection to a PostgreSQL database, we use SQLAlchemy, but not ORM. We have a table in which one of the columns is an array of rows, and we want to get all rows for which the input parameter is NOT present in the column of the array. Please note that we can get the results of other queries for SQLAlchemy, so the problem should be creating a query.

The SQL we need to implement is as follows:

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

The function we created in Python is as follows:

 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 are welcome.

Answer 1

Your problem is that you are using the in operator back. It is used to search for strings that have an entry that is in the list, and not to search for strings that contain a list containing a string.

In other words, what you have is generated:

 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 items in a Postgres array column.

They suggest using a raw filter to accomplish what you want:

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