如何将多任务case语句添加到@hybrid_属性表达式中？

Question

我正在尝试在“parent”表中创建一个@hybrid_property，如果存在一个“child”表，则使用case语句从该表中输出一个datetime值。如果没有，那么它应该从原始表中输出datetime值。由于两个表之间的关系将是一对一的，因此我将遵循指南here

我把这个MRE放在一起：

from sqlalchemy import Integer, Column, DateTime, case, create_engine, ForeignKey
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
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)

当我运行它时，我得到一个错误：

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

不知道我哪里出错了？提前谢谢

我正在使用python 3.9.6和sqlalchemy 1.4.23

Answer 1

以下内容应该有效：

@hybrid_property
def date_time_hybrid(self):
    return self.tbl_child_rel and self.tbl_child_rel.date_time or self.date_time

@date_time_hybrid.expression
def date_time_hybrid(cls):
    subq = (
        select([TblChild.date_time.label("date_time")])
        .where(TblChild.tbl_parent_id == cls.id_)
        .scalar_subquery()
    )
    return func.ifnull(subq, cls.date_time).label("date_time_hybrid")

---

完整包含的工作示例如下：

import datetime as dt

from sqlalchemy import (
    Column,
    DateTime,
    ForeignKey,
    Integer,
    create_engine,
    func,
    select,
)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.orm import relationship, sessionmaker

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 self.tbl_child_rel and self.tbl_child_rel.date_time or self.date_time

    @date_time_hybrid.expression
    def date_time_hybrid(cls):
        subq = (
            select([TblChild.date_time.label("date_time")])
            .where(TblChild.tbl_parent_id == cls.id_)
            .scalar_subquery()
        )
        return func.ifnull(subq, cls.date_time).label("date_time_hybrid")


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("tbl_parent.id_"), unique=True)

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


CONN_STRING = "sqlite:///:memory:"
engine = create_engine(CONN_STRING, echo=True)
Session = sessionmaker(bind=engine)
session = Session()
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)

parents = (p_no_child_no_dt, p_no_child_dt, p_child_no_dt, p_child_dt,) = (
    TblParent(id_=1, date_time=None),
    TblParent(id_=2, date_time=dt.datetime(2000, 1, 1)),
    TblParent(id_=3, date_time=dt.datetime(2000, 1, 2), tbl_child_rel=TblChild()),
    TblParent(
        id_=4,
        date_time=dt.datetime(2000, 1, 3),
        tbl_child_rel=TblChild(date_time=dt.datetime(2000, 1, 4)),
    ),
)
session.add_all(parents)
session.commit()
# qry = session.query(TblParent.date_time_hybrid)

# in-memory
session.expunge_all()
qry = session.query(TblParent)
for p in qry.all():
    print(p.id_, p.date_time_hybrid)

print("-" * 80)

# query
session.expunge_all()
qry = session.query(TblParent.id_, TblParent.date_time_hybrid)
for p in qry:
    print(p)