I am trying to execute a raw update query using psycopg2 in django. The code is as below:
from django.db import connection
from psycopg2 import sql
model_instances_to_update = []
for model_instance in models_queryset:
model_instances_to_update.append(
sql.Identifier(
f"({id},{col_1_value},{col_2_value},{col_3_value},{col_4_value})"
)
)
model_instance_query_values_string = sql.SQL(", ").join(model_instances_to_update)
model_instance_sql_params = {"updated_model_instance_values": model_instance_query_values_string}
model_instance_update_query = sql.SQL(
"""
UPDATE {table} AS model_table SET
col_1 = model_table_new.col_1,
col_2 = model_table_new.col_2,
col_3 = model_table_new.col_3,
col_4 = model_table_new.col_4
FROM (VALUES (%(updated_model_instance_values)s)) AS model_table_new(id, col_1, col_2, col_3, col_4)
WHERE model_table.id = model_table_new.id;
"""
).format(
table=sql.Identifier("table_name"),
)
with connection.cursor() as cursor:
cursor.execute(
model_instance_update_query,
params=model_instance_sql_params,
)
But when I try to execute this query I am getting the following error:
TypeError: "object of type 'Composed' has no len()"
What am I doing wrong here and how to correct it?
UPDATE I am now passing the list of tuples directly in cursor.execute. I have added placeholders in the SQL object string but I am still getting the same error.
for model_instance in models_queryset:
model_instances_to_update.append(
(
id,
col_1_value,
col_2_value,
col_3_value,
col_4_value
)
)
model_instance_update_query = sql.SQL(
"""
UPDATE {table} AS model_table SET
col_1 = model_table_new.col_1,
col_2 = model_table_new.col_2,
col_3 = model_table_new.col_3,
col_4 = model_table_new.col_4
FROM (VALUES ({records_list_template})) AS model_table_new(id, col_1, col_2, col_3, col_4)
WHERE model_table.id = model_table_new.id;
"""
).format(
table=sql.Identifier("table_name"),
records_list_template=sql.SQL(",").join(
[sql.Placeholder()] * len(model_instances_to_update)
),
)
with connection.cursor() as cursor:
cursor.execute(
model_instance_update_query,
model_instances_to_update,
)
UPDATE 2
Below is the output of printing sql from inside execute function.
Composed([SQL('\n UPDATE '),
Identifier('table_name'),
SQL(' AS model_table SET\n
col_2 = model_table_new.col_2,\n
col_3 = model_table_new.col_3,\n
col_4 = model_table_new.col_4,\n
col_5 = model_table_new.col_5\n
FROM (VALUES ('),
Composed([Placeholder(), SQL(', '), Placeholder(), SQL(', '),
Placeholder(), SQL(', '), Placeholder(), SQL(', '),
Placeholder()]),
SQL(')) AS
model_table_new(col_1, col_2, col_3, col_4, col_5)\n
WHERE model_table.id = model_table_new.id;\n ')])
I tried executing print(model_instance_update_query.as_string(connection)) but I got this error - argument 2 must be connection or cursor. This is because I am using connection object from django.db which is a proxy and not the connection object from psycopg2. So tried as above.
f"({id},{col_1_value},{col_2_value},{col_3_value},{col_4_value})"and I already know you are off track. The point ofsqlis to eliminatefstrings. I'm guessing the issue is here:model_instance_query_values_string = sql.SQL(", ").join(model_instances_to_update).fstrings is because then can be a major security hole when used in SQL queries.print(model_instance_update_query.as_string(connection))2) To simplify I would useexecute_values()from here Fast execution helpers.execute_valuesand update here but I still would like to know the cause of this error. Also if I just pass a string then I able to execute the query. This is happening when I am usingsql.SQL.