CRUD

import sqlite3
import sys
import os

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(ex)
        conn = ''
    return conn

def displayRecords(conn):
    #cursor data needs to be fetched before you can use it
    #fetchall returns a list of lists
    cursor = conn.execute('SELECT * FROM {}'.format(getTableName()))
    records = cursor.fetchall()
#    records = cursor.fetchmany(5)
    for record in records:
        item = ''
        print(buildRecordString(record))

def displayRecord(rid,conn):
    table = getTableName()
    cursor = conn.execute("SELECT * FROM {} WHERE name = '{}'".format(table,rid))
    record = cursor.fetchone()
    if(record == None):
        print('No records found with a name of {}'.format(rid))
    else:
        print(buildRecordString(record))

def updateRecord(rid,conn,name,cores,speed,ram,cost):
    sql = "UPDATE {} SET name = '{}', cores = {}, cpu_speed = {}, ram = {}, cost = {} WHERE name = '{}'".format(getTableName(),name,cores,speed,ram,cost,rid)
    cursor = conn.execute(sql)
    print("Rows updated: {}".format(cursor.rowcount));

def leaveRecord(rid):
    print('A record with the name {} does not exist'.format(rid))

def buildRecordString(record):
    item = ''
    for i in range(len(record)):
        item += str(record[i]) + '\t' + '\t'
    return item

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


def getCommand():
    response = ''
    while response == '':
        response = input('Enter command: ').strip().upper()
    return response

def displayMenu():
    os.system('cls')
    print('A > Add Record')
    print('P > Print Records')
    print('R > Print Record')
    print('U > Update Record')
    print('D > Delete Record')
    print('M > Menu')
    print('Q > Quit')

def validateText(text):
    text = text.strip()
    if len(text)>0:
        valid = True
    else:
        valid = False
    return text,valid

def validateInt(text):
    text = text.strip()
    try:
        text = int(text)
        valid = True
    except:
        valid = False
    return text,valid

def validateFloat(text):
    text = text.strip()
    try:
        text = float(text)
        valid = True
    except:
        valid = False
    return text,valid


def getData():
    #TODO Need to validation input
    valid = True
    name, valid = validateText(input('Enter Computer Name: '))
    if valid:
        cores = -1
        speed = -1
        ram = -1
        cost = -1
        if valid:
            cores,valid = validateInt(input('Enter number of cores: '))
            if valid:
                speed,valid = validateInt(input('Enter CPU speed: '))
                if valid:
                    ram,valid = validateInt(input('Enter amount of RAM: '))
                    if valid:
                        cost,valid = validateFloat(input('Enter cost: '))
    return name,cores,speed,ram,cost,valid

def addRecord(conn,name,cores,speed,ram,cost):
    try:
        sql = "INSERT INTO computer(name, cores,cpu_speed,ram,cost) VALUES ('{}', {}, {}, {}, {})".format(name, cores,speed,ram,cost)
        cursor = conn.execute(sql)
        conn.commit()
        print("Rows inserted: {}".format(cursor.rowcount));
    except Exception:
        print('Add Record Failed')
        #traceback.print_exc()

def getRecordID():
    try:
        cid = input('Enter Computer ID: ').strip()
    except:
        cid = 0
    return cid

def deleteRecord(cid,conn):
    try:
        sql = "DELETE FROM computer WHERE name = '{}'".format(cid)
        cursor = conn.execute(sql)
        conn.commit()
        print("Rows deleted: {}".format(cursor.rowcount));
    except Exception:
        print('Delete Record Failed')

def checkCursor(cursor):
    if cursor == '':
        print('No table cursor established')
    else:
        print('Have Cursor - {}'.format(cursor))

def getTableName():
    return 'computer'

def recordExists(rid,conn):
    exists = False
    cursor = conn.execute("SELECT * FROM {} WHERE name = '{}'".format(getTableName(),rid))
    record = cursor.fetchone()
    if(record == None):
        exists = False
    else:
        exists = True
    return exists

def mainLoop(conn):
    action = ''
    while action != 'Q':
        action = getCommand()
        if action == 'P':
            displayRecords(conn)
        elif action == 'R':
            rid = getRecordID()
            if rid != '':
                displayRecord(rid,conn)
        elif action == 'U':
            rid = getRecordID()
            if rid != '':
                if(recordExists(rid,conn)):
                    name,cores,speed,ram,cost,valid = getData()
                    if valid:
                        updateRecord(rid,conn,name,cores,speed,ram,cost)
                else:
                    leaveRecord(rid)
        elif action == 'M':
            displayMenu()
        elif action == 'A':
            name,cores,speed,ram,cost,valid = getData()
            if valid:
                addRecord(conn,name,cores,speed,ram,cost)
        elif action == 'D':
            rid = getRecordID()
            if rid != '':
                deleteRecord(rid,conn)

def leave():
    print('Exit')


#Main Program
conn = getConnection()
if conn == '':
    print('Failed to establish connection to database')
else:
    displayMenu()
    mainLoop(conn)
    closeConnection(conn)
leave()