Multiple update using RAW SQL in Django

Question

I have a situation in Django where I need to update multiple rows in a database using RAW SQL, because it is a table created on another system and not managed by the ORM.

Since there are multiple rows to be updated, is it possible to run a single command that updates all things at once without having to include a cursor.execute(SQL,params) inside a for loop?

At the moment, I was only able to get this update to work correctly by including each update line inside a for loop, but I don't think it's a good practice. It's working something like this:

person_list=[[john,33,50],[mary,20,41],[peter,12,93]]

with connection.cursor() as cursor:
    for item in person_list:
        SQL="UPDATE PERSON SET NAME = %s, AGE = %s WHERE PERSON_ID = %s"
        params.extend([item[0], item[1], item[2]])
        cursor.execute(SQL,params)

I would like Django to run a single SQL command like this:

    SQL+="""
        update person name='john', age='33' where person_id=50;
        update person name='mary', age='20' where person_id=41;
        update person name='John', age='12' where person_id=93;
    """
    cursor.execute(SQL)

I tried to do as follows, but without success, with error message "ORA-00933:SQL command not properly ended":

    for item in person_list:
        SQL+="UPDATE PERSON SET NAME = %s, AGE = %s WHERE PERSON_ID = %s; "
        params.extend([item[0], item[1], item[2]])

    with connection.cursor() as cursor:
        cursor.execute(SQL,params)
        

I don't know if I'm worrying too much about this, and if it's really something that will affect system's performance. My concern is precisely because I've always heard from everyone that you should never include an SQL command inside a loop.

The database is Oracle.

Thank you

Answer 1

If you have a Person model, you can do the update in bulk with the Django ORM. So if the model looks like:

class Person(models.Model):
    person_id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=255)
    age = models.IntegerField()

Then you can update in bulk with .bulk_update(…) ^[Django-doc]:

person_list=[['john', 33, 50], ['mary', 20, 41], ['peter', 12, 93]]

Person.objects.bulk_update(
    [Person(person_id=person_id, name=name, age=age) for name, age, person_id in person_list]
    fields=['name', 'age']
)

Using the Django ORM is more convenient, makes it less likely to make mistakes, and it will, if you properly define the settings.py talk in the correct dialect to the database.