Bot Quest with Stats

import sqlite3
from random import randint
from time import time
from time import sleep
import sys


def getConnection():
    #create connection to the database
    print('Creating connection to the Database')
    conn = ''
    try:
        conn = sqlite3.connect('computer_cards.db')
    except Exception as ex:
        print('Failed to establish connection to database - {}'.format(ex))
        conn = ''
    return conn

def closeConnection(conn):
    #close connection to database
    print('Closing connection to the Database')
    try:
        conn.close()
    except Exception as ex:
        print('Error closing connection {}'.format(ex))

def clearTable(conn,table):
    #clear all records from the picked table
    try:
        sql = "DELETE FROM {}".format(table)
        cursor = conn.execute(sql)
        conn.commit()
        print('Rows deleted - {}'.format(cursor.rowcount))
    except Exception as ex:
        print('Failed to clear table - {}'.format(ex))

def getCards(conn):
    #create list of all card names from the computer table
    try:
        result = conn.execute("SELECT * FROM computer")
        return result.fetchall()
    except Exception as ex:
        print('Failed to get table - {}'.format(ex))
        return ''

def getCardNames(conn):
    try:
        result = conn.execute("SELECT name FROM computer")
        return result.fetchall()
    except Exception as ex:
        print('Failed to get table - {}'.format(ex))
        return ''


def displayRecords(conn):
    cursor = conn.execute('SELECT * FROM picked')
    records = cursor.fetchall()
    for record in records:
        print(record)

def pickAnotherCard(conn):
    response = input('Pick Another Card? ').strip().upper()
    if response == 'D':
        displayRecords(conn)
        response = 'Y'
    return (response == 'Y')

def pickCard():
    response = input('Pick Another Card? ').strip().upper()
    return (response == 'Y')

def numberOfRounds():
    rounds = -1
    while rounds < 0:
        try:
            rounds = int(input('Enter rounds to play (3 - 25000): ').strip())
            if rounds < 3 or rounds > 25000:
                print('Invalid Input')
                rounds = -1
        except:
            print('Invalid Input')
            rounds = -1
    return rounds

def getPlayerNames():
    name1 = input('Enter Player Name 1:').strip()
    if name1 == '':
        name1 = 'Player 1'

    name2 = input('Enter Player Name 2:').strip()
    if name2 == '':
        name2 = 'Player 2'
    return name1,name2


def addCard(conn,name):
    #add card to the picked table
    try:
        sql = "INSERT INTO picked(name, time) VALUES ('{}', {})".format(name,time())
        cursor = conn.execute(sql)
        conn.commit()
        print('Added {}'.format(name))
    except Exception as ex:
        print('Failed to enter card {} - {}'.format(name, ex))

def getLastCard(conn):
    try:
        result = conn.execute("SELECT name FROM picked ORDER BY time DESC")
        return result.fetchone()[0]
    except Exception as ex:
        #print('Error getting last card picked, table empty - {}'.format(ex))
        return ''

def getLastCards(conn):
    try:
        result = conn.execute("SELECT name FROM picked ORDER BY time DESC")
        return result.fetchmany()
    except Exception as ex:
        #print('Error getting last card picked, table empty - {}'.format(ex))
        return ''

def buildCardString(card):
    details = ''
    details += '----------' + '\n'
    details += 'Name:' + '\t' + card[0] + '\n'
    details += 'Cores:' + '\t' + str(card[1]) + '\n'
    details += 'Speed:' + '\t' + str(card[2]) + '\n'
    details += 'Ram:' + '\t' + str(card[3]) + '\n'
    details += 'Cost:' + '\t' + str(card[4]) + '\n'
    details += '----------' + '\n'
    return details

def chooseStat():
    response = 0
    while response == 0:
        try:
            response = int(input('Choose Stat (1>Cores  2>Speed  3>Ram  4>Cost): '))
            if response < 1 or response > 4:
                response = 0
        except:
            response = 0
    return response

def addCard(conn,name):
    #add card to the picked table
    try:
        sql = "INSERT INTO picked(name, time) VALUES ('{}', {})".format(name,time())
        cursor = conn.execute(sql)
        conn.commit()
        #print('Added {}'.format(name))
    except Exception as ex:
        print('Failed to enter card {} - {}'.format(name, ex))

def addResult(conn,card1,card2,winner):
    try:
        sql = "INSERT INTO result(card1, card2, winner) VALUES ('{}', '{}', '{}')".format(card1,card2,winner)
        #print(sql)
        cursor = conn.execute(sql)
        conn.commit()
        print('Added Result {}'.format(winner))
    except Exception as ex:
        print('Failed to enter result for {} v {}'.format(card1, card2))

def getRoundResult(stat1,stat2):
    if(stat1 > stat2):
        result = 1
    elif(stat2 > stat1):
        result = 2
    else:
        result = 0
    return result

