On Tue, May 30, 2017 at 4:57 PM, Jon Ribbens <jon+use...@unequivocal.eu> wrote: > On 2017-05-30, Ian Kelly <ian.g.ke...@gmail.com> wrote: >> On Tue, May 30, 2017 at 1:27 PM, Jon Ribbens <jon+use...@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. -- https://mail.python.org/mailman/listinfo/python-list
