Activity 2.14

Source Code

import sqlite3
import os

def main():
    done = False
    # Connect to the SQLite database
    db = 'activity214.db'
    conn = sqlite3.connect(db)
    print("Welcome to the Main Function")
    while not done:
        print("Menu")
        print("E1 - CREATE Example")
        print("E2 - INSERT Example")
        print("E3 - SELECT Example")
        print("E4 - DELETE Example")
        print("E5 - DROP DB Example")
        print("Q - Quit")
        choice = input("Choice: ")
        match choice:
            case "E1":
                create(conn)
            case "E2":
                insert(conn)
            case "E3":
                select(conn)
            case "E4":
                delete(conn)
            case "E5":
                drop(db)
            case "Q":
                print("Quitting!")
                done = True
            # default case
            case _:
                print("Invalid, try again!")

def create(conn):
    print("Create Table")

    # Create a cursor object using the cursor() method
    cursor = conn.cursor()

    # Create table
    sql = '''CREATE TABLE IF NOT EXISTS students (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            age INTEGER
            )'''

    # Execute query
    cursor.execute(sql)
    print("SQL query executed")

def insert(conn):
    print("Insert Data")

    # Create a cursor object using the cursor() method
    cursor = conn.cursor()

    # Insert data into table
    cursor.execute("INSERT INTO students (name, age) VALUES \
                ('Alice', 21), \
                ('Bob', 22), \
                ('Charlie', 20)")

    # Commit changes
    conn.commit()
    print("changed committed")

def select(conn):
    print("Select Data")

    # Create a cursor object using the cursor() method
    cursor = conn.cursor()

    # SQL Query to SELECT all students
    cursor.execute("SELECT * FROM students")

    # fetch the rows
    rows = cursor.fetchall()
    for row in rows:
        print(row)
    print("SQL query executed")

def delete(conn):
    print("Delete")
    cursor = conn.cursor()
    cursor.execute("DELETE FROM students")
    conn.commit()
    print("changed committed")

def drop(db):
    print("dropping database file: " + str(db))
    # Remove the database file
    os.remove(db)
    print("database deleted")

main()