웹크롤링 데이터 MySQL 저장 (한글 처리와 AWS RDS 활용 예)

이해하기 쉽고, 장황하지 않은 자료를 기반으로 강의를 진행합니다.

잔재미코딩 소식 공유

좀더 제약없이, IT 컨텐츠를 공유하고자, 자체 온라인 강의 사이트와 유투브 채널을 오픈하였습니다
응원해주시면, 곧 좋은 컨텐츠를 만들어서 공유하겠습니다

● 잔재미코딩 유투브 오픈 [구독해보기]

1. 웹크롤링 데이터 MySQL 저장 (한글 처리와 AWS RDS 활용 예)¶

In [1]:

import requests
from bs4 import BeautifulSoup
import pandas as pd
import pymysql

In [2]:

host_name = "funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com"
username = ""
password = ""
database_name = "dave_db"    # 데이터베이스 이름을 sakila 로 바꿔줘야 합니다.

In [3]:

db = pymysql.connect(
    host=host_name,  # DATABASE_HOST
    port=3306,
    user=username,  # DATABASE_USERNAME
    passwd=password,  # DATABASE_PASSWORD
    db=database_name,  # DATABASE_NAME
    charset='utf8'
)

1.1. 클라이언트에서 INSERT 시 한글이 깨지지 않도록 다음 SQL 실행이 필요함¶

http://egloos.zum.com/webprog/v/322064

In [4]:

cursor = db.cursor()
cursor.execute("set names utf8")
db.commit()

본 자료 보다 업데이트된 자료와 함께 온라인 강의로 익히면 체계적으로 이해할 수 있습니다!

퀄러티가 다른 온라인 강의로 차근차근 익혀보세요

SQL/DB(MySQL) 기본부터 파이썬/데이터 분석 활용 강의

SQL 과 데이터베이스, 그리고 파이썬으로 MySQL을 다루는 방법까지 쉽고 견고하게 정리하였습니다

자세히 알아보기 모든 강좌 보기

1.2. 테이블 만들기 + 한글 처리 + AWS RDS¶

database 에도 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
table 에도 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

CREATE TABLE IF NOT EXISTS estate_db.estate
- IF NOT EXISTS : 생성이 안되어 있을 때만 만들어라, 그렇지 않으면 테이블 정보가 다 날라가기 때문
- estate_db.estate : 디비.테이블 로 테이블을 만들 수도 있음

In [5]:

SQL_QUERY = """
DROP DATABASE IF EXISTS estate_db;
CREATE DATABASE estate_db DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

USE estate_db;
DROP TABLE IF EXISTS estate_db.estate;
CREATE TABLE estate_db.estate (
    estate_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    price INT NOT NULL,
    year CHAR(4) NOT NULL,
    dong VARCHAR(30) NOT NULL,
    apartname VARCHAR(30) NOT NULL,
    month CHAR(2) NOT NULL,
    day CHAR(5) NOT NULL,
    space VARCHAR(30) NOT NULL,
    address VARCHAR(30) NOT NULL,
    addresscode CHAR(5) NOT NULL,
    floor CHAR(3) NOT NULL,
    PRIMARY KEY(estate_id)
) DEFAULT CHARSET=utf8 COLLATE=utf8_bin
"""

estate_id 를 PRIMARY KEY로 작성

In [6]:

cursor.execute(SQL_QUERY)
db.commit()

본 자료와 같이 IT 기술을 잘 정리하여, 온라인 강의로 제공하고 있습니다

체계적으로 전문가 레벨까지 익힐 수 있도록 온라인 강의 로드맵을 제공합니다

데이터 분석/과학 로드맵

데이터 분석가와 데이터 과학자 직군의 기본기를 차근차근 쌓을 수 있도록 꾸몄습니다

자세히 알아보기 모든 강좌 보기

1.3. 크롤링 준비 (자신의 서비스키, 지역, 기간 설정)¶

In [57]:

servic_key = "WMV3wl%2BU%2BMG%2FOYQK3Wv99q1H79wjvVwIwHEjvrDV3K4gG2h9P1%2BKiB%2FFa1QoYI0yxBpJ3sm1L0OAEam9Rp2sbw%3D%3D"          # 인증키
locate_code = '11380'    # 서울 은평구
contract_date = '201708' # 실거래가 기간

