Opened 20 months ago

Closed 20 months ago

Last modified 19 months ago

#34434 closed Bug (invalid)

psycopg 3 cursor.execute no longer accepts Python tuple binding

Reported by: David Burke Owned by: nobody
Component: Database layer (models, ORM) Version: 4.2
Severity: Normal Keywords:
Cc: Florian Apolloner, Simon Charette Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

This may be a bug or a missing feature of psycopg 3. If expected, it may be worth mentioning as a breaking change when using psycopg3.

The following Django code works with psycopg2. Notice the usage of a tuple with the "in" statement.

cursor.execute("select 1 where 1 in %s", ((1,),)

But in psycopg 3 it gives an error

django.db.utils.ProgrammingError: syntax error at or near "'{1}'"
LINE 1: select 1 where 1 in '{1}'::int2[]

A fix is to use ANY with a list. It must specifically be a list and not a tuple.

cursor.execute("select 1 where 1 = ANY(%s)", ([1],))

With a tuple, we get the error

django.db.utils.ProgrammingError: syntax error at or near "'(1)'"
LINE 1: select 1 where 1 in '(1)'

I would expect execute to treat a Python list and tuple the same when binding to a postgresql parameter. But this is not so.

Change History (3)

in reply to:  description comment:1 by Mariusz Felisiak, 20 months ago

Cc: Florian Apolloner Simon Charette added
Resolution: invalid
Status: newclosed

Replying to David Burke:

This may be a bug or a missing feature of psycopg 3. If expected, it may be worth mentioning as a breaking change when using psycopg3.

Thanks for the ticket. This is a backward incompatibility change explicitly stated in psycopg docs (there are other small caveats when using raw SQL statements). It's not something that we want/can change in Django itself. Moreover, it crashes with other backends so it's now more consistent.

We normally don't document backward incompatibility changes in database adapters, especially on a low-level of executing raw SQL statements. We don't want to copy psycopg 3 docs here. I think your ticket will be enough to raise awareness on this small inconvenience.

comment:2 by Mariusz Felisiak <felisiak.mariusz@…>, 19 months ago

In ff9ceed3:

Refs #34434 -- Added note about breaking changes in psycopg version 3 to release notes.

comment:3 by Mariusz Felisiak <felisiak.mariusz@…>, 19 months ago

In 272dc386:

[4.2.x] Refs #34434 -- Added note about breaking changes in psycopg version 3 to release notes.

Backport of ff9ceed32b880733d94190742e45dbcf5927b161 from main

Note: See TracTickets for help on using tickets.
Back to Top