Using function of sqlalchemy.sql multiple times in query

Question

I am using sqlalchemy version 0.7.8. I have come across an issue where my user can search a text in plain english but record in db can have special characters between text. So if user enters "please come early to the hospital" and in db i have "please, come early, to the hospital." then my query must return exactly please, come early, to the hospital.

i have found this easy solution.

needle = func.replace(func.replace(Message.text, ',', ''), '.', '')
Message.query.filter(needle==query_term.strip()).one()

But problem with this is that there can be many special characters in db like "! ; : ? &" etc and needle will look very inefficient so please suggest me any efficient solution to avoid repeating func.replace function.

I cannot find same question on stackoverflow let me know if someone has already answered it.

Answer 1

Thanks to all replies. I am able to improve it through this way.

IGNORE_CHARS = [',', '.', '!', '&', ';', ':', '@']
needle = build_replace_func(IGNORE_CHARS, Message.text)
record = Message.query.filter(needle == query_term.strip()).one()

def build_replace_func(chars, attr, replace_with=''):
    for character in chars:
        attr = func.replace(attr, character, replace_with)
    return attr

Answer 2

Use a query with RegEx

There are examples in the SqlAlchemy documentation.

See also this question.

Answer 3

You can use this to remove all spacial character in python

>>> impore re
>>> string = "please, come early# to the@ hospital"
>>> cleanString = re.sub('\W+',' ', string )
>>> print cleanString
please come early to the hospital

store your text as a string in a variable and then run your query with this variable.