Re: psycopg3 and adaptation choices

Hello,

From what I understood from your messages, I like passing 'unknown' for
both strings and numbers.
Roundtripping parameters seems to be a less common case (with a possible
fix if it's necessary).
Is there anything else that does not work or works counterintuitively with
Python 'int' -> 'unknown'?

BTW, may I ask another question regarding parameters?
Don't you want to step away from '%s' syntax and use '$1, $2, ...' which
seems to be more traditional in the database world?
'%s' feels like old-school string formatting, new server-side parameter
binding may want to give some new impression.
Moreover, it appears more convenient when you have parameters numbered and
can reuse them a few times in a query.

Vladimir

On Sun, 8 Nov 2020 at 14:22, Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com>
wrote:

> On Sun, 8 Nov 2020 at 20:35, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
> wrote:
>
> > Alright I understand now.
> > More below.
> >
> > >
> > > In psycopg3 the idea is to use a more advanced protocol, which
> > > separates query and parameters. It brings several benefits: can use
> > > prepared statements (send a query once, several parameters later),
> > > passing large data doesn't bloat the parser (the params don't hit the
> > > lexer/parser), can use binary format (useful to pass large binary
> > > blobs without escaping them in a textual form), the format of the data
> > > is more homogeneous (no need to quoting), so we can use Python objects
> > > in COPY instead of limiting the interface for the copy functions to
> > > file-like objects only.
> > >
> > > Both in psycopg2 and 3 there is an adaptation from Python types to
> > > Postgres string representation. In pg2 there is additional quoting,
> > > because apart from numbers and bools you need to quote a literal
> > > string to merge it to the query and make it syntactically valid.
> >
> > So the issue in the psycopg3 protocol is making the parameters that are
> > passed in separately match up correctly in type to what the server is
> > expecting(or can cast implicitly)?
>
> Yes, correct. What we have to choose is which Postgres oid to map to
> each Python type.
>
> Sometimes the mapping is trivial (e.g. `datetime.date` -> `date` in
> Postgres, `uuid.UUID` -> `uuid`...)
>
> Sometimes it might be ambiguous: is a `datetime.datetime` a
> `timestamp` or a `timestamptz`? In some cases we don't care (here we
> can say `timestamptz` no problem: if the Python datetime doesn't have
> tzinfo, Postgres will use the `TimeZone` setting).
>
> Sometimes it's messy: what Python type corresponds to a Postgres
> `jsonb`? It might be a dict, or a list, or types that have other
> representations too (numbers, strings, bools). In this case, as in
> psycopg2, there can be a wrapper, e.g. `Json`, to tell psycopg that
> this dict, or list, or whatever else, must be jsonified for the db.
>
> When there are mismatches, sometimes the database cast rules help
> (e.gi in the timestamp[tz] case). Sometimes not: if we say `text` to a
> jsonb field, it will raise an error. Sometimes a cast is automatic on
> inserting in a table but not on passing a function parameter.
>
> Numbers are messy, as they usually are: Python has int, float,
> Decimal, Postgres has int2, int4, int8, float4, float8, numeric. The
> mappings float -> float8 and Decimal -> numeric are more or less
> straightforward. `int` is not, as in Python it's unbounded. If you say
> `select 10` in psql, the server understands "unknown type, but a
> number", and can try if either int* or numeric fit the context. But we
> don't have the help from the syntax that psql has: because 10 doesn't
> have quotes, Postgres is sure that it is a number, and not a string,
> but executing query/params separately we lose that expressivity: we
> cannot quote the strings and not the number. So choices are:
>
> 1. If we specify `numeric` or `int8` as oid, inserting in an int field
> in a table will work ok, but some functions/operators won't (e.g. "1
> >> %s").
> 2. If we specify `int4` it would work for those few functions defined
> as `integer`, but if we try to write a number that doesn't fit in 32
> bits into a Postgres bigint field I assume something will overflow
> along the way, even if both python and postgres can handle it.
> 3. If we specify `unknown` it might work more often, but
> `cursor.execute("select %s", [10]) will return the string "10" instead
> of a number.
>
> So I wonder what's the best compromise to do here: the less bad seems
> 1. 3. might work in more contexts, but it's a very counterintuitive
> behaviour, and roundtripping other objects (dates, uuid) works no
> problem: they don't come back as strings.
>
> -- Daniele
>
>
>