1.4. 크롤링에서도 인코딩 이슈가 있어서 response.content.decode('utf-8') 처리¶

In [58]:

request_url = "http://openapi.molit.go.kr:8081/OpenAPI_ToolInstallPackage/service/rest/RTMSOBJSvc/getRTMSDataSvcAptTrade?LAWD_CD=" + locate_code + "&DEAL_YMD=" + contract_date + "&serviceKey=" + servic_key
pparams = {'LAWD_CD': 'value1', 'DEAL_YMD': 'value'}
response = requests.get(request_url)
# print(response.content.decode('utf-8'))
soup = BeautifulSoup(response.content.decode('utf-8'), 'xml')
items = soup.find_all('item')

In [ ]:

items

본 자료와 같이 IT 기술을 잘 정리하여, 온라인 강의로 제공하고 있습니다

가장 빠르게 풀스택 개발자가 될 수 있도록, 최적화된 로드맵을 제공합니다

웹/앱서비스 풀스택 로드맵

자세히 알아보기 모든 강좌 보기

4번 항목에서 에러가 나는 경우(윈도우에서 xml 라이브러리 설치시, 일부 PC에서 이슈 있음)¶

다음과 같은 시도를 해볼 수 있으나, 윈도우 환경에서 일부 동작이 안되는 경우가 확인됨
- pip install lxml
- unofficial python lxml library를 설치하는 방법
이 경우에는 다음과 같이 다른 xml 파싱 라이브러리를 사용해서 처리할 수 있음

In [ ]:

from urllib.request import urlopen
import xml.etree.ElementTree as etree
import pandas as pd

key = "WMV3wl%2BU%2BMG%2FOYQK3Wv99q1H79wjvVwIwHEjvrDV3K4gG2h9P1%2BKiB%2FFa1QoYI0yxBpJ3sm1L0OAEam9Rp2sbw%3D%3D"  # 인증키

loc = '11380'  # 서울 은평구
ym = '201708'

request_url = "http://openapi.molit.go.kr:8081/OpenAPI_ToolInstallPackage/service/rest/RTMSOBJSvc/getRTMSDataSvcAptTrade?LAWD_CD=" + loc + "&DEAL_YMD=" + ym + "&serviceKey=" + key

response = urlopen(request_url)
xml_data = response.read().decode('UTF-8')  # 데이터를 가져오고 UTF-8 String으로  변환
print(xml_data)
root = etree.fromstring(xml_data)
estate_data = list()
for item in root.iter('item'):
    estate_item1 = child.find('거래금액').text
    estate_item2 = child.find('년').text
    estate_item3 = child.find('법정동').text
    estate_item4 = child.find('아파트').text
    estate_item5 = child.find('월').text
    estate_item6 = child.find('일').text
    estate_item7 = child.find('전용면적').text
    estate_item8 = child.find('지번').text
    estate_item9 = child.find('지역코드').text
    estate_item10 = child.find('층').text

    estate_data.append(
        [estate_item1, estate_item2, estate_item3, estate_item4, estate_item5, estate_item6, estate_item7, estate_item8,
         estate_item9, estate_item10])

columns = ['거래금액', '년', '법정동', '아파트', '월', '일', '전용면적', '지번', '지역코드', '층']

df = pd.DataFrame(estate_data, columns=columns)

1.5. 데이터 처리 (테이블에 넣고, 데이터프레임으로도 만들고, csv 파일로도 만들어보기)¶

In [60]:

