SQLAlchemy Flag Insensitive Query

Question

I use Flask-SQLAlchemy to query from a user database; however, while

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

will return

 <User u'ganye'> 

do

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

returns

 None 

I am wondering if there is a way to query the database in a case-insensitive way, so the second example will still return

 <User u'ganye'> 

Answer 1

You can do this using 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 search using ilike instead of like :

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

Answer 2

Improving the answer on @plaes, this will make the query shorter if you specify only the columns you need:

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

The above example is very useful if you need to use Flask jsonify for AJAX purposes, and then in your javascript access it using data.result :

 from flask import jsonify jsonify(result=user)