# Error when calling cursor.execute with psycopg2 sql.SQL object

**URL:** <https://forum.djangoproject.com/t/error-when-calling-cursor-execute-with-psycopg2-sql-sql-object/31302>\
**Category:** Using the ORM\
**Created:** [May 19, 2024, 5:43am UTC](https://forum.djangoproject.com/t/error-when-calling-cursor-execute-with-psycopg2-sql-sql-object/31302 "2024-05-19T05:43:13Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Laxman-M-R](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/laxman-m-r/32/21396_2.png) [@Laxman-M-R](https://forum.djangoproject.com/u/Laxman-M-R)\
**Post date:** [May 19, 2024, 5:43am UTC](https://forum.djangoproject.com/t/error-when-calling-cursor-execute-with-psycopg2-sql-sql-object/31302/1 "2024-05-19T05:43:14Z")

</div>

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**

```auto
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**

```auto
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?

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [May 19, 2024, 2:08pm UTC](https://forum.djangoproject.com/t/error-when-calling-cursor-execute-with-psycopg2-sql-sql-object/31302/2 "2024-05-19T14:08:18Z")

</div>

> [@Laxman-M-R](#):
>
> am I doing something wrong here?

Yes. You do not directly use the psycopg2.sql.SQL object with a Django connection.

See [Performing raw SQL queries | Django documentation | Django](https://docs.djangoproject.com/en/5.0/topics/db/sql/#executing-custom-sql-directly) for how to create and execute raw SQL using the connection object. (You don’t create an `sql.SQL` object to pass to cursor.execute, you pass the SQL string itself.)

---

<div class="post-metadata">

**Author:** ![Laxman-M-R](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/laxman-m-r/32/21396_2.png) [@Laxman-M-R](https://forum.djangoproject.com/u/Laxman-M-R)\
**Post date:** [May 19, 2024, 2:33pm UTC](https://forum.djangoproject.com/t/error-when-calling-cursor-execute-with-psycopg2-sql-sql-object/31302/3 "2024-05-19T14:33:52Z")

</div>

Got it. So django internally prevents sql injection if placeholders are not surrounded by quotes?

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [May 19, 2024, 2:35pm UTC](https://forum.djangoproject.com/t/error-when-calling-cursor-execute-with-psycopg2-sql-sql-object/31302/4 "2024-05-19T14:35:36Z")

</div>

Correct. It builds the sql as “sql with parameters” and not as a complete sql query string.

---

<div class="post-metadata">

**Author:** ![Laxman-M-R](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/laxman-m-r/32/21396_2.png) [@Laxman-M-R](https://forum.djangoproject.com/u/Laxman-M-R)\
**Post date:** [May 19, 2024, 2:45pm UTC](https://forum.djangoproject.com/t/error-when-calling-cursor-execute-with-psycopg2-sql-sql-object/31302/5 "2024-05-19T14:45:49Z")

</div>

Ok. In fact I was writing and sending sql strings directly with placeholders and passed values in list just as it is in documentation. But I ran static code checker on my django application and it showed this as security issue and it stopped showing that only when I passed list to params using `AsIs` from `psycopg2`. Basically it was as below:

```auto
from django.db import connection
from psycopg2.extensions import AsIs

params_dict = {"list_of_values": AsIs(model_instances_to_update)}
cursor.execute(sql_string, params)

```

Now `sql.SQL` is the recommended way as per psycopg2 instead of `AsIs`. So thought of executing it like this. So I think I have to make some config changes in static code checker to silence these warnings.