5

Look at this answer: Case Insensitive Flask-SQLAlchemy Query

Why is it better to use SQLAlchemy's func.lower(mystring) instead of python's native mystring.lower()?

Community
  • 1
  • 1
Saqib Ali
  • 11,931
  • 41
  • 133
  • 272
  • 1
    It probably doesn't do much on a string literal, but it does make a difference in that you can use it on a column: `func.lower(SomeModel.some_column)` – mgilson Apr 20 '16 at 16:47

1 Answers1

9

The context is important.

user = models.User.query.filter(func.lower(User.username) == func.lower("GaNyE")).first()

Here, there is no string with a lower method to call; you are composing a SQL command to execute in the database. func.lower doesn't return a lowercase string; it returns an object that represents SQL code that can be used to produce a lowercase string.

As mgilson points out, there's no need to use func.lower on actual Python values; they are constant for each row the filter will be applied against, but I'm not sure SQLAlchemy is able to detect and optimize such situations. Either of the following

user = models.User.query.filter(func.lower(User.username) == "ganye").first()
user = models.User.query.filter(func.lower(User.username) == "GaNyE".lower()).first()

would produce SQL code like lower(username) = :lower_1 instead of lower(username) = lower(:lower_1), which can make the SQL query more efficient by eliminating a call to lower for each line.

chepner
  • 497,756
  • 71
  • 530
  • 681
  • The second `func.lower('GaNyE')` probably doesn't buy you much above `'ganye'` or `'GaNyE'.lower()`, does it? It seems to me that it would only be useful as an operation on the column... – mgilson Apr 20 '16 at 16:48
  • 1
    Right. Constants are converted to bind parameters, so you end up with `lower(:1)` instead of `(:1)`. I don't know if SQLAlchemy can recognize that the same value is always passed to `lower` and optimize the resulting code, so it would be better to lowercase the string in Python first in that case. – chepner Apr 20 '16 at 16:56