Python DB API - commit() v. execute("commit transaction")?

[Ian Kelly]

On Wed, May 31, 2017 at 4:26 AM, Jon Ribbens <jon+usenet at unequivocal.eu> wrote:
> On 2017-05-31, Ian Kelly <ian.g.kelly at gmail.com> wrote:
>> On Tue, May 30, 2017 at 4:57 PM, Jon Ribbens <jon+usenet at unequivocal.eu> wrote:
>>> A DB-API "cursor" is a database connection
>>
>> Baloney. Creating a cursor does not spawn a new connection to the
>> database. Cursors created from a connection share that connection.
>
> Baloney yourself - I didn't say it was a *new* connection. In DB-API,
> a Connection is basically nothing - you can do nothing at all with it.
> A Cursor is actually what any other language would call a connection.
> If you say that this description is confusing connections and cursors,
> then you have understood my point - that's exactly what DB-API is doing.

I don't understand what you mean by "connection", then. You seem to be
defining it as "something that executes queries". I would instead
define it as "something responsible for managing the low-level state
of communication with the database server". There's no particular
reason why that should include the higher-level concept of managing
query execution. APIs that do this are just conflating concepts in a
different way.

I tend to think of DB-API cursors as being more analogous with DBI
(Perl) or ODBC statement handles. DBI lets you call conn.execute
directly, but more normally you call conn.prepare(query) which does
some allocations and maybe parses the query in advance and gives you a
statement handle, followed by stmt.execute(params) which actually
executes the query. You then call fetchone, etc. from the statement
handle, not the connection. It's not exactly like DB-API because the
cursor doesn't use a prepared statement and might be reused for
different statements, but it has a similar feel to it.

>>> No other language's database API works this way - they all work by
>>> executing queries on the connection (I checked Go, Java, PHP, Ruby,
>>> Perl).
>>
>> So Python is unique in this regard. What of it?
>
> So it's a big hint that Python is wrong, if it takes terminology used
> by databases and all other languages, and uses that same terminology
> to mean something that no other language uses it to mean. Having your
> own quirky API for a standard function is generally not a good thing,
> especially when the quirky API has no advantages whatsoever.

I think we'll just have to agree to disagree about whether Python's
usage of the terms "connection" and "cursor" are correct.

> Yes, this is indeed a problem with DB-API - you have to keep *two*
> objects around all the time (the connection and the cursor) and pass
> them to functions, etc, when in any sensible system as used by all
> other languages you would only need to pass the connection.

Either a function needs the connection and will create its own cursor
if necessary, or a function just works with the result of a query and
only needs the cursor. The only reason I can think of to pass both is
if the function is going to make additional queries based on a result
set, but in that case you would still need to clearly separate the
connection from the input data, one way or another. Regardless, I
can't recall that I've ever found this burdensome.