SQLAlchemy, eager load postgresql INET/CIDR relationships

postgresql, python, sqlalchemy

Solution

the missing piece here is that the custom operator isn't working within the relationship framework. To assist in this case I've added a new feature for SQLAlchemy 0.9.2 which is the "is_comparison" flag, and an example is here, at Using custom operators in join conditions.

Here is a version that accomplishes this same result using less public APIs, which will work in 0.8 also:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.dialects.postgresql import INET, CIDR

Base = declarative_base()

# workaround before 0.9.2
from sqlalchemy.sql import operators
is_contained_by = operators.custom_op("<<")
operators._comparison.add(is_contained_by)

class IPA(Base):
    __tablename__ = 'ip_address'

    id = Column(Integer, primary_key=True)
    v4address = Column(INET)

    network = relationship("Network",
                        primaryjoin=lambda: is_contained_by(
                                     IPA.v4address, 
                                     (foreign(Network.v4representation))
                                    ),
                        viewonly=True
                    )
class Network(Base):
    __tablename__ = 'network'

    id = Column(Integer, primary_key=True)
    v4representation = Column(CIDR)

print Session().query(IPA).join(IPA.network)

In 0.9.2 and greater, it can be done as:

class IPA(Base):
    __tablename__ = 'ip_address'

    id = Column(Integer, primary_key=True)
    v4address = Column(INET)

    network = relationship("Network",
                        primaryjoin="IPA.v4address.op('<<', is_comparison=True)"
                            "(foreign(Network.v4representation))",
                        viewonly=True
                    )

Problem

I have two tables in postgresql, one named `ip_address`, and another named `network`. The `ip_address` table has two columns: - `id` is an `INTEGER` - `v4address` is an `INET` The `network` table has two columns: - `id` is an `INTEGER` - `v4representation` is a `CIDR` I want to be able to select an `ip_address` and eagerly load the `ip_addresses` `network`(s) without having to define an `id` based foreign key relationship between the tables. The `id` columns are used by other, unrelated relationships to other tables. The SQL to accomplish this is: ``` select * from ip_address join network on ip_address.v4address << network.v4representation; ``` In postgresql, the `<<` operator can be used to compare an `INET` and a `CIDR`. It will match rows where the `INET` is contained within the `CIDR`. I can define a `property` on my `IPAddress` model which will accomplish this: ``` @property def networks(self): query = session.query(Network) query = query.filter("v4representation >> :v4addr").params(v4addr=self.v4address) return query.all() ``` This works, but then when I'm actually attempting to use this `property` in an application, I experience the typical "N + 1" queries problem. I'd like to define this in such a way as to be able to eager load an ip addresses networks. I've tried to define it as a `relationship` using `primaryjoin`, but can't figure out what's needed. I've tried this: ``` networks = db.relationship("Network", primaryjoin='IPAddress.v4address << Network.v4representation', viewonly=True) ``` But sqlalchemy doesn't know what to do with the `<<` operator, so I've switched to this: ``` networks = db.relationship("Network", primaryjoin='IPAddress.v4address.op("<<")(Network.v4representation)', viewonly=True) ``` But sqlalchemy throws an `ArgumentError`: ``` ArgumentError: Could not locate any relevant foreign key columns for primary join condition 'public.ip_address.v4address << public.network.v4representation' on relationship IPAddress.networks. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or are annotated in the join condition with the foreign() annotation. ``` I've tried several combinations of defining a `foreign_key` for the `relationship`: ``` networks = db.relationship("Network", primaryjoin='IPAddress.v4address.op("<<")(Network.v4representation)', foreign_keys='[Network.v4representation]', viewonly=True) ``` But sqlalchemy throws `ArgumentErrors`: ArgumentError: Relationship IPAddress.networks could not determine any unambiguous local/remote column pairs based on join condition and remote_side arguments. Consider using the remote() annotation to accurately mark those elements of the join condition that are on the remote side of the relationship. Neither specifying `IPAddress.v4address` or `Network.v4representation` as a `remote_side` changes the exception. No attempts at annotating the `primaryjoin` condition with `foreign`/`remote` has helped either. Coming back to my original intent, I want to be able to perform a query which will return ip addresses and eager load their networks (and possibly data from the networks other relations, as this is a simplification of my full schema). Does anyone have any suggestions? Thanks in advance.

Original source