CODEWITHSUNDEEP
pythonregexdjangopostgresqlglob
jperelli
jperelli

Reputation: 7207

Can I query the DB using a glob pattern?

I'm having trouble trying to query db using a glob pattern.

I thougth that maybe glob could be translated to regex, and I know I can query db using regex. I was going to to that translation myself, but I found that python has fnmatch to do just that, explicitly the function translate

fnmatch.translate(pattern)

Return the shell-style pattern converted to a regular expression for using with re.match().

So I tried to combine both things but...

>>> from vte.models import VTE
>>> import fnmatch
>>> regex = fnmatch.translate('19*')
>>> regex
'19.*\\Z(?ms)'
>>> VTE.objects.filter(ipaddr__regex=regex)
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/var/www/vtfx/env/local/lib/python2.7/site-packages/django/db/models/query.py", line 234, in __repr__
    data = list(self[:REPR_OUTPUT_SIZE + 1])
  File "/var/www/vtfx/env/local/lib/python2.7/site-packages/django/db/models/query.py", line 258, in __iter__
    self._fetch_all()
  File "/var/www/vtfx/env/local/lib/python2.7/site-packages/django/db/models/query.py", line 1074, in _fetch_all
    self._result_cache = list(self.iterator())
  File "/var/www/vtfx/env/local/lib/python2.7/site-packages/django/db/models/query.py", line 52, in __iter__
    results = compiler.execute_sql()
  File "/var/www/vtfx/env/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 848, in execute_sql
    cursor.execute(sql, params)
  File "/var/www/vtfx/env/local/lib/python2.7/site-packages/django/db/backends/utils.py", line 79, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/var/www/vtfx/env/local/lib/python2.7/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/var/www/vtfx/env/local/lib/python2.7/site-packages/django/db/utils.py", line 95, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/var/www/vtfx/env/local/lib/python2.7/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
DataError: invalid regular expression: quantifier operand invalid

I don't understand the error message.

According to django's documentation, that should be translated in postgresql (db I'm using, by the way) to something like this

SELECT ... WHERE ipaddr ~ '19.*\\Z(?ms)';

Here it is the ~ operator documentation.

So I tried changing a little the regexp returned by translate(), and it does not throw error when removing the ? char.

Then I thought, maybe the glob->regex translation works ok without this last part \\Z(?ms) but I'm not sure, I might be missing something.

Recap:

So the new code would be like this

>>> VTE.objects.filter(ipaddr__regex=regex.replace('\\Z(?ms)', ''))
[<VTE: 192.168.56.100>]

What am I missing when doing .replace('\\Z(?ms)', '')? Why is that necessary? and is this a good solution?

Upvotes: 1

Views: 1534

Answers (1)

jperelli
jperelli

Reputation: 7207

Based on comments on my question, I ended up doing the following

import fnmatch
globex = ......
regex = '^{}'.format(fnmatch.translate(globex).replace('\\Z(?ms)', '$'))
VTE.objects.filter(ipaddr__regex=regex)

This seems to be compatible with postgresql and javascript regex implementations.

Upvotes: 3

Related Questions