2021-06-23 2_곡 정보 크롤링.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "32de05d2",
   "metadata": {},
   "source": [
    "# 기초 작업"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0eadb7a4",
   "metadata": {},
   "source": [
    "### 모듈 불러오기"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "2162fa1a",
   "metadata": {},
   "outputs": [],
   "source": [
    "import selenium\n",
    "from selenium import webdriver as wd\n",
    "import time\n",
    "import pandas as pd\n",
    "from bs4 import BeautifulSoup\n",
    "import requests\n",
    "from itertools import repeat\n",
    "import re\n",
    "import random\n",
    "import sqlite3\n",
    "from pymongo import MongoClient"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "68754415",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "413eaf6c",
   "metadata": {},
   "source": [
    "### 크롬드라이버가 url 접속하도록 하기"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "2df7c61f",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-2-264a2b05d9a8>:6: DeprecationWarning: use options instead of chrome_options\n",
      "  driver = wd.Chrome('chromedriver', chrome_options=options) # 크롬드라이버 경로\n"
     ]
    }
   ],
   "source": [
    "# 창 실행 옵션x\n",
    "options = wd.ChromeOptions()\n",
    "options.add_argument(\"headless\") \n",
    "\n",
    "# 크롬드라이버 열기\n",
    "driver = wd.Chrome('chromedriver', chrome_options=options) # 크롬드라이버 경로\n",
    "\n",
    "# 드라이버가 해당 url 접속\n",
    "url = 'https://www.melon.com/chart/index.htm' # 멜론차트 페이지\n",
    "driver.get(url)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "70b8a7cf",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b00e91c5",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fa94e9d4",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "53504e51",
   "metadata": {},
   "source": [
    "# 함수 만들기_1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a0d5011a",
   "metadata": {},
   "source": [
    "### 기존 함수"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "6d50b3ef",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 연대선택 era_num=1 : 2020년대, era_num=2 : 2010년대\n",
    "def click_era(era_num) :    \n",
    "    driver.find_element_by_xpath('//*[@id=\"d_chart_search\"]/div/div/div[1]/div[1]/ul/li['+str(era_num)+']/span/label').click()\n",
    "    time.sleep(0.1)\n",
    "    \n",
    "# 연도선택 year_num : 최근 -> 과거\n",
    "def click_year(year_num) :\n",
    "    driver.find_element_by_xpath('//*[@id=\"d_chart_search\"]/div/div/div[2]/div[1]/ul/li['+str(year_num)+']/span/label').click()\n",
    "    time.sleep(0.1)\n",
    "\n",
    "# 월간선택 \n",
    "def click_month(month_year) :\n",
    "    driver.find_element_by_xpath('//*[@id=\"d_chart_search\"]/div/div/div[3]/div[1]/ul/li['+str(month_year)+']/span/label').click()\n",
    "    time.sleep(0.1)\n",
    "\n",
    "# 주간 선택\n",
    "def click_week(week_num) :\n",
    "    driver.find_element_by_xpath('//*[@id=\"d_chart_search\"]/div/div/div[4]/div[1]/ul/li['+str(week_num)+']/span/label').click()\n",
    "    time.sleep(0.1)\n",
    "\n",
    "# 장르선택 ('장르 종합'으로 선택)\n",
    "def click_genre() :\n",
    "    driver.find_element_by_xpath('//*[@id=\"d_chart_search\"]/div/div/div[5]/div[1]/ul/li[1]/span/label').click()\n",
    "    time.sleep(0.1)\n",
    "    \n",
    "    \n",
    "# 검색버튼 클릭\n",
    "def click_search() :\n",
    "    driver.find_element_by_xpath('//*[@id=\"d_srch_form\"]/div[2]/button/span/span').click()\n",
    "    time.sleep(0.1)\n",
    "    \n",
    "    \n",
    "\n",
    "# 연대선택 개수 찾기\n",
    "def find_era_length() :\n",
    "    length = len(driver.find_elements_by_css_selector('div.box_chic.nth1.view > div.list_value > ul > li'))\n",
    "    return length    \n",
    "    \n",
    "# 연도선택 개수 찾기\n",
    "def find_year_length() :\n",
    "    length = len(driver.find_elements_by_css_selector('div.box_chic.nth2.view > div.list_value > ul > li'))\n",
    "    return length\n",
    "\n",
    "# 월간선택 개수 찾기\n",
    "def find_month_length() :\n",
    "    length = len(driver.find_elements_by_css_selector('div.box_chic.nth3.view > div.list_value > ul > li'))\n",
    "    return length\n",
    "\n",
    "# 주간선택 개수 찾기\n",
    "def find_week_length() :\n",
    "    length = len(driver.find_elements_by_css_selector('div.box_chic.nth4.view > div.list_value > ul > li'))\n",
    "    return length"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "33679663",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "1ff7e245",
   "metadata": {},
   "source": [
    "### 곡 id 리스트 만드는 함수 만들기"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "7bd40433",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 리스트 만들기\n",
    "def get_song_id_list() :\n",
    "    \n",
    "    html = driver.page_source\n",
    "    soup = BeautifulSoup(html, 'lxml')\n",
    "    \n",
    "    id_list=[]\n",
    "    for id in soup.find_all('input',{'name':'input_check'}):\n",
    "        id_list.append(id.get('value'))\n",
    "            \n",
    "    return id_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2e1e7e53",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a2309d6f",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ac988c77",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "e9154d5d",
   "metadata": {},
   "source": [
    "# 크롤링_1 : 곡 ID 가져오기"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ed9bfad2",
   "metadata": {},
   "source": [
    "### 곡 id 리스트 만들기 (중복 제거)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "cb468f7a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "전체 곡 수 :  15900\n",
      "중복이 제거된 전체 곡 수 :  1137\n"
     ]
    }
   ],
   "source": [
    "# 리스트 만들기\n",
    "song_id_list_all =[]\n",
    "\n",
    "# 차트파인더 클릭\n",
    "driver.find_element_by_xpath('//*[@id=\"gnb_menu\"]/ul[1]/li[1]/div/div/button/span').click()\n",
    "\n",
    "# 주간차트 클릭\n",
    "driver.find_element_by_xpath('//*[@id=\"d_chart_search\"]/div/h4[1]/a').click()\n",
    "time.sleep(0.1)\n",
    "\n",
    "# 나머지 멈추는 시기 결정\n",
    "stop_era = 2\n",
    "stop_year = 2\n",
    "stop_month = 6\n",
    "\n",
    "# 나머지 자동 선택\n",
    "len_era = find_era_length()\n",
    "for i in range(1,len_era+1) :   \n",
    "    click_era(i)\n",
    "        \n",
    "    len_year = find_year_length()\n",
    "    for j in range(1,len_year+1) :\n",
    "        click_year(j)\n",
    "                \n",
    "        len_month = find_month_length()\n",
    "        \n",
    "        if i == stop_era and j == stop_year :\n",
    "            for k in range(stop_month, len_month+1) :\n",
    "                click_month(k)\n",
    "\n",
    "                len_week = find_week_length()\n",
    "                for l in range(1, len_week+1) :\n",
    "                    click_week(l)\n",
    "                    click_genre()\n",
    "                    click_search()\n",
    "                        \n",
    "                    song_id_list_all += get_song_id_list()\n",
    "        \n",
    "        else :\n",
    "            for k in range(1, len_month+1) :\n",
    "                click_month(k)\n",
    "\n",
    "                len_week = find_week_length()\n",
    "                for l in range(1, len_week+1) :\n",
    "                    click_week(l)\n",
    "                    click_genre()\n",
    "                    click_search()\n",
    "\n",
    "                    song_id_list_all += get_song_id_list()\n",
    "            \n",
    "        if i == stop_era and j == stop_year : break    \n",
    "                            \n",
    "    if i == stop_era : break;\n",
    "\n",
    "# 드라이버 종료\n",
    "driver.quit()        \n",
    "\n",
    "# 중복 제거\n",
    "song_id_list = list(set(song_id_list_all))\n",
    "\n",
    "print(\"전체 곡 수 : \", len(song_id_list_all))\n",
    "print(\"중복이 제거된 전체 곡 수 : \", len(song_id_list))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "65e21521",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6ba9799a",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e07cc8b8",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "01d48490",
   "metadata": {},
   "source": [
    "# 함수 만들기_2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "61f0c30f",
   "metadata": {},
   "source": [
    "### 곡 정보 가져오는 함수 만들기"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "72a08bd6",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_song_info(song_id_num) :\n",
    "    \n",
    "    song_id =  song_id_num\n",
    "    \n",
    "    # 주소 입력\n",
    "    url = 'https://www.melon.com/song/detail.htm?songId='+str(song_id)\n",
    "    \n",
    "    # 차단 방지\n",
    "    headers = {'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/79.0.3945.130 Safari/537.36'}\n",
    "    \n",
    "    # 접속\n",
    "    req = requests.get(url, headers=headers)\n",
    "    source = BeautifulSoup(req.text, 'lxml')\n",
    "    \n",
    "    # 곡명 가져오기\n",
    "    song_name = source.find_all('div', class_ = 'song_name')[0].text\n",
    "    song_name = song_name[10:].split()\n",
    "    song_name = \" \".join(song_name)\n",
    "\n",
    "    # 가수 이름 가져오기\n",
    "    artist_name_source =  source.select('#downloadfrm > div > div > div.entry > div.info > div.artist > a > span:nth-child(1)')\n",
    "    artist_name_list = []\n",
    "    for i in range(len(artist_name_source)) :\n",
    "        artist_name_list.append(artist_name_source[i].get_text())\n",
    "    artist_name = ', '.join(artist_name_list)\n",
    "    \n",
    "    # 앨범 가져오기\n",
    "    album_name = source.select_one('#downloadfrm > div > div > div.entry > div.meta > dl > dd:nth-child(2) > a').get_text()\n",
    "\n",
    "    # 발매일 가져오기\n",
    "    release_date = source.select_one('#downloadfrm > div > div > div.entry > div.meta > dl > dd:nth-child(4)').get_text()\n",
    "    # yyyy.mm.dd -> yyyy-mm-dd\n",
    "    release_date = release_date.replace('.', '-')\n",
    "\n",
    "    # 장르 가져오기\n",
    "    genre = source.select_one('#downloadfrm > div > div > div.entry > div.meta > dl > dd:nth-child(6)').get_text()\n",
    "    \n",
    "\n",
    "    return song_name, artist_name, album_name, release_date, genre\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e5699f31",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8723c75a",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "16cf1189",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "f42fe4ec",
   "metadata": {},
   "source": [
    "# 크롤링_2 : 곡 정보 가져오기"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "077e3316",
   "metadata": {},
   "source": [
    "### 각각의 정보를 list에 저장"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "9bd59fb9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "총 id 개수 :  1137\n",
      "\n",
      "시작 :\n",
      "1  2  3  4  5  6  7  8  9  10  11  12  13  14  15  16  17  18  19  20  21  22  23  24  25  26  27  28  29  30  31  32  33  34  35  36  37  38  39  40  41  42  43  44  45  46  47  48  49  50  51  52  53  54  55  56  57  58  59  60  61  62  63  64  65  66  67  68  69  70  71  72  73  74  75  76  77  78  79  80  81  82  83  84  85  86  87  88  89  90  91  92  93  94  95  96  97  98  99  100  101  102  103  104  105  106  107  108  109  110  111  112  113  114  115  116  117  118  119  120  121  122  123  124  125  126  127  128  129  130  131  132  133  134  135  136  137  138  139  140  141  142  143  144  145  146  147  148  149  150  151  152  153  154  155  156  157  158  159  160  161  162  163  164  165  166  167  168  169  170  171  172  173  174  175  176  177  178  179  180  181  182  183  184  185  186  187  188  189  190  191  192  193  194  195  196  197  198  199  200  201  202  203  204  205  206  207  208  209  210  211  212  213  214  215  216  217  218  219  220  221  222  223  224  225  226  227  228  229  230  231  232  233  234  235  236  237  238  239  240  241  242  243  244  245  246  247  248  249  250  251  252  253  254  255  256  257  258  259  260  261  262  263  264  265  266  267  268  269  270  271  272  273  274  275  276  277  278  279  280  281  282  283  284  285  286  287  288  289  290  291  292  293  294  295  296  297  298  299  300  301  302  303  304  305  306  307  308  309  310  311  312  313  314  315  316  317  318  319  320  321  322  323  324  325  326  327  328  329  330  331  332  333  334  335  336  337  338  339  340  341  342  343  344  345  346  347  348  349  350  351  352  353  354  355  356  357  358  359  360  361  362  363  364  365  366  367  368  369  370  371  372  373  374  375  376  377  378  379  380  381  382  383  384  385  386  387  388  389  390  391  392  393  394  395  396  397  398  399  400  401  402  403  404  405  406  407  408  409  410  411  412  413  414  415  416  417  418  419  420  421  422  423  424  425  426  427  428  429  430  431  432  433  434  435  436  437  438  439  440  441  442  443  444  445  446  447  448  449  450  451  452  453  454  455  456  457  458  459  460  461  462  463  464  465  466  467  468  469  470  471  472  473  474  475  476  477  478  479  480  481  482  483  484  485  486  487  488  489  490  491  492  493  494  495  496  497  498  499  500  501  502  503  504  505  506  507  508  509  510  511  512  513  514  515  516  517  518  519  520  521  522  523  524  525  526  527  528  529  530  531  532  533  534  535  536  537  538  539  540  541  542  543  544  545  546  547  548  549  550  551  552  553  554  555  556  557  558  559  560  561  562  563  564  565  566  567  568  569  570  571  572  573  574  575  576  577  578  579  580  581  582  583  584  585  586  587  588  589  590  591  592  593  594  595  596  597  598  599  600  601  602  603  604  605  606  607  608  609  610  611  612  613  614  615  616  617  618  619  620  621  622  623  624  625  626  627  628  629  630  631  632  633  634  635  636  637  638  639  640  641  642  643  644  645  646  647  648  649  650  651  652  653  654  655  656  657  658  659  660  661  662  663  664  665  666  667  668  669  670  671  672  673  674  675  676  677  678  679  680  681  682  683  684  685  686  687  688  689  690  691  692  693  694  695  696  697  698  699  700  701  702  703  704  705  706  707  708  709  710  711  712  713  714  715  716  717  718  719  720  721  722  723  724  725  726  727  728  729  730  731  732  733  734  735  736  737  738  739  740  741  742  743  744  745  746  747  748  749  750  751  752  753  754  755  756  757  758  759  760  761  762  763  764  765  766  767  768  769  770  771  772  773  774  775  776  777  778  779  780  781  782  783  784  785  786  787  788  789  790  791  792  793  794  795  796  797  798  799  800  801  802  803  804  805  806  807  808  809  810  811  812  813  814  815  816  817  818  819  820  821  822  823  824  825  826  827  828  829  830  831  832  833  834  835  836  837  838  839  840  841  842  843  844  845  846  847  848  849  850  851  852  853  854  855  856  857  858  859  860  861  862  863  864  865  866  867  868  869  870  871  872  873  874  875  876  877  878  879  880  881  882  883  884  885  886  887  888  889  890  891  892  893  894  895  896  897  898  899  900  901  902  903  904  905  906  907  908  909  910  911  912  913  914  915  916  917  918  919  920  921  922  923  924  925  926  927  928  929  930  931  932  933  934  935  936  937  938  939  940  941  942  943  944  945  946  947  948  949  950  951  952  953  954  955  956  957  958  959  960  961  962  963  964  965  966  967  968  969  970  971  972  973  974  975  976  977  978  979  980  981  982  983  984  985  986  987  988  989  990  991  992  993  994  995  996  997  998  999  1000  1001  1002  1003  1004  1005  1006  1007  1008  1009  1010  1011  1012  1013  1014  1015  1016  1017  1018  1019  1020  1021  1022  1023  1024  1025  1026  1027  1028  1029  1030  1031  1032  1033  1034  1035  1036  1037  1038  1039  1040  1041  1042  1043  1044  1045  1046  1047  1048  1049  1050  1051  1052  1053  1054  1055  1056  1057  1058  1059  1060  1061  1062  1063  1064  1065  1066  1067  1068  1069  1070  1071  1072  1073  1074  1075  1076  1077  1078  1079  1080  1081  1082  1083  1084  1085  1086  1087  1088  1089  1090  1091  1092  1093  1094  1095  1096  1097  1098  1099  1100  1101  1102  1103  1104  1105  1106  1107  1108  1109  1110  1111  1112  1113  1114  1115  1116  1117  1118  1119  1120  1121  1122  1123  1124  1125  1126  1127  1128  1129  1130  1131  1132  1133  1134  1135  1136  1137  \n",
      "끝\n"
     ]
    }
   ],
   "source": [
    "print(\"총 id 개수 : \", len(song_id_list))\n",
    "print()\n",
    "\n",
    "song_name_list = []\n",
    "artist_name_list = []\n",
    "album_name_list = []\n",
    "release_date_list = []\n",
    "genre_list = []\n",
    "\n",
    "print(\"시작 :\")\n",
    "i = 1\n",
    "for id in song_id_list :\n",
    "    song_name, artist_name, album_name, release_date, genre = get_song_info(id)\n",
    "    \n",
    "    # 차단 방지\n",
    "    # time.sleep(random.uniform(0, 0.5))\n",
    "    \n",
    "    song_name_list.append(song_name)\n",
    "    artist_name_list.append(artist_name)\n",
    "    album_name_list.append(album_name)\n",
    "    release_date_list.append(release_date)\n",
    "    genre_list.append(genre)\n",
    "    \n",
    "    \n",
    "    print(i,\" \", end = '')\n",
    "    i += 1\n",
    "    \n",
    "    # if i == 400 or i == 800 :\n",
    "    #     time.sleep(120)\n",
    "\n",
    "\n",
    "print()\n",
    "print(\"끝\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9458f09a",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "abc125f9",
   "metadata": {},
   "source": [
    "### 데이터프레임으로 저장"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "05e32d82",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>song_id</th>\n",
       "      <th>song_name</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>album_name</th>\n",
       "      <th>release_date</th>\n",
       "      <th>genre</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>32163199</td>\n",
       "      <td>낙서 (Feat. 백예린) (Prod. by WOOGIE)</td>\n",
       "      <td>펀치넬로 (punchnello)</td>\n",
       "      <td>낙서</td>\n",
       "      <td>2019-11-11</td>\n",
       "      <td>랩/힙합</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>31399725</td>\n",
       "      <td>닿은 순간 (Ooh La La La)</td>\n",
       "      <td>EXO</td>\n",
       "      <td>DON'T MESS UP MY TEMPO - The 5th Album</td>\n",
       "      <td>2018-11-02</td>\n",
       "      <td>댄스</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>32217924</td>\n",
       "      <td>춤 (Groove)</td>\n",
       "      <td>EXO</td>\n",
       "      <td>OBSESSION - The 6th Album</td>\n",
       "      <td>2019-11-27</td>\n",
       "      <td>댄스</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>711627</td>\n",
       "      <td>죄와벌</td>\n",
       "      <td>SG 워너비</td>\n",
       "      <td>살다가</td>\n",
       "      <td>2005-03-23</td>\n",
       "      <td>발라드</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>31133898</td>\n",
       "      <td>너 없인 안 된다</td>\n",
       "      <td>비투비</td>\n",
       "      <td>THIS IS US</td>\n",
       "      <td>2018-06-18</td>\n",
       "      <td>발라드</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1132</th>\n",
       "      <td>31417871</td>\n",
       "      <td>SOLO</td>\n",
       "      <td>제니 (JENNIE)</td>\n",
       "      <td>SOLO</td>\n",
       "      <td>2018-11-12</td>\n",
       "      <td>랩/힙합</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1133</th>\n",
       "      <td>31584299</td>\n",
       "      <td>잊지마요 (Feat. 박지훈)</td>\n",
       "      <td>하성운</td>\n",
       "      <td>잊지마요</td>\n",
       "      <td>2019-01-28</td>\n",
       "      <td>발라드</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1134</th>\n",
       "      <td>32872978</td>\n",
       "      <td>Dynamite</td>\n",
       "      <td>방탄소년단</td>\n",
       "      <td>Dynamite (DayTime Version)</td>\n",
       "      <td>2020-08-24</td>\n",
       "      <td>댄스</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1135</th>\n",
       "      <td>33265558</td>\n",
       "      <td>흑백사진 (2021) (바른연애 길잡이 X KCM)</td>\n",
       "      <td>KCM</td>\n",
       "      <td>흑백사진 (2021) (바른연애 길잡이 X KCM)</td>\n",
       "      <td>2021-02-06</td>\n",
       "      <td>발라드</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1136</th>\n",
       "      <td>32183387</td>\n",
       "      <td>자장가</td>\n",
       "      <td>아이유</td>\n",
       "      <td>Love poem</td>\n",
       "      <td>2019-11-18</td>\n",
       "      <td>록/메탈</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1137 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       song_id                         song_name        artist_name  \\\n",
       "0     32163199  낙서 (Feat. 백예린) (Prod. by WOOGIE)  펀치넬로 (punchnello)   \n",
       "1     31399725              닿은 순간 (Ooh La La La)                EXO   \n",
       "2     32217924                        춤 (Groove)                EXO   \n",
       "3       711627                               죄와벌             SG 워너비   \n",
       "4     31133898                         너 없인 안 된다                비투비   \n",
       "...        ...                               ...                ...   \n",
       "1132  31417871                              SOLO        제니 (JENNIE)   \n",
       "1133  31584299                  잊지마요 (Feat. 박지훈)                하성운   \n",
       "1134  32872978                          Dynamite              방탄소년단   \n",
       "1135  33265558      흑백사진 (2021) (바른연애 길잡이 X KCM)                KCM   \n",
       "1136  32183387                               자장가                아이유   \n",
       "\n",
       "                                  album_name release_date genre  \n",
       "0                                         낙서   2019-11-11  랩/힙합  \n",
       "1     DON'T MESS UP MY TEMPO - The 5th Album   2018-11-02    댄스  \n",
       "2                  OBSESSION - The 6th Album   2019-11-27    댄스  \n",
       "3                                        살다가   2005-03-23   발라드  \n",
       "4                                 THIS IS US   2018-06-18   발라드  \n",
       "...                                      ...          ...   ...  \n",
       "1132                                    SOLO   2018-11-12  랩/힙합  \n",
       "1133                                    잊지마요   2019-01-28   발라드  \n",
       "1134              Dynamite (DayTime Version)   2020-08-24    댄스  \n",
       "1135            흑백사진 (2021) (바른연애 길잡이 X KCM)   2021-02-06   발라드  \n",
       "1136                               Love poem   2019-11-18  록/메탈  \n",
       "\n",
       "[1137 rows x 6 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_song_info = pd.DataFrame({\"song_id\" : song_id_list, \"song_name\" : song_name_list,\n",
    "                             \"artist_name\" : artist_name_list, \"album_name\" : album_name_list, \n",
    "                             \"release_date\" : release_date_list, \"genre\" : genre_list})\n",
    "\n",
    "df_song_info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9c37500b",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "ecc3029e",
   "metadata": {},
   "source": [
    "### CSV로 저장"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "57d6ae6e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_song_info.to_csv('csv/song_info/song_info.csv', encoding = 'utf-8-sig', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "daa96fd8",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "c7b4f5dc",
   "metadata": {},
   "source": [
    "### SQLite3 DB로 저장"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "f9685298",
   "metadata": {},
   "outputs": [],
   "source": [
    "con = sqlite3.connect(\"db/song.db\")\n",
    "\n",
    "df_song_info.to_sql(\"song_info\", con, if_exists=\"replace\", index=False)\n",
    "\n",
    "con.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cf4afb0f",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "47772bcc",
   "metadata": {},
   "source": [
    "### SQLite3 DB 저장 확인"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "0697bf80",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>song_id</th>\n",
       "      <th>song_name</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>album_name</th>\n",
       "      <th>release_date</th>\n",
       "      <th>genre</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>32163199</td>\n",
       "      <td>낙서 (Feat. 백예린) (Prod. by WOOGIE)</td>\n",
       "      <td>펀치넬로 (punchnello)</td>\n",
       "      <td>낙서</td>\n",
       "      <td>2019-11-11</td>\n",
       "      <td>랩/힙합</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>31399725</td>\n",
       "      <td>닿은 순간 (Ooh La La La)</td>\n",
       "      <td>EXO</td>\n",
       "      <td>DON'T MESS UP MY TEMPO - The 5th Album</td>\n",
       "      <td>2018-11-02</td>\n",
       "      <td>댄스</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>32217924</td>\n",
       "      <td>춤 (Groove)</td>\n",
       "      <td>EXO</td>\n",
       "      <td>OBSESSION - The 6th Album</td>\n",
       "      <td>2019-11-27</td>\n",
       "      <td>댄스</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>711627</td>\n",
       "      <td>죄와벌</td>\n",
       "      <td>SG 워너비</td>\n",
       "      <td>살다가</td>\n",
       "      <td>2005-03-23</td>\n",
       "      <td>발라드</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>31133898</td>\n",
       "      <td>너 없인 안 된다</td>\n",
       "      <td>비투비</td>\n",
       "      <td>THIS IS US</td>\n",
       "      <td>2018-06-18</td>\n",
       "      <td>발라드</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1132</th>\n",
       "      <td>31417871</td>\n",
       "      <td>SOLO</td>\n",
       "      <td>제니 (JENNIE)</td>\n",
       "      <td>SOLO</td>\n",
       "      <td>2018-11-12</td>\n",
       "      <td>랩/힙합</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1133</th>\n",
       "      <td>31584299</td>\n",
       "      <td>잊지마요 (Feat. 박지훈)</td>\n",
       "      <td>하성운</td>\n",
       "      <td>잊지마요</td>\n",
       "      <td>2019-01-28</td>\n",
       "      <td>발라드</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1134</th>\n",
       "      <td>32872978</td>\n",
       "      <td>Dynamite</td>\n",
       "      <td>방탄소년단</td>\n",
       "      <td>Dynamite (DayTime Version)</td>\n",
       "      <td>2020-08-24</td>\n",
       "      <td>댄스</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1135</th>\n",
       "      <td>33265558</td>\n",
       "      <td>흑백사진 (2021) (바른연애 길잡이 X KCM)</td>\n",
       "      <td>KCM</td>\n",
       "      <td>흑백사진 (2021) (바른연애 길잡이 X KCM)</td>\n",
       "      <td>2021-02-06</td>\n",
       "      <td>발라드</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1136</th>\n",
       "      <td>32183387</td>\n",
       "      <td>자장가</td>\n",
       "      <td>아이유</td>\n",
       "      <td>Love poem</td>\n",
       "      <td>2019-11-18</td>\n",
       "      <td>록/메탈</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1137 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       song_id                         song_name        artist_name  \\\n",
       "0     32163199  낙서 (Feat. 백예린) (Prod. by WOOGIE)  펀치넬로 (punchnello)   \n",
       "1     31399725              닿은 순간 (Ooh La La La)                EXO   \n",
       "2     32217924                        춤 (Groove)                EXO   \n",
       "3       711627                               죄와벌             SG 워너비   \n",
       "4     31133898                         너 없인 안 된다                비투비   \n",
       "...        ...                               ...                ...   \n",
       "1132  31417871                              SOLO        제니 (JENNIE)   \n",
       "1133  31584299                  잊지마요 (Feat. 박지훈)                하성운   \n",
       "1134  32872978                          Dynamite              방탄소년단   \n",
       "1135  33265558      흑백사진 (2021) (바른연애 길잡이 X KCM)                KCM   \n",
       "1136  32183387                               자장가                아이유   \n",
       "\n",
       "                                  album_name release_date genre  \n",
       "0                                         낙서   2019-11-11  랩/힙합  \n",
       "1     DON'T MESS UP MY TEMPO - The 5th Album   2018-11-02    댄스  \n",
       "2                  OBSESSION - The 6th Album   2019-11-27    댄스  \n",
       "3                                        살다가   2005-03-23   발라드  \n",
       "4                                 THIS IS US   2018-06-18   발라드  \n",
       "...                                      ...          ...   ...  \n",
       "1132                                    SOLO   2018-11-12  랩/힙합  \n",
       "1133                                    잊지마요   2019-01-28   발라드  \n",
       "1134              Dynamite (DayTime Version)   2020-08-24    댄스  \n",
       "1135            흑백사진 (2021) (바른연애 길잡이 X KCM)   2021-02-06   발라드  \n",
       "1136                               Love poem   2019-11-18  록/메탈  \n",
       "\n",
       "[1137 rows x 6 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con = sqlite3.connect(\"db/song.db\")\n",
    "\n",
    "df = pd.read_sql(\"SELECT * FROM song_info\", con)\n",
    "\n",
    "con.close()\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6b4a2306",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "44e8cc01",
   "metadata": {},
   "source": [
    "### MongoDB 저장"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "e4670432",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pymongo.results.InsertManyResult at 0x276c59dfc40>"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "client = MongoClient('localhost', 27017)\n",
    "db = client['song']\n",
    "col = db['song_info']\n",
    "\n",
    "df_dictionarys = [{**row.dropna().to_dict()} for index,row in df_song_info.iterrows()]\n",
    "\n",
    "col.insert_many(df_dictionarys)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2ad23958",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}