Case-insensitive exact match with SQLAlchemy

Question

How can I ensure that the = operator is always rendered case-insensitive? Are comparisions with the LOWER or the UPPER functions the best bet for performance? ILIKE seems to be very slow.

[Related](https://stackoverflow.com/q/16573095/5320906). – snakecharmerb Aug 25 '22 at 11:15 — snakecharmerb, Aug 25 '22 at 11:15

score 17 · Accepted Answer · edited Apr 27 '22 at 17:51

17

If you need only case-insensitivity use upper or lower since like is not only about case-insensitivity

example of lower:

my_string = 'BarFoo'
session.query(Foo).filter(func.lower(Foo.bar) == my_string.lower()).all()

see some more info on like here how to execute LIKE query in sqlalchemy?

edited Apr 27 '22 at 17:51

Oded BD

2,788
27
30

answered Dec 04 '17 at 21:35

Yoav Glazner

7,936
1
19
36

5

Imported as `from sqlalchemy import func` – sshow Apr 04 '20 at 12:09
1

For flask-sqlalchemy do we have such func ? I found it in '''db.func''' – Viktor Jul 14 '22 at 14:55

score 2 · Answer 2 · edited Apr 27 '22 at 17:52

For case-insensitive comparisons, you can subclass Comparator.

Building Custom Comparators

The example class below allows case-insensitive comparisons on the attribute named word_insensitive:

    from sqlalchemy.ext.hybrid import Comparator, hybrid_property
    from sqlalchemy import func, Column, Integer, String
    from sqlalchemy.orm import Session
    from sqlalchemy.ext.declarative import declarative_base

    Base = declarative_base()

    class CaseInsensitiveComparator(Comparator):
        def __eq__(self, other):
            return func.lower(self.__clause_element__()) == func.lower(other)

    class SearchWord(Base):
        __tablename__ = 'searchword'
        id = Column(Integer, primary_key=True)
        word = Column(String(255), nullable=False)

        @hybrid_property
        def word_insensitive(self):
            return self.word.lower()

        @word_insensitive.comparator
        def word_insensitive(cls):
            return CaseInsensitiveComparator(cls.word)

Above, SQL expressions against word_insensitive will apply the LOWER() SQL function to both sides:

    >>> print Session().query(SearchWord).filter_by(word_insensitive="Trucks")
    SELECT searchword.id AS searchword_id, searchword.word AS searchword_word FROM searchword
    WHERE lower(searchword.word) = lower(:lower_1)

The CaseInsensitiveComparator above implements part of the ColumnOperators interface. A “coercion” operation like lowercasing can be applied to all comparison operations (i.e. eq, lt, gt, etc.) using Operators.operate():

    class CaseInsensitiveComparator(Comparator):
        def operate(self, op, other):
            return op(func.lower(self.__clause_element__()), func.lower(other))

Case-insensitive exact match with SQLAlchemy

2 Answers2