Activity 3.10
Source Code
import sqlite3
def main():
# Connect to the SQLite database
db = 'activity310.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("DELETE - be careful!")
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 "DELETE":
delete(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 movies (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
release_year INTEGER,
director TEXT,
genre TEXT,
runtime_minutes INTEGER,
rating TEXT, -- PG, PG-13, R, etc.
imdb_rating REAL, -- Rating out of 10
rt_audience_score INTEGER, -- Rotten Tomatoes audience score (percentage)
streaming_on TEXT, -- Netflix, Disney+, HBO Max, etc.
is_sequel BOOLEAN DEFAULT 0,
part_of_franchise TEXT,
main_actors TEXT,
has_post_credits_scene BOOLEAN DEFAULT 0,
viral_on_tiktok BOOLEAN DEFAULT 0,
iconic_quotes TEXT,
watch_count INTEGER DEFAULT 0, -- Number of times a student has watched this movie
would_recommend BOOLEAN DEFAULT 1
)
'''
# 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 = [
('Spider-Man: No Way Home', 2021, 'Jon Watts', 'Action/Superhero', 148, 'PG-13', 8.2, 98, 'Disney+', 1, 'Marvel Cinematic Universe', 'Tom Holland, Zendaya, Benedict Cumberbatch', 1, 1, 'With great power comes great responsibility', 3, 1),
('The Batman', 2022, 'Matt Reeves', 'Action/Superhero', 176, 'PG-13', 7.8, 87, 'HBO Max', 0, 'Batman', 'Robert Pattinson, Zoë Kravitz', 1, 1, 'I am vengeance', 2, 1),
('Everything Everywhere All at Once', 2022, 'Daniel Kwan, Daniel Scheinert', 'Sci-Fi/Comedy', 139, 'R', 8.0, 89, 'Prime Video', 0, None, 'Michelle Yeoh, Ke Huy Quan, Stephanie Hsu', 0, 1, 'In another life, I would have really liked just doing laundry and taxes with you', 1, 1),
('Barbie', 2023, 'Greta Gerwig', 'Comedy/Fantasy', 114, 'PG-13', 7.0, 83, 'Max', 0, 'Barbie', 'Margot Robbie, Ryan Gosling', 0, 1, 'I\'m Kenough', 2, 1),
('Dune', 2021, 'Denis Villeneuve', 'Sci-Fi/Adventure', 155, 'PG-13', 8.0, 90, 'HBO Max', 0, 'Dune', 'Timothée Chalamet, Zendaya', 0, 1, 'Fear is the mind-killer', 1, 1),
('Top Gun: Maverick', 2022, 'Joseph Kosinski', 'Action/Drama', 130, 'PG-13', 8.3, 99, 'Paramount+', 1, 'Top Gun', 'Tom Cruise, Miles Teller', 0, 1, 'Don\'t think, just do', 2, 1),
('Black Panther: Wakanda Forever', 2022, 'Ryan Coogler', 'Action/Superhero', 161, 'PG-13', 6.7, 94, 'Disney+', 1, 'Marvel Cinematic Universe', 'Letitia Wright, Angela Bassett', 1, 0, 'Show them who we are', 1, 1),
('Avatar: The Way of Water', 2022, 'James Cameron', 'Sci-Fi/Adventure', 192, 'PG-13', 7.6, 92, 'Disney+', 1, 'Avatar', 'Sam Worthington, Zoe Saldana', 0, 0, 'The way of water has no beginning and no end', 1, 1),
('The Menu', 2022, 'Mark Mylod', 'Horror/Comedy', 107, 'R', 7.2, 76, 'HBO Max', 0, None, 'Anya Taylor-Joy, Ralph Fiennes', 0, 1, 'Yes, chef!', 2, 1),
('Oppenheimer', 2023, 'Christopher Nolan', 'Biography/Drama', 180, 'R', 8.5, 91, 'Peacock', 0, None, 'Cillian Murphy, Robert Downey Jr.', 0, 1, 'Now I am become Death, the destroyer of worlds', 1, 1),
('The Super Mario Bros. Movie', 2023, 'Aaron Horvath, Michael Jelenic', 'Animation/Adventure', 92, 'PG', 7.1, 95, 'Netflix', 0, 'Mario', 'Chris Pratt, Anya Taylor-Joy, Jack Black', 1, 1, 'It\'s-a me, Mario!', 1, 1),
('John Wick: Chapter 4', 2023, 'Chad Stahelski', 'Action/Thriller', 169, 'R', 7.7, 90, 'Starz', 1, 'John Wick', 'Keanu Reeves, Donnie Yen', 0, 1, 'Yeah, I\'m thinking I\'m back', 1, 1),
('Guardians of the Galaxy Vol. 3', 2023, 'James Gunn', 'Action/Superhero', 150, 'PG-13', 7.9, 94, 'Disney+', 1, 'Marvel Cinematic Universe', 'Chris Pratt, Zoe Saldana, Dave Bautista', 1, 0, 'We are Groot', 2, 1),
('The Hunger Games: The Ballad of Songbirds & Snakes', 2023, 'Francis Lawrence', 'Sci-Fi/Adventure', 157, 'PG-13', 6.8, 89, 'Starz', 0, 'The Hunger Games', 'Tom Blyth, Rachel Zegler', 0, 1, 'It\'s the things we love most that destroy us', 1, 1),
('Wonka', 2023, 'Paul King', 'Fantasy/Musical', 116, 'PG', 7.2, 87, 'Max', 0, 'Willy Wonka', 'Timothée Chalamet, Olivia Colman', 0, 1, 'Every good thing in this world started with a dream', 1, 1),
('Five Nights at Freddy\'s', 2023, 'Emma Tammi', 'Horror/Mystery', 109, 'PG-13', 5.6, 87, 'Peacock', 0, 'FNAF', 'Josh Hutcherson, Matthew Lillard', 0, 1, 'These characters hold a special place in the hearts of children', 2, 1),
('The Fault in Our Stars', 2014, 'Josh Boone', 'Romance/Drama', 126, 'PG-13', 7.7, 85, 'Disney+', 0, None, 'Shailene Woodley, Ansel Elgort', 0, 1, 'Okay? Okay.', 4, 1),
('Deadpool & Wolverine', 2024, 'Shawn Levy', 'Action/Comedy', 127, 'R', 8.3, 95, 'Disney+', 1, 'Marvel Cinematic Universe', 'Ryan Reynolds, Hugh Jackman', 1, 1, 'Wade Wilson, you\'ve broken the timeline!', 1, 1),
('Mean Girls', 2004, 'Mark Waters', 'Comedy', 97, 'PG-13', 7.1, 84, 'Paramount+', 0, None, 'Lindsay Lohan, Rachel McAdams', 0, 1, 'That\'s so fetch!', 5, 1),
('The Perks of Being a Wallflower', 2012, 'Stephen Chbosky', 'Drama/Romance', 103, 'PG-13', 7.9, 87, 'Netflix', 0, None, 'Logan Lerman, Emma Watson', 0, 0, 'We accept the love we think we deserve', 3, 1),
('Knives Out', 2019, 'Rian Johnson', 'Mystery/Comedy', 130, 'PG-13', 7.9, 92, 'Prime Video', 0, 'Benoit Blanc Mysteries', 'Daniel Craig, Ana de Armas', 0, 1, 'I suspect foul play', 2, 1),
('To All the Boys I\'ve Loved Before', 2018, 'Susan Johnson', 'Romance/Comedy', 99, 'PG-13', 7.0, 86, 'Netflix', 0, 'To All the Boys', 'Lana Condor, Noah Centineo', 0, 1, 'I write letters when I have feelings too intense to talk about', 3, 1),
('Clueless', 1995, 'Amy Heckerling', 'Comedy/Romance', 97, 'PG-13', 6.9, 80, 'Paramount+', 0, None, 'Alicia Silverstone, Brittany Murphy', 0, 1, 'As if!', 2, 1),
('Ferris Bueller\'s Day Off', 1986, 'John Hughes', 'Comedy', 103, 'PG-13', 7.8, 93, 'Paramount+', 0, None, 'Matthew Broderick, Alan Ruck', 0, 1, 'Life moves pretty fast. If you don\'t stop and look around once in a while, you could miss it', 2, 1),
('Inside Out 2', 2024, 'Kelsey Mann', 'Animation/Comedy', 96, 'PG', 8.4, 96, 'Disney+', 1, 'Inside Out', 'Amy Poehler, Maya Hawke', 0, 1, 'I\'m anxiety, and I\'m here to protect you', 1, 1)
]
# Insert data into table query
sql = """
INSERT INTO movies (title, release_year, director, genre, runtime_minutes, rating, imdb_rating, rt_audience_score, streaming_on, is_sequel, part_of_franchise, main_actors, has_post_credits_scene, viral_on_tiktok, iconic_quotes, watch_count, would_recommend)
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 movies"
# Execute query
cursor.execute(sql)
print("SQL query executed")
# fetch the rows
rows = cursor.fetchall()
for row in rows:
print(row)
# delete function
def delete(conn):
print("Delete Data")
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# SQL Query to SELECT all
sql = "DELETE FROM movies"
# Execute query
cursor.execute(sql)
print("SQL query executed")
# Commit changes
conn.commit()
print("changed committed")
# Example 1 Function
def example1(conn):
print("Example 1")
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# before
sql1 = '''
SELECT COUNT(*) FROM movies
'''
# Execute query
cursor.execute(sql1)
rows = cursor.fetchall()
print("Count: " + str(rows[0][0]))
# Delete Barbie
sql2 = '''
'''
# Execute query
cursor.execute(sql2)
print("SQL query executed")
# Commit changes
conn.commit()
print("changed committed")
# after
sql3 = '''
SELECT COUNT(*) FROM movies
'''
# Execute query
cursor.execute(sql3)
rows = cursor.fetchall()
print("Count: " + str(rows[0][0]))
# Example 2 Function
def example2(conn):
print("Example 2")
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# before
sql1 = '''
SELECT COUNT(*) FROM movies
'''
# Execute query
cursor.execute(sql1)
rows = cursor.fetchall()
print("Count: " + str(rows[0][0]))
# Delete Movies with Low Ratings
sql2 = '''
'''
# Execute query
cursor.execute(sql2)
print("SQL query executed")
# Commit changes
conn.commit()
print("changed committed")
# after
sql3 = '''
SELECT COUNT(*) FROM movies
'''
# Execute query
cursor.execute(sql3)
rows = cursor.fetchall()
print("Count: " + str(rows[0][0]))
# Example 3 Function
def example3(conn):
print("Example 3")
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# before
sql1 = '''
SELECT COUNT(*) FROM movies
'''
# Execute query
cursor.execute(sql1)
rows = cursor.fetchall()
print("Count: " + str(rows[0][0]))
# Delete 'Horror' like movies
sql2 = '''
'''
# Execute query
cursor.execute(sql2)
print("SQL query executed")
# Commit changes
conn.commit()
print("changed committed")
# after
sql3 = '''
SELECT COUNT(*) FROM movies
'''
# Execute query
cursor.execute(sql3)
rows = cursor.fetchall()
print("Count: " + str(rows[0][0]))
# 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()
Last updated