0

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.

7
  • 1
    You really need to spend some time here sql. I see f"({id},{col_1_value},{col_2_value},{col_3_value},{col_4_value})" and I already know you are off track. The point of sql is to eliminate f strings. I'm guessing the issue is here: model_instance_query_values_string = sql.SQL(", ").join(model_instances_to_update). Commented May 17, 2024 at 18:28
  • 1
    And just to strengthen the previous comment. The goal of getting rid of f strings is because then can be a major security hole when used in SQL queries. Commented May 17, 2024 at 21:00
  • @AdrianKlaver I have updated the code but I am still getting the same error. Commented May 18, 2024 at 7:32
  • 1) To question add output of print(model_instance_update_query.as_string(connection)) 2) To simplify I would use execute_values() from here Fast execution helpers. Commented May 18, 2024 at 15:21
  • @AdrianKlaver I have added the sql string. I will try using execute_values and 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 using sql.SQL. Commented May 18, 2024 at 16:04

1 Answer 1

1

Based on the comment by @Adrian Klaver, the solution was to use direct psycopg2 connection object instead of django.db.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)
    ),
)

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,
  )

sql.SQL cannot be used with django.db.connection. Raw sql strings can be directly passed in django's connection object and sql injection is prevented by django internally if placeholders are not surrounded by quotes. Ref: https://docs.djangoproject.com/en/5.0/topics/db/sql/#executing-custom-sql-directly

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.