PSidhu February 2016

Parenthesis in MySQL Regex queries with SQLAlchemy

I have a python application which searches a database using SQLAlchemy like so:

query = raw_input('Search: ')
db.session.query(Posts).filter(Posts.name.op('REGEXP')(r'[[:<:]]{}'.format(query))).all()

This works fine with my MySQL database with most characters however I have found some searches do not work.

  • Searches that include ( without a ) following it somewhere after return an error
  • Searches that contain foreign characters like Б, э, д or ц also returns an error

Is there a solution to support ALL kinds of search queries including special and foreign characters? Thanks.

Answers


alecxe February 2016

Searches that include ( without a ) following it somewhere after return an error

This is happening since ( and ) have special meaning in regular expressions. The expression would not be valid, if, for example, there is an opening parenthesis without a closing one:

>>> re.compile(r"test (")
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  ...
error: unbalanced parenthesis

You need to escape the query:

import re

query = re.escape(query)

Post Status

Asked in February 2016
Viewed 2,956 times
Voted 7
Answered 1 times

Search




Leave an answer