jperelli jperelli - 9 days ago 8
Python Question

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:


  • This throws error
    19.*\\Z(?ms)

  • This does not throw error
    19.*\\Z(ms)

  • I think this works as expected
    19.*



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?

Answer

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.