[Python] Unable to filter hybrid_property on SQLAlchemy Model due to InstrumentedAttribute...

The database I'm working with has several columns that have been encrypted prior to being stored. The data gets stored as JSON, with its nonce and encrypted data:

ie,

# This is what's stored in the DB
{
"nonce": "abcdefg",
"data": "wefgsafawfasdadsfsa"
}


My SQLAlchemy User table has been set up to handle these encrypted data as a hybrid_property, where it decrypts and returns the actual plaintext value when accessed:

import json
from encryption_lib import decrypt

class User(Model):
__tablename__ == "user"

id = Column(Integer, primary_key=True)
_private1 = Column("private1", Text)
_private2 = Column("private2", Text)

@hybrid_property
def private1(self):
json_data = json.loads(self._private1)
nonce = json_data["nonce"]
data = json_data["data"]
return decrypt(nonce, data) # Decrypt the value so the application can use it when queried

@private1.setter
def private1(self, encrypted):
nonce = encrypted[0]
data = encrypted[1]
self._private1 = json.dumps({"nonce": nonce, "data": data}) # Save the pre-encrypted value to database


# private2 is set up the same way as above


However when I try to filter a query using ilike, or even use built-in func, the query throws an exception:

# ilike (errors)
db.session.query(User).filter(User.private1.ilike(f"%{searchterm}%")).all()

# func.sum (errors)
db.session.query(func.sum(User.private2)).scalar()

TypeError: the JSON object must be str, bytes or bytearray, not InstrumentedAttribute
​

The problem is that sqlalchemy is returning User.private1 as InstrumentedAttribute instead of the actual encryption string from the database, so when json.loads() runs, it fails since the data is not actually json.

How can I run searches on these hybrid properties using ilike and func?

Continue reading...