How does a database 'cursor' work?
A cursor is a moving placement or pointer that indicates a position. English-speakers have used the term with this meaning since the 16th century, for a wide variety of movable or mobile position-markers. wikipedia description
It is supposed to conjure up the image of a cursor in a text editor. It is (in some contexts) a place holder for where the pointer (cursor) is in a given dataset. A row (i.e. a line) is returned with cursor.fetchone() and the cursor is advanced to the beginning of the next row/line.
The cursor abstracts how many rows are currently buffered at a database client. As the cursor nears the end of a buffer, the underlying framework will fetch more content. The defaults are usually a good guess at a good tradeoff between memory allocation, network latency and other factors.
It becomes a question of optimization. Google maps provides a good comparison. The user can pan around and it seems like the whole country map was downloaded to the client, but in reality it is downloading adjacent panels right before you need them.
Having the database client perform this buffering relieves the programmer from having to think about it before optimization is required.