SQLAlchemy从表中获取值（如果在另一个上不存在）

Question

我正努力做到以下几点：

class Property(Base):
  name = Column(String(50))
  default_value = Column(String(50))

class AssociatedProperty(Base):
  property_id = Column(Integer, ForeignKey('properties.id'), primary_key=True)
  collector_id = Column(Integer, ForeignKey('collectors.id'), primary_key=True)
  property = relationship("Property", backref="ass_prop")
  value = Column(String(50))

class Collector(Base):
  properties = relationship("AssociatedProperty",
                          cascade="all, delete, delete-orphan",
                          lazy="dynamic",
                          backref="owner")

  def get_properties(self, list_of_names):
     """
     When called from a CollectorA instance i need to get all CollectorA name/value pairs.
     When called from a CollectorB instance i need to get all CollectorB name/Value pairs and when a property name is not present i search in collectorA properties.
     If not present in CollectorA's properties i return the property's default value.
     """

class CollectorA(Collector):
  collectors_b = relationship("CollectorB", backref='owner', foreign_keys="[CollectorB.collector_a_id]")


class CollectorB(Collector):
   collector_a_id = Column(Integer, ForeignKey('collectors_a.id'))
    pass

我试图用最少的查询数来实现这个行为，但我不确定如何以一种优雅的方式实现这一点。 谢谢你的帮助。在

Answer 1

在SQL中，可以让外部联接表

例如

select tableA.value 
from tableA
left join tableB on tableA.value = tableB.value
where tableB.value is null