Activity 3.9

Source Code

import sqlite3

def main():
    # Connect to the SQLite database
    db = 'activity39.db'
    conn = sqlite3.connect(db)

    done = False
    print("Welcome to the Main Function")
    while not done:
        print("Menu")
        print("CREATE - CREATE TABLE")
        print("INSERT - INSERT INTO TABLE")
        print("SELECT - SELECT *")
        print("E1 - Example 1")
        print("Q - Quit")
        choice = input("Choice: ").upper()
        match choice:
            case "CREATE":
                create(conn)
            case "INSERT":
                insert(conn)
            case "SELECT":
                select(conn)
            case "E1":
                example1(conn)
            case "E2":
                example2(conn)
            case "E3":
                example3(conn)
            case "E4":
                example4(conn)
            case "P1":
                problem1(conn)
            case "P2":
                problem2(conn)
            case "P3":
                problem3(conn)
            case "P4":
                problem4(conn)
            case "P5":
                problem5(conn)
            case "P6":
                problem6(conn)
            case "P7":
                problem7(conn)
            case "P8":
                problem8(conn)
            case "P9":
                problem9(conn)
            case "P10":
                problem10(conn)
            case "Q":
                print("Quitting!")
                done = True
            # default case
            case _:
                print("Invalid, try again!")

    # close the connection
    conn.close()

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

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

    # Create table query
    sql = '''
        CREATE TABLE IF NOT EXISTS shows (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            genre TEXT NOT NULL,
            seasons INTEGER DEFAULT 1,
            episodes_per_season INTEGER,
            release_year INTEGER,
            rating REAL DEFAULT 0,
            views INTEGER DEFAULT 0,
            trending_rank INTEGER,
            platform TEXT,
            is_completed BOOLEAN DEFAULT 0
        )
        '''

    # Execute query
    cursor.execute(sql)
    print("Table created successfully!")

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

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

    # data
    data = [
        ('Euphoria', 'Teen Drama', 2, 8, 2019, 8.4, 5300000, 3, 'HBO Max', 0),
        ('Stranger Things', 'Sci-Fi/Horror', 4, 9, 2016, 8.7, 12500000, 1, 'Netflix', 0),
        ('Outer Banks', 'Adventure/Mystery', 3, 10, 2020, 7.6, 4800000, 5, 'Netflix', 0),
        ('Wednesday', 'Fantasy/Comedy', 1, 8, 2022, 8.1, 8900000, 2, 'Netflix', 0),
        ('The Last of Us', 'Post-Apocalyptic', 1, 9, 2023, 8.8, 7200000, 4, 'HBO Max', 0),
        ('Heartstopper', 'Coming-of-Age', 2, 8, 2022, 8.7, 3800000, 8, 'Netflix', 0),
        ('All American', 'Sports Drama', 5, 16, 2018, 7.9, 2900000, 12, 'The CW/Netflix', 0),
        ('Squid Game', 'Thriller', 1, 9, 2021, 8.0, 9500000, 9, 'Netflix', 0),
        ('The Umbrella Academy', 'Superhero', 3, 10, 2019, 8.0, 4100000, 11, 'Netflix', 0),
        ('Never Have I Ever', 'Teen Comedy', 4, 10, 2020, 7.9, 3600000, 10, 'Netflix', 1),
        ('Demon Slayer', 'Anime', 3, 26, 2019, 8.7, 5800000, 6, 'Crunchyroll/Netflix', 0),
        ('Attack on Titan', 'Anime', 4, 25, 2013, 9.0, 6200000, 7, 'Crunchyroll/Hulu', 1),
        ('Riverdale', 'Mystery/Drama', 7, 22, 2017, 6.8, 2100000, 20, 'The CW/Netflix', 1),
        ('Cobra Kai', 'Action/Comedy', 5, 10, 2018, 8.5, 4300000, 13, 'Netflix', 0),
        ('Outer Range', 'Mystery/Sci-Fi', 2, 8, 2022, 7.4, 1800000, 21, 'Prime Video', 0),
        ('Bel-Air', 'Drama', 2, 10, 2022, 6.9, 2000000, 19, 'Peacock', 0),
        ('The Summer I Turned Pretty', 'Romance', 2, 8, 2022, 7.5, 3200000, 14, 'Prime Video', 0),
        ('One Piece', 'Adventure/Fantasy', 1, 8, 2023, 7.8, 4500000, 15, 'Netflix', 0),
        ('Percy Jackson and the Olympians', 'Fantasy/Adventure', 1, 8, 2023, 7.6, 3100000, 17, 'Disney+', 0),
        ('My Hero Academia', 'Anime', 6, 25, 2016, 8.4, 4900000, 16, 'Crunchyroll/Hulu', 0),
        ('XO, Kitty', 'Teen Romance', 1, 10, 2023, 6.7, 2700000, 18, 'Netflix', 0),
        ('The Kardashians', 'Reality', 5, 10, 2022, 5.9, 3000000, 23, 'Hulu', 0),
        ('Young Royals', 'Teen Drama', 3, 6, 2021, 8.3, 2200000, 22, 'Netflix', 1),
        ('Shadow and Bone', 'Fantasy', 2, 8, 2021, 7.7, 3500000, 24, 'Netflix', 1),
        ('Ted Lasso', 'Comedy/Sports', 3, 12, 2020, 8.8, 5100000, 25, 'Apple TV+', 1)
        ]

    # Insert data into table query
    sql = """
        INSERT INTO shows (title, genre, seasons, episodes_per_season, release_year, rating, views, trending_rank, platform, is_completed)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    """

    # Insert data into table query
    cursor.executemany(sql, data)
    print("SQL query executed")

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

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

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

    # SQL Query to SELECT all
    sql = "SELECT * FROM shows"

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

    # fetch the rows
    rows = cursor.fetchall()
    for row in rows:
        print(row)

# Example 1 Function
def example1(conn):
    print("Example 1")

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

    # Increase the number of seasons for "Stranger Things" after a new season is released
    sql = '''

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

    # Verify the change
    sql2 = '''
    
    '''

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

    # fetch the rows
    rows = cursor.fetchall()
    for row in rows:
        print(row)

# Example 2 Function
def example2(conn):
    print("Example 2")

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

    # Update all Netflix shows to increase their views during a special promotion weekend
    # Increase views by 15%
    # Improve trending rank (lower number = better rank)
    # Only update shows not already in top 2
    sql = '''
    
    '''

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

    # Verify the change
    sql2 = '''
    
    '''

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


    # fetch the rows
    rows = cursor.fetchall()
    for row in rows:
        print(row)

# Problem 1 Function
def problem1(conn):
    print("Problem 1")

# Problem 2 Function
def problem2(conn):
    print("Problem 2")

# Problem 3 Function
def problem3(conn):
    print("Problem 3")

# Problem 4 Function
def problem4(conn):
    print("Problem 4")

# Problem 5 Function
def problem5(conn):
    print("Problem 5")

# Problem 6 Function
def problem6(conn):
    print("Problem 6")

# Problem 7 Function
def problem7(conn):
    print("Problem 7")

# Problem 8 Function
def problem8(conn):
    print("Problem 8")

# Problem 9 Function
def problem9(conn):
    print("Problem 9")

# Problem 10 Function
def problem10(conn):
    print("Problem 10")

# call to main function, do not delete!
main()