Activity 3.6
Source Code
import sqlite3
def main():
# Connect to the SQLite database
db = 'activity36.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 game_scores (
id INTEGER PRIMARY KEY AUTOINCREMENT,
player_name TEXT NOT NULL,
game_title TEXT NOT NULL,
score INTEGER NOT NULL,
playing_time_minutes INTEGER NOT NULL,
date_played TEXT NOT NULL,
grade_level TEXT NOT NULL
)
'''
# 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 = [
('Alex', 'Fortnite', 2800, 45, '2023-09-15', '9th'),
('Jamie', 'Minecraft', 1500, 120, '2023-09-20', '10th'),
('Taylor', 'Rocket League', 850, 30, '2023-10-10', '11th'),
('Jordan', 'Among Us', 400, 25, '2023-10-15', '9th'),
('Casey', 'Fortnite', 3100, 50, '2023-11-05', '12th'),
('Riley', 'Minecraft', 2200, 90, '2023-11-10', '10th'),
('Morgan', 'Rocket League', 950, 35, '2023-11-15', '11th'),
('Drew', 'Among Us', 600, 30, '2023-12-05', '9th'),
('Parker', 'Fortnite', 3500, 60, '2023-12-10', '12th'),
('Quinn', 'Minecraft', 1800, 100, '2023-12-20', '10th'),
('Sam', 'Valorant', 2500, 40, '2023-09-18', '11th'),
('Avery', 'Valorant', 2800, 45, '2023-10-12', '12th'),
('Cameron', 'Apex Legends', 3200, 55, '2023-10-22', '9th'),
('Harper', 'Apex Legends', 2900, 50, '2023-11-08', '10th'),
('Logan', 'Valorant', 3100, 60, '2023-11-25', '11th'),
('Reese', 'League of Legends', 1800, 70, '2023-12-02', '12th'),
('Dakota', 'League of Legends', 2200, 75, '2023-12-15', '9th'),
('Blake', 'Genshin Impact', 1650, 120, '2023-09-25', '10th'),
('Emerson', 'Genshin Impact', 1950, 140, '2023-10-18', '11th'),
('Hayden', 'Fortnite', 2950, 40, '2023-11-20', '10th'),
('Jordan', 'Minecraft', 1700, 110, '2023-11-28', '9th'),
('Kai', 'Rocket League', 1100, 40, '2023-12-08', '12th'),
('Kennedy', 'Among Us', 550, 30, '2023-09-30', '11th'),
('Elliott', 'Apex Legends', 3400, 65, '2023-10-25', '10th'),
('Charlie', 'League of Legends', 2400, 80, '2023-11-12', '9th')
]
# Insert data into table query
sql = """
INSERT INTO game_scores (player_name, game_title, score, playing_time_minutes, date_played, grade_level)
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 game_scores"
# 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()
# Count total number of game entries
sql = ""
# Execute query
cursor.execute(sql)
print("SQL query executed")
# fetch the row
# Example 2 Function
def example2(conn):
print("Example 2")
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# Count how many students played each game
sql = ""
# Execute query
cursor.execute(sql)
print("SQL query executed")
# fetch the rows
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()
# Calculate total points scored across all games
sql = ""
# Execute query
cursor.execute(sql)
print("SQL query executed")
# fetch the rows
total = cursor.fetchone()[0]
print(total)
# Example 4 Function
def example4(conn):
print("Example 4")
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# Calculate total playing time for each game
sql = ""
# Execute query
cursor.execute(sql)
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()Last updated