Django bulk update with string replace

Question

I am trying to update and modify a string field Django's ORM. The equivalent SQL to do this is:

UPDATE example_table SET string_field = REPLACE(string_field, 'old text', 'new text');

With that query, I expect old text and old text more text to be replaced with new text and new text more text respectively, for all the entries in the string_field column.

Bulk update() seems promising, but doesn't allow me to modify only part of the field, and F() expressions only implement numeric changes, not string replace. I also looked at using raw queries to run the above SQL, but that seems like a sideways hack (especially since F() exists to do the same functionality on numbers), and I couldn't get them to actually execute.

I ended up with this, but it seems a shame to execute all the extra queries when I know there's a one line SQL statement to do it.

for entry in ExampleModel.objects.all():
    entry.string_field = entry.string_field.replace('old text', 'new text', 1)
    entry.save()

Does this feature not exist in Django's ORM for strings yet? Is there something I overlooked in the docs?

Related SO questions:

I have come with this problem back there. The best way I could found is to do the string processing in Python code. If you do not mind using raw SQL, the replace function is quite consistent between any databases. — Edwin Lunando
– Edwin Lunando, Commented Feb 22, 2015 at 13:27

Seonghyeon Cho · Accepted Answer · 2021-06-10 08:09:23Z

42

Tested with django 1.9

from django.db.models import F, Func, Value

ExampleModel.objects.filter(<condition>).update(
    string_field=Func(
        F('string_field'),
        Value('old text'), Value('new text'),
        function='replace',
    )
)

UPDATE Django 2.1 https://docs.djangoproject.com/en/2.2/ref/models/database-functions/#replace

from django.db.models import Value
from django.db.models.functions import Replace

ExampleModel.objects.filter(<condition>).update(
    string_field=Replace('string_field', Value('old text'), Value('new text'))
)

edited Jun 10, 2021 at 8:09

Seonghyeon Cho

4501 gold badge7 silver badges17 bronze badges

answered Jan 11, 2017 at 17:50

Nhu Trinh

14k6 gold badges65 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

int_ua Over a year ago

Works on 1.8+ docs.djangoproject.com/en/1.8/ref/models/expressions/… and PostgreSQL

Mohammed Shareef C Over a year ago

Working on 1.11 with mysql (mariadb)

urek mazino Over a year ago

wokring on 10.6.7-MariaDB

Andrew Magee · Accepted Answer · 2015-03-03 00:11:46Z

5

You could make your own F-like object to represent the string replacing in SQL. Here is a proof of concept:

from django.db.models.expressions import ExpressionNode

class StringReplaceF(ExpressionNode):
    def __init__(self, field, replace_from, replace_to):
        self.field = field
        self.replace_from = replace_from
        self.replace_to = replace_to
        super(StringReplaceF, self).__init__()

    def evaluate(self, evaluator, qn, connection):
        return (
            "REPLACE({}, %s, %s)".format(self.field),
            (self.replace_from, self.replace_to)
        )

 >>> f = StringReplaceF('string_field', 'old text', 'new text')
 >>> ExampleModel.objects.update(string_field=f)

You'd need to do a bit more work with the class if you need it to behave nicely with other F objects, but then again, the existing F objects don't seem to work with strings anyway.

edited Mar 3, 2015 at 0:11

answered Mar 3, 2015 at 0:03

Andrew Magee

6,7044 gold badges38 silver badges58 bronze badges

2 Comments

Kevin Brown-Silva Over a year ago

Any idea what the Django version and DBMS support for this is?

Andrew Magee Over a year ago

Not sure exactly. I just tried it on 1.6.6; I expect it would work in (much) earlier versions but not sure of the specifics. SQL-wise, the REPLACE function seems to exist in that form in at least Postgres, MySQL and SQLite.

tripleee · Accepted Answer · 2021-06-13 13:17:37Z

5

New in Django 2.1 - Replace database function

Your example can now be expressed most easily via:

ExampleModel.objects.update(
    string_field=Replace('string_field', Value('old_text'), Value('new_text'))
)

edited Jun 13, 2021 at 13:17

tripleee

192k37 gold badges318 silver badges367 bronze badges

answered May 29, 2019 at 13:28

Dave Lawrence

1,23610 silver badges8 bronze badges

1 Comment

Nhu Trinh Over a year ago

You have extra ) after Replace

pymen · Accepted Answer · 2023-08-18 09:18:11Z

Use replace_field_substring(YourModel, "your_field", "value_from", "value_to")

from django.db.models import F, Value, QuerySet
from django.db.models.functions import Replace

def replace_field_substring(model_or_qs, field, value_from, value_to):
    """Generates ORM partial replace, which can be used in Django ORM data migrations,
    for example for partial replace tbl.cont from="twitter.com" to="x.com" will give you
    UPDATE tbl SET cont = REPLACE(cont, 'twitter.com', 'x.com') WHERE cont LIKE '%twitter.com%';
    """
    qs = model_or_qs if isinstance(model_or_qs, QuerySet) else model_or_qs.objects.all()
    replacement_expression = Replace(F(field), Value(value_from), Value(value_to))
    qs.update(**{field: replacement_expression})

Alhamsya Bintang Dyasta · Accepted Answer · 2019-07-26 05:10:49Z

-1

Django 2.2 support bulk update, can you using function that one.

Check this one : https://docs.djangoproject.com/en/2.2/ref/models/querysets/#django.db.models.query.QuerySet.bulk_create

answered Jul 26, 2019 at 5:10

Alhamsya Bintang Dyasta

719 bronze badges

Collectives™ on Stack Overflow

Django bulk update with string replace

5 Answers 5

3 Comments

2 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related