[Django] #34434: psycopg 3 cursor.execute no longer accepts Python tuple binding
Django
-------------------------------------+-------------------------------------
Reporter: David | Owner: nobody
Burke |
Type: Bug | Status: new
Component: Database | Version: 4.2
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/34434>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: Florian Apolloner, Simon Charette (added)
* status: new => closed
* resolution: => invalid
Comment:
Replying to [ticket:34434 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`
[https://www.psycopg.org/psycopg3/docs/basic/from_pg2.html#you-cannot-use-
in-s-with-a-tuple docs] (there are other small
[https://www.psycopg.org/articles/2020/11/24/psycopg3-adaptation/ 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/34434#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: David Burke | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"ff9ceed32b880733d94190742e45dbcf5927b161" ff9ceed3]:
{{{
#!CommitTicketReference repository=""
revision="ff9ceed32b880733d94190742e45dbcf5927b161"
Refs #34434 -- Added note about breaking changes in psycopg version 3 to
release notes.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34434#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: David Burke | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"272dc386c28a4999b179de8ed57bcddea09676a7" 272dc386]:
{{{
#!CommitTicketReference repository=""
revision="272dc386c28a4999b179de8ed57bcddea09676a7"
[4.2.x] Refs #34434 -- Added note about breaking changes in psycopg
version 3 to release notes.
Backport of ff9ceed32b880733d94190742e45dbcf5927b161 from main
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34434#comment:3>