jperelli jperelli - 1 year ago 75
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



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
>>> 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/", 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/", line 258, in __iter__
File "/var/www/vtfx/env/local/lib/python2.7/site-packages/django/db/models/", 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/", line 52, in __iter__
results = compiler.execute_sql()
File "/var/www/vtfx/env/local/lib/python2.7/site-packages/django/db/models/sql/", line 848, in execute_sql
cursor.execute(sql, params)
File "/var/www/vtfx/env/local/lib/python2.7/site-packages/django/db/backends/", 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/", line 64, in execute
return self.cursor.execute(sql, params)
File "/var/www/vtfx/env/local/lib/python2.7/site-packages/django/db/", 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/", 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
, and it does not throw error when removing the

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


  • This throws error

  • This does not throw error

  • I think this works as expected

So the new code would be like this

>>> VTE.objects.filter(ipaddr__regex=regex.replace('\\Z(?ms)', ''))

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

Answer Source

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

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

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download