[python, MySQL] from 파이썬 크롤러 to MySQL via pymysql

파이썬으로 크롤링 한 데이터를 pymysql로 MySQL DB에 입력하기
코드를 설명하기 위한 것이 아니라 흐름을 정리하기 위해 쓰는 포스트

*잔재미코딩님의 인프런 강의 중 "SQL/DB(MySQL) 기본부터 파이썬/데이터분석 활용까지!" 의 강좌 내용을 정리한 것입니다.
** 잔재미코딩님의 페이지 : https://www.fun-coding.org/

1. 스키마(Schema)정의 

어던 데이터를 어떻게 저장할지 (what/how) 설계해야 한다
- gmarket Best 상품 목록을 DB화 하는 것이 목적
- 랭킹 정보를 담는 ranking 테이블과 상품 정보를 담는 items 테이블 두 개로 나누고 상품코드를 포린키(FK)로 지정하여 연결하는 것으로 설계
- 포린키의 reference를 받는 items 테이블이 먼저 생성되어야 ranking 테이블을 만들 수 있다.
- pymysql 라이브러리를 import하여 db에 접속하고 테이블을 sql 쿼리문을 전달하여 생성한다.

import pymysql
db = pymysql.connect(host='localhost', port=3306, user='root', passwd='desarraigado1595', db='pytomy')
cursor = db.cursor()

sql = '''
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,
    dis_percent INT NOT NULL,
    provider VARCHAR(100)
);
'''
cursor.execute(sql)

sql = '''
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)
);
'''
cursor.execute(sql)

db.commit()
db.close()

2. 크롤러 제작

2-1. [LEVEL1] 메인 카테고리의 목록 가져오기
- gmarket Best 메인 페이지에 requests 모듈로 접속하여 BeautifulSoup으로 페이지를 soup 객체에 담는다.
- select문으로 메인 카테고리의 각 카테고리 이름과 링크를 categories 변수에 담는다.
- for문을 이용하여 categories의 각 메인 카테고리 이름과 링크를 category 변수에 담아 서브 카테고리의 이름과 링크를 추출하기 위해 만든 함수인 get_category 함수에 전달한다.

import requests
from bs4 import BeautifulSoup
#db접속
db = pymysql.connect(host='localhost', port=3306, user='root', passwd='desarraigado1595', db='pytomy')
cursor = db.cursor()

#gmarekt best 메인 페이지
res= requests.get("http://corners.gmarket.co.kr/Bestsellers?viewType=G&groupCode=G01")
soup = BeautifulSoup(res.content, 'html.parser')

#카테고리 목록 따기
categories = soup.select('div.gbest-cate ul.by-group li a')
for category in categories[:2]:
    get_category("http://corners.gmarket.co.kr"+category['href'], category.get_text())

#db 커밋 후 종료
db.commit()
db.close()

2-2. [LEVEL2] 서브 카테고리의 목록 가져오기
- LEVEL1에서 추출한 메인 카테고리 링크와 이름을 매개변수로 받는다.
- 서브 카테고리에 포함되지 않는 메인 카테고리의 하위 카테고리인 ALL 카테고리는 중간에 상품 정보를 가져오는 함수인 get_items를 삽입하여 상품정보를 가져온다.
- LEVEL1에서 했던 것과 마찬가지로 서브 카테고리에서도 각 서브 카테고리의 이름과 링크를 변수에 담아 상품 정보를 가져오기 위한 함수인 get_items에 전달한다.

def get_category(category_link, category_name):
    # level1에서 추출한 메인 카테고리 링크와 이름을 매개변수로 받는다.
    print(category_link, category_name)
    res = requests.get(category_link)
    soup = BeautifulSoup(res.content, 'html.parser')
    
    # 메인 카테고리_ ALL에서 상품 정보 추출
    get_items(soup, category_name, 'ALL')
    
    # 메인 카테고리_ Sub 카테고리 링크 추출
    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())

2-3. [LEVEL3] 각 페이지 당 상품 정보 가져오기
- LEVEL2에서 추출한 서브 카테고리 페이지의 soup객체와 메인 카테고리 이름, 서브 카테고리 이름을 매개변수로 받는다.
- 상품 가격 부분이 비어있거나 '무료'등의 문자로 되어 있을 때 발생할 수 있는 오류들을 예외처리하기 위해 if/else구문이 삽입된다.
- 판매자정보는 각 상품 페이지에 나와있기 때문에 다시 requests/BeautifulSoup으로 접속하여 정보를 추출해야한다.
- data_dict라는 딕셔너리를 생성하여 상품정보를 할당한다.
- data_dict를 pymysql모듈을 이용하여 MySQL DB에 전달하기 위한 함수인 save_data 함수에 전달한다.

