Tuesday, 28 September 2021

How do I add a multi-table case statement to a @hybrid_property expression?

I'm trying to create a @hybrid_property in a 'parent' table with a case statement that outputs a datetime value from a 'child' table if one exists. If it doesn't then it should output the datetime value from the original table. As the relationship between the two tables will be one-to-one then I'm following the guide here.

I've put together this MRE:

from sqlalchemy import Integer, Column, DateTime, case, create_engine, ForeignKey
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.ext.hybrid import hybrid_property
import datetime as dt

Base = declarative_base()

class TblParent(Base):

    __tablename__ = "tbl_parent"
    __table_args__ = {"schema": "test"}

    id_ = Column(Integer, primary_key=True)
    date_time = Column(DateTime)

    tbl_child_rel = relationship("TblChild", back_populates="tbl_parent_rel", uselist=False)

    @hybrid_property
    def date_time_hybrid(self):
        return

    @date_time_hybrid.expression
    def date_time_hybrid(cls):
        return case(
            [
                (cls.tbl_child_rel.date_time.__ne__(None), cls.tbl_child_rel.date_time),
            ],
            else_=cls.date_time,
        )


class TblChild(Base):

    __tablename__ = "tbl_child"
    __table_args__ = {"schema": "test"}

    id_ = Column(Integer, primary_key=True)
    date_time = Column(DateTime)
    tbl_parent_id = Column(Integer, ForeignKey("test.tbl_parent.id_"), unique=True)

    tbl_parent_rel = relationship("TblParent", back_populates="tbl_child_rel")


engine = create_engine(cn.CONN_STRING)
Session = sessionmaker(bind=engine)
session = Session()
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
session.add(TblParent(id_=1, date_time=dt.datetime(2000, 1, 1)))
session.add(TblChild(id_=1, date_time=dt.datetime(2000, 1, 1), tbl_parent_id=1))
session.commit()
qry = session.query(TblParent.date_time_hybrid)

When I run it I get the error:

AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with TblParent.tbl_child has an attribute 'date_time'

Not sure where I'm going wrong? Thanks in advance!

I'm using python 3.9.6 and sqlalchemy 1.4.23.



from How do I add a multi-table case statement to a @hybrid_property expression?

No comments:

Post a Comment