MIME-Version: 1.0
References: 
 <CA+mi_8auX2vz6i+E74bxOC7hFcGDQzBAY3GypUBkAK+NveFtfQ@mail.gmail.com>
 <trinity-991ceed6-31a1-43fc-a219-b075ec77d5d5-1605289047367@3c-app-gmx-bs22>
In-Reply-To: 
 <trinity-991ceed6-31a1-43fc-a219-b075ec77d5d5-1605289047367@3c-app-gmx-bs22>
From: Daniele Varrazzo <daniele.varrazzo@gmail.com>
Date: Fri, 13 Nov 2020 19:28:51 +0000
Message-ID: 
 <CA+mi_8Z0zXrySXHMhjzvNnP_WXmucF-NpbO7iRRXqM_po5KQNA@mail.gmail.com>
Subject: Re: First psycopg3 docs
To: Karsten Hilbert <Karsten.Hilbert@gmx.net>
Cc: psycopg@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Precedence: bulk

On Fri, 13 Nov 2020 at 17:37, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:
>
> Hello Daniele,
>
> I see that the Cursor.execute() will continue to support
> a Mapping for passing in values.
>
> Perhaps my understanding is lacking, so here goes:
>
> Will using a mapping still allow for use of the binary
> protocol ?  I am asking because all the examples I've
> seen show the %s (rather than %(varname)s way of
> value passing).

Yes, mapping is still supported, %(name)s parameters are not going to disappear.

>>> cur.execute("select %(p1)s, %(p2)s, %(p1)s", {"p1": "v1", "p2": "v2", "p3": "v3"}).fetchone()

There is a query transformation object that will convert a query with
Python placeholders to postgres placeholders ($1, $2). In case a
mapping is used, it also converts the mapping to a list. Everything
happens behind the scene. Roughly:

    >>> pgq = psycopg3.cursor.PostgresQuery(cur._transformer)
    >>> pgq.convert("select %(p1)s, %(p2)s, %(p1)s", {"p1": "v1",
"p2": "v2", "p3": "v3"})
    >>> pgq.query
    b'select $1, $2, $1'
    >>> pgq.params
    [b'v1', b'v2']

On the way out, binary and text parameters can be selected on a per-value basis:

    >>> pgq.convert("select %(p1)s, %(p2)b, %(p1)s", {"p1": "v1",
"p2": Int4(64), "p3": "v3"})
    >>> pgq.query
    b'select $1, $2, $1'
    >>> pgq.params
    [b'v1', b'\x00\x00\x00@']
    >>> pgq.formats
    [<Format.TEXT: 0>, <Format.BINARY: 1>]

> In GNUmed I nearly always use %(varname)s with dicts
> as that allows for easier collection of values
> before the execute() call without needing to account
> for the *order* of values.

Of course: names placeholders are the handiest way to deal with
parameters. While psycopg3's different way of runnng queries will have
incompatibilities, the intention is not to force everyone to rewrite
the entirety of their codebase :)

-- Daniele