sqlachemy hybrid property error - operator is not supported on this expression

Question

Following example from the docs, but cannot understand why it's not working

class CCStandard(Base):
    """Codes, grade, code description, and name"""
    __tablename__ = "DimCCStandard"

CCStandardKey = deferred(Column('CCStandardKey', Integer, primary_key=True))
CCStdCode = Column('CCStdCode', String)

#ccstd_info group
CCStdDesc = deferred(Column('CCStdDesc', String), group='ccstd_info')
CCStdName = deferred(Column('CCStdName', String), group='ccstd_info')
CCStdCluster = deferred(Column('CCStdCluster', String), group='ccstd_info')

@hybrid_property
def Cluster(self):
    return self.CCStdCode[:1]

simple query below return "Operator 'getitem' is not supported on this expression"

a=session.query(CCStandard.Cluster)

I'm sure this column is a string, so not sure why I'm getting this. If I try a using a + operator it does work, ie:

@hybrid_property
def Cluster(self):
    return self.CCStdCode + 'well this works'

Underlying database is a SQLServer. This column in question is an nvarchar.

jumbopap · Accepted Answer · 2016-01-14T23:06:26.033

SQLAlchemy doesn't support substring querying in this way. If you want to query for a column with a substring, you can do something like:

session.query(CCStandard).filter(CCStandard.CCStdCode.contains(sub_string))

If you want to create a custom expression for a substring you can follow what the docs say. You have to use the expression decorator. Here is a minimal example:

class CCStandard(Base):
    """Codes, grade, code description, and name"""
    __tablename__ = "ccstandard"
    CCStandardKey = Column(Integer, primary_key=True)
    _CCStandardCode = Column(String)

    @hybrid_property
    def CCStandardCode(self):
        return self._CCStandardCode

    @CCStandardCode.setter
    def CCStandardCode(self, code):
        self._CCStandardCode = code


    @hybrid_property
    def Cluster(self):
        return self._CCStandardCode[:1]

    @Cluster.expression
    def Cluster(cls):
        # use the SQLite `substr` method to search for a substring
        return func.substr(cls._CCStandardCode, 0, 2)

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

>>> c = CCStandard(CCStandardKey=1, CCStandardCode="foo")
>>> session.add(c)
>>> session.commit()
>>> session.query(CCStandard).filter(CCStandard.Cluster == 'f').one()
<foo.CCStandard object at 0x1047305d0>

The docs don't have an example of what you are doing, there is an `expression` decorator. I will edit. — jumbopap, Jan 14 '16 at 22:40
docs have this example: @hybrid_property def email(self): """Return the value of _email up until the last twelve characters.""" return self._email[:-12] — AZhao, Jan 14 '16 at 22:53

sqlachemy hybrid property error - operator is not supported on this expression

1 Answers1

Linked