crud model

#The CRUD (Create, Read, Update, Delete) model
'''
(1) Create: inserting new data
(2) Read: retrieving data
(3) Update: updating data which is already stored
(4) Delete: removing data
'''
#Create a Python program which can insert (create), read, update, or delete rows in the computer table

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

#(1) Create
#To insert a new row into a table you will need to use
#an INSERT SQL statement. The syntax looks like:

#SQL syntax >>> INSERT INTO table(field_a, field_b) VALUES (value_a, value_b)

#Create a function which, when passed the name of a computer
#and the number of cores, will construct a SQL INSERT statement
#and execute it.
def create(name, cores, cpu_speed, ram, cost):
    insert_sql = "INSERT INTO computer(name,cores,cpu_speed,ram,cost) VALUES ('{}',{},{},{},{})".format(name,cores,cpu_speed,ram,cost)
    #note use of .format method above; {} are called markers
    conn.execute(insert_sql)

    conn.commit()

#(2) Read
#SQL syntax >>> SELECT * from computer WHERE name = 'name of computer'
def read(name):        # name of desired computer
    select_sql = "SELECT * FROM computer WHERE name = '{}'".format(name)

    result = conn.execute(select_sql)

    return result.fetchone()         # note method .fetchone() instead of .fetchall()

'''Tip:
If more than one row is returned, you can still use fetchone().
When called multiple times it returns the next row in order
until there are no more rows left, when it returns None.
This can be useful for iterating through rows.
'''

#(3) Update
#SQL syntax >>> UPDATE computer SET cores = {}, cpu_speed = {}, ram = {}, cost = {} WHERE name = '{}'
def update (name, cores, cpu_speed, ram, cost):
    update_sql = "UPDATE computer SET cores = {}, cpu_speed = {}, ram = {}, cost = {} WHERE name = '{}'".format(cores,cpu_speed,ram,cost,name)

    result = conn.execute(update_sql)

    conn.commit()

#(4) Delete
#SQL syntax >>> DELETE FROM computer WHERE name = '{}'
def delete(name):
    delete_sql = "DELETE FROM computer WHERE name = '{}'".format(name)

    result = conn.execute(delete_sql)

    conn.commit()

#user interface
command = input("(C)reate or (R)ead or (U)pdate or (D)elete a card > ")
name = input("Name >")
if command == "C":
#    name = input("Name >")
    cores = input("Cores >")
    cpu_speed = input("CPU speed (GHz) >")
    ram = input("RAM (MB) >")
    cost = input("Cost ($) >")

    create(name, cores, cpu_speed, ram, cost)

elif command == "R":
#    name = input("Name >")

    card = read(name)

    print(card)

elif command == "U":
#    name = input("Name >")
    if read(name) == None:
        print(name + " does not exist in database -> cannot update")
    else:
        update(name, cores, cpu_speed, ram, cost)

elif command == "D":
#    name = input("Name >")
    if read(name) == None:
        print(name + " does not exist in database -> cannot delete")
    else:
        delete(name)

    if read(name) == None:
        print(name + " successfully deleted from database")
    else:
        print("why is " + name + " still here?")

conn.close()