[Tutor] Proper SQLite cursor handling?

[boB Stepp]

On Wed, Jul 7, 2021 at 8:52 PM Dennis Lee Bieber <wlfraed at ix.netcom.com> wrote:
>
> On Thu, 8 Jul 2021 00:43:01 +0100, Alan Gauld via Tutor <tutor at python.org>
> declaimed the following:
>
> >On 06/07/2021 10:06, Alan Gauld via Tutor wrote:

[...]

> >I've been thinking about this and am unclear about the strategy business.

[...]

> >There are many ambiguities in the specification. But
> >those need to be resolved before the database schema
> >design can be completed.
>
>         Concur: we have just words to build off now (hand, game, strategy),
> with no proper description of just what each entails. As I recall, "hand"
> has a datetime for completion, with some score.

[...]

Guys, I *do* know you are trying to be helpful, but I am beginning to
feel frustrated with the direction this thread has taken.  I am sure I
bear the brunt of this, as something I thought was rather simple in
concept has become something else.  I thought my communications skills
were better than this, but apparently I have really fumbled the ball
on this thread.

I will endeavor to try again and hopefully do a better job of communication.

First, I have "meta"-program goals:

1)  Always to learn useful things.

2)  More specifically to take a relatively simple concept -- but not
too simple! -- and use it as a learning tool to better understand best
software construction practices.

3)  Amongst the things I wish to better understand are database usage
and design, OOP, applying MVC to a program's structure effectively,
and, once I get there, learn tkinter and UI design better, and so on.

It has often been said on this list that a good way to learn Python
and other programming concepts, it is a good idea to pick a project
that interests one and try to implement it.  While reading technical
stuff, I like to break up the study by playing various forms of
solitaire.  As Dennis notes there are many, ... , many forms of
solitaire.  The ones I enjoy playing are open-ended and have scoring
systems.  Also there is flexibility in the rules, so that, for
instance, I don't have to play cards to the foundations area unless I
feel it is beneficial.  Or I can instead focus on building long
sequences of cards in the build area, or, whatever.  The games I play
do not have a single pass through the deck, but allow one to turn the
cards over and go through them again and again until there are no
legal plays left.  But regardless of what the rules are -- for the
program the rules don't matter -- each play of a hand results in a
score.  The reserve cards left subtract from the hand score by a
certain number of points per card left and the foundation cards add
points positively to the hand score by a certain number of points per
card there.

The name of the program captures its purpose:  Solitaire Scorekeeper.
The summary line of the module as originally posted read:  "Program to
keep track of solitaire scores."  It is that simple in concept.  The
program does not care what the rules are, what the actual cards are,
or how they are played.  The particular solitaire game and its rules
being played only matter in that it might help in giving a sensible
name to the game to differentiate it from other games I might be
playing and scoring.

To make it more interesting as a programming project I wish to collect
other data, including a desire to see how effective different
approaches to playing a particular solitaire game are to getting the
best possible cumulative score over time.

So, to keep things simple, say I am playing a solitaire called
boB_Solitaire.  It doesn't matter what its rules are.  It has a
scoring system.  I want to try two naive strategies and see how the
cumulative scores tend to play out:  (1) Do everything possible to
play cards to the foundations area in preference to any other possible
play available.  This will increase the positive score for the hand.
Say each foundation card is worth +1 point for a total maximum of 52
points if I am fortunate and get the entire deck of cards to that area
(no jokers please).  Or naive strategy (2) do everything possible to
reduce the number of cards in the reserve pile.  Each card left in the
reserve pile counts -2 points per card.  The pile starts out with 13
cards in it, so the max possible negative score would be -26.
Therefore, for this particular solitaire game and scoring system each
hand at the end will have a possible score ranging from -26 to +52,
inclusive.

In this scenario I would have two solitaire games I'm tracking over
time, boB_Solitaire1 and boB_Solitaire2.  They both use the same exact
set of rules of play, but the first uses naive strategy (1) and the
second naive strategy (2).  So for the purposes of the data about each
game, *not* the record of hands played I would want to have in a
"games" table:

In this example there would be two entries in the games table, one row
for boB_Solitaire1, one for boB_Solitaire2.

The columns would be:

A primary key field.

Name of the game -- here boB_Solitaire1 or boB_Solitaire2.

A text field for "strategy":
    For boB_Solitaire1 it would be something like:  "Play to maximize
number of cards to the foundations area."
    For boB_Solitaire2 it would be something like:  "Play to minimize
the number of cards left in the reserve pile."

An integer field for max_num_of_reserve_cards:  Some kinds of
solitaire have differing numbers of starting cards in the reserve pile
than others.  This is needed to compute scores for each hand.

An integer field for max_num_of_foundation_cards:  Often this will be
52 for a normal pack of cards without jokers being used, but some
games use multiple packs of cards or even limit to less than a pack of
cards to the foundation areas.  Again, this is needed to compute a
hand score.

An integer field for reserve_card_value:  Usually a negative integer.
Again, the exact value for a given game's rules may vary game to game.
Needed to compute a hand score.

An integer field for foundation_card_value:  Usually a positive
integer.  Exact value per card depends on a game's rules.  Needed to
compute a hand score.

A computed integer field for the cumulative score for a game.  Yes, it
does not have to be in the table, but could be computed on demand, but
I am stating things as originally presented in the code.

A date field for the date the game was originally started/created.  I
did not mention this previously, but intended all along to have it
recorded.

As you (Dennis in particular) demonstrated, I only need one more table
to record the scores for all hands of all games played.  It would have
these columns:

A primary key field.

A foreign key field referencing the games table and to which row/game
the hand score belongs to.

A date field to record the date the hand score was recorded.

A time field to record the time that the hand was recorded.

The actual hand score for the hand played.

That's it.  Two tables.  Later, once the code to implement all of the
above is done, I could explore analyzing the collected data.  I might
look at the average score per hand.  I might look at the distribution
of possible scores for a hand and perhaps generate a bar graph for the
results.  Use such data to make calls on which strategies appear to do
best for a given kind of solitaire game.  Whatever.  But the program
under discussion is as simple as I outlined above.  There is no
recording of specific cards or card layouts or ....  Just scores, the
needed information to compute scores, some dates and time for when
played, and simple text descriptions of the strategic approach used.
That's it!

Remember this is simply a vehicle for me to learn with your ever
kindly assistance.  And to better automate something I do anyway
either by hand or in a plain text file.

I hope this clears things up?!?

boB Stepp