Re: Latest developments in psycopg3 - Mailing list psycopg

Hello Denis,

On Mon, 8 Feb 2021 at 12:14, Denis Laxalde <denis.laxalde@dalibo.com> wrote:
> I spent a bit of time looking around this last week.

Thank you very much, that's very appreciated :)


> The first thing I did was to try out psycopg3 on a (small) project I
> maintain (pg_activity). Porting from psycopg2 wasn't too hard:

Nice to know!

> - It seems to me that the conn-info developments work fine (also looked
> at the commits, which look fine as well to me): what needs to be done
> here? Any help needed?

I can't remember what is missing: probably the interface is complete,
it is missing test coverage and documentation. I will review if there
are methods missing: its purpose is to expose pretty much all the info
about the connection that the libpq can give, correctly decoded from
bytes. Complete tests and docs are definitely to be added.


> - There appears to be no card about dictcursor on the project board.
> What's the plan (and priority) for that (if any)?

I forgot to add a point about them, sorry: I added one now. There is
no firm plan for me, but probably a conversation to have, because I
have my own usage patterns for psycopg2, when I am its user, and I
want to be mindful of other use cases too.

In short: yes, I would like to provide alternative record factories.
We can review if the best way is to create cursor subclasses
out-of-the-box or mixins. In psycopg2 there are:

- DictCursor (returns a hybrid object between a sequence and a dictionary)
- RealDictCursor (returns a straight subclass of a dictionary, so it's
not a legit dbapi cursor as it doesn't expose a sequence)
- NamedTupleCursor (what it says on the tin).

Personally I use the NamedTupeCursor most often, as I prefer the dot
notation to access attribute. Named tuples also have a _dict() method
when a dict is needed. If psycopg3 was only my pet project, I would
personally have NamedTuples as the default and only thing supported...
But that's hardly the case.

1) We can provide a feature to select the type of cursor that is not
much dissimilar from psycopg2
2) Do we need DictCursor/RealDictCursor? ISTM that one of the two
would be sufficient? Possibly an object inheriting from a Python dict
instead of emulating it so that e.g. json.dumps()
3) Or, conversely, we could have a Row object providing both attribute
and getattr access, both as index and name:

   row.myattr
   row["myattr"]
   row[1]

4) There are reports of the *DictCursor being slow: the objects in
psycopg2 have hardly been profiled. I would look well at the
performance of the objects created.

I am curious about why people use DictCursor: what are its advantages?
Should we provide such objects only to guarantee a smoother upgrade
path or there are use cases where they are genuinely better than
NamedTuples or a would-be Row object (JSON does come to mind).


> Then, following your idea to implement a django backend, I was wondering
> if doing the same for sqlalchemy would be a good thing to work on now?

Yes, why not. I don't know SQLAlchemy but I assume it would be way
less work to create a SA backend than a Django one.

While on this topic, in case anyone takes a look at the Django backend
and thinks about using or hacking on it: the last commits were done
before I implemented the two-steps dumers; as such there are *way*
more changes than there should be now that Python int are dumped as
int2/int4 if they are smaller than their respective MAX. So the
current state is not representative of how it can look and I expect it
will have many less differences than the psycopg2 backend.


> I'm also curious about the "strictly typed" thing you wrote above. What
> do you have in mind more specifically?

The codebase uses mypy --strict, so it is extensively type-annotated
and of course all the public interfaces are type-annotated too, to
collaborate with projects using static typing. I am also curious about
whether it would be possible to provide types out of the query
results, but I doubt it is.

Which makes me think... 5) PydanticCursor? Or, as a more general case,
implement all the cursor flavours not as subclasses but using a
rowfactory function, and allow people to use their own (e.g. asking a
cursor to create a Pydantic model provided by the user, so that they
can have psycopg3 collaborate with e.g. FastAPI strictly typed
models). Or a Python dataclass...


Thank you for the feedback so far!

-- Daniele