I need to perform a batch update since the ORM bulk_update seems to be a bit slow for a 1000 records. Since I am updating strings that could contain single quotation marks, e.g. Mc'Donald, I cannot simply template a string variable with all of the attributes.
The documentation recommends the following:
cursor.execute("UPDATE bar SET foo = 1 WHERE baz = %s", [self.baz])
Question: How do I apply this correctly for the below use case?
Technical Details:
- Django 3.0.2
- PostgreSQL
I am generating a SQL string and then calling it as follows:
from django.db import connection
people = [{"name": "Person A", "age": 15, "height_m": 1.83, "id": 5}, {"name": "Person B", "age": 19, "height_m": 1.73, "id": 45}]
sql = ""
params = []
for p in people:
sql += 'UPDATE people SET name = %s, age = %d, height_m = %d WHERE id = %d;'
params.append(p['name'])
params.append(p['age'])
params.append(p['height_m'])
params.append(p['id'])
with connection.cursor() as cursor:
cursor.execute(sql, params)
cursor.connection.commit()
This throws the following error:
ValueError: unsupported format character 'd' (0x64) at index 104
