Case Insensitive Flask-SQLAlchemy Query

Question

I'm using Flask-SQLAlchemy to query from a database of users; however, while

user = models.User.query.filter_by(username="ganye").first()

will return

<User u'ganye'>

doing

user = models.User.query.filter_by(username="GANYE").first()

returns

None

I'm wondering if there's a way to query the database in a case insensitive way, so that the second example will still return

<User u'ganye'>

Answer 1

You can do it by using either the lower or upper functions in your filter:

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

Another option is to do searching using ilike instead of like:

.query.filter(Model.column.ilike("ganye"))

Answer 2

Improving on @plaes's answer, this one will make the query shorter if you specify just the column(s) you need:

user = models.User.query.with_entities(models.User.username).\
filter(models.User.username.ilike("%ganye%")).all()

The above example is very useful in case one needs to use Flask's jsonify for AJAX purposes and then in your javascript access it using data.result:

from flask import jsonify
jsonify(result=user)