db_curd.py

import sqlite3


class ItemComputer:
    """
    Wrapper class for a record in the table computer
    field names/types of table computer
    name      : text
    cores     : integer
    cpu_speed : real
    ram       : real
    cost      : real
    """

    def __init__(self, name='', cores=0, cpu_speed=0, ram=0, cost=0):
        self.__set_name(name)
        self.__set_cores(cores)
        self.__set_cpu_speed(cpu_speed)
        self.__set_ram(ram)
        self.__set_cost(cost)

    def __str__(self):
        return "ItemComputer[name:" + self.__name + "|cores:" + str(self.__cores) + \
               "|cpu_speed:" + str(self.__cpu_speed) + \
               "|ram:" + str(self.__ram) + "|cost:" + str(self.__cost) + "]"

    def __get_name(self):
        return self.__name

    def __set_name(self, name):
        if name is None:
            raise ValueError('None is not a valid value')
        if not isinstance(name, str):
            raise ValueError('wrong type: must be a string')
        self.__name = name

    def __get_cores(self):
        return self.__cores

    def __set_cores(self, cores):
        if cores is None:
            raise ValueError('None is not a valid value')
        if not isinstance(cores, int):
            cores = int(cores)
        self.__cores = cores

    def __get_cpu_speed(self):
        return self.__cpu_speed

    def __set_cpu_speed(self, cpu_speed):
        if cpu_speed is None:
            raise ValueError('None is not a valid value')
        if not isinstance(cpu_speed, int):
            cpu_speed = int(cpu_speed)
        self.__cpu_speed = cpu_speed

    def __get_ram(self):
        return self.__ram

    def __set_ram(self, ram):
        if ram is None:
            raise ValueError('None is not a valid value')
        if not isinstance(ram, int):
            ram = int(ram)
        self.__ram = ram

    def __get_cost(self):
        return self.__cost

    def __set_cost(self, cost):
        if cost is None:
            raise ValueError('None is not a valid value')
        if not isinstance(cost, int) and not isinstance(cost, float):
            try:
                cost = int(cost)
            except:
                try:
                    cost = float(cost)
                except Exception as e:
                    print(e)
                    raise e
        self.__cost = cost

    name = property(__get_name, __set_name)
    cores = property(__get_cores, __set_cores)
    cpu_speed = property(__get_cpu_speed, __set_cpu_speed)
    ram = property(__get_ram, __set_ram)
    cost = property(__get_cost, __set_cost)

    def print_item(self):
        print("{:10}: {}".format("name", self.__name))
        print("{:10}: {}".format("cores", self.__cores))
        print("{:10}: {}".format("cpu_speed", self.__cpu_speed))
        print("{:10}: {}".format("ram", self.__ram))
        print("{:10}: {}".format("cost", self.__cost))

    def set_item(self, row):
        if not isinstance(row, tuple):
            raise ValueError('list expected')
        self.__set_name("Invalid")
        self.__set_cores(0)
        self.__set_cpu_speed(0)
        self.__set_ram(0)
        self.__set_cost(0)

        self.__set_name(row[0])
        self.__set_cores(row[1])
        self.__set_cpu_speed(row[2])
        self.__set_ram(row[3])
        self.__set_cost(row[4])

    def fill_item(self, change_name=True):
        if change_name:
            self.__set_name("Invalid")
        self.__set_cores(0)
        self.__set_cpu_speed(0)
        self.__set_ram(0)
        self.__set_cost(0)

        print("Enter the details:")
        if change_name:
            while True:
                resp = input("{0:10}: ".format("name"))
                resp = resp.strip()
                try:
                    self.__set_name(resp)
                except ValueError as e:
                    print(e)
                    continue
                break

        while True:
            resp = input("{0:10}: ".format("cores"))
            resp = resp.strip()
            try:
                self.__set_cores(resp)
            except Exception as e:
                print(e)
                continue
            break

        while True:
            resp = input("{0:10}: ".format("cpu_speed"))
            resp = resp.strip()
            try:
                self.__set_cpu_speed(resp)
            except Exception as e:
                print(e)
                continue
            break

        while True:
            resp = input("{0:10}: ".format("ram"))
            resp = resp.strip()
            try:
                self.__set_ram(resp)
            except Exception as e:
                print(e)
                continue
            break

        while True:
            resp = input("{0:10}: ".format("cost"))
            resp = resp.strip()
            try:
                self.__set_cost(resp)
            except Exception as e:
                print(e)
                continue
            break


