[MySQL] (20200829) TIL

TIL - 인프런 강의를 듣고 공부한 내용을 정리(강의 링크)

• 공부한 내용
  1. 크롤링을 활용해 데이터를 DB에 저장하기
  2. 외래키(Foreign Key)

1. 크롤링을 활용해 데이터를 DB에 저장하기

• G마켓 베스트 상품 페이지에서 각 카테고리 별로 랭크 되어있는 모든 아이템의 데이터를 DB에 저장할 것임.

  

• rank, items 테이블 생성 : 최종적으로 이 두 테이블에 데이터를 저장할 것임.

  • rank, items 테이블 생성 sql 및 스키마
    • items 테이블의 기본키는 item_code로 지정.
    • ranking의 item_code는 items의 기본키를 참조함(외래키).
      

      

      

CREATE TABLE items (
    item_code VARCHAR(20) NOT NULL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    ori_price INT NOT NULL,
    dis_price INT NOT NULL,
    discount_percent INT NOT NULL,
    provider VARCHAR(100)
);

CREATE TABLE ranking (
    num INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    main_category VARCHAR(50) NOT NULL,
    sub_category VARCHAR(50) NOT NULL,
    item_ranking TINYINT UNSIGNED NOT NULL,
    item_code VARCHAR(20) NOT NULL,
    FOREIGN KEY (item_code) references items(item_code)
);

• 크롤링 코드
  • 아래 코드를 실행하면, bestproducts DB의 items, ranking 테이블에 데이터가 저장됨.
  • python requests 라이브러리, beautifulsoup 라이브러리, pymysql 사용
  • #1 : items 테이블에 중복되는 item이 들어가는 가는 것을 방지하기 위한 sql문
  • #2 : #1에서 COUNT()를 사용하였으므로, #1을 실행한 결과 값은 하나 밖에 존재하지 않으므로 fetchone()을 사용.
  • #3 : #2와 같은 이유로 인덱스를 [0]으로 지정해준다. items테이블에 존재하지 않는 item_code인 경우에만, #4를 실행하여 데이터를 추가함.
  • #4 : ranking테이블이 items테이블을 참조하고 있으므로, sql문 실행 순서는 #4 -> #5가 되어야한다. 그래서 #4 -> #5순으로 코드 작성.
  • #6 : get_items()의 세번 째 인자(sub_category_name)로 문자열 'ALL'을 지정한 이유는 위쪽의 G마켓 사이트 캡쳐 이미지에서 ①,②에 해당하는 데이터들을 나타내기 위함임. (즉, 각 main카테고리별로 sub카테고리가 선택 되지 않았을 때, 전체 랭킹)
    ranking 테이블에 아래와 같이 저장됨.
    

    

    
    

    

import requests
from bs4 import BeautifulSoup
import pymysql


def save_data(item_info):
    sql = """ SELECT COUNT(*) FROM items WHERE item_code = '""" + item_info['item_code'] + """';""" #1
    cursor.execute(sql) 
    result = cursor.fetchone() #2
    if result[0] == 0: #3
        #4
        sql =  """INSERT INTO items VALUES (
        '""" + str(item_info['item_code']) + """',
        '""" + item_info['title'] + """',
        """ + str(item_info['ori_price']) + """,
        """ + str(item_info['dis_price']) + """,
        """ + str(item_info['discount_percent']) + """,
        '""" + item_info['provider'] + """');"""
        cursor.execute(sql)

    #5
    sql = """INSERT INTO ranking (main_category, sub_category, item_ranking, item_code) VALUES ('""" + item_info['category_name'] + """',
    '""" + item_info['sub_category_name'] + """',
    """ + str(item_info['ranking']) + """,
    '""" + str(item_info['item_code']) + """');"""
    cursor.execute(sql)


def get_items(html, category_name, sub_category_name):
    best_item = html.select('div.best-list')

    for index, item in enumerate(best_item[1].select('li')):
        data_dict = dict()

        ranking = index + 1
        title = item.select_one('a.itemname')
        ori_price = item.select_one('div.o-price')
        dis_price = item.select_one('div.s-price strong span')
        discount_percent = item.select_one('div.s-price span em')

        # div.ori_price 태그가 아예 존재하지 않거나(None),
        # 태그는 존재하는데, text가 없는 경우
        if ori_price == None or ori_price.get_text() == '':
            ori_price = dis_price

        if dis_price == None:
            ori_price, dis_price = 0, 0
        else:
            ori_price = ori_price.get_text().replace(',', '').replace('원', '')
            dis_price = dis_price.get_text().replace(',', '').replace('원', '')

        if discount_percent == None or discount_percent.get_text() == '':
            discount_percent = 0
        else:
            discount_percent = discount_percent.get_text().replace('%', '')

        product_link = item.select_one('div.thumb > a')
        item_code = product_link.attrs['href'].split('=')[1].split('&')[0]

        res = requests.get(product_link.attrs['href'])
        soup = BeautifulSoup(res.content, 'html.parser')

        provider = soup.select_one('div.item-topinfo_headline > p > a > strong')
        if provider == None:
            provider = ''
        else:
            provider = provider.get_text()

        data_dict['category_name'] = category_name
        data_dict['sub_category_name'] = sub_category_name
        data_dict['ranking'] = ranking
        data_dict['title'] = title.get_text()
        data_dict['ori_price'] = ori_price
        data_dict['dis_price'] = dis_price
        data_dict['discount_percent'] = discount_percent
        data_dict['provider'] = provider
        data_dict['item_code'] = item_code

        save_data(data_dict)


def get_category(category_link, category_name):
    res = requests.get(category_link)
    soup = BeautifulSoup(res.content, 'html.parser')

    get_items(soup, category_name, 'ALL') #6 

    sub_categories = soup.select('div.navi.group ul li > a')
    for sub_category in sub_categories:
        res = requests.get('http://corners.gmarket.co.kr' + sub_category['href'])
        soup = BeautifulSoup(res.content, 'html.parser')
        get_items(soup, category_name, sub_category.get_text())


# DB 연결
host_name = 'localhost'
host_port = 3306
username = 'root'
password = '설정한 비밀번호'
database_name = 'bestproducts'

db = pymysql.connect(
    host=host_name,
    port=host_port,
    user=username,
    passwd=password,
    db=database_name,
    charset='utf8'
)

cursor = db.cursor()

res = requests.get("http://corners.gmarket.co.kr/Bestsellers")
soup = BeautifulSoup(res.content, 'html.parser')

categories = soup.select('div.gbest-cate ul.by-group li a')
for category in categories:
    get_category("http://corners.gmarket.co.kr" + category.attrs['href'], category.get_text())

db.commit()
db.close()