[Tutor] Proper SQLite cursor handling?

[Alan Gauld]

On 03/07/2021 04:55, boB Stepp 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?

You might get away with it but in general its a terrible idea. cursors
hold a reference to the result of a query, you may not process all of a
cursors data in one iteration of a method. If another method then
performs another query it will lose the reference to the results from
the first query.

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

At the very least you should have a separate cursor per game and
then its up to you to ensure it never gets clobbered by another
method within the game (maybe have a cursor_busy flag or something.
but its still kind of defeating the purpose of cursors!
Better to pass in a reference to the connection in the init()
and then have methods create cursors as needed.

You can, of course, just always store all of the data in a cursor
in a local  list of tuples using fetchall() but that only works
on small databases. The whole point of cursors is that they avoid
the need to store humungous amounts of data locally!


> this might cause issues, but I don't know enough

Think of the cursor as a pointer into the database result
set (which is, significantly, stored on the database and
not in your application. This is less important with SQLite
but in a client/server DB makes a massive difference!)
The cursor is not that big, it just marks the location
of the current row of interest. But the total data
result set could be gigabytes big... Because it's
potentially so big you would normally process the
result in batches or one row at a time. This could be
in a long running loop(maybe in a thread) or as part
of an idle(or timer) event in an event-driven architecture.

Now, in your specific scenario:
1) Game data tends not to be too big and you can store
   the entire cursor content locally.
2) SQLite databases are a single file and you usually
   have it on the local machine so storage is the same
   wherever you keep it!
3) SQLite can perform cursor operations directly from
   the connection so you probably don't even need a cursor
   at all if you plan on reading all the data each time.

So, in general, shared cursors are bad but in your
specific case you probably don't even need one, let
alone a shared one!

-- 
Alan G
Author of the Learn to Program web site
http://www.alan-g.me.uk/
http://www.amazon.com/author/alan_gauld
Follow my photo-blog on Flickr at:
http://www.flickr.com/photos/alangauldphotos