chapter07.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 第7章: データベース\n",
    "> artist.json.gzは，オープンな音楽データベースMusicBrainzの中で，アーティストに関するものをJSON形式に変換し，gzip形式で圧縮したファイルである．このファイルには，1アーティストに関する情報が1行にJSON形式で格納されている．JSON形式の概要は以下の通りである．  \n",
    "(略)  \n",
    "artist.json.gzのデータをKey-Value-Store (KVS) およびドキュメント志向型データベースに格納・検索することを考える．KVSとしては，LevelDB，Redis，KyotoCabinet等を用いよ．ドキュメント志向型データベースとして，MongoDBを採用したが，CouchDBやRethinkDB等を用いてもよい．"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import locale\n",
    "from pprint import pprint\n",
    "\n",
    "import redis"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 60. KVSの構築\n",
    "> Key-Value-Store (KVS) を用い，アーティスト名（name）から活動場所（area）を検索するためのデータベースを構築せよ．"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ANSI_X3.4-1968\n"
     ]
    }
   ],
   "source": [
    "r = redis.StrictRedis(host='localhost', port=6379, db=0)\n",
    "# redis returns error when you add a value to an existing key and holding the wrong kind of value.\n",
    "r.flushall()\n",
    "# python3's `open` default encoding depends on OS.\n",
    "print(locale.getpreferredencoding())\n",
    "with open('artist.json', encoding='utf-8') as f:\n",
    "    for line in f:\n",
    "        artist = json.loads(line)\n",
    "        if 'area' in artist:\n",
    "            # Since I may have same artist name, I use lists.\n",
    "            r.rpush(artist['name'], artist['area'])\n",
    "        else:\n",
    "            r.rpush(artist['name'], 'UNK')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 61. KVSの検索\n",
    "> 60で構築したデータベースを用い，特定の（指定された）アーティストの活動場所を取得せよ．"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "b'France'\n",
      "b'United Kingdom'\n",
      "b'Germany'\n",
      "b'United States'\n",
      "b'United States'\n"
     ]
    }
   ],
   "source": [
    "print(*r.lrange('Rock', 0, -1), sep='\\n')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 62. KVS内の反復処理\n",
    "> 60で構築したデータベースを用い，活動場所が「Japan」となっているアーティスト数を求めよ．"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "22821"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# return 1 each area == Japan artist in the list for each key (=artist name).\n",
    "sum(1 for key in r.keys() for area in r.lrange(key, 0, -1) if area == b'Japan' )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 63. オブジェクトを値に格納したKVS\n",
    "> KVSを用い，アーティスト名（name）からタグと被タグ数（タグ付けされた回数）のリストを検索するためのデータベースを構築せよ．さらに，ここで構築したデータベースを用い，アーティスト名からタグと被タグ数を検索せよ．"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "r = redis.StrictRedis(host='localhost', port=6379, db=1)\n",
    "r.flushdb()\n",
    "with open('artist.json', encoding='utf-8') as f:\n",
    "    for line in f:\n",
    "        artist = json.loads(line)\n",
    "        if 'tags' in artist:\n",
    "            r.hset(artist['name'], 'tags', artist['tags'])\n",
    "        else:\n",
    "            r.hset(artist['name'], 'tags', 'UNK')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'count': 2, 'value': 'rock'}\n",
      "{'count': 1, 'value': 'pop rock'}\n",
      "{'count': 1, 'value': 'piano pop'}\n",
      "{'count': 3, 'value': 'british'}\n",
      "{'count': 1, 'value': 'parlophone'}\n",
      "{'count': 1, 'value': 'english'}\n",
      "{'count': 3, 'value': 'uk'}\n",
      "{'count': 1, 'value': 'piano rock'}\n",
      "{'count': 1, 'value': 'britannique'}\n",
      "{'count': 1, 'value': 'rock and indie'}\n",
      "{'count': 1, 'value': 'chapel'}\n",
      "{'count': 2, 'value': 'alternative rock'}\n",
      "{'count': 1, 'value': 'pop/rock'}\n"
     ]
    }
   ],
   "source": [
    "print(*eval(r.hget('Coldplay', 'tags')), sep='\\n')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 64. MongoDBの構築\n",
    "> アーティスト情報（artist.json.gz）をデータベースに登録せよ．さらに，次のフィールドでインデックスを作成せよ: name, aliases.name, tags.value, rating.value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# %%bash\n",
    "# echo \"$(pwd)/data/db\" | xargs -I {} mongod -dbpath {} &"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pymongo import MongoClient, DESCENDING\n",
    "\n",
    "client = MongoClient('localhost', 27017)\n",
    "mdb = client['artist'] # database\n",
    "artist = mdb['artist'] # collection\n",
    "artist.drop()\n",
    "with open('artist.json', encoding='utf-8') as f:\n",
    "    for line in f:\n",
    "        artist_info = json.loads(line)\n",
    "        artist.insert_one(artist_info)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'rating.value_-1'"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "artist.create_index([('name', DESCENDING)]) \n",
    "artist.create_index([('aliases.value', DESCENDING)])\n",
    "artist.create_index([('tags.value', DESCENDING)])\n",
    "artist.create_index([('rating.value', DESCENDING)])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 65. MongoDBの検索\n",
    "> MongoDBのインタラクティブシェルを用いて，\"Queen\"というアーティストに関する情報を取得せよ．さらに，これと同様の処理を行うプログラムを実装せよ．"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MongoDB shell version v3.6.5\n",
      "connecting to: mongodb://127.0.0.1:27017\n",
      "MongoDB server version: 3.6.5\n",
      "switched to db artist\n",
      "{ \"_id\" : ObjectId(\"5b3cb1c20454aa0d865e7dff\"), \"name\" : \"Queen\", \"area\" : \"Japan\", \"gender\" : \"Female\", \"tags\" : [ { \"count\" : 1, \"value\" : \"kamen rider w\" }, { \"count\" : 1, \"value\" : \"related-akb48\" } ], \"sort_name\" : \"Queen\", \"ended\" : true, \"gid\" : \"420ca290-76c5-41af-999e-564d7c71f1a7\", \"type\" : \"Character\", \"id\" : 701492, \"aliases\" : [ { \"name\" : \"Queen\", \"sort_name\" : \"Queen\" } ] }\n",
      "{ \"_id\" : ObjectId(\"5b3cb1c90454aa0d865f44ab\"), \"rating\" : { \"count\" : 24, \"value\" : 92 }, \"begin\" : { \"date\" : 27, \"month\" : 6, \"year\" : 1970 }, \"name\" : \"Queen\", \"area\" : \"United Kingdom\", \"tags\" : [ { \"count\" : 2, \"value\" : \"hard rock\" }, { \"count\" : 1, \"value\" : \"70s\" }, { \"count\" : 1, \"value\" : \"queen family\" }, { \"count\" : 1, \"value\" : \"90s\" }, { \"count\" : 1, \"value\" : \"80s\" }, { \"count\" : 1, \"value\" : \"glam rock\" }, { \"count\" : 4, \"value\" : \"british\" }, { \"count\" : 1, \"value\" : \"english\" }, { \"count\" : 2, \"value\" : \"uk\" }, { \"count\" : 1, \"value\" : \"pop/rock\" }, { \"count\" : 1, \"value\" : \"pop-rock\" }, { \"count\" : 1, \"value\" : \"britannique\" }, { \"count\" : 1, \"value\" : \"classic pop and rock\" }, { \"count\" : 1, \"value\" : \"queen\" }, { \"count\" : 1, \"value\" : \"united kingdom\" }, { \"count\" : 1, \"value\" : \"langham 1 studio bbc\" }, { \"count\" : 1, \"value\" : \"kind of magic\" }, { \"count\" : 1, \"value\" : \"band\" }, { \"count\" : 6, \"value\" : \"rock\" }, { \"count\" : 1, \"value\" : \"platinum\" } ], \"sort_name\" : \"Queen\", \"ended\" : true, \"gid\" : \"0383dadf-2a4e-4d10-a46a-e9e041da8eb3\", \"type\" : \"Group\", \"id\" : 192, \"aliases\" : [ { \"name\" : \"������\", \"sort_name\" : \"������\" } ] }\n",
      "{ \"_id\" : ObjectId(\"5b3cb1d80454aa0d8660ff03\"), \"ended\" : true, \"gid\" : \"5eecaf18-02ec-47af-a4f2-7831db373419\", \"sort_name\" : \"Queen\", \"id\" : 992994, \"name\" : \"Queen\" }\n",
      "bye\n"
     ]
    }
   ],
   "source": [
    "%%bash\n",
    "mongo\n",
    "use artist\n",
    "db.artist.find({'name':'Queen'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('5b3cb1c20454aa0d865e7dff'),\n",
      " 'aliases': [{'name': 'Queen', 'sort_name': 'Queen'}],\n",
      " 'area': 'Japan',\n",
      " 'ended': True,\n",
      " 'gender': 'Female',\n",
      " 'gid': '420ca290-76c5-41af-999e-564d7c71f1a7',\n",
      " 'id': 701492,\n",
      " 'name': 'Queen',\n",
      " 'sort_name': 'Queen',\n",
      " 'tags': [{'count': 1, 'value': 'kamen rider w'},\n",
      "          {'count': 1, 'value': 'related-akb48'}],\n",
      " 'type': 'Character'}\n",
      "{'_id': ObjectId('5b3cb1c90454aa0d865f44ab'),\n",
      " 'aliases': [{'name': '女王', 'sort_name': '女王'}],\n",
      " 'area': 'United Kingdom',\n",
      " 'begin': {'date': 27, 'month': 6, 'year': 1970},\n",
      " 'ended': True,\n",
      " 'gid': '0383dadf-2a4e-4d10-a46a-e9e041da8eb3',\n",
      " 'id': 192,\n",
      " 'name': 'Queen',\n",
      " 'rating': {'count': 24, 'value': 92},\n",
      " 'sort_name': 'Queen',\n",
      " 'tags': [{'count': 2, 'value': 'hard rock'},\n",
      "          {'count': 1, 'value': '70s'},\n",
      "          {'count': 1, 'value': 'queen family'},\n",
      "          {'count': 1, 'value': '90s'},\n",
      "          {'count': 1, 'value': '80s'},\n",
      "          {'count': 1, 'value': 'glam rock'},\n",
      "          {'count': 4, 'value': 'british'},\n",
      "          {'count': 1, 'value': 'english'},\n",
      "          {'count': 2, 'value': 'uk'},\n",
      "          {'count': 1, 'value': 'pop/rock'},\n",
      "          {'count': 1, 'value': 'pop-rock'},\n",
      "          {'count': 1, 'value': 'britannique'},\n",
      "          {'count': 1, 'value': 'classic pop and rock'},\n",
      "          {'count': 1, 'value': 'queen'},\n",
      "          {'count': 1, 'value': 'united kingdom'},\n",
      "          {'count': 1, 'value': 'langham 1 studio bbc'},\n",
      "          {'count': 1, 'value': 'kind of magic'},\n",
      "          {'count': 1, 'value': 'band'},\n",
      "          {'count': 6, 'value': 'rock'},\n",
      "          {'count': 1, 'value': 'platinum'}],\n",
      " 'type': 'Group'}\n",
      "{'_id': ObjectId('5b3cb1d80454aa0d8660ff03'),\n",
      " 'ended': True,\n",
      " 'gid': '5eecaf18-02ec-47af-a4f2-7831db373419',\n",
      " 'id': 992994,\n",
      " 'name': 'Queen',\n",
      " 'sort_name': 'Queen'}\n"
     ]
    }
   ],
   "source": [
    "for queen in artist.find({'name':'Queen'}):\n",
    "    pprint(queen)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 66. 検索件数の取得\n",
    "> MongoDBのインタラクティブシェルを用いて，活動場所が「Japan」となっているアーティスト数を求めよ．"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MongoDB shell version v3.6.5\n",
      "connecting to: mongodb://127.0.0.1:27017\n",
      "MongoDB server version: 3.6.5\n",
      "switched to db artist\n",
      "22821\n",
      "bye\n"
     ]
    }
   ],
   "source": [
    "%%bash\n",
    "mongo\n",
    "use artist\n",
    "db.artist.find({'area':'Japan'}).count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 67. 複数のドキュメントの取得\n",
    "> 特定の（指定した）別名を持つアーティストを検索せよ．"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('5b3cb1720454aa0d8655dccd'),\n",
      " 'aliases': [{'name': 'ハルイチ', 'sort_name': 'ハルイチ'},\n",
      "             {'name': 'Shindo Haruichi', 'sort_name': 'Shindo Haruichi'},\n",
      "             {'name': '新藤 晴一', 'sort_name': '新藤 晴一'}],\n",
      " 'begin': {'date': 20, 'month': 9, 'year': 1974},\n",
      " 'ended': True,\n",
      " 'gid': '2686d3ca-40ca-4d15-aa55-ea700783cb6f',\n",
      " 'id': 565379,\n",
      " 'name': '新藤晴一',\n",
      " 'sort_name': 'Shindo, Haruichi',\n",
      " 'type': 'Person'}\n"
     ]
    }
   ],
   "source": [
    "for artist_info in artist.find({'$or': [{'name': 'ハルイチ'},{'aliases.name': 'ハルイチ'}]}):\n",
    "    pprint(artist_info)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 68. ソート\n",
    "> \"dance\"というタグを付与されたアーティストの中でレーティングの投票数が多いアーティスト・トップ10を求めよ．"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Madonna\n",
      "Björk\n",
      "The Prodigy\n",
      "Rihanna\n",
      "Britney Spears\n",
      "Maroon 5\n",
      "Adam Lambert\n",
      "Fatboy Slim\n",
      "Basement Jaxx\n",
      "Cornershop\n"
     ]
    }
   ],
   "source": [
    "for artist_info in artist.find({'tags.value': 'dance'}).sort('rating.count', DESCENDING).limit(10):\n",
    "    print(artist_info['name'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 69. Webアプリケーションの作成\n",
    "> ユーザから入力された検索条件に合致するアーティストの情報を表示するWebアプリケーションを作成せよ．アーティスト名，アーティストの別名，タグ等で検索条件を指定し，アーティスト情報のリストをレーティングの高い順などで整列して表示せよ．"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "website: https://artist-search-flask.herokuapp.com/  \n",
    "code: https://github.com/marukaz/artist_search"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}