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)