Subject: Executing on the connection?

From: Daniele Varrazzo <[email-masked]>
Date: Wed, 2 Dec 2020 11:20:16 +0000

Hello,

if there is a thing that people approaching psycopg find confusing is
the connection/cursor duality. The connection object as a wrapper for
the database connection is clear to understand for anyone who has used
psql. But the cursor as a means to give command is spurious: you can
give commands but all the cursors are serialised, not parallelised, on
the same connection, and it isn't useful for transactions either...

The only unique thing a cursor has is to hold a result and consume it;
for the rest it doesn't own neither the postgres session nor the
transaction. But it gives them command. Weird.

You are surely familiar with the psycopg2 usage pattern:

    conn = psycopg2.connect(dsn)
    cur = conn.cursor()
    cur.execute(query, params):
    for record in cur:
        ... # do something

The cursor() can take parameters, e.g. to create server-side "named"
cursors, but most often people will use the standard client-side
cursor, which is a lightweight object, little more than a wrapper for
a PGresult.

One little change I've made to psycopg3 cursors is to make it return
"self" on execute() (it currently returns None, so it's totally
unused). This allows chaining a fetch operation right after execute,
so the pattern above can be reduced to:

    conn = psycopg3.connect(dsn)
    cur = conn.cursor()
    record = cur.execute(query, params).fetchone()
    # or
    for record in cur.execute(query, params):
        ... # do something

And of course nastymess such as:

    conn.cursor().execute(query).fetchone()
    psycopg3.connect(dsn).cursor().execute(query).fetchone()

But, taste.

I'm toying with the idea of adding a 'connection.execute(query,
[params])' methd, which would basically just create a cursor
internally, query on it, and return it. No parameter could be passed
to the cursor() call, so it could only create the most standard,
client-side cursor (or whatever the default for the connection is, if
there is some form of cursor_factory, which hasn't been implemented in
psycopg3 yet). For anything more fancy, cursor() should be called
explicitly.

As a result people could use:

    conn = psycopg3.connect(dsn)
    record = conn.execute(query, params).fetchone()
    # or
    for record in conn.execute(query, params):
        ... # do something

No other methods bloating the connection interface: no executemany(),
copy(), callproc (actually there will be no callproc at all in
psycopg3: postgres has no fast path for function call and too much
semantics around stored procedure that a single callproc() couldn't
cover).

Being the cursor client-side, its close() doesn't actually do anythin
apart from making it unusable, so just disposing of it without calling
close() is totally safe.

Thoughts?

Cheers!

-- Daniele

---

From: Christophe Pettus <[email-masked]>
Date: Wed, 2 Dec 2020 03:22:28 -0800

> On Dec 2, 2020, at 03:20, Daniele Varrazzo <[email-masked]> wrote:
> unused).

Very nice!

>    for record in conn.execute(query, params):

+1.  I think it would be a handy addition.

--
-- Christophe Pettus
   [email-masked]

---

From: Rory Campbell-Lange <[email-masked]>
Date: Wed, 2 Dec 2020 12:48:07 +0000

>     for record in conn.execute(query, params):

The only issue I see with this sort of approach is the problem of having
to set the search path, which I believe needs to be done for each
cursor where custom search paths are used.

Maybe there can be a with context to allow explicit cursor setup and
teardown around an execute statement if needed, or to provide settings
to the execute function.

> semantics around stored procedure that a single callproc() couldn't

Ah. We presently use callproc a lot, together with NamedTupleConnection,
which works brilliantly for us.

Rory

---

From: Marco Beri <[email-masked]>
Date: Wed, 2 Dec 2020 14:41:43 +0100

> apart from making it unusable, so just disposing of it without calling

I like it a lot!

Ciao.
Marco.

---

From: Daniele Varrazzo <[email-masked]>
Date: Wed, 2 Dec 2020 13:56:31 +0000

> cursor where custom search paths are used.

No, being the search path a property of the session, it is a property
of the connection, and all the cursors on the same connection will
share the same.

The use case suggested is only for the barebone use of connection +
cursor + execute + fetch. I wouldn't advise it as the most generic
usage, only as a shortcut.

> > ...actually there will be no callproc at all in
> > psycopg3...

> which works brilliantly for us.

The problem with callproc is that it has a confusing semantic in
itself: from the DBAPI
<https://www.python.org/dev/peps/pep-0249/#callproc>:

    """
    The result of the call is returned as modified copy of the input sequence.
    Input parameters are left untouched, output and input/output
parameters replaced
    with possibly new values.
    """

which 1) is silly unless you are writing C, 2) in postgres you don't
know if the params are I/O unless you do extra query to query the
catalog. Maybe it's modelled after databases using this precise same
interface, but postgres doesn't.

