import sqlite3

from employees import Employee

conn = sqlite3.connect('employees2.db')

c = conn.cursor()

#c.execute("""CREATE TABLE employees4 (

# first text,

# last text,

# pay integer

# )""")

def insert_employee(emp):

with conn:

c.execute("INSERT INTO employees4 VALUES (?, ?, ?)", (emp.first, emp.last, emp.pay))

def get_emp_by_lastname(lastname):

c.execute("SELECT * FROM employees4 WHERE last=?", (lastname,))

print(c.fetchall())

def update_pay(emp, pay):

with conn:

emp.pay = pay

c.execute("UPDATE employees4 SET pay=(?) WHERE first=(?) AND last=(?)", (emp.pay, emp.first, emp.last))

def delete_employee(emp):

with conn:

c.execute("DELETE FROM employees4 WHERE first=? AND last=?", (emp.first, emp.last))

e1 = Employee('Caroline', 'Channing', 30000)

e2 = Employee('Max', 'Black', '30000')

# Following way of passing input to sql leads to sql injection attack

# So it is not advised to pass arguments using string formatting to sqlite

# c.execute("INSERT INTO employees3 VALUES ({}, {}, {}).format(e1.first, e1.last, e1.pay)")

insert_employee(e1)

insert_employee(e2)

# In this way we are passing arguments as tuple, if we dont want to pass any value

# We have to still pass it as tuple for example, if we only want to send first name they statement would be

# c.execute("INSERT INTO employees3 VALUES (?, ?, ?)", (e1.first,))

#c.execute("INSERT INTO employees3 VALUES (?, ?, ?)", (e1.first, e1.last, e1.pay))

# Other way to pass values is using dictionary

#c.execute("INSERT INTO employees3 VALUES (:first, :last, :pay)", {'first':e2.first, 'last':e2.last, 'pay':e2.pay})

#c.execute("SELECT * FROM employees3 WHERE last=?", ('Dennings',))

#print(c.fetchall())

#c.execute("SELECT * FROM employees3 WHERE last=:last", {'last':'Holmes'})

#print(c.fetchall())

get_emp_by_lastname('Channing')

update_pay(e1, 40000)

delete_employee(e2)

c.execute("SELECT * FROM employees4")

print(c.fetchall())

# Select statements doesn't need to be commited

# INSERT statemets need to be commit

conn.commit()

conn.close()