How to handle symbols (*, +, ~,...) in sqlalchemy on a flask app

Question

I have a full web-page working and now I am debugging the errors. Until now, the only one I can't get rid of is when the input on my web-page introduces a symbol (+, *, ~,...)

If I change the operator: op('~* ') for a like, it works but I don't need the like operator, I need the case insensitive op('~* ')

# first, i take from my web the filter that an user have introduced
filter_tag = str(request.form.get('filter_tag'))
if take_filter.filter_tag != '':
    conditions.append(clothes.c.column_tag==filter_tag)

query = session.query(clothes).filter(and_(*conditions),).distinct(clothes.c.nummer).order_by(clothes.c.nummer)

It works perfectly with a normal text but if the user introduces a sign (*, ~, +,...) it returns an error:

sqlalchemy.exc.DataError: (psycopg2.DataError) invalid regular expression: quantifier operand invalid

Please [edit] the question and add the code that you need to work, not some other code that's only vaguely related to that — Antti Haapala -- Слава Україні, Jan 21 '19 at 16:44

score 0 · Accepted Answer · answered Jan 22 '19 at 11:46

I figured out how to do it based on this post: Case insensitive with a equal operator , the main difference is that I need to check if a value of a column contains a string and on the other post is a equal relation (==).

For that, I changed the filtering from op('~*') to contains. But, contains works on a case sensitive way and to fix this I just put in both lower funtion.

#import the upper and lower function from sqlalchemy
from sqlalchemy import func

#take the filter from browser
filter_tag = str(request.form.get('filter_tag'))

#Check that the filter is not empty
if take_filter.filter_tag != '':
    conditions.append(func.lower(clothes.c.column_tag).contains(func.lower(filter_tag)))

query = session.query(clothes).filter(and_(*conditions)).distinct(clothes.c.nummer).order_by(clothes.c.nummer)

How to handle symbols (*, +, ~,...) in sqlalchemy on a flask app

1 Answers1