[Tutor] Proper SQLite cursor handling?

[Cameron Simpson]

On 02Jul2021 22:55, boB Stepp <robertvstepp at gmail.com> wrote:
>I have the start of a class to create solitaire game objects:
>
>class SolitaireGame:
>    """Representation of a solitaire game."""
>
>    def __init__(self, db_cursor: sqlite3.Cursor, game_name: str) -> None:
>        """Create or open a solitaire game."""
>        self.cur = db_cursor
>        self.game_name = game_name
>
>My current thought is to create only a single cursor object and use it
>throughout the program's lifetime.  Is this a sensible thing to do?

I'd have thought generally not. Cursors tend to be ephemeral, made for a 
specific operation and then discarded. Usually I would pass in the db 
connection and make cursors at need.  It looks like you cannot commit 
from a cursor.  Well, I guess there is cursor.connection.commit, but 
that's really going back to the connection to do that work.

In fact, it looks like you can do a number of cursor operations directly 
from the connection (a Cursor gets made behind the scenes apparently).  
Eg this example from the sqlite3 module docs:

    con = sqlite3.connect(":memory:")

    # Create the table
    con.execute("create table person(firstname, lastname)")

    # Fill the table
    con.executemany("insert into person(firstname, lastname) values (?, ?)", 
    persons)

    # Print the table contents
    for row in con.execute("select firstname, lastname from person"):
        print(row)

Of course, if you switch dbs to something else that nonstandard approach 
would need to be ported to use cursors anyway...

>In the above class skeleton my intent is to retrieve the game
>information from the database using the passed in cursor.  During the
>course of a program session the user may have any number of different
>solitaire games open, so each of these game objects would have its own
>reference to the _same_ cursor object.  I have an uneasy feeling that
>this might cause issues, but I don't know enough (yet) and am still at
>the pondering point for this class.C

I'd be concerned about methods which call other methods.

Supposing you have a method which performs some query and yields stuff 
during the query by caling fetchone() a lot. And something's consuming 
those and making other method calls - the single cursor is tied up with 
the fetchone() and doing other things with it would be bad or 
nonsensical. I would expect to want a cursor per method call (well, per 
db operation).

Cheers,
Cameron Simpson <cs at cskk.id.au>