def get_all_users():
'''
Get all available users
'''
session = get_session()
all_users = session.query(User).filter(User.is_valid == 1).all()
return all_users
def add_user(username, session_key, register_time):
session = get_session()
user = User(username=username,
session_key=session_key,
register_time=register_time)
session.add(user)
session.commit()
def delete_invalid_tracks(username):
'''
Delete all tracks without any mp3 info
and delete user track
'''
db_session = get_session()
invalid_tracks = db_session.query(TrackInfo)\
.filter(TrackInfo.mp3_url.is_(None)).all()
print(len(invalid_tracks))
for invalid_track in invalid_tracks:
db_session.delete(invalid_track)
db_session.commit()
tracks_uuids = [track.track_uuid for track in invalid_tracks]
lib_tracks = db_session.query(UserTrack)\
.filter(UserTrack.username == username)\
.filter(UserTrack.track_uuid.in_(tracks_uuids)).all()
for lib_track in lib_tracks:
db_session.delete(lib_track)
db_session.commit()
rec_tracks = db_session.query(UserTrack)\
.filter(UserTrack.username == username)\
.filter(UserTrack.track_uuid.in_(tracks_uuids)).all()
for rec_track in rec_tracks:
db_session.delete(rec_track)
db_session.commit()
def test():
db_session = get_session()
for emotion in main.NOT_EMOTION_TAGS:
tags = db_session.query(TrackInfo)\
.filter(TrackInfo.tag == emotion).all()
print(emotion)
print(len(tags))
def get_emotion_tracks(username, emotion):
'''
Get all tracks from one emotion
'''
db_session = get_session()
return db_session.query(EmotionTag).\
filter(EmotionTag.username == username).\
filter(EmotionTag.emotion == emotion).all()
def choose_no_emotion_tracks():
'''
Choose all tracks without any emotion value(-100)
'''
db_session = get_session()
tracks_info = db_session.query(TrackInfo)\
.filter(TrackInfo.emotion_value == -100).all()
return tracks_info
def get_session_key(username):
'''
Get user's session key
'''
db_session = get_session()
chosen_user = db_session.query(User).filter(User.username == username).\
first()
return chosen_user.session_key
def update_session(username, session_key):
'''
Update session for the user
'''
session = get_session()
chosen_user = session.query(User).filter(User.username == username).first()
chosen_user.session_key = session_key
session.commit()
def choose_all_tracks(username):
"""
Choose All tracks from one user
"""
db_session = get_session()
all_tracks = db_session.query(UserTrack)\
.filter(UserTrack.username == username).all()
return all_tracks
def choose_rec_tracks(username):
'''
Choose all recommendation tracsk from user
'''
db_session = get_session()
rec_tracks = db_session.query(RecTrack)\
.filter(RecTrack.username == username).all()
return rec_tracks
def choose_tracks_info(all_tracks):
'''
Choose all the tracks info like mp3 url
'''
db_session = get_session()
tracks_uuids = [track.track_uuid for track in all_tracks]
tracks_info = db_session.query(TrackInfo)\
.filter(TrackInfo.track_uuid.in_(tracks_uuids)).all()
return tracks_info
def get_random():
'''
Get random background
'''
db_session = get_session()
random_row = random.randrange(0, db_session.query(Background).count())
background = db_session.query(Background)[random_row]
background_url = background.background_url
print(background_url)
return background_url
def get_one_track_detail(track_uuid):
'''
Get one user's track in detail like mp3 url
'''
db_session = get_session()
one_track = db_session.query(TrackInfo)\
.filter(TrackInfo.track_uuid == track_uuid).first()
print(one_track)
one_track = _extra_info(one_track)
return one_track
def get_source_rec(last_title, username):
'''
According to the last track title to get the rec songs
'''
db_session = get_session()
# return None
sample_tracks = db_session.query(TrackInfo).\
join(RecTrack, RecTrack.track_uuid == TrackInfo.track_uuid)\
.filter(RecTrack.username == username)\
.filter(RecTrack.source_type == 1)\
.filter(RecTrack.source == last_title)\
.all()
return sample_tracks
def add_rec_tracks(username, rec_tracks):
'''
Add the recommendation tracks into database
'''
add_tracks_info(rec_tracks)
db_session = get_session()
for rec_track in rec_tracks:
rec_track = RecTrack(username=username,
track_uuid=rec_track.track_uuid,
source_type=rec_track.source_type,
source=rec_track.source)
db_session.add(rec_track)
db_session.commit()
def add_artist_tracks_emotion(user_tracks):
db_session = get_session()
tracks_info = dict([(track.track_uuid,
[track.emotion_value, track.top_tags])
for track in user_tracks])
emotion_tracks = db_session.query(TrackInfo)\
.filter(TrackInfo.track_uuid.in_(tracks_info.keys())).all()
for emotion_track in emotion_tracks:
emotion_track.emotion_value = tracks_info[emotion_track.track_uuid][0]
if tracks_info[emotion_track.track_uuid][1]:
tags = tracks_info[emotion_track.track_uuid][1]
for tag_track, number in zip(tags, range(1, 5)):
setattr(emotion_track, 'tag%s' % number, tag_track[0])
setattr(emotion_track, "tag_value%s" % number, tag_track[1])
db_session.commit()
def store_tracks_info(user_tracks):
'''
Store tracks info like mp3 url
'''
db_session = get_session()
for track in user_tracks:
track_info = db_session.query(TrackInfo)\
.filter(TrackInfo.track_uuid == track.track_uuid).first()
track_info.mp3_url = track.mp3_url
track_info.album_url = track.album_url
track_info.album_id = track.album_id
track_info.artist_id = track.artist_id
track_info.duration = track.duration
track_info.song_id = track.song_id
db_session.commit()
def add_tracks(username, final_tracks_list):
'''
Add user tracks info into UserTrack
'''
add_tracks_info(final_tracks_list)
session = get_session()
for final_track in final_tracks_list:
track_uuid = final_track.track_uuid
level = final_track.level
is_star = final_track.is_star
user_track = UserTrack(username=username,
track_uuid=track_uuid,
level=level,
is_star=is_star)
session.add(user_track)
session.commit()
def store_emotion(username, emotion_array, track_type):
'''
Store emotion in database
emotion_array df9d13ea
'''
db_session = get_session()
for emotion in emotion_array:
emotion_tag = EmotionTag(track_uuid=emotion.track_uuid,
username=username,
track_type=track_type,
artist=emotion.artist,
title=emotion.title,
emotion=emotion.emotion[0],
emotion_value=emotion.emotion[1])
db_session.add(emotion_tag)
db_session.commit()
def add_tracks_info(final_tracks_list):
'''
Add tracks basic info into TrackInfo
'''
db_session = get_session()
for final_track in final_tracks_list:
added_track = db_session.query(TrackInfo)\
.filter(TrackInfo.artist == final_track.artist)\
.filter(TrackInfo.title == final_track.title).first()
if added_track:
final_track.track_uuid = added_track.track_uuid
else:
track = TrackInfo(track_uuid=final_track.track_uuid,
artist=final_track.artist,
title=final_track.title)
db_session.add(track)
db_session.commit()
def add_tracks_emotion(tracks_list_mix):
'''
Add the tracks' emotion to db
'''
db_session = get_session()
tracks_info = dict([(track[0].track_uuid,
[track[0].emotion_value, track[1]])
for track in tracks_list_mix])
emotion_tracks = db_session.query(TrackInfo)\
.filter(TrackInfo.track_uuid.in_(tracks_info.keys())).all()
for emotion_track in emotion_tracks:
emotion_track.emotion_value = tracks_info[emotion_track.track_uuid][0]
if tracks_info[emotion_track.track_uuid][1]:
tags = tracks_info[emotion_track.track_uuid][1]
for tag_track, number in zip(tags, range(1, 5)):
setattr(emotion_track, 'tag%s' % number, tag_track[0])
setattr(emotion_track, "tag_value%s" % number, tag_track[1])
db_session.commit()
def get_user_tracks_detail(emotion_range, last_tag, uuids):
'''
'''
db_session = get_session()
emo_start, emo_end = emotion_range
if last_tag:
# @(todo) if no tracks return refetch again with larger emotion range
sample_tracks = db_session.query(TrackInfo)\
.filter(TrackInfo.track_uuid.in_(uuids))\
.filter(TrackInfo.emotion_value >= emo_start)\
.filter(TrackInfo.emotion_value <= emo_end)\
.filter(or_(TrackInfo.tag1 == last_tag,
TrackInfo.tag2 == last_tag,
TrackInfo.tag3 == last_tag,
TrackInfo.tag4 == last_tag))\
.all()
else:
sample_tracks = db_session.query(TrackInfo)\
.filter(TrackInfo.track_uuid.in_(uuids))\
.filter(TrackInfo.emotion_value >= emo_start)\
.filter(TrackInfo.emotion_value <= emo_end).all()
return sample_tracks
def do_exist_user(username):
'''
Check wheter user has alredy existed
'''
session = get_session()
return session.query(User).filter(User.username == username).first()
def get_top_level_tracks(username):
db_session = get_session()
all_top_tracks = db_session.query(UserTrack)\
.filter(UserTrack.username == username)\
.filter(UserTrack.level == 4).all()
return all_top_tracks