def roundResult(win1,win2,currentPlayer, stat1,stat2,name1,name2):
    if(stat1 > stat2):
        win1 += 1
        currentPlayer = 1
        print('*** PLAYER {} WON ***'.format(name1))
    elif(stat2 > stat1):
        win2 += 1
        currentPlayer = 2
        print('*** PLAYER {} WON ***'.format(name2))
    else:
        print('*** DRAW ***')
    print('*** CURRENT SCORE {} v {} ***'.format(win1,win2) + '\n')
    return win1,win2,currentPlayer

def finalResult(win1,win2,name1,name2):
    if(win1 > win2):
        print('*** PLAYER {} WON ***'.format(name1) + '\n')
    elif(win2 > win1):
        print('*** PLAYER {} WON ***'.format(name2) + '\n')
    else:
        print('*** GAME IS A DRAW ***' + '\n')

def displayRecords(conn):
    cursor = conn.execute('SELECT * FROM result')
    records = cursor.fetchall()
    for record in records:
        print(record)

def alreadyPicked(picked,lastCards):
    if len(lastCards) > 0:
        for card in lastCards:
            if card == picked:
                return True
    return False

def mainLoop(conn,rounds,name1,name2):
    #pick random cards and add to picked table
    cards = getCards(conn)
    win1 = 0
    win2 = 0
    currentPlayer = 1
    for round in range(rounds):
        print('ROUND {}'.format(round + 1))
        print('CURRENT PICKER - PLAYER {}'.format(name1))
        card1 = cards[randint(0, len(cards) - 1)]
        lastCards = getLastCards(conn)
        while alreadyPicked(card1[0],lastCards): # == getLastCard(conn):
            card1 = cards[randint(0, len(cards) - 1)]
        addCard(conn,card1[0])

        card2 = cards[randint(0, len(cards) - 1)]
        while card2[0] == card1[0] or alreadyPicked(card2[0],lastCards): # == getLastCard(conn):
            card2 = cards[randint(0, len(cards) - 1)]
        addCard(conn,card2[0])

        if currentPlayer == 1:
            print('CARD FOR {}'.format(name1))
            print(buildCardString(card1))
            #stat = chooseStat()
            stat = randint(1, 4)
            print('CARD FOR {}'.format(name2))
            print(buildCardString(card2)+'\n')
            #sleep(2)
            print('Statistic for {} > {} - {}'.format(name1,stat,card1[stat]))
            print('Statistic for {} > {} - {}'.format(name2,stat,card2[stat]))
            #sleep(2)
        else:
            print('CARD FOR {}'.format(name1))
            print(buildCardString(card1))
            #sleep(2)
            print('CARD FOR {}'.format(name2))
            print(buildCardString(card2)+'\n')
            print('{} choosing stat'.format(name2))
            stat = randint(1, 4)
            #sleep(2)
            print('Statistic for {} > {} - {}'.format(name1,stat,card1[stat]))
            print('Statistic for {} > {} - {}'.format(name2,stat,card2[stat]))
            #sleep(2)

        result = getRoundResult(card1[stat],card2[stat])
        if result == 1:
            currentPlayer = 1
            win1 += 1
            print('*** PLAYER {} WON ***'.format(name1))
            addResult(conn,card1[0],card2[0],card1[0])
        elif result == 2:
            currentPlayer = 2
            win2 += 1
            print('*** PLAYER {} WON ***'.format(name2))
            addResult(conn,card1[0],card2[0],card2[0])
        else:
            print('*** DRAW ***')
        print('*** CURRENT SCORE {} v {} ***'.format(win1,win2) + '\n')
        #sleep(3)

    finalResult(win1,win2,name1,name2)

def getUse(conn,card):
    try:
        sql = "SELECT * FROM result WHERE card1 = '{}' OR card2 = '{}'".format(card,card)
        cursor = conn.execute(sql)
        records = cursor.fetchall()
        return len(records)
    except Exception as ex:
        return 0

def getWins(conn,card):
    try:
        sql = "SELECT * FROM result WHERE winner = '{}'".format(card)
        cursor = conn.execute(sql)
        records = cursor.fetchall()
        return len(records)
    except Exception as ex:
        return 0

def reportStatistics(conn):
    cards = getCardNames(conn)
    for card in cards:
        uses = getUse(conn,card[0])
        wins = getWins(conn,card[0])
        if uses > 0:
            rate = "{:.2%}".format(wins/uses)
        else:
            rate = '0'
        print("{}:\t  uses = {}\t  wins = {}\t win rate = {}".format(card[0],uses,wins,rate))


#Main Program
conn = getConnection()
if conn != '':
    #displayRecords(conn)
    #reportStatistics(conn)
    clearTable(conn,'picked')
    #clearTable(conn,'result')
    rounds = numberOfRounds()
    name1,name2 = getPlayerNames()
    mainLoop(conn,rounds,name1,name2)
    reportStatistics(conn)
    closeConnection(conn)