Django: Update multiple objects with regex

Question

I want to remove 'blog/' substring from slug field of multiple objects according to this and this docs:

>>> import re
>>> from django.db.models import F
>>> p = re.compile('blog/')
>>> Blog.objects.update(slug=p.sub('', F('slug')))
Traceback (most recent call last):
  File "<console>", line 1, in <module>
TypeError: expected string or buffer

I tried to add str() to the last string, and it passes without errors:

>>> Blog.objects.update(slug=p.sub('', str(F('slug'))))

but it inserts (DEFAULT: ) into slug field for all objects.

Any suggestions?

Answer 1

To update multiple objects at once in Django with regular expressions over a queryset you can use a Func expression to access regex functions in your database:

django.db.models import F, Func, Value


pattern = Value(r'blog(/?)')  # the regex
replacement = Value(r'new-blog-slug\1')  # replacement string
flags = Value('g')  # regex flags

Blog.objects.update(
    slug=Func(
        models.F('slug'),
        pattern, replacement, flags,
        function='REGEXP_REPLACE',
        output_field=models.TextField(),
    )
)

Check your DB vendor documentation for details and specific functions support.

Use raw strings r'' in pattern and replacement to avoid having to escape the backslashes.

Reference matched substrings in replacement using \n with n from 1 to 9.

You can use F expressions to provide pattern, replacement and flags from fields of each instance:

pattern = F('pattern_field')
replacement = F('replacement_field')
flags = F('flags_field')

You can also use the Func expression to make annotations.

Currently there is an open pull request to add regular expressions database functions in Django. Once merged you will probably have RegexpReplace, RegexpStrIndex and RegexpSubstr function expressions available under django.db.models.functions to make your code more concise and have a single API unified across DB vendors.

Answer 2

You can't do that. The update is done completely within the database, so it must be something translatable to SQL, which your code isn't. You'll need to iterate through and update:

for blog in Blog.objects.filter(slug__startswith='blog/'):
    blog.slug = blog.slug.replace('blog/', '')
    blog.save()