Unit 12

Source Code

import sqlite3

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

    done = False
    print("Welcome to the Main Function")
    while not done:
        print("Menu")
        print("CREATE1 - CREATE TABLE 1")
        print("CREATE2 - CREATE TABLE 2")
        print("CREATE3 - CREATE TABLE 3")
        print("INSERT1 - INSERT INTO TABLE 1")
        print("INSERT2 - INSERT INTO TABLE 2")
        print("INSERT3 - INSERT INTO TABLE 3")
        print("SELECT1 - SELECT * TABLE 1")
        print("SELECT2 - SELECT * TABLE 2")
        print("SELECT3 - SELECT * TABLE 3")
        print("E1 - Example 1")
        print("Q - Quit")
        choice = input("Choice: ").upper()
        match choice:
            case "CREATE1":
                create1(conn)
            case "CREATE2":
                create2(conn)
            case "INSERT1":
                insert1(conn)
            case "INSERT2":
                insert2(conn)
            case "SELECT1":
                select1(conn)
            case "SELECT2":
                select2(conn)
            case "SELECT3":
                select3(conn)
            case "CREATE3":
                create3(conn)
            case "INSERT3":
                insert3(conn)
            case "E1":
                example1(conn)
            case "E2":
                example2(conn)
            case "E3":
                example3(conn)
            case "E4":
                example4(conn)
            case "E5":
                example5(conn)
            case "Q":
                done = True
            # default case
            case _:
                print("Invalid, try again!")

    # close the connection
    conn.close()

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

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

    # Create table query
    sql = '''CREATE TABLE IF NOT EXISTS clubs (
            club_id INTEGER PRIMARY KEY AUTOINCREMENT,
            club_name TEXT NOT NULL,
            meeting_day TEXT,
            room_number TEXT
            )
           '''

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

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

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

    # Create table query
    sql = '''CREATE TABLE IF NOT EXISTS students (
        student_id INTEGER PRIMARY KEY AUTOINCREMENT,
        first_name TEXT NOT NULL,
        grade INTEGER,
        club_id INTEGER,
        gpa REAL,
        FOREIGN KEY (club_id) REFERENCES clubs (club_id)
        )
        '''

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

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

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

    # data
    data = [
        ('eSports Club', 'Monday', 'GLAB'),
        ('Debate Team', 'Tuesday', 'NAAB'),
        ('Basketball', 'Wednesday', 'Pioneer'),
        ('Art Club', 'Thursday', 'Cougar Hall'),
        ('Robotics', 'Friday', 'Libery Hall'),
        ('Cybersecurity', 'Saturday', 'Zoom'),
        ('Digital Forensics', 'Sunday', 'Zoom')
    ]

    # Insert data into table query
    sql = """
        INSERT INTO clubs (club_name, meeting_day, room_number)
        VALUES (?, ?, ?)
    """

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

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

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

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

    # data
    data = [
        ('Alex', 10, 1, 3.7),
        ('Alex', 10, 2, 3.7),
        ('Taylor', 11, 2, 3.9),
        ('Taylor', 11, 4, 3.9),
        ('Jordan', 10, 1, 3.5),
        ('Casey', 12, 3, 3.8),
        ('Riley', 11, 2, 3.6),
        ('Riley', 11, 5, 3.6),
        ('Morgan', 10, 1, 3.4),
        ('Jamie', 12, None, 3.1),
        ('Avery', 9, None, 3.2)
    ]

    # Insert data into table query
    sql = """
        INSERT INTO students (first_name, grade, club_id, gpa)
        VALUES (?, ?, ?, ?)
    """

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    # 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()

    # sql
    sql = '''SELECT s.student_id, s.first_name, c.club_name, c.meeting_day
            FROM students s
            INNER JOIN clubs c ON s.club_id = c.club_id
            '''
    # Execute query
    cursor.execute(sql)
    rows = cursor.fetchall()
    for row in rows:
        print(row)

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

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

    # Create table query
    sql = '''CREATE TABLE IF NOT EXISTS competitions (
            comp_id INTEGER PRIMARY KEY AUTOINCREMENT,
            comp_name TEXT NOT NULL,
            club_id INTEGER,
            location TEXT,
            comp_date TEXT,
            FOREIGN KEY (club_id) REFERENCES clubs (club_id)
            )
    '''

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

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

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

    # data
    data = [
        ('State Gaming Tournament', 1, 'Convention Center', '2023-11-15'),
        ('Regional Debate Championship', 2, 'Central High School', '2023-10-25'),
        ('Basketball Finals', 3, 'Main Gym', '2023-12-10'),
        ('Robotics Challenge', 5, 'Tech University', '2023-11-05'),
        ('Art Exhibition', 4, 'Community Center', '2023-10-30')
    ]

    # Insert data into table query
    sql = """
        INSERT INTO competitions (comp_name, club_id, location, comp_date)
        VALUES (?, ?, ?, ?)
    """

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

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

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

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

    # sql
    sql = '''SELECT s.first_name, s.last_name, c.club_name, comp.comp_name, comp.comp_date
            FROM students s
            INNER JOIN clubs c ON s.club_id = c.club_id
            INNER JOIN competitions comp ON c.club_id = comp.club_id
    '''

    # Execute query
    cursor.execute(sql)
    rows = cursor.fetchall()
    for row in rows:
        print(row)

# Example 3 Function
def example3(conn):
    print("Example 3")

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

    # sql
    sql = '''SELECT s.first_name, c.club_name
            FROM students s
            LEFT JOIN clubs c ON s.club_id = c.club_id
    '''

    # Execute query
    cursor.execute(sql)
    rows = cursor.fetchall()
    for row in rows:
        print(row)

# Example 4 Function
def example4(conn):
    print("Example 4")

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

    # sql
    sql = '''SELECT s.first_name, c.club_name
            FROM clubs c
            LEFT JOIN students s ON c.club_id = s.club_id
    '''

    # Execute query
    cursor.execute(sql)
    rows = cursor.fetchall()
    for row in rows:
        print(row)


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

project3.py

import sqlite3
from datetime import datetime

def main():
    print("CPS 3320 Python Programming")
    print("Project 3")
    print("Name: ")
    db = 'project6.db'
    conn = sqlite3.connect(db)
    done = False
    while not done:
        print("Main Menu")
        print("A - Admin Menu")
        print("S - Start New Order")
        print("Q - Quit")
        choice = input("Choice: ").upper()
        if choice == "A":
            admin(conn)
        elif choice == "S":
            restaurant(conn)
        elif choice == "P":
            past_orders(conn)
        elif choice == "C":
            view_customers(conn)
        elif choice == "Q":
            print("Quitting!")
            done = True
        else:
            print("Invalid, try again!")

    #close the connection
    conn.close()

# admin function
def admin(conn):
    print("Admin Menu")
    done = False
    while not done:
        print("CREATE1 - Create menu table")
        print("CREATE2 - Create customers table")
        print("CREATE3 - Create orders table")
        print("CREATE4 - Create order_items table")
        print("INSERT1 - Insert menu items")
        print("S - View All Menu Items")
        print("P - Past Orders")
        print("C - View All Customers")
        print("Q - Quit")
        choice = input("Choice: ").upper()
        if choice == "Q":
            print("Quitting!")
            done = True
        else:
            print("Invalid, try again!")

# start new order function
def restaurant(conn):
    print("Restaurant Menu")

# past orders function
def past_orders(conn):
    print("Past Orders")

# view customers function
def view_customers(conn):
    print("View Customers")

main()