CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
exhuma
exhuma

Reputation: 21737

How do I use the "ANY" filter in conjucntion with the "<<=" operator in SQLAlchemy?

I have a table with INET columns and want to run a query filtering all entries which are in any of a set of given networks. For example:

CREATE TABLE foo (
   cidr INET
);

In SQL I can write the following:

SELECT cidr FROM foo WHERE cidr <<= ANY '{"192.168.1.0/24", "192.168.3.0/24"}';

I found the "any" operator in SQLAlchemy which works well for standard operators, but cannot get it to work with the <<= operator. How do I do that?

So I have something like this:

from sqlalchemy.dialects.postgresql import Any

query = session.query(Foo)
query = query.filter(Any(
    Foo.cidr,
    [ip_network('192.168.1.0/24'), ip_network('192.168.3.0/24')],
    operator=?  # <-- What do I need to put here?
))

Additionally, the model defines a custom type to adapt the "ip_network" type to the appropriate DB type. This change prevents me from using a text construct with bound params, so currently I have to resort to string concatenations.

Upvotes: 2

Views: 4286

Answers (1)

univerio
univerio

Reputation: 20538

To solve your immediate problem, you can use custom_op:

session.query(Foo).filter(Any(
    Foo.cidr,
    array([cast('192.168.1.0/24', INET), cast('192.168.3.0/24', INET)]),
    operator=custom_op("<<="),
))

But dialects.postgresql.Any is deprecated in favor of sql.expression.any_, which is more natural to use:

arr = array([cast('192.168.1.0/24', INET), cast('192.168.3.0/24', INET)])
session.query(Foo).filter(Foo.cidr.op("<<=")(any_(arr)))

This renders SQL like this:

SELECT ...
FROM foo 
WHERE foo.cidr <<= ANY (ARRAY[CAST(%(param_1)s AS INET), CAST(%(param_2)s AS INET)])

Upvotes: 2

Related Questions