멜론차트 크롤링

복습목표

오늘 오전에 배운 멜론차트 크롤링을 이용해서 DB에 넣어보자.

DB구조

songs 테이블, top100 테이블을 생성
songs 테이블

song_id (곡아이디) : PK 값으로 사용
singer (가수)
title (제목)
album (앨범)
released (발매일)
gen (장르)

top100 테이블

chart_date : 이 날짜에 따라서 top100이 변동되니까 중복되지 않는 유니크한 값이어야 한다고 생각함
rank : 1-100까지 숫자이고 양수만 사용하기 때문에 tinyint unsigned를 사용함
song_id : song_id를 이용해서 곡에 대한 정보를 가져와야 한다고 생각함 → FK?
TINYINT (signed) : -128 ~ 127
TINYINT UNSIGNED : 0 ~ 225

# 날짜, 순위, 가수, 제목, songid 를 집어넣는 테이블 만들기
# -> 아이디어! 이걸 이용해서 노래방 번호도 크롤링해서 매칭시켜보는 걸 해보자.
# 혹은 날짜별 순위 변동을 추정하거나?

import MySQLdb

conn = MySQLdb.connect(host='myserver', user='play', passwd='123', db='encore') #autocommit=True
cursor = conn.cursor()

song_tbl_create = """CREATE TABLE songs(
  song_id INT PRIMARY KEY,
  singer VARCHAR(100) NOT NULL,
  title VARCHAR(150) NOT NULL,
  album VARCHAR(150) NOT NULL,
  released DATE NOT NULL,
  genre VARCHAR(50) NOT NULL,
  lyrics TEXT NULL);"""

top100_tbl_create = """CREATE TABLE top100(
  chart_date DATE NOT NULL,
  ranking TINYINT UNSIGNED NOT NULL,
  song_id INT NOT NULL,
  UNIQUE KEY (chart_date, ranking));"""

# cursor.execute(song_tbl_create)
cursor.execute(top100_tbl_create)
conn.commit()

테이블이 잘 생성되었다

from bs4 import BeautifulSoup
import re
import pandas as pd
from datetime import datetime

p = re.compile("[0-9]+")
bs = BeautifulSoup(r.text)
# 100위까지 +로 합치기 가능. bs4 내부에 __add__가 구현되어 있음.
data = bs.find("div", class_='service_list_song type02 d_song_list').find_all('tr', class_='lst50') + bs.find("div", class_='service_list_song type02 d_song_list').find_all('tr', class_='lst100')

songs_data = []
chart_date_ = datetime.today().strftime("%Y-%m-%d").format()

for i, x in enumerate(data):
    tmp = x.find_all('a')
    song_id_ = p.findall(x.find(class_="btn button_icons type03 song_info")['href'])[0]
    ranking_ = i+1
    title_ = x.find(class_='ellipsis rank01').find('a').text
    singer_ = ', '.join(list(set([y.text for y in x.find("div", class_='ellipsis rank02').find_all('a')])))
    url_ = "<https://www.melon.com/song/detail.htm?songId={}>".format(song_id_)

    songs_data.append({
        'song_id' : song_id_,
        'title' : title_,
        'singer' : singer_,
        'url' : url_
    })
    # print(f"{i+1}위 - {title_} - {singer_} - {song_id_} \n {url_}")

    # top100 테이블 먼저 채우기
    top100_data = [chart_date_, ranking_, song_id_]
    cursor.execute("INSERT INTO top100 VALUES (%s, %s, %s)", top100_data)
    
conn.commit()

songs에 넣어야 할 lyrics와 album, genre 등이 url에 song_id를 넣어서 한 번 더 파싱해와야 했다.
df에 담아서 한 번에 songs, top100에 넣어야 할지 아니면 for문 안에서 또 for문으로 url로 파싱해와서 넣어야할지 고민했다.
우선 top100부터 먼저 테이블을 완성하고, song_id, title, singer, url는 리스트에 담아두고 나머지를 값을 채워넣는 방식을 고안했다.

for s in songs_data:
    url_ = s['url']
    lyrics_repose = requests.get(url_, headers= head)
    lyrics_bs = BeautifulSoup(lyrics_repose.text)

    # 가사 추출
    s['lyrics'] = BeautifulSoup(str(lyrics_bs.find('div', id='d_video_summary')).replace("<br/>", '\n')).text.strip()

    # 세부정보 추출    
    detail = lyrics_bs.find('div', class_='meta').find_all('dd')
    s['album'] = detail[0].text # 앨범
    s['released'] = detail[1].text # 발매일
    s['genre'] = detail[2].text # 장르

songs_data # 40.2초 걸림-> url에 계속 요청하는 거라 시간을 줄일 방법이 있을까?

songs_rows = [(s['song_id'], s['singer'], s['title'], s['album'], s['released'], s['genre'], s['lyrics']) for s in songs_data]
# 처음에 딕셔너리 구조 자체를 순서대로 할 걸!

cursor.executemany("""INSERT INTO songs (song_id, singer, title, album, released, genre, lyrics)
                   VALUES (%s, %s, %s, %s, %s, %s, %s)""", songs_rows)

conn.commit()

잘 들어갔다!