def get_items(html, category_name, sub_category_name):
    # level2에서 추출한 서브 카테고리 페이지의 soup 객체와, 메인 카테고리 이름, 서브 카테고리 이름을 매개변수로 받는다.
    
    #상품 리스트 태그
    best_item = html.select('div.best-list')
    #상품을 enumerate객체로 반환하여 인덱스를 삽입하고
    for index, item in enumerate(best_item[1].select('li')[:30]):
        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')
        dis_percent = item.select_one('div.s-price em')
        
        #예외처리
        # or 전후 순서가 중요
        # 순서 : 1번이면 실행 / 1번이 아니면 2번이면 실행
        if ori_price == None or ori_price.get_text() =='':
            ori_price = dis_price
        # 가격이 무료일 경우 두 변수 모두에 0을 할당
        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 dis_percent == None or dis_percent.get_text() == '':
            dis_percent = 0
        else:
            dis_percent = dis_percent.get_text().replace('%', '')
        
        #상품 코드 추출
        product_link = item.select_one('div.thumb > a')
        # re를 할 것을 split으로 간단히!
        item_code = product_link.attrs['href'].split('=')[1]
        
        # 판매자 정보 추출을 위해 상품 해당 페이지 정보 추출
        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()
            
        #딕셔너리 key에 맞는 value에 각 정보를 저장
        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['dis_percent'] = dis_percent
        data_dict['item_code'] = item_code
        data_dict['provider'] = provider
        
        #정보를 MySQL 서버에 저장하는 함수 실행
        save_data(data_dict)
        #print(category_name, sub_category_name, ranking, item_code, title.get_text(), provider, ori_price, dis_price, dis_percent)

2-4. [Out of LEVEL] 추출한 정보를 MySQL DB에 저장
- 2-1단계에서 이미 DB에 접속하고 커서를 생성했기 때문에 query문을 작성하여 cursor.execute로 전달하면 된다.
- 상품정보는 먼저 FK의 레퍼런스 테이블인 items 테이블에 저장한 후 ranking 테이블에 저장해야 데이터의 무결성으로 인한 오류가 일어나지 않는다.
- items 테이블에 저장 전에 count 쿼리 문으로 동일한 item에 저장되어 있는지 확인한다.

def save_data(item_info):
    # level3에서 추출한 상품 정보가 저장된 data_dict 딕셔너리를 매개변수로 받아
    # 상품 정보를 pymysql로 MySQL에 전달
    item_list = item_info
    
    #items 테이블에 상품이 이미 등록되어 있는지를 count쿼리로 확인
    sql = """SELECT COUNT(*) FROM items WHERE item_code = '""" + item_info['item_code'] + """';"""
    cursor.execute(sql)
    result = cursor.fetchone()
    
    #등록되어있지 않다면 저장
    if result[0] == 0:
        sql = """INSERT INTO items VALUES(
        '""" + item_info['item_code'] + """',
        '""" + item_info['title'] + """',
        """ + str(item_info['ori_price']) + """,
        """ + str(item_info['dis_price']) + """,
        """ + str(item_info['dis_percent']) + """,
        '""" + item_info['provider'] + """')"""
        print(sql)
        cursor.execute(sql)
    
    #ranking 테이블에 저장
    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']) + """',
        '""" + item_info['item_code'] + """')"""
    print(sql)
    cursor.execute(sql)
    

 //

항상 for문으로 크롤링을 하다가 이렇게 함수로 매개변수를 전달하며 크롤링하는 방식은 처음 해본다. 어렵지만 더 재밌고 더 신기하다. 함수를 차례차례 작성해나가며 level(깊이란 뜻에서 depth라고 할까 고민하고 있다)에 따라 크롤링의 단계가 만들어져가는 것이 므찌다. 이것이 바로 객체지향...?

파이썬 코드에서는 큰 오류가 없었지만 오히려 DB생성과 관리에서 오류가 생겼다. 다음 포스트에서 역시 다룰 것이다.