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

[Jon Ribbens]

On 2017-05-30, Ian Kelly <ian.g.kelly at gmail.com> wrote:
> On Tue, May 30, 2017 at 1:27 PM, Jon Ribbens <jon+usenet at unequivocal.eu> wrote:
> A cursor is just a control structure for traversing over a result set.

Exactly - so it makes no sense at all to have one when there is no
result set. It makes even less sense to require one in order to
execute queries that don't even have result sets (e.g. INSERT).

> As to the idea earlier in the thread that "the DB-API concept of
> cursors seems to bear almost no resemblance to the SQL concept of
> cursors", I disagree there as well.

If you explained to someone who was an SQL expert but knew nothing
about Python that you wrote a Python program that did the following:

  (a) Connected to an SQL database
  (b) Created a cursor on the connection (with no query)
  (c) Executed an INSERT on the cursor

Do you think they would recognise that as being similar to their
understanding of SQL cursors?

> Again, the concept of a cursor is just a control structure. The only
> real difference between a DB-API cursor and a SQL cursor (as used
> e.g. in PL/SQL) is that one is native to the database process while
> the other lives in the client.

That's completely untrue. An SQL cursor is a result set and a position
in that result set. A DB-API "cursor" is a database connection (even
though there is something else in DB-API that is also called a
database connection.) You don't need an SQL cursor unless you're doing
something fancy and complicated. You need a DB-API cursor before you
can do anything at all.

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

> Cursor.commit on the other hand would never make sense because you
> can't commit a single cursor.

Indeed - that's my point. The problem isn't that it's
Connection.commit, the problem is that it's Cursor.execute.

> You only can commit the current transactional state of the
> connection as a whole. Maybe that's another reason for having a
> Connection.commit method -- it lets you commit without going to the
> trouble of creating a cursor in the first place

Er, there can't *be* anything to commit unless you've already created  
a "cursor".