SQLAlchemy，提前加载postgresql INET/CIDR关系

Question

我在postgresql中有两个表，一个名为ip_address，另一个名为network。 ip_address表有两列：

1. id是一个INTEGER
2. v4address是一个INET

network表有两列：

1. id是一个INTEGER
2. v4representation是一个CIDR

我希望能够选择一个ip_address并及时加载ip_addresses的network，而无需在表之间定义基于id的外键关系。id列用于与其他表的不相关关系。

实现此目的的SQL语句如下：

select * from ip_address join network on ip_address.v4address << network.v4representation;

在postgresql中，可以使用“<<”运算符来比较INET和CIDR。它将匹配INET在CIDR内的行。我可以在我的IPAddress模型上定义一个属性来实现这一点。

@property
def networks(self):
    query = session.query(Network)
    query = query.filter("v4representation >> :v4addr").params(v4addr=self.v4address)
    return query.all()

这个方法是有效的，但是当我试图在实际应用程序中使用该属性时，我会遇到典型的“N+1”查询问题。我希望能定义一个可以预加载IP地址网络的方法。

我尝试将其定义为关系，并使用primaryjoin，但无法弄清楚需要什么。我尝试了以下代码：

networks = db.relationship("Network",
                           primaryjoin='IPAddress.v4address << Network.v4representation',
                           viewonly=True)

但是sqlalchemy不知道如何处理<<运算符，所以我改用了这个：

networks = db.relationship("Network",
                           primaryjoin='IPAddress.v4address.op("<<")(Network.v4representation)',
                           viewonly=True)

但是SQLAlchemy抛出了一个“参数错误（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.

我尝试了几种为关系定义 foreign_key 的组合：

networks = db.relationship("Network",
                           primaryjoin='IPAddress.v4address.op("<<")(Network.v4representation)',
                           foreign_keys='[Network.v4representation]',
                           viewonly=True)

但是 Sqlalchemy 抛出了 ArgumentErrors 异常：

ArgumentError: Relationship IPAddress.networks could not determine any unambiguous local/remote column pairs based on join condition and remote_side arguments. 考虑使用 remote() 注释来准确地标记那些在关系的远程一侧的元素。

指定 IPAddress.v4address 或 Network.v4representation 作为 remote_side 并不会改变异常情况。

将 primaryjoin 条件注释为 foreign/remote 也没有帮助。

回到我的原始意图，我希望能够执行一个查询，返回 IP 地址并及时加载它们的网络（以及可能从网络其他关系中获取的数据，因为这是我的完整模式的简化）。

有人有什么建议吗？

提前致谢。

- mkomitee

Answer 1

这里缺少的一块是自定义运算符在关系框架中无法工作。为了解决这个问题，我已经为SQLAlchemy 0.9.2添加了一个新功能，“is_comparison”标志，并提供了一个示例，位于在联接条件中使用自定义运算符。

以下是实现相同结果但使用更少公共API的版本，它也适用于0.8：

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)

在0.9.2版本及更高版本中，可以这样做：

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
                    )

- zzzeek