sqlalchemy具有复杂匹配逻辑的外键。

Question

我正在尝试用sqlalchemy建立一个数据库

我有两个表：flow和krbr

from __future__ import print_function
import numpy as np
import pandas as pd
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
import sqlalchemy_utils
from sqlalchemy_utils.types.ip_address import IPAddressType


Base = declarative_base()

## Define the tables schema

class Flow(Base):
    __tablename__ = 'flow'

    Id  = Column(Integer, primary_key=True)
    First = Column(Integer, index=True)
    Protocol  = Column(String(10))
    Src = Column(IPAddressType, index=True)
    SrcPort = Column(Integer)
    Dst = Column(IPAddressType, index=True)
    DstPort = Column(Integer)
    GroupId = Column(Integer)
    Port = Column(String(10))
    VPort = Column(Integer)
    IpTos = Column(String)
    VlanId = Column(String)
    VlanPri = Column(String)
    Application = Column(String(100))
    Packets = Column(Integer)
    Messages = Column(Integer)
    Bytes = Column(Integer)
    Last = Column(Integer)
    #LearnedIPs alertable 
    #LearnedIPs learned-ip
    #   u'LearnedIPs new-ips', u'LearnedIPs subnet-name',
    #   u'LearnedIPs timestamp-sec', u'LearnedIPs total-ips', u'SrcSubnet',
    #   u'DstSubnet'],
    # 'MPLS Exp'


class Krbr(Base):
    __tablename__ = 'krbr'
    Id  = Column(Integer, primary_key=True)
    Src = Column(IPAddressType, index=True)
    SrcPort = Column(Integer)
    Dst = Column(IPAddressType, index=True)
    DstPort = Column(Integer)
    TimeNs = Column(Integer)

与flow中的某些行相关联的是krbr的一行或多行

krbr中的行与flow中的行关联，如果：

1）它们具有相同的值Src, Dst, SrcPort, DstPort

2）时间接近。i、 例如np.abs(Flow.first - Krbr.TimeNs/1000000000) < threshold

我想知道在两个表之间创建链接的正确方法是什么。i、 给定一个表的一行，我希望能够得到另一个表的行

我对炼金术知之甚少。我想我应该定义一个外键，但我不知道如何执行这样一个复杂的关系

Answer 1

下面是“处理多个连接路径”的示例：

from sqlalchemy import Integer, ForeignKey, String, Column
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

Base = declarative_base()

class Customer(Base):
    __tablename__ = 'customer'
    id = Column(Integer, primary_key=True)
    name = Column(String)

    billing_address_id = Column(Integer, ForeignKey("address.id"))
    shipping_address_id = Column(Integer, ForeignKey("address.id"))

    billing_address = relationship("Address")
    shipping_address = relationship("Address")

class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    street = Column(String)
    city = Column(String)
    state = Column(String)
    zip = Column(String)

有关“处理多个连接路径”的详细信息，请访问 SQLAlchemy 0.9 Documentation