Search code examples
databaseperformancesqlalchemystring-comparison

Case-insensitive exact match with SQLAlchemy

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.

Solution

  • 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?