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

[Ian Kelly]

On Tue, May 30, 2017 at 1:27 PM, Jon Ribbens <jon+usenet at unequivocal.eu> wrote:
> Indeed. As I say, I think perhaps the source of the confusion is that
> DB-API cursors make no sense at all, rather than the problem being
> transactions per se.
>
> It's almost as if DB-API was written by someone who had never seen
> a database. 'execute' being a method of the cursor rather than the
> connection is a logical impossibility - cursors are something that
> are returned by SQL execution, not the other way around.

A cursor is just a control structure for traversing over a result set.
Whether you call execute and it creates and returns a cursor, or you
create the cursor first and then bind it to a result set by calling
execute (like the DB-API) seems no more meaningful to me than asking
which should be created first: the model or the view. Personally I
prefer the DB-API approach because it makes it easier to create cursor
subclasses and potentially allows for reuse of buffers.

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

> If DB-API had Connection.execute, then Connection.commit wouldn't
> be strange. Cursor.execute is bizarre, and it being paired with
> Connection.commit is just icing on the bizarre cake.

Cursor.execute is fine since DB-API cursors are bound after
construction. The alternative would be to have Connection.execute
return a handle to the result set and then calling something like
Cursor.bind to attach it.

Cursor.commit on the other hand would never make sense because you
can't commit a single cursor. 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
(but then, why not have Connection.insert or Connection.update for the
same purpose?)