def getPlaylistTracks(user, playlist_id):
query_str = (
"""SELECT artist, track_name, trackid FROM playlist INNER JOIN tracks ON tracks.playlistID=playlist.id WHERE playlist.id='%s' AND playlist.owner='%s' """
% (playlist_id, user))
#result = connection.execute(query_str).fetchall()
result_dict = dict()
result_list = list()
result = connection.execute(query_str)
if result.rowcount != 0:
for row in result.fetchall():
row_dict = dict(row)
result_list.append(row_dict)
query_str = (
"""SELECT name,owner,message FROM playlist WHERE id='%s' AND owner='%s' """
% (playlist_id, user))
playlist_results = connection.execute(query_str).fetchall()[0]
result_dict['tracks'] = result_list
result_dict['owner'] = playlist_results['owner']
result_dict['message'] = playlist_results['message']
result_dict['playlist_name'] = playlist_results['name']
return result_dict
def saveTrack(youtubeURL, playlist_id, trackName, artistName):
result_dict = dict()
trackName = trackName.replace("'",'').replace('"','')
artistName = artistName.replace("'",'').replace('"','')
track_id = int(time.time())
query_str = ("""INSERT INTO tracks (trackid, playlistid, artist, track_name) VALUES ('%s', '%s','%s','%s'); """ % (track_id, playlist_id, artistName, trackName))
if connection.execute(query_str):
# only download if song does not already exist in $OPENSHIFT_DATA_LOG
query_str = ("""SELECT * FROM tracks WHERE track_name='%s' AND artist='%s'""" % (trackName, artistName))
result = connection.execute(query_str)
result_dict['artist'] = artistName
result_dict['track_name'] = trackName
result_dict['track_id'] = track_id
if result.rowcount <= 1: # only need to save one copy of track
download(youtubeURL, artistName, trackName, track_id)
else:
time.sleep(1) # must sleep in case track is already downloaded, then track_id may yield duplicates if data is processed to quickly
return result_dict
def saveTrack(youtubeURL, playlist_id, trackName, artistName):
result_dict = dict()
trackName = trackName.replace("'", '').replace('"', '')
artistName = artistName.replace("'", '').replace('"', '')
track_id = int(time.time())
query_str = (
"""INSERT INTO tracks (trackid, playlistid, artist, track_name) VALUES ('%s', '%s','%s','%s'); """
% (track_id, playlist_id, artistName, trackName))
if connection.execute(query_str):
# only download if song does not already exist in $OPENSHIFT_DATA_LOG
query_str = (
"""SELECT * FROM tracks WHERE track_name='%s' AND artist='%s'""" %
(trackName, artistName))
result = connection.execute(query_str)
result_dict['artist'] = artistName
result_dict['track_name'] = trackName
result_dict['track_id'] = track_id
if result.rowcount <= 1: # only need to save one copy of track
download(youtubeURL, artistName, trackName, track_id)
else:
time.sleep(
1
) # must sleep in case track is already downloaded, then track_id may yield duplicates if data is processed to quickly
return result_dict
def createUserTable():
connection.execute("""
CREATE TABLE users (
username VARCHAR PRIMARY KEY,
password VARCHAR NOT NULL
);
""")
def createPlaylistTable():
connection.execute("""
CREATE TABLE playlist(
id VARCHAR PRIMARY KEY,
name VARCHAR NOT NULL,
owner VARCHAR NOT NULL
);
""")
def createTrackTable():
connection.execute("""
CREATE TABLE tracks (
playlistid,
trackid,
artist,
track_name
);
""")
def createUserTable():
connection.execute(
"""
CREATE TABLE users (
username VARCHAR PRIMARY KEY,
password VARCHAR NOT NULL
);
"""
)
def createPlaylistTable(): connection.execute( """ CREATE TABLE playlist( id VARCHAR PRIMARY KEY, name VARCHAR NOT NULL, owner VARCHAR NOT NULL ); """ )
def createTrackTable():
connection.execute(
"""
CREATE TABLE tracks (
playlistid,
trackid,
artist,
track_name
);
"""
)
def createUser(un, pw):
rt_message = 0
query_str = ("""SELECT * FROM users WHERE username='******' """ % (un))
if connection.execute(query_str).rowcount > 0:
rt_message = 0
else:
query_str = ("""INSERT INTO users (username, password) VALUES ('%s', '%s'); """ % (un, pw))
if connection.execute(query_str):
rt_message = 1
return rt_message
def createUser(un, pw):
rt_message = 0
query_str = ("""SELECT * FROM users WHERE username='******' """ % (un))
if connection.execute(query_str).rowcount > 0:
rt_message = 0
else:
query_str = (
"""INSERT INTO users (username, password) VALUES ('%s', '%s'); """
% (un, pw))
if connection.execute(query_str):
rt_message = 1
return rt_message
def getUser(un, pw):
query_str = (
"""SELECT * FROM users WHERE username='******' AND password='******'; """ %
(un, pw))
result = connection.execute(query_str)
return result.rowcount
def createNewPlaylist(playlist_id, playlist_name, message, user):
print ('Creating playlist %s' % (playlist_id))
rt_val = 0
query_str = ("""INSERT INTO playlist (owner, message, id, name) VALUES ('%s', '%s','%s','%s'); """ % (user, message, playlist_id, playlist_name))
if connection.execute(query_str):
rt_val = 1
return rt_val
def createNewPlaylist(playlist_id, playlist_name, message, user):
print('Creating playlist %s' % (playlist_id))
rt_val = 0
query_str = (
"""INSERT INTO playlist (owner, message, id, name) VALUES ('%s', '%s','%s','%s'); """
% (user, message, playlist_id, playlist_name))
if connection.execute(query_str):
rt_val = 1
return rt_val
def deleteTrack(track_id):
query_str = ("""SELECT * FROM tracks WHERE trackid='%s'""" % (track_id))
result = connection.execute(query_str).fetchall()[0]
artistName = result['artist']
trackName = result['track_name']
time.sleep(1) # make sure user gets track before it is deleted
query_str = ("""DELETE FROM tracks WHERE trackid='%s'""" % (track_id))
if connection.execute(query_str):
# only delete file if track no longer exists in tracks table
query_str = ("""SELECT * FROM tracks WHERE track_name='%s' AND artist='%s'""" % (trackName, artistName))
result = connection.execute(query_str)
if result.rowcount == 0:
filename = getTrackName(artistName, trackName)
path_to_track = ('"%s/%s"' % (app.config['DEST_DIR'], filename))
cmd = ("""rm %s""" % (path_to_track))
os.popen(cmd)
rt_val = 1
return rt_val
def deleteTrack(track_id):
query_str = ("""SELECT * FROM tracks WHERE trackid='%s'""" % (track_id))
result = connection.execute(query_str).fetchall()[0]
artistName = result['artist']
trackName = result['track_name']
time.sleep(1) # make sure user gets track before it is deleted
query_str = ("""DELETE FROM tracks WHERE trackid='%s'""" % (track_id))
if connection.execute(query_str):
# only delete file if track no longer exists in tracks table
query_str = (
"""SELECT * FROM tracks WHERE track_name='%s' AND artist='%s'""" %
(trackName, artistName))
result = connection.execute(query_str)
if result.rowcount == 0:
filename = getTrackName(artistName, trackName)
path_to_track = ('"%s/%s"' % (app.config['DEST_DIR'], filename))
cmd = ("""rm %s""" % (path_to_track))
os.popen(cmd)
rt_val = 1
return rt_val
def getPlaylistTracks(user, playlist_id):
query_str = ("""SELECT artist, track_name, trackid FROM playlist INNER JOIN tracks ON tracks.playlistID=playlist.id WHERE playlist.id='%s' AND playlist.owner='%s' """ % (playlist_id,user))
#result = connection.execute(query_str).fetchall()
result_dict = dict()
result_list= list()
result = connection.execute(query_str)
if result.rowcount != 0:
for row in result.fetchall():
row_dict = dict(row)
result_list.append(row_dict)
query_str = ("""SELECT name,owner,message FROM playlist WHERE id='%s' AND owner='%s' """ % (playlist_id, user))
playlist_results = connection.execute(query_str).fetchall()[0]
result_dict['tracks'] = result_list
result_dict['owner'] = playlist_results['owner']
result_dict['message'] = playlist_results['message']
result_dict['playlist_name'] = playlist_results['name']
return result_dict
def getUser(un, pw):
query_str = ("""SELECT * FROM users WHERE username='******' AND password='******'; """ % (un, pw))
result = connection.execute(query_str)
return result.rowcount
