def get(self, playlist_id: int) -> Playlist:
cursor = self.database_connector.cursor()
query = "SELECT * FROM Playlists WHERE playlist_id = %s"
cursor.execute(query, (playlist_id, ))
results = cursor.fetchone()
if results is None:
cursor.close()
raise PlaylistNotFound()
playlist_id, title = results
playlist = Playlist()
playlist.playlist_id = playlist_id
playlist.title = title
query = "SELECT user_id FROM Users_Playlists WHERE playlist_id = %s"
cursor.execute(query, (playlist_id, ))
user_id, = cursor.fetchone()
playlist.user_id = user_id
query = "SELECT s.song_id, s.album_id, s.artist_id, s.title, s.duration FROM Songs s, Playlist_Songs ps WHERE " \
"s.song_id = ps.song_id AND ps.playlist_id = %s"
cursor.execute(query, (playlist_id, ))
for (song_id, album_id, artist_id, title, duration) in cursor:
song = Song()
song.song_id = song_id
song.artist_id = artist_id
song.album_id = album_id
song.title = title
song.duration = duration
playlist.songs.append(song)
for song in playlist.songs:
query = "SELECT artist_name FROM Artists WHERE artist_id = %s"
cursor.execute(query, (song.artist_id, ))
artist_name, = cursor.fetchone()
song.artist_name = artist_name
for song in playlist.songs:
query = "SELECT title FROM Albums WHERE album_id = %s"
cursor.execute(query, (song.album_id, ))
title, = cursor.fetchone()
song.album_name = title
cursor.nextset()
cursor.close()
return playlist
def retrive(self, album_id: int) -> Album:
cursor = self.database_connector.cursor()
query = "SELECT * FROM Albums WHERE album_id = %s"
cursor.execute(query, (album_id,))
album = Album()
(album_id, title, year, artist_id, genre_id) = cursor.fetchone()
album.album_id = album_id
album.artist_id = artist_id
album.year = year
album.genre_id = genre_id
album.title = title
query = "SELECT genre_name FROM Genres WHERE genre_id = %s"
cursor.execute(query, (album.genre_id,))
genre_name, = cursor.fetchone()
album.genre_name = genre_name
query = "SELECT artist_name FROM Artists WHERE artist_id = %s"
cursor.execute(query, (album.artist_id,))
artist_name, = cursor.fetchone()
album.artist_name = artist_name
query = "SELECT * FROM Songs WHERE album_id = %s"
cursor.execute(query, (album_id,))
songs = []
for (song_id, album_id, artist_id, title, duration) in cursor:
song = Song()
song.song_id = song_id
song.artist_id = artist_id
song.artist_name = album.artist_name
song.album_id = album.album_id
song.album_name = album.title
song.title = title
song.duration = duration
song.genre_id = album.genre_id
song.genre_name = album.genre_name
songs.append(song)
cursor.close()
album.songs = songs
return album