public inbox for [email protected]
help / color / mirror / Atom feed2-to-3 Question about adapter using AsIs
3+ messages / 2 participants
[nested] [flat]
* 2-to-3 Question about adapter using AsIs
@ 2023-11-20 18:57 Ams Fwd <[email protected]>
2023-11-20 22:46 ` Re: 2-to-3 Question about adapter using AsIs Daniele Varrazzo <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Ams Fwd @ 2023-11-20 18:57 UTC (permalink / raw)
To: psycopg
Hi,
I am working on migrating our django codebase from ver 2 to ver 3+ and
have run into a code conversion issue that I have not quite been able
to figure out.
Previously for a generated column in our schema (dealing with accented
names) we used to have:
from django.db import models
from psycopg2.extensions import AsIs, register_adapter
class PostgresDefaultValueType:
pass
register_adapter(PostgresDefaultValueType, lambda _: AsIs("DEFAULT"))
class PostgresHandledTextField(models.TextField):
def get_prep_value(self, value):
return PostgresDefaultValueType()
This was based off of a comment on a ticket
(https://code.djangoproject.com/ticket/21454#comment:28) to be able to
handle DEFAULT columns in postgres when using Django.
Trying to convert this to psycopg3's updated adapter framework, what I
have tried so far is this:
from django.db import models
from psycopg import adapters, sql
from psycopg.adapt import Dumper
class PostgresDefaultValueType:
pass
class PostgresDefaultValueTypeDumper(Dumper):
def dump(self, obj):
return sql.DEFAULT
adapters.register_dumper(PostgresDefaultValueType,
PostgresDefaultValueTypeDumper)
class PostgresHandledTextField(models.TextField):
def get_prep_value(self, value):
return PostgresDefaultValueType()
As far as I can tell from the documentation the `sql.DEFAULT` should
be the appropriate thing to put in the dumper so that generated query
uses `'DEFAULT'` in the correct place during query generation.
However when I do use this I run into
> ???
E TypeError: bytes or buffer expected, got <class 'psycopg.sql.SQL'>
psycopg_binary/pq/pqbuffer.pyx:111: TypeError
which in some ways makes sense as `AsIs` previously did something
special and it's not the same? Looking at the code, it should merely
be doing `PyUnicode_AsUTF8String` but I am assuming that `sql.DEFAULT`
is not generating the appropriate quoted string?
Any help in debugging this would be greatly appreciated
TIA.
AM
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: 2-to-3 Question about adapter using AsIs
2023-11-20 18:57 2-to-3 Question about adapter using AsIs Ams Fwd <[email protected]>
@ 2023-11-20 22:46 ` Daniele Varrazzo <[email protected]>
2023-11-21 01:24 ` Re: 2-to-3 Question about adapter using AsIs Ams Fwd <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Daniele Varrazzo @ 2023-11-20 22:46 UTC (permalink / raw)
To: Ams Fwd <[email protected]>; +Cc: psycopg
Hello,
On Mon, 20 Nov 2023 at 19:58, Ams Fwd <[email protected]> wrote:
> As far as I can tell from the documentation the `sql.DEFAULT` should
> be the appropriate thing to put in the dumper so that generated query
> uses `'DEFAULT'` in the correct place during query generation.
>
> However when I do use this I run into
>
> > ???
> E TypeError: bytes or buffer expected, got <class 'psycopg.sql.SQL'>
>
> psycopg_binary/pq/pqbuffer.pyx:111: TypeError
>
> which in some ways makes sense as `AsIs` previously did something
> special and it's not the same? Looking at the code, it should merely
> be doing `PyUnicode_AsUTF8String` but I am assuming that `sql.DEFAULT`
> is not generating the appropriate quoted string?
Psycopg 3 uses server-side binding
(https://www.psycopg.org/psycopg3/docs/basic/from_pg2.html#server-side-binding);
as a consequence, the DEFAULT value cannot be passed as a query
argument, because it is an SQL construct, not a value.
You can force client-side binding (like psycopg2 does) by using a
ClientCursor instead of the ordinary Cursor class
(https://www.psycopg.org/psycopg3/docs/advanced/cursors.html#client-side-binding-cursors).
The ClientCursor will call the `quote()` method on the dumper
(https://www.psycopg.org/psycopg3/docs/api/abc.html#psycopg.abc.Dumper.quote),
which should return the value's literal (so, a normal SQL literal
would need to be single-quoted; the DEFAULT singleton doesn't want
quotes as it's not a value).
So, your attempt was always right: your dumper should look like:
class PostgresDefaultValueTypeDumper(Dumper):
def dump(self, obj):
raise NotImplementedError("can't represent DEFAULT as
server-side value")
def quote(self, obj):
return b"DEFAULT"
but you can only use `PostgresDefaultValueType` with a ClientCursor
(or in a `sql.Literal()`); you can visualize the query that a client
cursor would execute using the classic `mogrify()`:
>>> cur = psycopg.ClientCursor(conn)
>>> cur.mogrify("INSERT INTO tbl (f1, f2) VALUES (%s, %s)",
[PostgresDefaultValueType(), "hell'o"])
"INSERT INTO tbl (f1, f2) VALUES (DEFAULT, 'hell''o')"
However you need to negotiate with Django the creation of a
client-side cursor. I don't know if Django normally uses, or allows to
use them. It's easy to create one one-off, as you see from the
example, but integration with the Django ORM is... left as exercise
(you may ask Django people about it, they would be more informed than
I am).
The `sql.DEFAULT` object is not designed to be passed as value for a
query. It can only participate in query composition using the
psycopg.sql objects
(https://www.psycopg.org/psycopg3/docs/api/sql.html), but using it
would require coding a query execution in a way much more different
than the normal query, for instance using `{}` placeholders, for
instance:
cur.execute(sql.SQL("INSERT INTO tbl (f1, f2) VALUES ({},
{})").format(sql.DEFAULT, "hell'o"))
I would rather go the ClientCursor way in this case.
Cheers
-- Daniele
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: 2-to-3 Question about adapter using AsIs
2023-11-20 18:57 2-to-3 Question about adapter using AsIs Ams Fwd <[email protected]>
2023-11-20 22:46 ` Re: 2-to-3 Question about adapter using AsIs Daniele Varrazzo <[email protected]>
@ 2023-11-21 01:24 ` Ams Fwd <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Ams Fwd @ 2023-11-21 01:24 UTC (permalink / raw)
To: Daniele Varrazzo <[email protected]>; +Cc: psycopg
Hi.
On Mon, Nov 20, 2023 at 2:46 PM Daniele Varrazzo
<[email protected]> wrote:
So it turns out that the Django default, at least for 4.2 is ClientCursors :)
https://docs.djangoproject.com/en/4.2/ref/databases/#server-side-parameters-binding
Which means I can get the `quote` based solution work ootb.
Thanks a ton for the clarification.
AM
> but you can only use `PostgresDefaultValueType` with a ClientCursor
> (or in a `sql.Literal()`); you can visualize the query that a client
> cursor would execute using the classic `mogrify()`:
>
> >>> cur = psycopg.ClientCursor(conn)
> >>> cur.mogrify("INSERT INTO tbl (f1, f2) VALUES (%s, %s)",
> [PostgresDefaultValueType(), "hell'o"])
> "INSERT INTO tbl (f1, f2) VALUES (DEFAULT, 'hell''o')"
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-11-21 01:24 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-11-20 18:57 2-to-3 Question about adapter using AsIs Ams Fwd <[email protected]>
2023-11-20 22:46 ` Daniele Varrazzo <[email protected]>
2023-11-21 01:24 ` Ams Fwd <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox