pythonsqlalchemyflaskdiacritics

SQLALCHEMY ignore accents on query

Considering my users can save data as "café" or "cafe", I need to be able to search on that fields with an accent-insensitive query.

I've found https://github.com/djcoin/django-unaccent/, but I have no idea if it is possible to implement something similar on sqlalchemy.

I'm using PostgreSQL, so if the solution is specific to this database is good to me. If it is generic solution, it is much much better.

Thanks for your help.

Solution

  • First install the unaccent extension in PostgreSQL with create extension unaccent;

    Next, declare the SQL function unaccent in Python:

    from sqlalchemy.sql.functions import ReturnTypeFromArgs

class unaccent(ReturnTypeFromArgs):
    pass

    and use it like this:

    for place in session.query(Place).filter(unaccent(Place.name) == "cafe").all():
    print place.name

    Make sure you have the correct indexes if you have a large table, otherwise this will result in a full table scan.