def item_create():
    conn = sqlite3.connect("computer_cards.db")

    item = ItemComputer()
    item.fill_item()

    sql = "INSERT INTO computer(name, cores, cpu_speed, ram, cost) VALUES ('{}', {}, {}, {}, {})".format(
          item.name, item.cores, item.cpu_speed, item.ram, item.cost)
    print("SQL:>>>>>" + sql + "<<<<<")
    conn.execute(sql)
    conn.commit()
    conn.close()


def item_read(name):
    conn = sqlite3.connect("computer_cards.db")
    sql = "SELECT * FROM computer WHERE name = '{}'".format(name)
    print("SQL:>>>>>" + sql + "<<<<<")
    result = conn.execute(sql)
    row = result.fetchone()
    if row is None:
        print("no item found with name {}".format(name))
        return
    item = ItemComputer()
    item.set_item(row)
    print("Current values of item {}".format(name))
    item.print_item()
    conn.close()

def item_update(name):
    conn = sqlite3.connect("computer_cards.db")
    sql = "SELECT * FROM computer WHERE name = '{}'".format(name)
    print("SQL:>>>>>" + sql + "<<<<<")
    result = conn.execute(sql)
    row = result.fetchone()
    if row is None:
        print("no item found with name {}".format(name))
        return
    item = ItemComputer()
    item.set_item(row)
    print("Current values of item {}".format(name))
    item.print_item()
    item.fill_item(change_name=False)
    sql = "UPDATE computer SET cores = {}, cpu_speed = {}, ram = {}, cost = {} WHERE name = '{}'".format(
          item.cores, item.cpu_speed, item.ram, item.cost, name)
    print("SQL:>>>>>" + sql + "<<<<<")
    cursor = conn.cursor()
    cursor.execute(sql)
    print("SQL updated items:", cursor.rowcount)
    cursor.close()
    conn.commit()
    conn.close()

def item_delete(name):
    conn = sqlite3.connect("computer_cards.db")
    cursor = conn.cursor()
    sql = "DELETE FROM computer WHERE name = '{}'".format(name)
    print("SQL:>>>>>" + sql + "<<<<<")
    cursor.execute(sql)
    print("SQL deleted items:", cursor.rowcount)
    if cursor.rowcount < 1:
        print("no item found with name '{}'".format(name))
    if cursor.rowcount > 1:
        print("{} items with name '{}' deleted".format(cursor.rowcount, name))
    cursor.close()
    conn.commit()
    conn.close()

def item_list_all():
    conn = sqlite3.connect("computer_cards.db")
    result = conn.execute("SELECT * FROM computer ORDER BY name")
    computers = result.fetchall()

    for computer in computers:
        item = ItemComputer()
        item.set_item(computer)
        item.print_item()
        print("--------------------")

    conn.close()


while True:
    action = input("Action: Create, Read, Update, Delete, List, Quit[c/r/u/d/l/q]: ")
    if action not in ["C","c","R","r","U","u","D","d","L","l","Q","q"]:
        print("Unknown action")
        continue;
    if action in ["Q","q"]:
        break
    if action in ["C", "c"]:
        item_create()
        continue
    if action in ["R", "r"]:
        while True:
            name = input("Give name of item to read:")
            if len(name) == 0:
                continue
            break
        item_read(name)
        continue
    if action in ["U", "u"]:
        while True:
            name = input("Give name of item to update:")
            if len(name) == 0:
                continue
            break
        item_update(name)
        continue
    if action in ["D", "d"]:
        while True:
            name = input("Give name of item to delete:")
            if len(name) == 0:
                continue
            break
        item_delete(name)
        continue
    if action in ["L", "l"]:
        print("Print content of table computer")
        item_list_all()
        continue