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

[Ian Kelly]

On Tue, May 30, 2017 at 4:57 PM, Jon Ribbens <jon+usenet at unequivocal.eu> wrote:
> 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).

What if you want to set cursor options before executing the query,
e.g. Cursor.setoutputsize? It doesn't make sense as a Connection
method, but it needs to be called before execute. Then you need to
access the cursor before it has a result set.

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

I'll grant that it's weird that cursors are also used for DML and DDL.
I think though that a SQL expert would understand what is meant when
PEP-249 says "These objects represent a database cursor, which is used
to manage the context of a fetch operation."

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

Per Wikipedia: "In computer science, a database cursor is a control
structure that enables traversal over the records in a database.
Cursors facilitate subsequent processing in conjunction with the
traversal, such as retrieval, addition and removal of database
records. The database cursor characteristic of traversal makes cursors
akin to the programming language concept of iterator."

Yes, SQL cursors are tied to specific result sets. But what you gain
above and beyond the result set by using a cursor is row-by-row
traversal.

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

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

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

Er, you cut off the part of my comment that laments exactly this situation.

But it may be the case that the code doing the commit is only loosely
coupled to the code that modified something and you don't have that
cursor hanging around still, in which case the method saves you the
need to create a *second* cursor in order to issue a "commit"
statement.