Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kcI7B-0000c9-Qn for psycopg@arkaria.postgresql.org; Tue, 10 Nov 2020 01:06:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kcI7A-0003qa-MP for psycopg@arkaria.postgresql.org; Tue, 10 Nov 2020 01:06:28 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kcI7A-0003qS-9t for psycopg@lists.postgresql.org; Tue, 10 Nov 2020 01:06:28 +0000 Received: from mail-ua1-x934.google.com ([2607:f8b0:4864:20::934]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kcI77-00025z-SA for psycopg@lists.postgresql.org; Tue, 10 Nov 2020 01:06:27 +0000 Received: by mail-ua1-x934.google.com with SMTP id g3so1775575uae.7 for ; Mon, 09 Nov 2020 17:06:25 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=JF+V81HsAUHHQJ733/I4uJFwS3YqS3sQ4k9ZBJI1nV0=; b=NXsmdGNArUaAF+QK95HMQ+4YTmAjSCF6xoXJXhOdy9CiMXjUGtlGk89ChQVlJGrPl1 iYrADbE/S8iZoQjGNUA17tm42gdXx/nspyShVm1Q4O7u8I/XH3jjfsQl1loLJ7kE/prc 8WqxYFAjdV7TECORznKfisrAjyIV5yFfpz1VWmdLYlp+3dUjqTlKfjX451I/N6bLfgKx WuyUDK4NHLX9hB5cr9RmvXr3vg6ZWKD46LX3sFzr0y3gT8hV1gky8P907Yu8kfzZ05Me rwesZ9JJISNgV2e8Y3teHMACkWWspEsqkahDYgwN+nOZIpERrhRD0nzTq2rtJjx7RNSB 4/dQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=JF+V81HsAUHHQJ733/I4uJFwS3YqS3sQ4k9ZBJI1nV0=; b=nRSmXoqcT2TfqWz7/2LGr8nX5pcB1SD/xrTqEUM6UlE4As1UIR4L+xIml06xcGLUnf /Zx9FjhELGAw2SCQz5ootxIlvM8CuhLEREM6UU0gVkCXG4MCV/yuzD6DSj7WKsW+qp1S DkXeLa+C6KWfS8nX/ktyZGC2ddv3yoeZ+sRgUq2Bt0rEorosSp8Hh1oeplfe2jlGAod2 RrwKif7wDFPH9WOPD3v5G/Q50xfYjX/DS0FdEgIpC7pOENfRYFexl7xm8+NBsg1YQtOo Eo3HrftuBN6D5ZgvsmBmom+k30Q11PsnnxYPfu/rRNABOUQBKNjvFHiBYFKkYy7k5vHD L5tQ== X-Gm-Message-State: AOAM53037U9MLyloKbjD+OXcsIIWvJRMENWqhFulokSAg5YqmWwwSSkw i11zOR99v3dsN5bSvXumhKmJvc8JHOYsjqhUMQn436gwiwY8QA== X-Google-Smtp-Source: ABdhPJz7A99nRHdeOyRTBQPt2zYslw9KlTGJwmmYVsnqqFVUp6cr1gkPyzYhPoQTUG9zciK7LfSR64a+G2noOJtVuuQ= X-Received: by 2002:ab0:7846:: with SMTP id y6mr8354750uaq.16.1604970384647; Mon, 09 Nov 2020 17:06:24 -0800 (PST) MIME-Version: 1.0 References: <2b9859f0-0964-2baa-b6bc-13f975ae0f67@aklaver.com> <88fb7a7e-a182-a816-c1a7-8a1f54b65215@aklaver.com> <4830fb8d-fa57-e0f8-0e4f-a96ed040dede@dndg.it> <1546497c-ff41-9bd0-b4d7-931bd305caac@dndg.it> In-Reply-To: <1546497c-ff41-9bd0-b4d7-931bd305caac@dndg.it> From: Vladimir Ryabtsev Date: Mon, 9 Nov 2020 17:06:13 -0800 Message-ID: Subject: Re: psycopg3 and adaptation choices To: Federico Di Gregorio Cc: psycopg@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000a6e91005b3b649d9" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --000000000000a6e91005b3b649d9 Content-Type: text/plain; charset="UTF-8" > I wouldn't want to step away from the %s placeholder ... Thanks for the elaboration, I agree with your argumentation. > If there is interest we can think about how to make this querying layer > more accessible (e.g. using a `cur.execute(PgQuery("select $1, $2"), [...])` It's always good if such customization is accessible. Having said that, I can't think of a really good example that benefits from that rather than from standard use, but basically the more possibilities the better. > In psycopg3 I've made it slightly easier to use by letting > `SQL.format()` to accept any Python object Great, thanks! May I ask you again about using 'unknown' for numbers? Could you recap all the downsides of this approach? Vladimir On Mon, 9 Nov 2020 at 04:05, Federico Di Gregorio wrote: > On 09/11/20 13:00, Daniele Varrazzo wrote: > > On Mon, 9 Nov 2020 at 06:57, Federico Di Gregorio wrote: > [snip] > >> IMHO, oid is a bad idea > >> because it has a very specific semantic and the error messages generated > >> by PostgreSQL will be more confusing. > > > > I'm not sure I understand this. At the moment, the oids are something > > that don't really surface to the end-users, who are not required to > > use them explicitly and shouldn't be seen in the error messages. For > > instance the query above might results in a call: > > > > >>> from psycopg3.oids import builtins > > >>> builtins["numeric"].oid > > 1700 > > > > >>> res = conn.pgconn.exec_params(b"select '[]'::jsonb -> $1", > > [b"1"], [1700]) > > >>> res.status > > > > > > >>> print(res.error_message.decode("utf8")) > > ERROR: operator does not exist: jsonb -> numeric > > LINE 1: select '[]'::jsonb -> $1 > > ^ > > HINT: No operator matches the given name and argument types. You > > might need to add explicit type casts. > > > > So the oid is only used internally, in the mapping python type -> > > exec_params() types array, the 1700 shouldn't surface anywhere. > > > > Maybe I'm misunderstanding your concern: can you tell me better? > > My fault. I misread and though you wanted to use OID as *the* type to > pass to PostggreSQL for numbers. > > federico > > > > --000000000000a6e91005b3b649d9 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
> I wouldn't want to step away from the %s placehol= der ...

Thanks for the elaboration, I agree with your argumentation= .

> If there is interest we can think about how to ma= ke this querying layer
> more accessible (e.g. using a `cur.ex= ecute(PgQuery("select $1, $2"), [...])`

= It's always good if such customization is accessible. Having said that,=
I can't think of=C2=A0a=C2=A0really good=C2=A0example that= =C2=A0benefits from that rather than
from standard use,=C2=A0but = basically the more possibilities the better.

> = In psycopg3 I've made it slightly easier to use by letting
> `S= QL.format()` to accept any Python object

Great, th= anks!

May I ask you again about using 'unknown= ' for numbers? Could you recap
all the downsides of this appr= oach?

Vladimir


On Mon, 9 Nov 2= 020 at 04:05, Federico Di Gregorio <fog@d= ndg.it> wrote:
On 09/11/20 13:00, Daniele Varrazzo wrote:
> On Mon, 9 Nov 2020 at 06:57, Federico Di Gregorio <fog@dndg.it> wrote:
[snip]
>> IMHO, oid is a bad idea
>> because it has a very specific semantic and the error messages gen= erated
>> by PostgreSQL will be more confusing.
>
> I'm not sure I understand this. At the moment, the oids are someth= ing
> that don't really surface to the end-users, who are not required t= o
> use them explicitly and shouldn't be seen in the error messages. F= or
> instance the query above might results in a call:
>
>=C2=A0 =C2=A0 =C2=A0 >>> from psycopg3.oids import builtins >=C2=A0 =C2=A0 =C2=A0 >>> builtins["numeric"].oid
>=C2=A0 =C2=A0 =C2=A0 1700
>
>=C2=A0 =C2=A0 =C2=A0 >>> res =3D conn.pgconn.exec_params(b&quo= t;select '[]'::jsonb -> $1",
> [b"1"], [1700])
>=C2=A0 =C2=A0 =C2=A0 >>> res.status
>=C2=A0 =C2=A0 =C2=A0 <ExecStatus.FATAL_ERROR: 7>
>
>=C2=A0 =C2=A0 =C2=A0 >>> print(res.error_message.decode("= utf8"))
>=C2=A0 =C2=A0 =C2=A0 ERROR:=C2=A0 operator does not exist: jsonb -> = numeric
>=C2=A0 =C2=A0 =C2=A0 LINE 1: select '[]'::jsonb -> $1
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ^
>=C2=A0 =C2=A0 =C2=A0 HINT:=C2=A0 No operator matches the given name and= argument types. You
> might need to add explicit type casts.
>
> So the oid is only used internally, in the mapping python type -> > exec_params() types array, the 1700 shouldn't surface anywhere. >
> Maybe I'm misunderstanding your concern: can you tell me better?
My fault. I misread and though you wanted to use OID as *the* type to
pass to PostggreSQL for numbers.

federico



--000000000000a6e91005b3b649d9--