I have several tables in my PostgreSQL database that need to contain timeseries like information in an IoT-link context, that require several data types supported for the value of each point in the series, like this:
class Telemetry(Base): __tablename__ = "ts_kv" key = Column(Integer, primary_key=True) bool_v = Column(Boolean) str_v = Column(String) long_v = Column(BigInteger) dbl_v = Column(DOUBLE_PRECISION) json_v = Column(JSON) ts = Column(BigInteger, primary_key=True) entity_id = Column(UUID(as_uuid=True), nullable=False, primary_key=True)
I need to parse an incoming model from a REST API that has a single value field which can be any of the above types. I can parse the input data type correctly into the corresponding column with no issues.
The problem arises when I try to query the table for a given value, without directly specifying the data type, I would need to achieve something like this:
data = session.query(Telemetry.ts, Telemetry.value).filter(Telemetry.value == MY_MULTITYPE_VALUE).all()
I’ve tried using hybrid properties and hybrid methods like below:
@hybrid_property def value(self): return self.str_v if self.str_v else ( self.dbl_v if self.dbl_v else ( self.bool_v if self.bool_v else ( self.json_v if self.json_v else self.long_v ) ) ) @value.expression def value(self): return self.str_v if self.str_v else ( self.dbl_v if self.dbl_v else ( self.bool_v if self.bool_v else ( self.json_v if self.json_v else self.long_v ) ) )
The first part of the operation works correctly, as if I query:
session.query(Telemetry.value).filter(ANY FILTER).all()
I do get correct values. However, querying like in the following example fails, because the expression for value evaluates always true for the first part of the conditional, IE: if self.str_v yields always True as it evals the COLUMN as existing, not the actual value for the row in that column. I’ve also tried using the func and not_/is_null operators with the exact same result.
a = session.query(Telemetry).filter(Telemetry.value == 55).first() #Always fails because long_v is behind in the expression evaluation.
How can I achieve this behaviour in the simplest fashion? I’ve looked into composite columns but couldn’t figure out a way to properly handle the parsing/de-parsing.
Advertisement
Answer
As @rfkortekaas correctly pointed out in his comment, the expression needs to be SQL. And if/else don’t qualify.
from sqlalchemy import case @hybrid_property def value(self): return self.str_v if self.str_v is not None else ( self.long_v if self.long_v is not None else ( self.dbl_v if self.dbl_v is not None else self.bool_v ) ) @value.expression def value(cls): return case( (cls.str_v.is_not(None), cls.str_v), (cls.dbl_v.is_not(None), cls.dbl_v), (cls.long_v.is_not(None), cls.long_v), else_=cls.bool_v )