Hi. I am trying to use sql.SQL
from psycopg2
to build an sql string with placeholders and then use connection
from django.db
and call cursor.execute
. But I am getting this error: TypeError: "object of type 'Composed' has no len()"
. Now if I create connection
object directly from psycopg2
, then it works. Is what I am doing using django.db.connection
correct?
Not working code
from django.db import connection
from psycopg2 import sql
model_instances_to_update = []
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,
)
Working Code
from psycopg2 import sql
model_instances_to_update = []
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)
),
)
database = settings.DATABASES["default"]
pg_connection_dict = {
"dbname": database["NAME"],
"user": database["USER"],
"password": database["PASSWORD"],
"port": database["PORT"],
"host": database["HOST"],
}
connection = psycopg2.connect(**pg_connection_dict)
cursor = connection.cursor()
cursor.execute(
model_instance_update_query,
model_instances_to_update,
)
connection.close()
Is this the intended behaviour of django.db.connection or am I doing something wrong here?