Thread: First psycopg3 docs

Hello,

I have started writing down the documentation for psycopg3,
highlighting some of the differences and the new features available in
this new project.

https://www.psycopg.org/psycopg3/docs/

There is a rough cut of the API of the Connection and Cursor objects
and their asyncio counterparts, and some description about the new
`cursor.copy()` feature, which now interacts with Python objects, not
only with file objects as in psycopg2, and the new ways to receive
asynchronous notifications.

https://www.psycopg.org/psycopg3/docs/usage.html#copy
https://www.psycopg.org/psycopg3/docs/usage.html#asynchronous-notifications

As you probably know, I started this project a few months ago, with
the intention of crowdfunding its development. I am very grateful with
the people who have sponsored the project so far, especially because
in the previous months I had other projects to work on too, and
psycopg3 had to simmer on the back burners for some time.

A few weeks ago I finished the contract which was keeping me busy and
now I have resources to develop psycopg3 as my main activity: I am
planning to keep it this way at least for a few other months, which
should be enough to release the project. It is not my plan to keep
this as a one-man show, so if someone would like to contribute to the
project you are more than welcome.

If you are a professional using Python and PostgreSQL and would like
to enjoy a new generation of Psycopg to work with, or if you want to
make sure that the new project will cater for your needs, your
sponsorship of the project will be invaluable: you can read more at
https://www.psycopg.org/psycopg3/

Thank you very much!

-- Daniele

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).

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.

Karsten

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