public inbox for [email protected]  

help / color / mirror / Atom feed

From: Daniele Varrazzo <[email protected]>
To: Karsten Hilbert <[email protected]>
Cc: [email protected]
Subject: Re: First psycopg3 docs
Date: Fri, 13 Nov 2020 19:28:51 +0000
Message-ID: <CA+mi_8Z0zXrySXHMhjzvNnP_WXmucF-NpbO7iRRXqM_po5KQNA@mail.gmail.com> (raw)
In-Reply-To: <trinity-991ceed6-31a1-43fc-a219-b075ec77d5d5-1605289047367@3c-app-gmx-bs22>
References: <CA+mi_8auX2vz6i+E74bxOC7hFcGDQzBAY3GypUBkAK+NveFtfQ@mail.gmail.com>
	<trinity-991ceed6-31a1-43fc-a219-b075ec77d5d5-1605289047367@3c-app-gmx-bs22>

On Fri, 13 Nov 2020 at 17:37, Karsten Hilbert <[email protected]> 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

view thread (3+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: First psycopg3 docs
  In-Reply-To: <CA+mi_8Z0zXrySXHMhjzvNnP_WXmucF-NpbO7iRRXqM_po5KQNA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox