<jupyter_start><jupyter_text>Building a Music Catalog with MongoDB--------------------------------------------------------------------------This tutorial makes use of the _Musicbrainz_ database: [https://musicbrainz.org/](https://musicbrainz.org/)<jupyter_code>#!pip install musicbrainzngs
#!pip install pymongo
import os
import time
import pprint
import datetime
from pymongo import MongoClient, IndexModel, ASCENDING, DESCENDING
import musicbrainzngs
from musicbrainzngs import *
# Config musicbrainz
# https://musicbrainz.org/doc/Development/XML_Web_Service/Version_1

# musicbrainzngs.set_useragent(app="coffeecode", version=1, contact=None)
# Connect to MongoDB

client = MongoClient('localhost', 27017)<jupyter_output><empty_output><jupyter_text>CRUD Operations<jupyter_code># Create a database and add some documents

db = client.crud
# Create a collection

db.drop_collection('simple')
simple = db.simpledocs
# Create a document and insert/add into collection

aDoc = {"author": "Jon", "text": "A MongoDB demo", "tags": ["mongodb", "pymongo"], "date": datetime.datetime.utcnow()}
simple.insert_one(aDoc)
# Add many docs at once - notice they don't all have the same structure

doc2 = {"author": "Waldo", "text": "Where is everybody?", "friends": ["Hal", "Tammy"], "date": datetime.datetime.utcnow()}
doc3 = {"org": "NASA", "text": "Where is everybody?", "tags": ["Fermi", "SETI"], "date": datetime.datetime.utcnow()}
doc4 = {"author": "Hal", "worry": "Are you there?", "date": datetime.datetime.utcnow()}

posts = [doc2, doc3, doc4]

result = simple.insert_many(posts)
result.inserted_ids
# Retrieve using find_one() and find()

findDoc = simple.find_one()
pprint.pprint(findDoc)
docs = simple.find()
for d in docs:
    pprint.pprint(d)
findDoc = simple.find_one({"author": "Hal"})
pprint.pprint(findDoc)
docs = simple.find({"tags": {"$exists": 'True'}})
for d in docs:
    pprint.pprint(d)
# Update

update = simple.update_one({"author": "Jon"}, {'$set': {'author': 'Hal'}})
update = simple.update_many({"author": "Hal"}, { '$addToSet': { 'tags': 'worried'} })
docs = simple.find()
for d in docs:
    pprint.pprint(d)
# Delete

delete = simple.delete_one({'tags': 'Fermi'})
docs = simple.find()
for d in docs:
    pprint.pprint(d)
delete = simple.delete_many({'author': 'Hal'})
docs = simple.find()
for d in docs:
    pprint.pprint(d)
db.drop_collection('simple')
client.database_names()
client.drop_database('crud')
client.database_names()
# Create db

db = client.jazz_catalog

# Create three collections
# Note that due to network limitations we will use only a subset of available Musicbrainz metadata

#db.drop_collection('artists')
artists = db.artists

#db.drop_collection('releases')
releases = db.releases<jupyter_output><empty_output><jupyter_text>``` search for an artistseedArtist = musicbrainzngs.search_artists("miles davis")```<jupyter_code>#for artist in seedArtist['artist-list']:
#    print("{name}, {id}".format(name=artist['name'], id=artist['id']))<jupyter_output><empty_output><jupyter_text>``` Create indexes for mongodb collections on ID field, enforce uniqueness constraintdb.artists.create_index('id', name='artist_id', unique=True)db.releases.create_index('id', name='release_id', unique=True)```<jupyter_code># Get artist info for top two
# Insert into db.artists

#for artist in seedArtist['artist-list'][:2]:
#    mbid = artist['id']
#    artist_info = musicbrainzngs.get_artist_by_id(mbid)
#    pprint.pprint(artist_info)<jupyter_output>{'artist': {'area': {'id': '489ce91b-6658-3307-9877-795b68554c98',
                     'iso-3166-1-code-list': ['US'],
                     'name': 'United States',
                     'sort-name': 'United States'},
            'begin-area': {'id': '39e1719e-2604-4b59-b698-dab7caf85b33',
                           'name': 'Alton',
                           'sort-name': 'Alton'},
            'country': 'US',
            'disambiguation': 'jazz trumpeter, bandleader, songwriter',
            'end-area': {'id': 'dbacf2e3-7e3e-4cee-8804-999b109285fa',
                         'name': '<NAME>',
                         'sort-name': '<NAME>'},
            'gender': 'Male',
            'id': '561d854a-6a28-4aa7-8c99-323e6ce46c2a',
            'ipi': '00007619785',
            'ipi-list': ['00007619785'],
            'isni-list': ['000000012144707X'],
            'life-span': {'begin': '1926-05-26',
                          'end': '1991-09-28',
                          'ended'[...]<jupyter_text>```for artist in seedArtist['artist-list'][:2]:    mbid = artist['id']    artist_info = musicbrainzngs.get_artist_by_id(mbid)    artists.insert_one(artist_info['artist'])```<jupyter_code># Try again to check uniqueness constraint

#for artist in seedArtist['artist-list'][:2]:
#    mbid = artist['id']
#    artist_info = musicbrainzngs.get_artist_by_id(mbid)
#    try:
#        artists.insert_one(artist_info['artist'])
#    except Exception as e:
#        print(e)
db.artists.count()
#for a in artists.find():
#    pprint.pprint(a)<jupyter_output>{'_id': ObjectId('5c086ce6c9efcc1303986738'),
 'area': {'id': '489ce91b-6658-3307-9877-795b68554c98',
          'iso-3166-1-code-list': ['US'],
          'name': 'United States',
          'sort-name': 'United States'},
 'begin-area': {'id': '39e1719e-2604-4b59-b698-dab7caf85b33',
                'name': 'Alton',
                'sort-name': 'Alton'},
 'country': 'US',
 'disambiguation': 'jazz trumpeter, bandleader, songwriter',
 'end-area': {'id': 'dbacf2e3-7e3e-4cee-8804-999b109285fa',
              'name': '<NAME>',
              'sort-name': '<NAME>'},
 'gender': 'Male',
 'id': '561d854a-6a28-4aa7-8c99-323e6ce46c2a',
 'ipi': '00007619785',
 'ipi-list': ['00007619785'],
 'isni-list': ['000000012144707X'],
 'life-span': {'begin': '1926-05-26', 'end': '1991-09-28', 'ended': 'true'},
 'name': '<NAME>',
 'sort-name': '<NAME>',
 'type': 'Person'}
{'_id': ObjectId('5c086ce7c9efcc1303986739'),
 'area': {'id': '489ce91b-6658-3307-9877-795b68554c98',
          'iso-316[...]<jupyter_text>``` Get the first set of releases with 'Official' status (keep result sets to default 25 to minimize network impact) Make a second call for complete track info and Insert response into mongodb releases collection ADD BACK 'artist-credits' includefor a in artists.find():    artist_id = a['id']    release_search = musicbrainzngs.get_artist_by_id(artist_id, includes=['releases',  'release-rels', 'label-rels'])    release_list = release_search['artist']['release-list']    for r in release_list:        if 'status' in r.keys():            if r['status'] == "Official":                release_id = r['id']                release_info = musicbrainzngs.get_release_by_id(release_id,                                                                includes=['artist-credits', 'recordings',                                                                          'recording-level-rels', 'artist-rels'])                try:                    releases.insert_one(release_info['release'])                except Exception as e:                    print(e)```<jupyter_code>db.releases.count()<jupyter_output>/usr/lib/python3/dist-packages/ipykernel_launcher.py:1: DeprecationWarning: count is deprecated. Use estimated_document_count or count_documents instead. Please note that $where must be replaced by $expr, $near must be replaced by $geoWithin with $center, and $nearSphere must be replaced by $geoWithin with $centerSphere
  """Entry point for launching an IPython kernel.<jupyter_text>``` so now get all artists info for all tracks on all releasesartist_ids = set()artist_count = 0for r in releases.find():    if 'medium-list' in r:        media = r['medium-list']        for m in media:            if 'track-list' in m:                tracks = m['track-list']                for each in tracks:                    if 'recording' in each.keys():                        if 'artist-relation-list' in each['recording'].keys():                            artist_list = each['recording']['artist-relation-list']                            for a in artist_list:                                artist_count += 1                                artist_id = a['artist']['id']                                artist_ids.add(artist_id)```<jupyter_code>#artist_count
#len(artist_ids)
#current_artist_ids = set(artists.distinct('id'))
#new_artist_search_ids = artist_ids - current_artist_ids
#len(new_artist_search_ids)<jupyter_output><empty_output><jupyter_text>```for a_id in list(new_artist_search_ids):    artist_info = musicbrainzngs.get_artist_by_id(a_id)    try:        artists.insert_one(artist_info['artist'])    except Exception as e:        print(e)```<jupyter_code>#artists.count()<jupyter_output><empty_output><jupyter_text>``` now redo release search for all new artists - there may be some duplicates Get the first set of releases with 'Official' status (keep result sets to default 25 to minimize network impact) Make a second call for complete track info and Insert response into mongodb releases collectionfor a_id in list(new_artist_search_ids):    release_search = musicbrainzngs.get_artist_by_id(a_id, includes=['releases',  'release-rels', 'label-rels'])    release_list = release_search['artist']['release-list']    for r in release_list:        if 'status' in r.keys():            if r['status'] == "Official":                release_id = r['id']                release_info = musicbrainzngs.get_release_by_id(release_id,                                                                includes=['artist-credits', 'recordings',                                                                           'recording-level-rels', 'artist-rels'])                try:                    releases.insert_one(release_info['release'])                except Exception as e:                    print(e)```<jupyter_code>db.releases.count()
a_release = db.releases.find_one()
pprint.pprint(a_release)<jupyter_output>{'_id': ObjectId('5c086cebc9efcc130398673c'),
 'artist-credit': [{'artist': {'disambiguation': 'jazz trumpeter, bandleader, '
                                                 'songwriter',
                               'id': '561d854a-6a28-4aa7-8c99-323e6ce46c2a',
                               'name': '<NAME>',
                               'sort-name': '<NAME>'}}],
 'artist-credit-phrase': '<NAME>',
 'country': 'US',
 'cover-art-archive': {'artwork': 'true',
                       'back': 'false',
                       'count': '1',
                       'front': 'true'},
 'date': '1954',
 'id': '16ed7b47-e00c-499d-8866-65e660f4c78f',
 'medium-count': 1,
 'medium-list': [{'format': '12" Vinyl',
                  'position': '1',
                  'track-count': 6,
                  'track-list': [{'artist-credit': [{'artist': {'disambiguation': 'jazz '
                                                                                  'trumpeter, '
                 [...]<jupyter_text>```artist_ids = set()artist_count = 0for r in releases.find():    if 'medium-list' in r:        media = r['medium-list']        for m in media:            if 'track-list' in m:                tracks = m['track-list']                for each in tracks:                    if 'recording' in each.keys():                        if 'artist-relation-list' in each['recording'].keys():                            artist_list = each['recording']['artist-relation-list']                            for a in artist_list:                                artist_count += 1                                artist_id = a['artist']['id']                                artist_ids.add(artist_id)```<jupyter_code>#artist_count
#len(artist_ids)
#current_artist_ids = set(artists.distinct('id'))
#new_artist_search_ids = artist_ids - current_artist_ids
#len(new_artist_search_ids)<jupyter_output><empty_output><jupyter_text>```for a_id in list(new_artist_search_ids):    artist_info = musicbrainzngs.get_artist_by_id(a_id)    try:        artists.insert_one(artist_info['artist'])    except Exception as e:        print(e)```<jupyter_code>db.artists.count()
db.releases.count()<jupyter_output>/usr/lib/python3/dist-packages/ipykernel_launcher.py:1: DeprecationWarning: count is deprecated. Use estimated_document_count or count_documents instead. Please note that $where must be replaced by $expr, $near must be replaced by $geoWithin with $center, and $nearSphere must be replaced by $geoWithin with $centerSphere
  """Entry point for launching an IPython kernel.<jupyter_text>Aggregations<jupyter_code># https://info-mongodb-com.s3.amazonaws.com/ReferenceCards15-PDF.pdf

# pseduo-SQL
'''
SELECT type, count(type)
FROM artists
GROUP BY type
'''

pipeline = [{"$group": {"_id": "$type", "count":{"$sum":1}}}]
artist_types = list(artists.aggregate(pipeline, allowDiskUse=True))
artist_types
# pseudo-SQL
# requires an artist table and a birthplace table

'''
SELECT begin-area.name, count(begin-area.name)
FROM begin-area
INNER JOIN artist ON begin-area.id == artist.begin-area-id
GROUP BY begin-area
'''

pipeline = [{"$group": {"_id": "$begin-area.name", "count":{"$sum":1}}},
           {"$sort": {"count":-1}},
           {"$limit": 10 }]
artist_birthplaces = list(artists.aggregate(pipeline, allowDiskUse=True))
artist_birthplaces
# pseudo-SQL

'''
SELECT begin-area.name, count(begin-area.name) as c, artist.name
FROM begin-area
INNER JOIN artist ON begin-area.id == artist.begin-area-id
GROUP BY begin-area.name, artist.name
ORDER BY c DESC
'''

# Exclude artists where begin-area field doesn't exist

pipeline = [{"$match": {"begin-area.name": {"$exists": 'True'}}},
            {"$group": {"_id": {"area": "$begin-area.name", "artist": "$name"}, "artistCount":{"$sum":1}}},
            {"$group": {"_id": "$_id.area", "artists": {"$push": {"artist": "$_id.artist"}}, "count":{"$sum":"$artistCount"}}},
            {"$sort": {"count":-1}},
            {"$limit": 2 }]
artist_birthplaces = list(artists.aggregate(pipeline, allowDiskUse=True))
artist_birthplaces
# single artist discography

pipeline = [{"$match": {'artist-credit-phrase': '<NAME>'}},
            {"$group": {"_id": {"artist": "$artist-credit-phrase"}, "release": {"$push": {"title": "$title", "year": "$date"}}}},
            {"$sort": {"year":1, "title":1}}]
discography = list(releases.aggregate(pipeline, allowDiskUse=True))
discography
# complete collection discography

pipeline = [{"$match": {'artist-credit-phrase': {"$exists": 'True'}}},
            {"$group": {"_id": {"artist": "$artist-credit-phrase"}, "release": {"$push": {"title": "$title", "year": "$date"}}}},
            {"$sort": {"artist":1, "year":1, "title":1}},
            {"$limit": 10 }]
discography = list(releases.aggregate(pipeline, allowDiskUse=True))
discography<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.16199376947040497, 'fraction_numerical': 0.03149126371393742, 'mean_word_length': 3.009503122454521, 'pattern_counts': {'":': 77, '<': 54, '<?xml version=': 0, '>': 54, 'https://': 4, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 2, 'word_list_counts': {'cursed_substrings.json': 18, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '356545', 'license': 'MIT', 'n_tokens_mistral': 5125, 'n_tokens_neox': 4701, 'n_words': 1030, 'path': 'MongoDB_Music_Catalog.ipynb', 'repo_name': 'unmrds/cc-noSQL', 'size': '14822'}