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.