Django regexp in field

Question

I have model:

class M(Model):
    re = CharacterField(max_length=50, blank=true)

For example in table I have:

table m
----------------
| id  |  re    |
----------------
|  1  |  \d+   |
|  2  |  \:abc |
----------------

I want to find some object which will match my input string (inp) by regexp stored in re field, see example:

inp = ":abc"
for m in M.objects.all():
    if re.match(m.re, inp)
        print("{} matched".format(m.id)) # 2 matched

But is it possible to perform match on DB server? So replace .all() to '.filter' with some expression?

score 2 · Answer 1 · edited May 23 '17 at 11:45

2

For the regex matching you need to use __iregex after fieldname in filter call:

    M.objects.filter(re__iregex=inp)

Take a look at official documentation to get more information

EDIT

If you want the reverse operation (to check if any regex saved on database is matching your value) you cannot use simple filter but you can define your custom Manager

class CurrentManager(models.Manager):
    def match(self, value):
        objects = super(CurrentManager, self).get_query_set().all()

        #here your code
        objects = [o for o in objects if re.match(o, value)]

        return objects

class M(Model):
    re = CharacterField(max_length=50, blank=true)
    objects = RegexManager()

#usage
matched = M.objects.match('123')

Take a look at this question also.

edited May 23 '17 at 11:45

Community

1
1

answered Feb 19 '16 at 14:27

m.antkowicz

13,268
18
37

`iregex` works in forward direction - when column in table matched to some regex expression, I need reverse operation - some value matched to regex in column. I tried `M.objects.filter(re__iregex="123").first()` and it returns `None` but should return `1` because `123` matches under `\d+` – Ivan Borshchov Feb 19 '16 at 14:48
thanks, it is interesting and informative solution, one question: will it first make select all from db and then find matched object in python? My aim is to perform regex match on DB server (I think fetching all raws from DB server will not be efficient if there are many rows in table). Seems like django has no such filter so I need to apply some extra to `where` clause, something like `'WHERE {} REGEXP m.re'.format(inp)`, I will try it later. – Ivan Borshchov Feb 19 '16 at 15:48
yup - it will do the thing in a way you said (first select all then filter on python level) - efficiency depends on rows count. If you want to execute your own select on DB you can use [extra](https://docs.djangoproject.com/en/1.9/ref/models/querysets/#django.db.models.query.QuerySet.extra) method – m.antkowicz Feb 19 '16 at 15:55

score 2 · Answer 2 · answered Feb 19 '16 at 17:44

2

First, \d is not handled by MySQL. Use instead [0-9] or [[:digit:]].

Second, to do the regexp in SQL, have your app construct

'[0-9]+|:abc'

and then build that into the query.

But you probably want to anchor the regexp:

'^([0-9]+|:abc)$'

answered Feb 19 '16 at 17:44

Rick James

135,179
13
127
222

thanks for notice about `\d`, I use MariaDB server, which should support PCRE regexps. My task is find what row in `m` table stores `re` which match some input string, there can be a lot of rows entered by system administrator, and they can have different expressions - 2 rows is very simplified example to simplify question, which have no practical value, only technical. – Ivan Borshchov Feb 22 '16 at 15:11

score 2 · Accepted Answer · answered Feb 22 '16 at 15:30

2

for m in M.objects.filter().extra(where=["'{}' RLIKE `m`.`re`".format(inp)])
    print("{} matched".format(m.id))

answered Feb 22 '16 at 15:30

Ivan Borshchov

3,036
5
40
62

Django regexp in field

3 Answers3