pi103assignment

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 insert_result(acard1,acard2,awin):
    insert_sql = "INSERT INTO result(card1,card2,winner) VALUES ('{}', '{}', '{}')".format(acard1[0], acard2[0], awin[0])
    conn.execute(insert_sql)
    conn.commit()

def read_last_picked():
    result = conn.execute("SELECT * FROM picked ORDER BY time DESC")
    return result.fetchone()
def show_card(acard):
    print('''
    |---------------------------------------|
    |{}                |
    |~~~~~~~~~~~~~~~~~~~~~~~~~~--------------|
    |   Cores     {}                           |
    |   CPU Speed {} Ghz                     |
    |   Ram       {} Mb                    |
    |   cost    $ {} bn                      |
    |                                           |
    +-------------------------------------------|
    '''.format(acard[0], acard[1], acard[2], acard[3], acard[4]))



rounds=int(input('how many round do you want to play?'))
p1_name=input('Enter the first players name ')
p2_name=input('Enter the second players name ')
last_winner=1 #set so player 1 goes first

for i in range(rounds):
    if last_winner == 1:
        fplayer_name=p1_name
        splayer_name=p2_name
    else :
        fplayer_name=p2_name
        splayer_name=p1_name
    print('{} it is you go now '.format(fplayer_name))
    card = pick_card()
    print('\n',fplayer_name,' your card ')
    show_card(card)
    try:
        field_picked=int(input('Enter the number of the parameter you want to  play! \n\t(1) #Cores, \n\t(2) CPU Speed, \n\t(3) Ram, \n\t(4) Cost \n\t>'))
    except:
        field_picked=0
    while field_picked < 1 or field_picked > 5:
        try:
            field_picked=int(input('Choose the parameter you want to  play! \n\t(1) #Cores, \n\t(2) CPU Speed, \n\t(3) Ram, \n\t(4) Cost \n\t>'))
        except:
            field_picked=0
    card2 = pick_card()
    print('\n',splayer_name, ' your card')
    show_card(card2)
    if card[field_picked] > card2[field_picked] :
        print(fplayer_name,' You win ')
        last_winner=1 if last_winner==1 else 0
        insert_result(card,card2,card)
    elif card[field_picked] < card2[field_picked] :
        print(fplayer_name,' You lose ')
        last_winner=0 if last_winner==1 else 1
        insert_result(card,card2,card2)
    else :
        print('Draw - Nobody wins')
    input('Press enter to continue\n-------------------\n')
##Now produce some statistics
##We select and group the cards in the 'winner' field of the result table
print('\n\n\nAnalysis of Winning Cards')
print('CARD \t\t\t\tWINS')
result = conn.execute("SELECT winner, COUNT(winner)  AS CN FROM result GROUP BY winner ORDER BY CN DESC")
results=result.fetchall()
for res in results:
    print(res[0],'   \tWon ',res[1], ' times')
print('There were ',len(results), ' Winning cards')
result = conn.execute("SELECT COUNT(winner) FROM result")
results=result.fetchone()

print('Out of ',results[0], 'wins')
conn.close()