H14 - API & Database - Lesson 8

cwhq.py

#!/usr/bin/python3

import cgitb
cgitb.enable()

print("Content-Type: text/html \n")

import sqlite3
connection = sqlite3.connect('database/codewizards.db')
sql = connection.cursor()

from helper import showData

def drop_students_table():
    query = "DROP TABLE IF EXISTS students;"
    sql.execute(query)


def create_students_table():
    query = """
        CREATE TABLE IF NOT EXISTS students(
            name TEXT,
            course TEXT,
            progress INTEGER,
            instructor TEXT
            );
        """

    sql.execute(query)


def populate_students_table():
    query = """
        INSERT INTO students VALUES
            ("Ben",     "H11: Introduction to Python",           8, "Tyler"),
            ("Anushka", "H11: Introduction to Python",           3, "Tyler"),
            ("Samuel",  "H11: Introduction to Python",           3, "Tyler"),
            ("Thomas",  "H11: Introduction to Python",           4, "Larry"),
            ("Ellie",   "H11: Introduction to Python",           5, "Larry"),
            ("Anushka", "H12: Fundamentals of Web Development",  8, "Andrew"),
            ("Ben",     "H13: User Interface Development",       3, "Andrew"),
            ("Panda",   "H21: Professional Web App Development", 7, "Sam");
        """

    sql.execute(query)
    

def select_all_students():
    data = sql.execute("SELECT * FROM students")
    showData(data)



drop_students_table()
create_students_table()
populate_students_table()

select_all_students()