3_11_Log_count_wins.py

# 3 11 LoggingWins.py

"""
You now have all the functions you need to interact with the database to make the game.
What is missing is the logic which controls the flow and applies the rules of the game.

To recap, the rules of the game are:

    A random card is drawn for each player.
    Player 1 picks a statistic, and the values are compared.
      Whoever has the higher value wins that round.
    Whoever wins the round picks the statistic in the next round.
    If the round is tied, both players draw a new card
      and the most recent winner still picks the statistic.

Each player will be running the game separately,
so the first thing it should do is
establish who is player 1 and who is player 2.
"""
""" 3 11
Your assignment is to:
    Modify the game to Update the Result Table with
    1. the Two Cards which were played against each other
    2. and which was the Winner
    Use the data you have stored to provide some interesting statistics
    (e.g. how many times the winning card has been used) after each round
    Document your changes, either as comments in your code or as a separate document
"""

import sqlite3
from random import randint
from time import time

conn = sqlite3.connect("computer_cards.db")


def read_all_cards():
    result = conn.execute("SELECT * FROM computer")
    return result.fetchall()


def pick_card():
    cards = read_all_cards()
    last_picked_card = read_last_picked()
    random_card = cards[randint(0, len(cards) - 1)]
    while random_card[0] == last_picked_card[0]:
        random_card = cards[randint(0, len(cards) - 1)]
    insert_picked(random_card[0])
    return random_card


def insert_picked(name):
    insert_sql = "INSERT INTO picked(name, time) VALUES ('{}', {})".format(name, time())
    conn.execute(insert_sql)
    conn.commit()


def read_last_picked():
    result = conn.execute("SELECT * FROM picked ORDER BY time DESC")
    return result.fetchone()


def winning_result(card1, card2, winning_card):
    insert_sql = ("INSERT INTO result(card1, card2, winning_card) VALUES ('{}', '{}', '{}')"
                  .format(card1, card2, winning_card))

    conn.execute(insert_sql)

    conn.commit()


"""def count_winning_card():
    result = conn.execute("SELECT * FROM result GROUP BY winning_card; winning_card count(*)")
    return result.fetchall()"""


# ___________________ end of setup

#  Main game  Basically from L T's script
player1 = input("Player 1 please enter your name > ")
player2 = input("Player 2 please enter your name > ")
choosing_player = "1"
other_player = "2"
player1_score = 0
player2_score = 0

for round in range(2):  # Default is 5

    input("Press enter to pick a card when both players are ready > ")

    #  Select cards and allow choosing player to select the category
    if choosing_player == "1":
        card1 = pick_card()
        card1_text = ("{}, cores={}, speed={}GHz, RAM={}MB, cost={}$"
                      .format(card1[0], card1[1], card1[2], card1[3], card1[4]))
        print("Player 1 draws card:", card1_text)

        print("Player 1 picks category.")
        category = input('Enter the category: (C)ores, (S)peed, (R)am or (P)rice > ')

        card2 = pick_card()
        card2_text = ("{}, cores={}, speed={}GHz, RAM={}MB, cost={}$"
                      .format(card2[0], card2[1], card2[2], card2[3], card2[4]))
        print("Player 2 card:", card2_text)

    else:
        card2 = pick_card()
        card2_text = ("{}, cores={}, speed={}GHz, RAM={}MB, cost={}$"
                      .format(card2[0], card2[1], card2[2], card2[3], card2[4]))
        print("Player 2 card:", card2_text)

        print("Player 2 pick a category.")
        category = input('Enter the category: (C)ores, (S)peed, (R)am or (P)rice > ')

        card1 = pick_card()
        card1_text = ("{}, cores={}, speed={}GHz, RAM={}MB, cost={}$"
                      .format(card1[0], card1[1], card1[2], card1[3], card1[4]))
        print("Player 1 card:", card1_text)

    #  Check for the winner
    compare = {"C": 1, "S": 2, "R": 3, "P": 4}
    index = compare[category]
    if card1[index] > card2[index]:
        print("Player 1 wins")
        player1_score += 1
        choosing_player = "1"
        winner = card1[0]
        winning_result(card1[0], card2[0], winner)
    elif card2[index] > card1[index]:
        print("Player 2 wins")
        player2_score += 1
        choosing_player = "2"
        winner = card2[0]
        winning_result(card1[0], card2[0], winner)
    else:
        print("It's a draw")


#  Print final scores
print(player1, "scored", player1_score)
print(player2, "scored", player2_score)

#  Count the Winning  Cards
# Count the number of times each winning card occurs.
# ref: https://www.sqlitetutorial.net/sqlite-count-function/
# ref: https://www.fosslinux.com/42798/basics-of-working-with-the-sqlite-databases-in-python.html
cur = conn.cursor()
cur.execute("SELECT winning_card, COUNT(*) "
            "FROM result "
            "GROUP BY winning_card "
            "ORDER BY COUNT(*) DESC")
table = cur.fetchall()
print("Winning card,Count")
for i in table:
    print(i)

cur.close()
conn.commit()
conn.close()


""">>>
Player 2 wins
A scored 0
B scored 2

Winning card,Count
('Raspberry Pi 4 Model B 2GB', 6)
('Raspberry Pi 4 Model B 1GB', 5)
('Raspberry Pi 4 Model B 4GB', 4)
('Raspberry Pi 3 Model B+', 4)
('Raspberry Pi 3 Model B', 3)
('Raspberry Pi 3 Model A+', 2)
('Raspberry Pi 2 Model B', 2)
('Raspberry Pi Zero W', 1)
('Raspberry Pi 1 Model B 256MB', 1)

"""