Skip to main content
 首页 » 编程设计

python之SQLAlchemy 混合表达式访问不同的行

2025年05月04日36zhoujg

我有以下数据模型:

class TS(Base):  # Parent 
    __tablename__ = 'ts' 
    id = Column(Integer, primary_key=True) 
    inspection_data = relationship('ID', backref='ts', uselist=False) 

和

class ID(Base):  # Child 
    __tablename__ = 'ins'  
    id = Column(Integer, primary_key=True) 
    created = Column(Date, default=datetime.datetime.utcnow(), nullable=False) 
    next_inspection = Column(Date, unique=False, nullable=True) 
    ts_id = Column(Integer, ForeignKey('ts.id')) 

现在我想创建一个混合属性“status”,告诉我 next_inspection 是否过期(即晚于今天)

到目前为止我解决了这个问题:

    @hybrid_property 
    def status(self): 
        now = datetime.datetime.utcnow().date() 
        delta = self.next_inspection - now 
        if delta.days > 30: 
            return 3  # OK, next inspection far away 
        elif delta.days > 0: 
            return 2  # Next inspection getting close 
        else:  # We are overdue 
            return 1 

这在类和实例级别都有效,这意味着我什至可以使用状态进行查询。为了工作,我必须添加:

    @status.comparator 
    def status(cls): 
        return DateTimeComparator(cls.next_inspection) 

与

class DateTimeComparator(Comparator): 
    def __gt__(self, other): 
        return self.__clause_element__() > other 
    # same for __lt__, __eq__ etc 

现在回答我的问题: 我不想像上面的示例那样返回状态值 1、2 或 3,而是想返回存储在另一个表中的三个值之一,模型如下:

class IDStatusValues(Base): 
    __tablename__ = 'ins_status_values' 
   id = Column(Integer, primary_key=True) 
   value = Column(Unicode, unique=True, nullable=True, info={}) 
   ID_id = Column(Integer, ForeignKey('ins.id')) 

具有以下数据:

id | value 
----------- 
0  | 'OK' 
1  | 'APPROACHING' 
2  | 'OVERDUE' 

也就是说,我想将状态方法更改为如下内容:

   @hybrid_property 
    def status(self): 
        now = datetime.datetime.utcnow().date() 
        delta = self.next_inspection - now 
        if delta.days > 30: 
            return IDStatusValues.get(0)  # OK, next inspection far away 
        elif delta.days > 0: 
            return IDStatusValues.get(1)  # Next inspection getting close 
        else:  # We are overdue 
            return IDStatusValues.get(2) 

但我不明白如何在这个函数中进行查询。我承认直到现在我只从事声明式工作,在真正的 SQL/表达性 sqlalchemy 方面不是很有经验。

请您参考如下方法:

class TS(Base):  # Parent 
    __tablename__ = 'ts' 
    id = Column(Integer, primary_key=True) 
    inspection_data = relationship('ID', backref='ts', uselist=False)` 
 
class ID(Base):  # Child 
    __tablename__ = 'ins' 
    id = Column(Integer, primary_key=True) 
    created = Column(Date, default=datetime.datetime.utcnow(), nullable=False) 
    next_inspection = Column(Date, unique=False, nullable=True) 
    ts_id = Column(Integer, ForeignKey('ts.id')) 
    IDStatusValues = relationship('IDStatusValues') **# Add this relationship** 
 
    @hybrid_property 
    def status(self): 
        now = datetime.datetime.utcnow().date() 
        delta = self.next_inspection - now 
        if delta.days > 30: 
            return self.IDStatusValues.property.table.c.id == 0  # OK, next inspection far away 
        elif delta.days > 0: 
            return self.IDStatusValues.property.table.c.id == 1  # Next inspection getting close 
        else:  # We are overdue 
            return self.IDStatusValues.property.table.c.id == 2` 
 
class IDStatusValues(Base): 
    __tablename__ = 'ins_status_values' 
    id = Column(Integer, primary_key=True) 
    value = Column(Unicode, unique=True, nullable=True, info={}) 
    ID_id = Column(Integer, ForeignKey('ins.id'))` 
 
session.query(ID).filter(ID.status).all()