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.

Answer 1

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?

Answer 2

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))