#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)
comment:1 by , 20 months ago
Cc: | added |
---|---|
Resolution: | → invalid |
Status: | new → closed |
Replying to David Burke:
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.