sql-update-delete 04/02/25, 10:00 AM

Lecture 4: In-class activity - Update,

delete

Let's setup the SQL environment

In [2]: #Install pysqlite3 for python and import pandas to use later
#!pip install pysqlite3
from sqlite3 import dbapi2 as sqlite3
print(sqlite3.sqlite_version)
import pandas as pd
from IPython.display import display, HTML

3.45.3

Let's define some helper functions for running queries and printing results

In [13]: dbname = "music_streaming4.db"

def printSqlResults(cursor, tblName):

try:
df = pd.DataFrame(cursor.fetchall(), columns=[i[0] for i in cursor.descr
display(HTML("<b><font color=Green> " + tblName + "</font></b>" + df.to_
except:
pass

def runSql(caption, query):

conn = sqlite3.connect(dbname) # Connect to the database
cursor = conn.cursor() # Create a cursor (think: it's like a "pointer")
cursor.execute(query) # Execute the query
conn.commit() # Critical missing line
printSqlResults(cursor, caption) # Print the results
conn.close()

def runStepByStepSql(query, fromline):

lines = query.strip().split('\n')
for lineidx in range(fromline, len(lines)):
partial_query = '\n'.join(lines[:lineidx])
caption = 'Query till line:' + partial_query
runSql(caption, partial_query + ';')

Let's setup a Schema and insert some data

In [14]: # Connect to database (creates the file if it doesn't exist)

"""
1. Connections: A connection represents a connection to a database through
which we can execute SQL queries. The dbname here specifies the database.
In SQLlite, if the DB doesn't exist, it will be created.
2. Cursors: A cursor is an object associated with a database connection.
It allows you to execute SQL queries, fetch query results.
"""

file:///Users/kunalsahni/Downloads/sql-update-delete.html Page 1 of 6
sql-update-delete 04/02/25, 10:00 AM

conn = sqlite3.connect(dbname)
cursor = conn.cursor()

# Create the Users table

cursor.execute("""
CREATE TABLE IF NOT EXISTS Users (
user_id INTEGER PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE
);
""")

# Create the Songs table

cursor.execute("""
CREATE TABLE IF NOT EXISTS Songs (
song_id INTEGER PRIMARY KEY,
title VARCHAR(100) NOT NULL,
artist VARCHAR(100) NOT NULL,
genre VARCHAR(100)
);
""")

# Create the Listens table

cursor.execute("""
CREATE TABLE IF NOT EXISTS Listens (
listen_id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL,
song_id INTEGER NOT NULL,
rating FLOAT,
listen_time TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES Users(user_id),
FOREIGN KEY (song_id) REFERENCES Songs(song_id)
);
""")

# Create the recommendations table

cursor.execute("""
CREATE TABLE IF NOT EXISTS Recommendations (
user_id INTEGER NOT NULL,
song_id INTEGER NOT NULL,
recommendation_id not NULL,
recommendation_time TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES Users(user_id),
FOREIGN KEY (song_id) REFERENCES Songs(song_id)
);
""")

# Commit changes and close the connection

conn.commit()
conn.close()

In [15]: # Connect to database again and insert sample data

conn = sqlite3.connect(dbname)
sqlite3.enable_callback_tracebacks(True)

file:///Users/kunalsahni/Downloads/sql-update-delete.html Page 2 of 6
sql-update-delete 04/02/25, 10:00 AM

cursor = conn.cursor()
cursor.execute("delete from Songs;")
cursor.execute("delete from Users;")
cursor.execute("delete from Listens;")
cursor.execute("delete from Recommendations;")

# Insert sample users

cursor.execute("""
INSERT INTO Users (user_id, name, email)
VALUES
(1, 'Mickey', '[email protected]'),
(2, 'Minnie', '[email protected]'),
(3, 'Daffy', '[email protected]'),
(4, 'Pluto', '[email protected]');
""")

# Insert sample songs from Taylor Swift, Ed Sheeran, Beatles

cursor.execute("""
INSERT INTO Songs (song_id, title, artist, genre)
VALUES
(1, 'Evermore', 'Taylor Swift', 'Pop'),
(2, 'Willow', 'Taylor Swift', 'Pop'),
(3, 'Shape of You', 'Ed Sheeran', 'Rock'),
(4, 'Photograph', 'Ed Sheeran', 'Rock'),
(5, 'Shivers', 'Ed Sheeran', 'Rock'),
(6, 'Yesterday', 'Beatles', 'Classic'),
(7, 'Yellow Submarine', 'Beatles', 'Classic'),
(8, 'Hey Jude', 'Beatles', 'Classic'),
(9, 'Bad Blood', 'Taylor Swift', 'Rock'),
(10, 'DJ Mix', 'DJ', NULL);
""")

# Insert sample listens

cursor.execute("""
INSERT INTO Listens (listen_id, user_id, song_id, rating)
VALUES
(1, 1, 1, 4.5),
(2, 1, 2, 4.2),
(3, 1, 6, 3.9),
(4, 2, 2, 4.7),
(5, 2, 7, 4.6),
(6, 2, 8, 3.9),
(7, 3, 1, 2.9),
(8, 3, 2, 4.9),
(9, 3, 6, NULL);
""")
# Commit changes and close the connection
conn.commit()
conn.close()

runSql('Users', "SELECT * FROM Users;")

runSql('Songs', "SELECT * FROM Songs;")
runSql('Listens', "SELECT * FROM Listens;")
runSql('filter users', "SELECT email FROM Users WHERE name='Mickey';")

file:///Users/kunalsahni/Downloads/sql-update-delete.html Page 3 of 6
sql-update-delete 04/02/25, 10:00 AM

Users
user_id name email

1 Mickey [email protected]

2 Minnie [email protected]

3 Daffy [email protected]

4 Pluto [email protected]

Songs
song_id title artist genre

1 Evermore Taylor Swift Pop

2 Willow Taylor Swift Pop

3 Shape of You Ed Sheeran Rock

4 Photograph Ed Sheeran Rock

5 Shivers Ed Sheeran Rock

6 Yesterday Beatles Classic

7 Yellow Submarine Beatles Classic

8 Hey Jude Beatles Classic

9 Bad Blood Taylor Swift Rock

10 DJ Mix DJ None

Listens
listen_id user_id song_id rating listen_time

1 1 1 4.5 None

2 1 2 4.2 None

3 1 6 3.9 None

4 2 2 4.7 None

5 2 7 4.6 None

6 2 8 3.9 None

7 3 1 2.9 None

8 3 2 4.9 None

9 3 6 NaN None

filter users
email

[email protected]

file:///Users/kunalsahni/Downloads/sql-update-delete.html Page 4 of 6
sql-update-delete 04/02/25, 10:00 AM

In-class exercise: Update the email address for user with the name 'Daffy' and
delete all listening records for the same user.

In [16]: # Update email address for Daffy

# original
runSql('filter users', "SELECT user_id, name, email from Users WHERE name='D

# update
runSql('upadte email', "UPDATE Users SET email='[email protected]'

# new
runSql('new filter users', "SELECT user_id, name, email from Users WHERE nam
runSql('Users', "SELECT * FROM Users;")

filter users
user_id name email

3 Daffy [email protected]

new filter users

user_id name email

3 Daffy [email protected]

Users
user_id name email

1 Mickey [email protected]

2 Minnie [email protected]

3 Daffy [email protected]

4 Pluto [email protected]

In [18]: # delete all listening records for the same user (Daffy)

# Original listening records

runSql('Listens', "SELECT * FROM Listens;")

# Update
runSql('Remove songs for user_id 3', "DELETE FROM Listens WHERE user_id = 3;

# New modified version

runSql('Listens', "SELECT * FROM Listens;")

# Conclusion : We can see below that Daffy (user_id : 3) records have been r

file:///Users/kunalsahni/Downloads/sql-update-delete.html Page 5 of 6
sql-update-delete 04/02/25, 10:00 AM

Listens
listen_id user_id song_id rating listen_time

1 1 1 4.5 None

2 1 2 4.2 None

3 1 6 3.9 None

4 2 2 4.7 None

5 2 7 4.6 None

6 2 8 3.9 None

7 3 1 2.9 None

8 3 2 4.9 None

9 3 6 NaN None

Listens
listen_id user_id song_id rating listen_time

1 1 1 4.5 None

2 1 2 4.2 None

3 1 6 3.9 None

4 2 2 4.7 None

5 2 7 4.6 None

6 2 8 3.9 None

In [ ]:

file:///Users/kunalsahni/Downloads/sql-update-delete.html Page 6 of 6