# Create db, table, insert into table, select from table
import sqlite3

sql_con = sqlite3.connect('db/test.db') # If db doesn't exist it will be created

sql_con.execute('''CREATE TABLE tbl_proglanguage
             (id INTEGER PRIMARY KEY autoincrement NOT NULL,
              name TEXT NOT NULL,
              age INT NOT NULL,
              prog_language CHAR(50));''')

sql_con.execute("INSERT INTO tbl_proglanguage (name,age,prog_language) \
      VALUES ('Guido ', 66, 'Python')");

sql_con.execute("INSERT INTO tbl_proglanguage (name,age,prog_language) \
      VALUES ('Eben ', 43, 'JavaScript' )");

sql_con.execute("INSERT INTO tbl_proglanguage (name,age,prog_language) \
      VALUES ('James', 55, 'HTML' )");

sql_con.execute("INSERT INTO tbl_proglanguage (name,age,prog_language) \
      VALUES ('Lisa', 33, 'C#' )");

sql_con.commit()

cursor = sql_con.execute("SELECT id, name, age, prog_language from tbl_proglanguage")
for row in cursor:
   print ("id = ", row[0])
   print ("name = ", row[1])
   print ("age = ", row[2])
   print ("prog_language =", row[3],"\n")

sql_con.close()

# Update table
import sqlite3

sql_con = sqlite3.connect('db/test.db')
sql_update = "UPDATE tbl_proglanguage SET prog_language = 'C++' WHERE id = 3"
sql_con.execute(sql_update)
sql_con.commit()

cursor = sql_con.execute("SELECT id, name, age, prog_language from tbl_proglanguage")
for row in cursor:
   print ("id = ", row[0])
   print ("name = ", row[1])
   print ("age = ", row[2])
   print ("prog_language =", row[3],"\n")

sql_con.close()

# Delete row in table
import sqlite3

sql_con = sqlite3.connect('db/test.db')
sql_delete = "DELETE FROM tbl_proglanguage WHERE id = 3"
sql_con.execute(sql_delete)
sql_con.commit()

cursor = sql_con.execute("SELECT id, name, age, prog_language from tbl_proglanguage")
for row in cursor:
   print ("id = ", row[0])
   print ("name = ", row[1])
   print ("age = ", row[2])
   print ("prog_language =", row[3],"\n")

sql_con.close()