On top of that, Postgres also provides named parameters, which are
supported in psycopg2, and were in psycopg3. You can see how sprawling
it was to convert named and positional parameters from this commit,
where I finally pulled out the plug
<https://github.com/psycopg/psycopg3/commit/9369a3b3717c12d4a398fe0934994ad19cbc564a#diff-9e33206c8c2b3b18daec4b778457f6fc54cf03754747cd476833048fc374b9e6L364>.

On top of on the top of that, recent PostgreSQL added a new concept of
procedure, different from what is called stored procedure, and which
require CALL instead of SELECT:
<https://www.postgresql.org/docs/13/sql-call.html> Again, without
knowing the catalog, you cannot tell by name which is which, but
people would expect us to know:
<https://github.com/psycopg/psycopg2/issues/1155>.

If you know which parameters are positional and which are named, if
you know whether a stored procedure is a function or a new procedure,
users can just use cursor.execute(): it will be faster than composing
SQL dynamically. Using the newly available fetch*() chaining you can
get results directly from the call, which in psycopg2 needs two lines
of code:

    result = cur.execute("SELECT my_function(%s, 42, foo=%s)", ["bar",
baz]).fetchone()

This is pretty much the most efficient thing that can be done (bar
making it a prepared statement), because the libpq/postgres don't
offer any fast-path for function calls. There is this
<https://www.postgresql.org/docs/current/libpq-fastpath.html>, but
it's deprecatish, non-feature-complete, and requires a pg_catalog
lookup to find the function oid.

Hope this explains the rationale behind dropping callproc (which is an
optional method of the DBAPI so it doesn't make us less
standard-compliant by not implementing it). Feedback is welcome anyway
:)

Cheers,

-- Daniele

---

From: Vladimir Ryabtsev <[email-masked]>
Date: Wed, 2 Dec 2020 06:49:33 -0800

I support this idea. Other drivers have such shortcuts (or even lack
cursor() equivalent at all).

I would even prefer a shorter shortcut, like

conn.fetchone(query, params)

But it breaks parameter consistency at some degree and bloats connection
interface, so it's up to you.

Vladimir

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 2 Dec 2020 07:02:28 -0800

>      for record in conn.execute(query, params):

That is what the sqlite3 module does and I find it handy.

> copy(), callproc (actually there will be no callproc at all in

Just to be clear the above(callproc excepted) or some variation of it 
will still be available off the cursor interface, correct?

-- 
Adrian Klaver
[email-masked]

---

From: Daniele Varrazzo <[email-masked]>
Date: Wed, 2 Dec 2020 15:42:12 +0000

> will still be available off the cursor interface, correct?

Yes, that's correct. The cursor remains with the methods it is known
for. This Connection.execute() would only be the moral equivalent of:

    class Connection:
        def execute(self, query, params=None):
            cur = self.cursor()
            cur.execute(query, params)
            return cur

but nothing would be removed from the cursor interface.

-- Daniele

---

From: Karsten Hilbert <[email-masked]>
Date: Wed, 2 Dec 2020 17:23:01 +0100

> > "self" on execute() (it currently returns None, so it's totally

+1

> >    for record in conn.execute(query, params):

+1

Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

---

From: Daniele Varrazzo <[email-masked]>
Date: Tue, 8 Dec 2020 13:16:24 +0000

> psycopg3 yet). For anything more fancy, cursor() should be called

This is what I've pushed earlier:

https://www.psycopg.org/psycopg3/docs/connection.html#psycopg3.Connection.execute

Feedback is welcome. Cheers!

-- Daniele

---

From: Denis Laxalde <[email-masked]>
Date: Tue, 8 Dec 2020 14:51:06 +0100

> https://www.psycopg.org/psycopg3/docs/connection.html#psycopg3.Connection.execute

It did not strike me earlier, but it looks a bit weird to have 
connection.execute() return a "cursor" to read results while this 
"cursor" can also be used to execute commands. So, perhaps, another 
object, with only the interface for result retrieval would be more 
appropriate?

Otherwise, that's a very nice addition, thanks!

---

From: Adrian Klaver <[email-masked]>
Date: Tue, 8 Dec 2020 06:44:56 -0800

> object, with only the interface for result retrieval would be more 

Why? It is no different from now where you can reuse a cursor.

---

From: Daniele Varrazzo <[email-masked]>
Date: Tue, 8 Dec 2020 14:56:14 +0000

> object, with only the interface for result retrieval would be more

It thought about that, and it would be weird if people used a cursor
for further queries after consuming it, yes. But hey, who are we to
deny them to do that? :D I wouldn't go about creating and maintaining
new objects and interfaces only to limit the possibility.

My idea is that those who are interested in using `conn.execute()` are
exactly the people who don't care about having execute() on a cursor.
I think there's no harm in leaving it there :)

-- Daniele