pythonpostgresqlsqlalchemyfuzzy-searchtrigram

Trigram distance operations in sqlalchemy

I'm currently trying to use the pg_trgm operations % and <->. The GIN Indices on the columns are already available, but I can't find the sqlalchemy equivalent to the previously mentioned operators.

What would be the best approach to solve this problem, except writing a pure text query.

A simple example query would be:

tag = test
tag_subq = session.query(sticker_tag.file_id, f'sticker_tag.name <-> {tag}'.label(distance)) \
    .filter(f'sticker_tag.name % {tag}')) \
    .filter('distance' < 0.3) \
    .subquery("tag_subq")

The query above is obviously not working, and the select and filter string are just placeholder to visualize what I intend to do.

Solution

  • you can use the Operators.op() method; this generates whatever operator you need:

    sticker_tag.name.op('<->')(tag)
sticker_tag.name.op('%%')(tag)

    The percent operator is doubled up to escape it because %foo or %(foo) syntax is used by python dbapi to insert parameters into the query.