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 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_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_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_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_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 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 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)
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 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 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()