[Tutor] Proper SQLite cursor handling?

[boB Stepp]

I have read all of the posts in this thread.  I have been convinced of
the dangers of using a single cursor!

I have spent most of my time pondering the overall structure of my
code.  I have especially been attentive to dn and Alan's discussions
on this.

At the end of this email is the current state of the code.  It is
*not* meant to be in any sort of runnable shape.  However, I think it
more than adequately describes what I am attempting and how I have
structured things.  Comments, suggestions but no thrown rotting
vegetables are welcome!  I am trying to follow the MVC design pattern.
I have not focused at all on the View(s) at this time.  My main focus
has been on the SolitaireGame and Controller classes along with who is
responsible for the database and its access.  The Controller is a bit
hodge-podge, but is meant to coordinate the currently known minimal
functionality I am planning.

Questions:

1)  Am I doing the Controller properly?

2)  You will note that SolitaireGame will detect a new game situation
where the user must enter needed data.  Alan has emphasized that
SolitaireGame is solely responsible for its data.  But getting user
input is normally handled by the Display and Controller.  How do I
"properly" acquire the needed data from the user?

3)  Am I handling the database stuff properly so far?

4)  I haven't made it this far yet, but part of each game will be a
record of each hand played.  The current table "games" will refer to
these tables, one per game.  The fields in each of these tables, named
by "game_name" will be:  hand_number (primary key), date_played,
time_recorded and hand_score.  Of course for every new game this table
will have to be created.  If it exists (not a new game) this data
needs to be acquired by the SolitaireGame object.  I want these game
tables to be able to extract statistical data to help compare various
strategies for playing the solitaire games.  I don't have any specific
questions yet, but wanted to make you aware of this additional
component which I haven't mentioned previously.

5)  I am about to start writing tests and then flesh out the code.
How does one test stuff reliant on databases?  Thoughts that come to
mind are having special test databases accessed by changing the
database path to a testing database directory.  Creating in-memory
databases as a mock.  Something else?

6)  The UI will be the last thing I worry about and will probably
develop iteratively from a command line display to possibly a
curses-based display (Because I want to make use of Alan's book!), and
finally to a tkinter UI.  How does one test UIs?  I can see relatively
easily testing UI-generated function calls, but mimicking mouse clicks
in an automated fashion?  I suppose one could figure out a way to pass
in mouse coordinates and trigger a mouse or keyboard event?

Anyway, awaiting your commentary.

Cheers!
boB Stepp

===========================================================
"""Program to keep track of solitaire scores.

Definitions:
    Foundations:  Target area of four piles arranged by suits.  Object of most
        games is to get as many cards as possible into this area as these cards
        count positively towards one's hand score.
    Reserve:  Most games start with a fixed number of cards in this pile, which
        the player endeavors to deplete, as in most solitaire games any
        remaining cards in this pile count negatively against one's hand score.
    Hand:  One round of a solitaire game.
"""

import sqlite3
from typing import Tuple


def open_db() -> sqlite3.Connection:
    """Create/open database and return connection object."""
    # TODO:  Implement more robust DB_PATH handling.
    DB_PATH = "C:/Projects/solitaire_scorekeeper/solitaire.db"
    con = sqlite3.connect(DB_PATH)

    # Create games table on first use of program.
    cur = con.cursor()
    cur.execute(
        """
        CREATE TABLE IF NOT EXISTS games (
            game_id INTEGER PRIMARY KEY,
            game_name TEXT UNIQUE,
            strategy TEXT,
            num_reserve_cards INTEGER,
            num_foundation_cards INTEGER,
            reserve_card_value INTEGER,
            foundation_card_value INTEGER,
            total_score INTEGER DEFAULT 0
        );
        """
    )
    cur.close()
    con.commit()
    return con


class SolitaireGame:
    """Representation of a solitaire game."""

    def __init__(self, game_name: str, db_connection:
sqlite3.Connection) -> None:
        """Create or open a solitaire game."""
        self.game_name = game_name
        self.con = db_connection
        (
            self.game_id,
            _,  # Already have game_name value.
            self.strategy,
            self.num_reserve_cards,
            self.num_foundation_cards,
            self.reserve_card_value,
            self.foundation_card_value,
            self.total_score,
        ) = self._get_game_data()
        self.max_hand_score = self.num_foundation_cards *
self.foundation_card_value
        self.min_hand_score = self.num_reserve_cards * self.reserve_card_value

    def _get_game_data(self) -> Tuple[int, str, str, int, int, int, int, int]:
        """Retrieve game data from database."""
        cur = self.con.cursor()
        cur.execute(
            f"""
        SELECT * FROM games WHERE game_name = {self.game_name};
        """
        )
        game_data = cur.fetchall()[0]
        if not game_data:
            # Must be new game, so no game_data present.
            # Get user input to initialize new game data.
            # Be sure to write user-inputted data to games table!
            pass

        return game_data


class Controller:
    """Coordinator of actions between Display and SolitaireGame."""

    def __init__(self) -> None:
        """Instantiate controller and initialize database."""
        self.con = open_db()
        self.open_games = {}
        self.active_game = ""

    def new_game(self):
        """Create new solitaire game."""
        """
        # Ask user for game_name: str.
            # Name must be unique.
            # Validate name:  BEWARE MALICIOUS USER INPUT!!!
        # Call SolitaireGame.
        """

    def open_game(self):
        """Open existing solitaire game."""
        # Call SolitaireGame.

    def record_game(self):
        """Record game score."""
        # Call appropriate method of SolitaireGame.

    def exit_program(self):
        """Shut down program safely."""
        # Ensure all db commits done.
        # Ensure db properly closed.


def Application():
    """Run program."""

    controller = Controller()
    # Program event loop.


if __name__ == "__main__":
    app = Application()