CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
Juan Enrique Muñoz Zolotoochin
Juan Enrique Muñoz Zolotoochin

Reputation: 3579

Case insensitive Array any() filter in sqlalchemy

I'm migrating some code from SqlAlchemy 1.3 to SqlAlchemy 1.4 with Postgres 12. I found a query that looks like this:

session.query(Horse)
  .filter(Horse.nicknames.any("charlie", operator=ColumnOperators.ilike))

The type of the column nicknames is Column(ARRAY(String(64))).

It seems to me that what this is doing is queryng any Horse whose one of their nicknames is charlie in a case-insensitive (ilike) way.

This code seems to work fine in SqlAlchemy==1.3.0 and fails in version 1.4.40 with the following error:

sqlalchemy.exc.UnsupportedCompilationError:
Compiler <sqlalchemy.dialects.postgresql.psycopg2.PGCompiler_psycopg2 object at 0x7fce54c80f10>
can't render element of type <function ColumnOperators.ilike at 0x7fce92944280>
(Background on this error at: https://sqlalche.me/e/14/l7de)

What would be an equivalent way of doing this that works, ideally for both versions?

Upvotes: 3

Views: 611

Answers (1)

snakecharmerb
snakecharmerb

Reputation: 55600

This query seems to have broken in SQLAlchemy version 1.3.20*. In 1.3.0 it generated this SQL (aliases removed for clarity):

SELECT id, nicknames 
FROM horse
WHERE 'charlie' ILIKE ANY (nicknames)

The docs for any mention that it has been superseded by any_, though it doesn't seem to have been formally deprecated. With this knowledge we can build an alternative that generates the same SQL:

import sqlalchemy as sa
...
session.query(Horse).filter(
    sa.literal('able').ilike(sa.any_(Horse.nicknames))
)

or in 2.0-style:

sa.select(Horse).where(
    sa.literal('charlie').ilike(sa.any_(Horse.nicknames))
)

* I don't see anything in the release notes regarding this change in behaviour, so it might be a regression, but at this stage it's probably better to go with the any_ construct.

Upvotes: 2

Related Questions