def create_playlist(playlist_name, user_id):
print 'Creating new playlist %s for user %s' % (playlist_name, user_id)
new_playlist = Playlist(user_id=user_id, name=playlist_name)
DB_SESSION.add(new_playlist)
DB_SESSION.commit()
# Seed soundcloud_songs database table
map(populate_soundcloud_songs, get_similar_artists(playlist_name))
return new_playlist
def get_similar_artists(artist, limit=10, min_song_ct=5, artist_first=True):
"""Returns a list of similar artist names to @artist."""
# Skip the ORM and directly execute the SQL for performance reasons
QUERYSTR = text('''
SELECT song1.artist, song2.artist, COUNT(sim.similarity) AS sim_count,
scounts.count AS song_count,
COUNT(sim.similarity) / CAST(scounts.count AS FLOAT) AS sim_count_norm
FROM songs AS song1 JOIN similarities AS sim ON song1.song_id=sim.song1_id
JOIN songs AS song2 ON sim.song2_id=song2.song_id
JOIN (SELECT s.artist AS artist, COUNT(s.id) AS count
FROM songs AS s
GROUP BY s.artist) AS scounts ON song2.artist=scounts.artist
WHERE song1.artist=:artist AND scounts.count > :count
GROUP BY song1.artist, song2.artist, scounts.artist, scounts.count
ORDER BY sim_count_norm DESC
LIMIT :limit;
''')
# This *should* be secure since it uses bound parameters which are
# passed to the underlying DPAPI:
# http://docs.sqlalchemy.org/en/rel_0_9/orm/session_api.html
# #sqlalchemy.orm.session.Session.execute
# http://docs.sqlalchemy.org/en/rel_0_9/core/sqlelement.html
# #sqlalchemy.sql.expression.text
# http://docs.sqlalchemy.org/en/rel_0_9/core/sqlelement.html
# #sqlalchemy.sql.expression.bindparam
params = { 'artist': artist, 'count': min_song_ct, 'limit': limit }
# Result tuple: ('artist1', 'artist2', sim_ct, song_ct, norm_sim_ct)
artists = [row[1] for row in DB_SESSION.execute(QUERYSTR, params)]
if artist_first:
if artist in artists:
artists.remove(artist)
return [artist] + artists
else:
return artists
def update_or_create(model, defaults={}, commit=True, **kwargs):
"""
Equivalent of Django's update_or_create, with an additional option to
commit the transaction (commits by default).
@param model: Model to update, e.g. Song
@param defaults: Parameters to update, e.g. {
'playback_count': 1000, 'likes_count': 10
}
@param commit: Commit the transaction?
@param **kwargs: Parameters to check uniqueness on, e.g. {
'title': 'How We Do', 'artist': '50 Cent'
}
"""
model_instance = DB_SESSION.query(model).filter_by(**kwargs).first()
if model_instance:
for arg, value in defaults.iteritems():
setattr(model_instance, arg, value)
if commit:
DB_SESSION.commit()
return model_instance, True
else:
params = { k: v for k, v in kwargs.iteritems() }
params.update(defaults)
model_instance = model(**params)
DB_SESSION.add(model_instance)
if commit:
DB_SESSION.commit()
return model_instance, False
def get_similar_songs(artist, limit=10):
"""Returns a list of similar (song, artist) tuples to @artist."""
QUERYSTR = text('''
SELECT song1.artist, song2.title, song2.artist, SUM(sim.similarity) AS total_sim
FROM songs AS song1 JOIN similarities AS sim ON song1.song_id=sim.song1_id
JOIN songs AS song2 ON sim.song2_id=song2.song_id
WHERE song1.artist=:artist
GROUP BY song1.artist, song2.title, song2.artist
ORDER BY total_sim DESC
LIMIT :limit;
''')
params = { 'artist': artist, 'limit': limit }
# Result tuple: ('artist1', 'song2', 'artist2', sim)
return ['%s %s' % (row[1], row[2])
for row in DB_SESSION.execute(QUERYSTR, params)]
def artists():
def artist_filter(artist_str):
stop_words = ['ft.', 'feat', 'featuring', '_', '+', '/', '|']
artist_str = artist_str.lower()
for stop_word in stop_words:
if stop_word in artist_str:
return False
return True
# Result set is a list of tuples [('[Artist]',), ...]
artists = [s[0] for s in DB_SESSION.query(Song.artist.distinct())
if artist_filter(s[0])]
return jsonify({
'length': len(artists),
'data': artists
})
def create_user(username, token, secret):
new_account = User(username=username, token=token, secret=secret)
DB_SESSION.add(new_account)
DB_SESSION.commit()
return new_account
def delete_playlist(playlist_name, user_id):
playlist = (Playlist.query
.filter_by(user_id=user.id, name=playlist_name)
.one())
DB_SESSION.delete(playlist)
DB_SESSION.commit()
def shutdown_session(exception=None):
DB_SESSION.remove()