Re: Executing on the connection? - Mailing list psycopg
From | Vladimir Ryabtsev |
---|---|
Subject | Re: Executing on the connection? |
Date | |
Msg-id | CAMqTPqk0Ca1Ky1tGbmCLCwQEEmtfUbyVtds8J_MRykg=NPevmA@mail.gmail.com Whole thread Raw |
In response to | Executing on the connection? (Daniele Varrazzo <daniele.varrazzo@gmail.com>) |
List | psycopg |
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
On Wed, 2 Dec 2020 at 03:20, Daniele Varrazzo <daniele.varrazzo@gmail.com> wrote:
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