I want to duplicate Google’s POSIX-like wildcard searches, but as inbound data filters. Writing SQLAlchemy wildcard LIKE
queries is straightforward, but saving the searches is a little different.
To do this, I need to transform my POSIX-style search “Hello *!” into the syntax for a LIKE query “Hello %!”
db = SQLAlchemy(app)
class Search(db.Model):
@declared_attr
def _referrer(cls):
return db.Column(db.String) # ORIGIN POSIX regex
@property
def referrer(self):
return re.sub('_', '?', re.sub('%', '*', self._referrer))
@referrer.setter
def referrer(self, value):
self._referrer = re.sub('\?', '_', re.sub('\*', '%', self.value))
@classmethod
def get_for_referrer(cls, referrer):
return cls.query.filter(literal(referrer).ilike(cls._referrer)).all()
Saving it, I now can use SQLAlchemy’s literal
function to run this odd query.
from sqlalchemy.sql.expression import literal
# save my search
search = Search(referrer="Hello *!")
db.session.add(search)
db.session.commit()
Search.get_for_referrer("Hello World!"). # returns my search!
Postgres’ documentation is here and will also walk you through how to do this for regex expressions if you’re so inclined. However, it does seem like that comes at a performance cost and in my case…there’s not much value, as most users aren’t looking to write regex.