웹크롤링 데이터 MySQL 저장 (한글 처리와 AWS RDS 활용 예)
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 실행이 필요함¶
In [4]:
cursor = db.cursor()
cursor.execute("set names utf8")
db.commit()
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()
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
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]:
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')
In [16]:
csv_test = pd.read_csv('estate.csv')
In [ ]:
csv_test
1.5.3 메모장에서 estate.csv 확인 가능¶
- 엑셀 프로그램, 워드패드로 오픈할 경우 유니코드 인코딩 csv 파일이 잘못 확인될 수 있음
1.6. 인코딩 문제 정리¶
- 가장 핵심적인 내용
- 테이블만들때 다음을 써줘야 한다. (RDS 특수성?)
- 결국 데이터베이스 만들때도 써줘라
- 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 파일은 엑셀 프로그램으로도 제대로 못 읽고, 워드패드로도 못읽고, 딸랑 메모장만 제대로 읽을 수 있다.