On Tue, May 30, 2017 at 1:27 PM, Jon Ribbens <jon+use...@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?) -- https://mail.python.org/mailman/listinfo/python-list