estate_data = list()
for item in items:
    estate_item = list()
    
    estate_item1 = item.find('거래금액').text.strip()
    estate_item2 = item.find('년').text.strip()
    estate_item3 = item.find('법정동').text.strip()
    estate_item4 = item.find('아파트').text.strip()
    estate_item5 = item.find('월').text.strip()
    estate_item6 = item.find('일').text.strip()
    estate_item7 = item.find('전용면적').text.strip()
    estate_item8 = item.find('지번').text.strip()
    estate_item9 = item.find('지역코드').text.strip()
    estate_item10 = item.find('층').text.strip()

    """
    sql = '''
        INSERT INTO estate_db.estate 
            (price, year, dong, apartname, month, day, space, address, addresscode, floor)
        VALUES 
            (%d, '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s') 
    ''' % (int(estate_item1.replace(',', '')), estate_item2, estate_item3, estate_item4, estate_item5, estate_item6, estate_item7, estate_item8,
         estate_item9, estate_item10)
    # print(sql)
    cursor.execute(sql)
    db.commit()
    """
    estate_item.append(int(estate_item1.replace(',', '')))
    estate_item.append(estate_item2)
    estate_item.append(estate_item3)
    estate_item.append(estate_item4)
    estate_item.append(estate_item5)
    estate_item.append(estate_item6)
    estate_item.append(estate_item7)
    estate_item.append(estate_item8)
    estate_item.append(estate_item9)
    estate_item.append(estate_item10)

    estate_data.append(estate_item)
print(cursor.lastrowid)

In [56]:

estate_data

Out[56]:

[]

본 자료 보다 업데이트된 자료와 함께 온라인 강의로 익히면 체계적으로 이해할 수 있습니다!

퀄러티가 다른 온라인 강의로 차근차근 익혀보세요

SQL/DB(MySQL) 기본부터 파이썬/데이터 분석 활용 강의

SQL 과 데이터베이스, 그리고 파이썬으로 MySQL을 다루는 방법까지 쉽고 견고하게 정리하였습니다

자세히 알아보기 모든 강좌 보기

1.5.1 데이터 처리 (데이터프레임으로 만들기)¶

In [13]:

columns = ['거래금액', '년', '법정동', '아파트', '월', '일', '전용면적', '지번', '지역코드', '층']
df = pd.DataFrame(estate_data, columns=columns)

In [ ]:

df

1.5.2 데이터 처리 (pandas DataFrame to_csv 함수와 한글 데이터, encoding='utf-8' 옵션으로 처리¶

In [15]:

df.to_csv('estate.csv', sep=',', encoding='utf-8')

본 자료와 같이 IT 기술을 잘 정리하여, 온라인 강의로 제공하고 있습니다

체계적으로 전문가 레벨까지 익힐 수 있도록 온라인 강의 로드맵을 제공합니다

데이터 분석/과학 로드맵

데이터 분석가와 데이터 과학자 직군의 기본기를 차근차근 쌓을 수 있도록 꾸몄습니다

자세히 알아보기 모든 강좌 보기

In [16]:

csv_test = pd.read_csv('estate.csv')

In [ ]:

csv_test

1.5.3 메모장에서 estate.csv 확인 가능¶

엑셀 프로그램, 워드패드로 오픈할 경우 유니코드 인코딩 csv 파일이 잘못 확인될 수 있음

1.6. 인코딩 문제 정리¶

가장 핵심적인 내용

테이블만들때 다음을 써줘야 한다. (RDS 특수성?)
- DEFAULT CHARSET=utf8 COLLATE=utf8_bin
- http://hongtebari.tumblr.com/post/44578052979/aws-rds-%EC%97%90%EC%84%9C-%ED%95%9C%EA%B8%80-%EC%9E%85%EB%A0%A5-%EA%B0%80%EB%8A%A5%ED%95%9C-%ED%85%8C%EC%9D%B4%EB%B8%94-%EB%A7%8C%EB%93%A4%EA%B8%B0
결국 데이터베이스 만들때도 써줘라
- CREATE DATABASE dave_db_utfset DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
pymysql 연결할때도 붙여줘라
- pymysql.connect( ..., charset='utf8')
혹시 모르니 cursor.execute 에도
- cursor = db.cursor()
- cursor.execute("set names utf8")
- db.commit()
DataFrame csv 파일 저장할 때도
- df.to_csv('estate.csv', sep=',', encoding='utf-8')
- 유니코드 인코딩 csv 파일은 엑셀 프로그램으로도 제대로 못 읽고, 워드패드로도 못읽고, 딸랑 메모장만 제대로 읽을 수 있다.