CODEWITHSUNDEEP
python-2.7sqlalchemy
AZhao
AZhao

Reputation: 14425

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

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.

Upvotes: 2

Views: 2263

Answers (1)

jumbopap
jumbopap

Reputation: 4137

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>

Upvotes: 1

Related Questions