def get_playlists_songs(playlist_id):
"""
:param playlist_id:
:return: Returns a list of Song objects related to given playlist_id
"""
with app.test_request_context():
cur = mysql.connection.cursor()
cmd = f"SELECT" \
f" song_list.song_id," \
f" song_list.title," \
f" song_list.tempo," \
f" song_list.hotness," \
f" song_list.loudness," \
f" song_list.album_name," \
f" song_list.year," \
f" artists.name" \
f" FROM (" \
f" SELECT distinct songs.*" \
f" FROM playlists_songs,songs" \
f" WHERE playlists_playlist_id = '{thwart(str(playlist_id)).decode()}'" \
f" AND songs.song_id = playlists_songs.songs_song_id" \
f" ) as song_list , artists" \
f" WHERE song_list.artist_id = artists.artist_id"
# print(cmd)
cur.execute(cmd)
mysql.connection.commit()
songs = cur.fetchall()
songs = [Song(*s) for s in songs]
# print(songs[1])
cur.close()
return songs
def create_user(form):
"""
Creates a user on database
:param form:
:return:
"""
with app.test_request_context():
username = form.username.data
# Encrypt password
password = sha256_crypt.encrypt(str(form.password.data))
birth_date = form.birth_date.data
email = form.email.data
# sql user exists query execution
cur = mysql.connection.cursor()
user = get_user(form.username.data)
# if user with that name already exists
if user:
return 0
# thwart is used to escape chars correctly
cmd = f"INSERT INTO users (username,password,birth_date,email) VALUES ( '{thwart(username).decode()}'," \
f"'{thwart(password).decode()}','{thwart(str(birth_date)).decode()}','{thwart(email).decode()}')"
# print(cmd)
cur.execute(cmd)
mysql.connection.commit()
cur.close()
# TODO: IS THIS NECESSARY ?
# mysql.connection.close()
# gc.collect()
return 1
def _new_db_playlist(self, user):
"""
Create a new playlist on database
:param user:
:return: Playlist ID on database
"""
with app.test_request_context():
# Insert a new playlist
cur = mysql.connection.cursor()
cmd = f"INSERT INTO playlists(user_id,name,mood,type)" \
f" VALUES('{thwart(str(user.id)).decode()}'," \
f"'{thwart(self.name).decode()}'," \
f"'{thwart(self.mood).decode()}'," \
f"'{thwart(self.ptype).decode()}'" \
f");"
# print(cmd)
cur.execute(cmd)
mysql.connection.commit()
# Get created playlist's ID
cmd = "SELECT LAST_INSERT_ID();"
cur.execute(cmd)
mysql.connection.commit()
id = cur.fetchone()[0]
# print(id)
cur.close()
return id
def get_playlist_by_pid(playlist_id):
"""
:param playlist_id:
:return: Returns Playlist Object by playlist_id
"""
with app.test_request_context():
cur = mysql.connection.cursor()
condition = f"playlist_id = '{thwart(str(playlist_id)).decode()}'"
# Get playlist object by ID
playlist = Playlist.get_playlist_data_by(condition)[0]
# fill playlist's songs
playlist.songs = Playlist.get_playlists_songs(playlist_id)
return playlist
def get_user_playlists(username):
"""
Get user playlist by user name
:param username:
:return:
"""
from db.Playlist import Playlist
with app.test_request_context():
user = get_user(username)
if not user:
return []
condition = f"user_id = '{thwart(str(user.id)).decode()}'"
return Playlist.get_playlist_data_by(condition)
def save_playlist(self, username):
"""
Save current playlist to database
:param username:
:return:
"""
from db.User import get_user
with app.test_request_context():
# get User object by username
user = get_user(username)
if not user:
raise KeyError('User does not exist')
playlist_id = self._new_db_playlist(user)
self._insert_playlist_songs(playlist_id)
def _insert_playlist_songs(self, playlist_id):
"""
Save playlist's songs on database
:param playlist_id:
:return: None
"""
with app.test_request_context():
cur = mysql.connection.cursor()
for song in self.songs:
cmd = f"INSERT INTO playlists_songs " \
f"VALUES('{thwart(str(playlist_id)).decode()}'," \
f"'{thwart(song.id).decode()}')"
print(cmd)
cur.execute(cmd)
mysql.connection.commit()
cur.close()
def get_user(username):
"""
Returns user info by username. usernames are unique.
:param username:
:return:
"""
with app.test_request_context():
c = mysql.connection.cursor()
cmd = f"SELECT * FROM users WHERE username = '******'"
# Query to db
c.execute(cmd)
user = c.fetchone()
if not user:
return 0
mysql.connection.commit()
c.close()
return User(*user)
def get_playlist_data_by(condition):
"""
:param condition:
:return: Returns all of the playlist data by given condition
"""
with app.test_request_context():
c = mysql.connection.cursor()
cmd = f"SELECT * FROM playlists WHERE {condition}"
# print(cmd)
c.execute(cmd)
playlists = c.fetchall()
if not playlists:
return []
mysql.connection.commit()
c.close()
playlists = [Playlist(playlist[4], playlist[3], name=playlist[2], pid=playlist[0]) for playlist in
playlists]
return playlists
def generate_from_db(self):
"""
Generates playlist's songs from the database.
:return: None
"""
with app.test_request_context():
cur = mysql.connection.cursor()
# We first set the required parameters for the query
cur.execute(f"SET @type_rows = 0, @mood_rows = 0, @hot1 = 0, @hot2=0, @hot3 = 0, @hot4 = 0;")
# Filter songs by params , get their IDs
mf = Moods.mood_from_string(self.mood)
tf = Types.type_from_string(self.ptype)
query = Playlist.filter_query(mf, tf)
# Query to db
cur.execute(query)
mysql.connection.commit()
res = cur.fetchall()
cur.close()
# Get all the songs data
cur = mysql.connection.cursor()
songs = []
# Generate song object from IDs
for row in res:
query = f"SELECT songs.song_id," \
f" songs.title," \
f" songs.tempo," \
f" songs.hotness," \
f" songs.loudness," \
f" songs.album_name," \
f" songs.year," \
f"artists.name" \
f" FROM songs,artists " \
f"WHERE songs.artist_id = artists.artist_id " \
f"AND songs.song_id='{row[3]}';"
cur.execute(query)
mysql.connection.commit()
song = cur.fetchone()
songs.append((Song(*song)))
cur.close()
self.songs = songs
def json_to_playlist(playlistJson):
"""
Generates a playlist object , from an existing HTML page , containing the required data.
:param playlistJson:
:return: Playlist object
"""
with app.test_request_context():
i = 1
ids = []
# Get parameters from json object
playlist_name = playlistJson['name']
mood = playlistJson['mood']
ptype = playlistJson['ptype']
songs = []
# Get all songs ids
for _id in playlistJson['songs']:
ids.append(_id)
cur = mysql.connection.cursor()
cmd = f"SELECT songs.song_id," \
f" songs.title," \
f" songs.tempo," \
f" songs.hotness," \
f" songs.loudness," \
f" songs.album_name," \
f" songs.year," \
f"artists.name" \
f" FROM songs,artists " \
f"WHERE songs.artist_id = artists.artist_id " \
f"AND songs.song_id='{thwart(_id).decode()}';"
# Query to db
cur.execute(cmd)
mysql.connection.commit()
res = cur.fetchone()
cur.close()
song = Song(*res)
songs.append(song)
i += 1
return Playlist(ptype, mood, playlist_name, songs)
