def cacheMusicBrainzDB(): artist = table('musicbrainz.artist') aa = table('musicbrainz.artist_alias') s = select([artist.c.id, artist.c.mbid, artist.c.name, artist.c.sort_name, artist.c.artist_type, artist.c.area_id, artist.c.gender, artist.c.disambiguation]) locales = ['es', 'en'] for a in MusicDatabase.execute(s).fetchall(): s2 = (select([aa.c.name, aa.c.sort_name, aa.c.locale, aa.c.artist_alias_type, aa.c.primary_for_locale]) .where(and_(aa.c.artist_id == a['id'], aa.c.locale.in_(locales)))) current = {} for x in MusicDatabase.execute(s2).fetchall(): if MusicBrainzDatabase.is_better_alias(x, current): current = {'locale_name': x['name'], 'locale_sort_name': x['sort_name'], 'locale': x['locale'], 'artist_alias_type': x['artist_alias_type']} if not current: current = {'locale_name': a['name'], 'locale_sort_name': a['sort_name'], 'locale': None, 'artist_alias_type': None} current['id'] = a['id'] MusicDatabase.insert_or_update('artists_mb', current)
def get_release_label(releaseID): rl = table('musicbrainz.release_label') label = table('musicbrainz.label') s = (select([label.c.name.label('label_name'), rl.c.catalog_number]) .where(and_(rl.c.label_id == label.c.id, rl.c.release_id == releaseID))) return MusicDatabase.execute(s).fetchall()
def get_release_mediums(releaseID): m = table('musicbrainz.medium') emfv = table('musicbrainz.enum_medium_format_values') s = (select([m.c.id, m.c.release_id, m.c.position, emfv.c.name.label('format_name'), m.c.name]) .where(and_(m.c.format == emfv.c.id_value, m.c.release_id == releaseID)) .order_by(m.c.position)) return MusicDatabase.execute(s).fetchall()
def get_release_group_info(rgID): rg = table('musicbrainz.release_group') ac = table('musicbrainz.artist_credit') s = (select([rg.c.id, rg.c.mbid, rg.c.name, rg.c.disambiguation, rg.c.release_group_type, rg.c.artist_credit_id, ac.c.name.label('artist_name')]) .where(and_(rg.c.artist_credit_id == ac.c.id, rg.c.id == rgID))) return MusicDatabase.execute(s).fetchone()
def get_artists_info(artistIDs): artist = table('musicbrainz.artist') artists_mb = table('artists_mb') s = (select([artist.c.id, artist.c.mbid, artist.c.name, artist.c.artist_type, artist.c.area_id, artist.c.gender, artist.c.disambiguation, artists_mb.c.locale_name, artists_mb.c.locale_sort_name, artists_mb.c.image_path]) .where(and_(artist.c.id == artists_mb.c.id, artist.c.id.in_(artistIDs)))) return MusicDatabase.execute(s).fetchall()
def get_range_artists(offset=0, page_size=500, metadata=False): artist = table('musicbrainz.artist') artists_mb = table('artists_mb') s = (select([artist.c.id, artist.c.mbid, artist.c.name, artist.c.artist_type, artist.c.area_id, artist.c.gender, artist.c.disambiguation, artists_mb.c.locale_name, artists_mb.c.locale_sort_name, artists_mb.c.image_path]) .where(artist.c.id == artists_mb.c.id) .order_by(artists_mb.c.locale_name) .limit(page_size) .offset(offset)) return MusicDatabase.execute(s).fetchall()
def create_in_db(self): if self.id: return c = MusicDatabase.getCursor() sql = text('INSERT INTO playlists (name, owner_id, playlist_type)' 'VALUES (:name, :owner_id, :playlist_type)') c.execute(sql.bindparams(name=self.name, owner_id=self.owner_id, playlist_type=self.playlist_type)) try: sql = "SELECT currval(pg_get_serial_sequence('playlists','id'))" result = c.execute(sql) except exc.OperationalError: # Try the sqlite way sql = 'SELECT last_insert_rowid()' result = c.execute(sql) self.id = result.fetchone()[0] pls = table('playlist_songs') for idx, item in enumerate(self.songs): entry = { 'playlist_id': self.id, 'song_id': item[0], 'recording_mbid': item[1], 'pos': idx, } MusicDatabase.insert_or_update(pls, entry, and_(pls.c.playlist_id == self.id, pls.c.pos == idx), connection=c) c.commit()
def insert_song(self, position, song_id=None, mbid=None, *, connection=None): assert song_id, "song_id must be used" if position > len(self.songs): position = len(self.songs) if not mbid: mbid = MusicDatabase.getRecordingMBID(song_id) item = (song_id, mbid) self.songs.insert(position, item) if self.id: c = connection or MusicDatabase.getCursor() self._update_positions_in_db(len(self.songs) - 1, position, 1, c) entry = { 'playlist_id': self.id, 'pos': position, 'song_id': song_id, 'recording_mbid': mbid, } pls = table('playlist_songs') MusicDatabase.insert_or_update(pls, entry, and_(pls.c.playlist_id == self.id, pls.c.pos == position), connection=c) if not connection: c.commit()
def append_song(self, song_id, mbid=None, *, connection=None): assert song_id, "song_id must be used" if not mbid: print(f'Obtaining MBID for song id {song_id}') mbid = MusicDatabase.getRecordingMBID(song_id) item = (song_id, mbid) self.songs.append(item) if self.id and self.store_songs_in_db: pos = len(self.songs) - 1 c = connection or MusicDatabase.getCursor() entry = { 'playlist_id': self.id, 'song_id': song_id, 'recording_mbid': mbid, 'pos': pos, } pls = table('playlist_songs') MusicDatabase.insert_or_update(pls, entry, and_(pls.c.playlist_id == self.id, pls.c.pos == pos), connection=c) if not connection: c.commit()
def insertMusicBrainzTags(song_id, mbIDs): MusicBrainzDatabase.insertMBArtistIDs(song_id, mbIDs.artistids) MusicBrainzDatabase.insertMBAlbumArtistIDs(song_id, mbIDs.albumartistids) MusicBrainzDatabase.insertMBWorkIDs(song_id, mbIDs.workids) songs_mb = table('songs_mb') mbTagRecord = songs_mb.select(songs_mb.c.song_id == song_id) mbTagRecord = MusicDatabase.execute(mbTagRecord).fetchone() if mbTagRecord: if (mbTagRecord['releasegroupid'] != mbIDs.releasegroupid or mbTagRecord['releaseid'] != mbIDs.releaseid or mbTagRecord['releasetrackid'] != mbIDs.releasetrackid or mbTagRecord['recordingid'] != mbIDs.recordingid or mbTagRecord['confirmed'] != mbIDs.confirmed): print(f'update mb data for {song_id}') u = songs_mb.update() \ .where(songs_mb.c.song_id == song_id) \ .values(song_id=song_id, releasegroupid=mbIDs.releasegroupid, releaseid=mbIDs.releaseid, releasetrackid=mbIDs.releasetrackid, recordingid=mbIDs.recordingid, confirmed=mbIDs.confirmed) MusicDatabase.execute(u) else: print(f'insert mb data for {song_id}') i = songs_mb.insert().values(song_id=song_id, releasegroupid=mbIDs.releasegroupid, releaseid=mbIDs.releaseid, releasetrackid=mbIDs.releasetrackid, recordingid=mbIDs.recordingid, confirmed=mbIDs.confirmed) MusicDatabase.execute(i)
def get_all_works(): songs_mb_workids = table('songs_mb_workids') s = select([songs_mb_workids.c.workid]).distinct() result = MusicDatabase.execute(s).fetchall() print('works', len(result)) r = set(x['workid'] for x in result) return r
def get_all_artists(): """Return all artists (used by the mb importer).""" songs_mb_artistids = table('songs_mb_artistids') s = select([songs_mb_artistids.c.artistid]).distinct() result_artists = MusicDatabase.execute(s).fetchall() print(len(result_artists)) s1 = set(x['artistid'] for x in result_artists) songs_mb_albumartistids = table('songs_mb_albumartistids') s = select([songs_mb_albumartistids.c.albumartistid]).distinct() result_albumartists = MusicDatabase.execute(s).fetchall() print(len(result_albumartists)) r = s1.union(x['albumartistid'] for x in result_albumartists) print('artists', len(r)) return r
def import_conversion_dict(self): tables = set(x[0] for x in conversion_dict.values()) for tablename in tables: t = table('analysis.' + tablename.lower()) vars = { v[1]: self.stats[k] for k, v in conversion_dict.items() if v[0] == tablename } vars['song_id'] = self.song_id self.connection.execute(t.insert().values(**vars))
def import_keys_with_lists(self): for key in keys_with_lists: if key in keys_to_ignore: continue t = table('analysis.' + key.replace('.', '__').lower()) vars = { 'values': list(x.item() for x in self.stats[key]), 'song_id': self.song_id } self.connection.execute(t.insert().values(**vars))
def import_keys_with_stats(self): for key in keys_with_stats: if key in keys_to_ignore: continue t = table('analysis.' + key.replace('.', '__').lower() + '_stats') vars = { db_var: self.stats[key + '.' + st_var] for st_var, db_var in stat_variables.items() } vars['song_id'] = self.song_id self.connection.execute(t.insert().values(**vars))
def create_in_db(self): super(GeneratedPlaylist, self).create_in_db() c = MusicDatabase.getCursor() t = table('playlist_generators') data = {'playlist_id': self.id, 'generator': self.generator} MusicDatabase.insert_or_update(t, data, t.c.playlist_id == self.id, connection=c) c.commit()
def get_all_elements_from_songs_mb(column=None): if not column: return [] songs_mb = table('songs_mb') s = select([getattr(songs_mb.c, column)]).distinct() result = MusicDatabase.execute(s).fetchall() r = set(x[column] for x in result) r.difference_update({None}) return r
def get_artist_aliases(artistID, locales=None, only_primary=False): alias = table('musicbrainz.artist_alias') s = (select([alias.c.name, alias.c.sort_name, alias.c.locale, alias.c.artist_alias_type, alias.c.primary_for_locale]) .where(alias.c.artist_id == artistID) .order_by(alias.c.locale) .order_by(desc(alias.c.primary_for_locale))) # query is ordered by locale and inside each locale, the primary # is returned first if locales: s = s.where(alias.c.locale.in_(locales)) if only_primary: s = s.where(alias.c.primary_for_locale.is_(True)) return MusicDatabase.execute(s).fetchall()
def import_keys_with_lists_stats(self): for key in keys_with_lists_stats: if key in keys_to_ignore: continue for pos in range(len(self.stats[key + '.var'])): t = table('analysis.' + key.replace('.', '__').lower() + '_stats') vars = { db_var: self.stats[key + '.' + st_var][pos].item() for st_var, db_var in stat_variables.items() } vars['song_id'] = self.song_id vars['pos'] = pos self.connection.execute(t.insert().values(**vars))
def insertMBAlbumArtistIDs(song_id, albumArtistIDs): if not albumArtistIDs: return songs_mb_albumartistids = table('songs_mb_albumartistids') s = select([songs_mb_albumartistids.c.albumartistid]) \ .where(songs_mb_albumartistids.c.song_id == song_id) result = MusicDatabase.execute(s).fetchall() if set(albumArtistIDs) == set(x['albumartistid'] for x in result): return d = songs_mb_albumartistids.delete() \ .where(songs_mb_albumartistids.c.song_id == song_id) MusicDatabase.execute(d) for artistID in albumArtistIDs: i = insert(songs_mb_albumartistids).values(song_id=song_id, albumartistid=artistID) MusicDatabase.execute(i)
def insertMBWorkIDs(song_id, workIDs): if not workIDs: return songs_mb_workids = table('songs_mb_workids') s = select([songs_mb_workids.c.workid]) \ .where(songs_mb_workids.c.song_id == song_id) result = MusicDatabase.execute(s).fetchall() if set(workIDs) == set(x['workid'] for x in result): return d = songs_mb_workids.delete() \ .where(songs_mb_workids.c.song_id == song_id) MusicDatabase.execute(d) for workID in workIDs: i = insert(songs_mb_workids).values(song_id=song_id, workid=workID) MusicDatabase.execute(i)