python - 如何使用 sqlalchemy hybrid_property 查询多个字段？

Question

我的模型定义如下：

class Patch(Base):
    id = Column(Integer, primary_key=True)
    major = Column(Integer, nullable=False)
    minor = Column(Integer, nullable=False)

    @hybrid_property
    def patch(self) -> str:
        return f'{self.major}.{self.minor}'

    @patch.expression
    def patch(self):
        return func.concat(self.major, '.', self.minor)

我想提出这样的请求：

Patch.query.order_by(Patch.patch)

这应该等于下一个 SQL 命令：

SELECT * FROM patch ORDER BY major DESC, minor DESC

我试着用hybrid_property.expression

    @patch.expression
    def patch(self):
        return self.major, self.minor

但有例外：

sqlalchemy.exc.InvalidRequestError: SQL expression, column, or mapped entity expected - got '(<sqlalchemy.orm.attributes.InstrumentedAttribute object at 0x7f1a5cd5fc50>, <sqlalchemy.orm.attributes.InstrumentedAttribute object at 0x7f1a5cd5fd00>)

我知道它可以使用custrom 比较器，但我还没有找到如何使用两个字段（即major，minor）来做到这一点。

有任何想法吗？

PS不一定是这样hybrid_property，任何一种方式都可以

标签： pythonsqlalchemy

Answer 1

以下适用于两个单独字段的排序。值得注意的是，python 的实现patch仍然是一个字符串，因此 Python 对象和 SQL 行之间的排序会有所不同，除非您更改patch为返回(major, minor). 使用 Python 3.6.5、SQLAlchemy 1.1.15、psycopg2 2.7.7 测试

from sqlalchemy import create_engine, Column, Integer, func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql.functions import concat

engine = create_engine('postgresql://localhost:5432/postgres', echo=True)
Base = declarative_base(bind=engine)


class Patch(Base):

    __tablename__ = "patch"

    id = Column(Integer, primary_key=True)
    major = Column(Integer, nullable=False)
    minor = Column(Integer, nullable=False)

    @hybrid_property
    def patch(self) -> str:
        return f'{self.major}.{self.minor}'

    @patch.expression
    def patch(self):
        return self.major.op(",")(self.minor)

    def __repr__(self):
        return f"Patch object {self.id} {self.patch}"


Base.metadata.create_all()
Session = sessionmaker(bind=engine)
session = Session()

if __name__ == "__main__":
    if session.query(Patch).count() == 0:
        example_patches = [Patch(major=major, minor=minor) for major in range(3) for minor in range(3)]
        session.add_all(example_patches)
        session.commit()

    patches = session.query(Patch).order_by(Patch.patch).all()
    print(patches)

2019-06-13 14:44:27,245 INFO sqlalchemy.engine.base.Engine SELECT patch.id AS patch_id, patch.major AS patch_major, patch.minor AS patch_minor 
  FROM patch ORDER BY patch.major , patch.minor
2019-06-13 14:44:27,245 INFO sqlalchemy.engine.base.Engine {}
[Patch object 2740 0.0, Patch object 2741 0.1, Patch object 2742 0.2, Patch object 2743 0.3, Patch object 2744 0.4, Patch object 2745 0.5, Patch object 2746 0.6, Patch object 2747 0.7, Patch object 2748 0.8, Patch object 2749 0.9, Patch object 2750 0.10, ...