번개장터(https://m.bunjang.co.kr/)는 사용자들의 중고 거래를 중개하는 중고 거래 플랫폼입니다. 자동차, 가전제품, 가구, 전자기기, 의류 등 다양한 품목의 거래가 가능하며, 번개장터 내 서비스인 번개페이를 이용해 안전 결제가 가능합니다. 또한, 지역 정보를 확인하여 가까운 지역일 경우 직거래를 요청하기에 용이합니다.
번개장터 앱에서는 필터링 기능이 존재합니다. 하지만 디테일한 기종, 지원 버전, 용량 등을 구분하여 확인할 수 없고, 예약중인 상품 게시글도 따로 볼 수 없습니다. 또한 번개장터 웹 사이트에는 필터링 기능이 따로 존재하지 않습니다.
따라서 번개장터 웹 사이트에서 원하는 상품 데이터를 크롤링하고 MySQL DB에 저장한 후, 기종, 지원 버전, 용량, 예약중인 상품 게시글까지 디테일하게 구분할 수 있는 상세 필터링 기능을 직접 만들어 사용해 보겠습니다.
원하는 태블릿은 Galaxy Tab S6 시리즈이고, 가격은 출고가보다 10만 원 이상 저렴하게 구매하려고 합니다. 또한 직거래가 가능하다면 직거래를 우선으로 하고, 직거래 가능한 매물이 없을 경우에는 택배 거래를 하되 안전한 거래를 위해 번개페이 안전 결제를 사용하려고 합니다.
그러면 아래와 같이 '갤럭시 탭 s6'의 '태블릿' 카테고리에 있는 상품들이 '정확도순'으로 나열된 것을 확인할 수 있습니다. ‘정확도순’은 상품 등록 시간이 순서대로 정렬되어 있지 않으며, 광고(AD)상품들이 상단에 다수 노출되어 있기에 ‘최신순’으로 상품을 확인하겠습니다.
data-pid에는 각 상품 별로 부여된 고유한 번호가 저장되어 있습니다. 이 고유 번호를 활용하여 중복 값을 처리하고, 데이터를 DB에 적재할 때 테이블의 row들을 구분하는 Primary-key로 지정하여 사용합니다.
href
href에는 해당 상품의 상세 페이지로 연결되는 링크 값이 저장되어 있습니다. 원하는 조건의 상품을 조회한 후, 이 링크 값을 사용하여 해당 상품의 상세 페이지로 이동이 가능합니다.
번개 페이 여부
번개 페이는 거래 완료 전까지 번개장터에서 결제 대금을 보관하다가 거래 완료 후 판매자에게 정산이 진행되는 안전 결제 시스템입니다. 택배 거래를 진행해야 하는 경우, 번개 페이 사용이 가능한 상품만 조회하여 중고 거래 사기를 사전에 방지할 수 있습니다.
배송비 포함 여부
택배 거래 시 배송비는 판매자 혹은 구매자가 부담합니다. 판매자가 제시한 가격이 배송비가 포함된 가격인지 확인할 수 있습니다.
검수 가능 여부
번개장터에서는 검수 서비스를 운영하고 있습니다. 검수 서비스란 판매 상품을 먼저 검수하여 인증을 받은 후에 번개장터에서 구매자에게 상품을 배송해 주는 서비스입니다. 이 검수 서비스 이용 가능 여부를 확인하여 이용이 가능하다면 보다 안전한 거래를 할 수 있습니다.
제목
판매자가 등록한 상품 게시글의 제목이 저장되어 있습니다. 여기에서 태블릿의 기종과 용량 등에 대한 정보를 얻어 필터링에 활용할 수 있습니다.
가격
판매자가 등록한 상품의 가격을 확인할 수 있습니다.
등록 시간
판매자가 상품 게시글을 등록한 대략적인 시간을 알 수 있습니다.
거래 지역
판매자가 직거래를 원하는 지역을 확인할 수 있습니다. 이 정보를 활용해 원하는 지역을 필터링 할 수 있습니다. 하지만 거래 지역을 ‘전국’으로 지정한 판매자가 많기에 구매자는 자신이 원하는 지역에서 직거래가 가능한지 판매자에게 확인할 필요가 있습니다.
3.4.3 상품 데이터 특이 사항
위에서 살펴본 데이터에는 고려해야 할 사항이 몇 가지 있습니다. 크롤링한 데이터를 정한 후에는 크롤링 목적에 부합하지 않는 정보가 있는지, 그리고 크롤링 시 코드나 데이터에 오류를 발생시킬 수 있는 부분이 있는지 확인하는 절차가 필요합니다. 위에서 살펴본 데이터들을 크롤링 할 때 고려해야 할 사항들을 살펴보겠습니다.
[AD 여부]
왼쪽 상품은 본래 ‘등록 시간’ 정보가 표시되는 부분에 광고 상품임을 고지하는 AD가 들어가 있습니다. 이러한 광고 상품의 경우, 광고비가 포함되어 가격이 더 높을 수 있으므로 크롤링에서 제외하도록 하겠습니다.
[등록 시간]
등록 시간은 업로드 된 시간으로부터 경과한 시간이 표시되어 있습니다. 따라서 24시간 내에서는 시, 분, 초 단위로 구체적인 시간 파악이 가능하지만, 24시간이 지나면 년, 월, 주, 일 단위로만 표기되어 상품 게시글이 등록된 정확한 날짜와 시간을 파악하기 어렵습니다. 따라서 현재 시간에서 경과 시간을 감한 대략적인 날짜를 계산하여 저장하겠습니다.
[번개 페이 / 배송비 포함 / 검수 가능]
HTML에서 번개페이 이미지가 존재하는지 확인함으로써 번개 페이 사용 가능 여부를 알 수 있습니다. 마찬가지로 배송비 포함 여부와 검수 가능 여부 또한 해당 값이 존재하는지 확인함으로써 그 여부를 알 수 있습니다. 값이 존재할 때는 1, 존재하지 않을 때는 0으로 값을 저장하겠습니다.
번개 페이 O / 배송비 포함 O / 검수 가능 O
아래 HTML에서는 번개 페이, 배송비 포함, 검수 가능이 모두 존재하는 것을 확인할 수 있습니다.
검색창의 위치를 나타내는 XPath를 활용하여 검색창을 찾습니다. 또한 크롤링하려는 상품명을 입력 받아 변수에 저장해줍니다. 이 때 상품명은 ‘갤럭시 탭 s6’로 입력합니다.
# 검색창의 위치를 XPath를 이용해 변수에 저장
search_box = driver.find_element(By.XPATH, '//*[@id="root"]/div/div/div[2]/div[1]/div[1]/div[1]/div[1]/input')
# 입력시에 핵심 키워드를 ' '로 구분하여 입력. 자세한 설명은 2.6.3에서 참고
item_name = input('검색하고자 하는 상품명을 입력하세요: ')
➡️
결과
검색하고자 하는 상품명을 입력하세요: 갤럭시 탭 s6
click 메서드와 send_keys 메서드를 이용해 검색창에 상품명을 입력하여 상품을 검색합니다.
# search_box 초기화를 위해 search_box에 있는 text 전체 선택
search_box.click();
search_box.send_keys(Keys.CONTROL + 'A');
# 입력한 상품 검색
search_box.send_keys(item_name)
search_box.send_keys(Keys.RETURN)
상품을 검색하면 여러 가지 카테고리가 나옵니다. 웹에서 직접 확인하지 않고 여러 카테고리 중 원하는 카테고리를 번호 입력을 통해 선택할 수 있도록 하겠습니다. enumerate는 입력으로 받은 자료형의 인덱스와 요소를 순회 가능한 객체로 반환하는 파이썬의 내장 함수입니다.
# 카테고리 리스트의 위치를 변수에 저장
categories = driver.find_elements(By.XPATH, '//*[@id="root"]/div/div/div[4]/div/div[1]/div/div[2]/a/div[2]')
# 카테고리들의 번호와 내용 출력
for num, c in enumerate(categories):
print(f'{num+1}. {c.text}')
category_selected = int(input('검색하고자 하는 카테고리의 번호를 입력하세요: '))
‘태블릿’ 카테고리의 번호인 ‘1’을 입력합니다.
➡️
결과
1. 태블릿
2. 케이스/보호필름/액세서리
3. 스마트폰
4. 케이블/충전기/주변기기
검색하고자 하는 카테고리의 번호를 입력하세요: 1
webdriver를 이용해 선택한 카테고리를 XPath로 찾고, 클릭해줍니다.
# 선택한 카테고리의 XPath를 변수에 저장
category_path = '//*[@id="root"]/div/div/div[4]/div/div[1]/div/div[2]/a'+'['+str(category_selected)+']'
# 선택한 카테고리 클릭
search_button = driver.find_element(By.XPATH, category_path)
search_button.click()
import pandas as pd
# 크롤링한 데이터를 저장할 DataFrame
df = pd.DataFrame(columns=['pid', 'title', 'price', 'upload_time', 'place', 'inspection', 'delivery_fee', 'pay', 'href'])
# 크롤링 종료 조건을 확인하기 위한 flag 변수
crawling_fin_flag = 0
# 검색 결과 페이지 수만큼 반복 (최대 10페이지까지)
for i in range(10):
product_list = driver.find_elements(By.XPATH, '//*[@id="root"]/div/div/div[4]/div/div[4]/div/div')
##### 페이지 별 갯수 확인 코드
start_len = len(df)
############################
for data in product_list:
'''크롤링 종료 조건'''
# '판매완료'인 상품 체크
try:
if data.find_element(By.XPATH, 'a/div[1]/div[2]/div[1]/img').get_attribute('alt') == '판매 완료':
crawling_fin_flag = 1
break
except:
pass
'''크롤링 예외 조건'''
# 예외조건1 - 예약중
try:
if data.find_element(By.XPATH, 'a/div[1]/div[2]/div[1]/img').get_attribute('alt') == '예약중':
continue
except:
pass
# 예외조건2 - AD 상품
text_list = data.text.split('\n')
if text_list[-2] == 'AD':
continue
'''DataFrame에 저장'''
# data-pid
pid = data.find_element(By.XPATH, 'a').get_attribute('data-pid')
# href
href = data.find_element(By.XPATH, 'a').get_attribute('href')
# 번개페이 가능 여부
try:
result = data.find_element(By.CLASS_NAME, 'styled__IconBadge-sc-3zkh6z-2.iDmRbz')
pay = 1
except:
pay = 0
# 게시글 제목, 상품 가격, 게시글 업로드 시간, 거래 장소
title = text_list[-4]
price = text_list[-3]
upload_time = text_list[-2]
place = text_list[-1]
# 검수 가능 여부
if '검수가능' in text_list:
inspection = 1
else:
inspection = 0
# 배송비 포함 여부
if '배송비포함' in text_list:
delivery_fee = 1
else:
delivery_fee = 0
# 데이터프레임에 저장
df.loc[len(df)] = [pid, title, price, upload_time, place, inspection, delivery_fee, pay, href]
##### 페이지 별 갯수 확인 코드
print(f'{i+1} page {len(df) - start_len}개 (총 {len(df)}개)')
############################
# 크롤링 종료 조건
if crawling_fin_flag:
print('Crawling Finish')
break
# 다음 페이지 버튼 클릭
page_button = driver.find_element(By.XPATH, '//*[@id="root"]/div/div/div[4]/div/div[5]/div/a['+str(i+3)+']')
page_button.click()
time.sleep(2)
# webdriver 종료
driver.close()
크롤링을 통해 2.4.2에서 살펴본 9가지 데이터를 저장할 Dataframe과 Column을 생성합니다.
import pandas as pd
# 크롤링한 데이터를 저장할 DataFrame
df = pd.DataFrame(columns=['pid', 'title', 'price', 'upload_time', 'place', 'inspection', 'delivery_fee', 'pay', 'href'])
# 크롤링 종료 조건을 확인하기 위한 flag 변수
crawling_fin_flag = 0
크롤링할 상품 게시글들을 XPath를 이용해 불러오기
상품 게시글을 찾는 XPath로 페이지 내의 모든 상품 게시글을 크롤링하여 가져올 수 있습니다. 가져온 데이터는 product_list 변수에 저장해줍니다.
# 검색 결과 페이지 수만큼 반복 (최대 10페이지까지)
for i in range(10):
product_list = driver.find_elements(By.XPATH, '//*[@id="root"]/div/div/div[4]/div/div[4]/div/div')
for문을 사용해 product_list에 저장된 각 상품 게시글의 정보를 순회하며 필요한 데이터를 저장해 보겠습니다.
for data in product_list:
크롤링 종료 조건
판매완료 이미지가 있는 경우 더 이상 크롤링 할 상품 게시글이 없는 것이므로 크롤링을 종료해야 합니다. 이를 위해 판매완료 이미지를 만났을 경우, crawling_fin_flag라는 변수에 저장된 값을 1로 변경해 주고 product_list를 순회하는 for문을 빠져나갑니다. 판매완료 이미지가 존재 하지 않을 경우, 코드가 비정상적으로 종료될 수 있기 때문에 try-except문을 사용해 줍니다.
# '판매완료'인 상품 체크
try:
if data.find_element(By.XPATH, 'a/div[1]/div[2]/div[1]/img').get_attribute('alt') == '판매 완료':
crawling_fin_flag = 1
break
except:
pass
product_list를 순회하는 for문 밖에서 crawling_fin_flag가 1인 것이 확인되면 전체 크롤링을 종료합니다.
if crawling_fin_flag:
print('Crawling Finish')
break
크롤링 예외 조건 1 - 예약중
예약중 이미지가 있는 경우, 크롤링 결괏값에 포함시키지 않고 continute를 이용해 데이터를 저장하지 않고 다음 상품 게시글의 정보로 넘어가도록 하겠습니다.
AD 상품임을 확인하기 위해 상품 게시글의 텍스트 요소를 가져옵니다. 가져온 텍스트 요소에는 [배송비 포함, 검수 가능, 제목, 가격, 등록 시간, 장소]과 같은 값이 줄 바꿈(\n)을 기준으로 구분되어 있습니다. 따라서 줄바꿈(\n)을 기준으로 구분하여 text_list에 저장해 줍니다.
# 예외조건2 - AD 상품
text_list = data.text.split('\n')
2.4.3에서 정했듯이 업로드 시간이 있어야 하는 부분에 업로드 시간 대신에 'AD' 문자열이 확인되면 아래 코드와 같이 continue를 이용해 데이터프레임에 값을 저장하지 않고 다음 상품 게시글의 정보로 넘어가도록 합니다.
if text_list[-2] == 'AD':
continue
변수에 저장된 데이터를 DataFrame에 저장
Primary-key로 사용할 data-pid는 상품 게시글 a 태그의 속성에 해당하므로 get_attribute 메서드로 값을 불러와 pid 변수에 저장해 줍니다.
한 페이지의 크롤링을 완료하고, Dataframe에 데이터를 저장한 후에는 다음 페이지 버튼을 XPath를 찾고 클릭하여 다음 페이지로 넘어갑니다. 페이지를 넘긴 후 로딩이 완료되기 전에 크롤링이 시작되면 오류가 나거나, 결괏값이 유실될 수 있으므로 time.sleep을 이용해 다음 코드의 실행 시간을 유예해 줍니다.
# 다음 페이지 버튼 클릭
page_button = driver.find_element(By.XPATH, '//*[@id="root"]/div/div/div[4]/div/div[5]/div/a['+str(i+3)+']')
page_button.click()
time.sleep(2)
📢
NoSuchElementError
웹 페이지가 모두 로딩이 되기 전에 크롤링이 시작되어서 나는 오류입니다.
해당 오류가 발생할 경우에 time.sleep() 의 시간을 늘려줌으로 해결할 수 있습니다.
webdriver 종료
크롤링이 정상적으로 종료되었다면 아래 코드를 실행시켜 webdriver를 종료합니다.
driver.close()
Dataframe 확인
df를 출력해 크롤링된 데이터와 그 형태를 확인합니다.
df
➡️
결과
3.6 데이터 전처리
Dataframe에 저장된 데이터의 전처리를 진행하겠습니다.
데이터 원본 복사
본격적인 전처리를 시작하기에 앞서, 데이터 원본 새로운 변수에 저장합니다. Dataframe 원본을 복사해 두는 이유는 전처리 과정에서 오류가 나거나, 실수를 했을 경우를 대비하는 것입니다.
df_temp = df.copy()
만약 전처리 과정에서 오류가 난다면, 아래 코드를 실행하여 데이터 원본을 불러와 사용할 수 있습니다.
# 전처리 과정 중에 데이터 원본이 다시 필요한 경우 아래 코드 실행
df = df_save.copy()
3.6.1 중복 상품 제거
만약 크롤링을 여러 번 반복하여 Dataframe에 데이터를 저장했다면, 중복되는 값이 존재할 수 있습니다. 이런 경우 아래 코드를 활용해 중복되는 행을 제거할 수 있습니다.
# 중복행 확인
df[df.duplicated(subset=['pid'], keep='first')]
price Column에 저장된 값들은 string으로 저장되어 있어 활용하기 어려운 형태입니다. 따라서 활용하기 쉬운 int형으로 변환하겠습니다. 이를 위해 값의 중간에 삽입되어 있는 문자열인 ‘,’를 제거해 주고, int형으로 변환하여 다시 저장해줍니다. 여기서 가격 대신 ‘연락요망’이 기재된 값의 행은 drop하고 진행해 주도록 합니다.
for index, row in df.iterrows():
price_str = row.copy()['price'] # SettingWithCopyWarning 발생 방지를 위한 copy
if price_str == '연락요망': # 가격이 표시되지 않은 제품 제외
df = df.drop(index)
else:
price_int = int(price_str.replace(',', ''))
df.loc[index, 'price'] = price_int
upload_time Column에 저장되어 있는 값은 2.4.3에서 설명했듯이 24시간을 기준으로 하여 표기 방식이 나뉩니다. 따라서 표기된 방식에 따라 현재 시간과의 차이를 계산하여 다시 표현해 주도록 하겠습니다. 여기서 시간과 관련된 메서드들을 사용해야하므로 관련 라이브러리도 먼저 import 해줍니다.
title Column의 값을 확인해 보면 구매 목적을 가진 게시글이 있습니다. 정규표현식을 이용해 해당 값이 존재하는 행을 삭제해 줍니다. 정규표현식을 사용하기 위해서는 re 라이브러리를 먼저 import 해야 합니다.
# '구매' 목적인 게시글의 상품 제외
import re
pattern = r'삽니다|구해|구합|구매|사요'
pattern_matching_index = df[df['title'].str.contains(pattern, flags=re.IGNORECASE, regex=True)].index
df = df.drop(index=pattern_matching_index)
또한 title Column에 검색한 상품의 키워드가 존재하지 않는 경우가 있습니다. 검색한 상품의 키워드는 입력한 상품명이 저장된 item_name 변수의 값을 공백을 기준으로 구분한 것입니다. 즉, ‘갤럭시 탭 s6’를 입력하였으니, ‘갤럭시’, ‘탭’, ‘s6’가 검색한 상품의 키워드입니다. 다만, '갤럭시'는 '갤럭시', '겔럭시', 'Galaxy'와 같이 사람마다 입력하는 방식이 달라 모든 예외사항을 확인하기는 어렵습니다. 하지만 다행히 ‘탭’과 ‘s6’라는 키워드 만으로도 갤럭시 탭 s6 시리즈임을 충분히 확인할 수 있습니다. 따라서 두 키워드를 이용해 제목에 원하는 상품명이 포함되어 있는지 확인하고, 없을 경우에는 해당 행을 삭제해 줍니다.
# 검색어와 관련없는 상품 제외
item_name_list = item_name.split(' ') # 검색어 입력시 키워드를 공백으로 구분
for index, row in df.iterrows():
title = row['title'].replace(' ','').lower() # 알파벳은 대소문자 구분하지 않음
for check in item_name_list[1:]: # '갤럭시', '겔럭시', 'Galaxy'가 많아서 '탭'과 's6'로만 확인
if check.lower() not in title:
df = df.drop(index)
break
Galaxy Tab S6에는 64GB, 128GB, 256B가 존재합니다. 64, 128, 256에 해당하는 숫자가 제목에 있을 경우, 각각 64GB, 128GB, 256GB로 저장해 주고 없을 경우 ‘-’로 저장합니다.
df['volume'] = ''
for index, row in df.iterrows():
title = row['title'].replace(' ','').lower()
if '64' in title:
df.loc[index, 'volume'] = '64GB'
elif '128' in title:
df.loc[index, 'volume'] = '128GB'
elif '256' in title:
df.loc[index, 'volume'] = '256GB'
else:
df.loc[index, 'volume'] = '-'
version Column 생성 및 값 저장
Galaxy Tab S6는 LTE, WIFI, 5G 버전으로 구분됩니다. 크롤링으로 가져온 데이터에서 지원 버전을 구분할 수 있는 데이터는 제목입니다. title Column 내에 해당 값이 존재할 경우 version 컬럼에 이를 저장하고, 존재하지 않을 경우에는 ‘-’를 저장해 줍니다. 여기에서 'SM-P610'과 'SM-T860'은 WIFI 버전이라는 점과 5G 기종의 용량은 모두 128GB라는 점을 고려하여 코드를 작성해 줍니다.
df['version'] = ''
flag_dict = {'5g':0, 'wifi': 0, 'lte': 0}
for index, row in df.iterrows():
title = row['title'].replace(' ','').lower()
# '5g', 'wifi', 'lte' 기종인지 확인
if '5g' in title or '866' in title:
flag_dict['5g'] = 1
if ('wifi' in title or '와이파이' in title or '610' in title or '860' in title):
flag_dict['wifi'] = 1
if ('lte' in title or '룰러' in title or '615' in title or '865' in title):
flag_dict['lte'] = 1
# 확인한 데이터를 토대로 '5G', 'WIFI', 'LTE' 저장
if flag_dict['5g'] == 1:
df.loc[index, 'version'] = '5G'
df.loc[index, 'volume'] = '128GB' # 5G 기종의 용량은 모두 128G
elif flag_dict['wifi'] == 1 and flag_dict['lte'] == 1:
df.loc[index, 'version'] = 'WIFI/LTE'
elif flag_dict['wifi'] == 1:
df.loc[index, 'version'] = 'WIFI'
elif flag_dict['lte'] == 1:
df.loc[index, 'version'] = 'lte'
else:
df.loc[index, 'version'] = '-'
# flag 값 초기화
flag_dict['5g'] = 0
flag_dict['wifi'] = 0
flag_dict['lte'] = 0
type colmun 생성 및 값 저장
Galaxy Tab S6의 기종 정보는 아래와 같습니다.
ㅤ
갤럭시탭 S6 Lite (WIFI)
갤럭시탭 S6 Lite (LTE)
갤럭시탭 S6 (WIFI)
갤럭시탭 S6 (LTE)
갤럭시탭 S6 (5G)
모델명
SM-P610
SM-P615N
SM-T860
SM-T865N
SM-T866N
저장 용량(GB)
64GB/128GB
64GB/128GB
128GB/256GB
128GB/256GB
128GB
지원 버전
WIFI
LTE
WIFI
LTE
LTE
출고가
45만원 (64GB)
49만원 (128GB)
49만원 (64GB)
54만원 (128GB)
79만9천원 (128GB)
89만8천원 (256GB)
89만8천원 (128GB)
99만9천원 (256GB)
99만 9천원
기종 정보 또한 제목에서 확인이 가능하지만, 지원 버전이나 용량보다 그 정보가 적습니다. 따라서 위 표의 정보를 활용해 기종 정보를 최대한 확인하겠습니다. 상품 게시글 제목의 일부에는 모델명을 직접 기재한 경우가 있으므로, 모델명으로 기종을 확인할 수 있습니다. 또한 한 가지 용량만을 가지는 기종이 있으므로 이를 통해 기종을 확인할 수 있습니다.
title에서 ‘S6’인지, ‘S6 Lite’인지 확인하여 저장합니다. 64GB 용량은 S6 Lite 기종만 존재하고, 256GB 용량은 S6 5G 기종만 존재하므로 이를 확인하여 값을 저장합니다. 'SM-P61*' 은 S6 Lite 모델명, 'SM-T86*'은 S6 모델명이므로 이를 확인하여 값을 저장합니다. S6 Lite가 아니라고 명시해 둔 상품 게시글이 많았기에, 이러한 상품들을 구분하여 S6로 저장합니다.
df['type'] = ''
for index, row in df.iterrows():
if row['volume'] == '64GB':
df.loc[index, 'type'] = 'S6 Lite'
elif row['volume'] == '256GB':
df.loc[index, 'type'] = 'S6'
elif '61' in row['title']:
df.loc[index, 'type'] = 'S6 Lite'
elif '86' in row['title']:
df.loc[index, 'type'] = 'S6'
else:
title = row['title'].replace(' ','').lower()
if len(re.findall(r'(lite|라이트)(아님|아닙|X)', title)) > 0:
df.loc[index, 'type'] = 'S6'
elif 'lite' in title or '라이트' in title:
df.loc[index, 'type'] = 'S6 Lite'
else:
df.loc[index, 'type'] = 'S6'
앞서 저장한 password를 이용하여 MySQL DB에 root 계정으로 접속하는 Connection 객체를 생성합니다.
# MySQL Connection
conn = pymysql.connect(host = '127.0.0.1',
port = 3306,
user = 'root',
password = password,
charset='utf8')
Connection 객체의 커서(cusor) 객체를 생성하고, ‘lightning_market’ DB를 생성하는 CREATE문을 실행합니다. 실행한 SQL문을 실제 DB에 적용하기 위해 commit을 실행합니다. with 절을 이용하였기에 Connection과 커서는 모든 작업을 끝낸 후 자동으로 close 됩니다.
# SQL문 실행 - DB 생성
with conn:
with conn.cursor() as cur:
cur.execute('CREATE DATABASE lightning_market')
conn.commit()
3.7.2 테이블 생성
앞에서 생성한 ‘lightning_market’ DB에 접속하여 테이블을 생성하고 크롤링한 데이터를 저장해 보겠습니다.
먼저 ‘lightning_market’ DB에 root 계정으로 접속하는 Connection 객체를 생성합니다.
# MySQL DB Connection
conn = pymysql.connect(host = '127.0.0.1',
port = 3306,
user = 'root',
password = password,
charset='utf8',
database='lightining_Market')
아래는 상품의 12개 속성을 저장할 테이블을 생성하는 SQL문입니다. 각 상품을 구분할 수 있는 pid 값을 primary key로 설정하고, 제목(title), 등록 시간(upload_time), 링크(href), 용량(volume), 기종(type), 지원버전(version)는 varchar형(문자형)으로, 가격(price), 검수 가능 여부(inspection), 배송비 포함 여부(delivery_fee), 번개페이 가능 여부(pay)는 int형(정수형)으로 설정하겠습니다.
# 테이블 생성 SQL문
sql = '''create table market_product (
pid varchar(10) primary key,
title varchar(50),
price int,
upload_time varchar(10),
place varchar(30),
inspection int,
delivery_fee int,
pay int,
href varchar(150),
volume varchar(10),
type varchar(10),
version varchar(10));'''
Connection 객체의 커서(cusor) 객체를 생성하고, ‘lightning_market’ DB에 ‘market_product’ 테이블을 생성하는 SQL문을 실행합니다. 실행한 SQL문을 실제 DB에 적용하기 위해 commit을 실행합니다. with 절을 이용하였기에 커서는 SQL문을 실행한 후 자동으로 close 됩니다.
# SQL문 실행 - 테이블 생성
with conn.cursor() as cur:
cur.execute(sql)
conn.commit()
3.7.3 데이터 저장
앞서 생성한 ‘market_product’ 테이블에 크롤링한 데이터를 저장하겠습니다.
Connection 객체의 커서(cusor) 객체를 생성하고, INSERT문을 실행하여 각 상품의 12개 속성들을 테이블에 저장합니다. 실행한 SQL문을 영구적으로 저장하기 위해 commit을 실행합니다. with 절을 이용하였기에 커서는 SQL문을 실행 한 후 자동으로 close됩니다.
# SQL문 실행 - 데이터 삽입
with conn.cursor() as cursor:
for _, row in df.iterrows():
sql = '''
INSERT INTO market_product (pid, title, price, upload_time, place, inspection, delivery_fee, pay, href, volume, type, version)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
'''
values = (
row['pid'], row['title'], row['price'], row['upload_time'], row['place'],
row['inspection'], row['delivery_fee'], row['pay'], row['href'], row['volume'], row['type'], row['version']
)
cursor.execute(sql, values)
conn.commit()
테이블 생성 및 데이터 저장 작업을 모두 마쳤으니 ‘lightning_market’ DB에 연결시켜둔 Connection 객체를 close 합니다.
# MySQL DB Connection 종료
conn.close()
3.7.4 MySQL에서 저장된 데이터 확인하기
MySQL Workbench 프로그램을 실행하여 생성한 ‘lightning_market’ DB와 ‘market_product’ 테이블과 테이블에 저장된 데이터들을 확인해 보겠습니다.
아래 코드를 실행시키면 MySQL DB에 생성되어 있는 모든 Database를 확인할 수 있습니다. 앞서 생성한 ‘lightning_market’ DB가 존재하는 것을 확인 할 수 있습니다.
SHOW DATABASES;
➡️
결과
아래 코드를 실행시키면 ‘lightning_market’ DB를 선택하여 사용할 수 있습니다.
USE lightning_market;
아래 코드를 실행시키면 현재 선택한 DB(’lightning_market’ DB)에 생성되어 있는 모든 테이블을 확인할 수 있습니다. 앞서 생성한 ‘market_product’ 테이블이 존재하는 것을 확인할 수 있습니다.
SHOW TABLES;
➡️
결과
아래 코드를 실행시키면 INSERT문으로 삽입한 데이터들이 모두 정상적으로 저장된 것을 확인 할 수 있습니다.
select * from market_product;
➡️
결과
3.8 DB 활용 - 필터링
필터링을 사용하여 DB에 저장한 데이터들에서 조건에 만족하는 제품을 찾아보겠습니다.
먼저 ‘lightning_market’ DB에 root 계정으로 접속하는 Connection 객체를 생성합니다.
# MySQL Connection
conn = pymysql.connect(host = '127.0.0.1',
port = 3306,
user = 'root',
password = password,
charset='utf8',
database='lightining_Market')
3.8.1 필터링1 - 용량, 지원버전, 가격, 등록 날짜 조건
먼저 용량, 지원 버전(WIFI/LTE), 가격, 등록 날짜에 조건을 걸어 상품을 조회해 보겠습니다. 원하는 조건은 아래와 같습니다.
Column
조건
이유
volume
(용량)
128GB
64GB는 용량이 충분하지 않을 것 같고, 또 256GB까지는 필요하지 않을 것 같습니다.
version
(지원 버전)
LTE X
WIFI가 가능한 곳에서만 태블릿을 사용할 예정입니다.
price
(가격)
350,000원 이하
Galaxy Tab S6 시리즈의 최저 출고가인 45만원에서 10만원 이상 저렴하길 원합니다.
upload_time
(등록 시간)
이번 달 이내
등록한 지 오래된 상품들은 거래완료가 되었음에도 판매완료 처리가 되지 않은 상품이 존재할 수 있으므로 이번 달에 올라온 제품으로 조회합니다.
위의 조건들을 만족하는 상품들을 조회하고, 최근 등록된 상품 순으로 정렬하는 SQL문입니다.
# 필터링1 조건에 만족하는 데이터를 조회하는 SQL문
query = '''SELECT *
FROM market_product
WHERE volume = '128GB'
AND version != 'LTE'
AND price < 350000
AND upload_time > '2023-06-01'
ORDER BY upload_time DESC;'''
Connection 객체의 커서(cusor) 객체를 생성하고, 조건을 만족하는 상품을 조회하는 SQL문을 실행합니다. 조건을 만족하는 상품들을 데이터를 fetchall 메서드를 통해 받아옵니다. with 절을 이용하였기에 커서는 SQL문을 실행한 후 자동으로 close 됩니다.
# SQL문 실행 - 데이터 조회
with conn.cursor() as cursor:
cursor.execute(query)
result = cursor.fetchall()
조건을 만족하는 데이터가 총 몇 개인지 len 메서드를 통해 확인했습니다. 또한 상품의 전체 데이터를 데이터프레임으로 변환하여 확인했습니다.
# 결과 출력
print(f'조건에 일치하는 상품: 총 {len(result)}개')
result_df = pd.DataFrame(result, columns=['pid', 'title', 'price', 'upload_time', 'place', 'Inspection', 'delivery_fee', 'pay', 'href', 'volume', 'type', 'version'])
result_df
데이터를 업로드 시간으로 정렬하였기에 가장 위에 있는 데이터의 상세 페이지 링크를 출력하였습니다. 이 링크를 통해 해당 상품의 상세 페이지에 접속이 가능합니다.
2.8.1에서 조회한 상품들 중에서 서울에서 직거래가 가능한 상품을 먼저 조회해 보겠습니다. 추가된 조건은 아래와 같습니다.
Column
조건
이유
place
(거래 지역)
서울 내
거주지가 서울이므로 서울 내에서 직거래를 희망합니다.
2.8.1의 조건을 만족하며 서울 내에서 직거래가 가능한 상품을 조회하는 SQL문입니다.
# 필터링2 조건에 만족하는 데이터를 조회하는 SQL문
query = '''SELECT *
FROM market_product
WHERE volume = '128GB'
AND version != 'LTE'
AND price < 330000
AND upload_time > '2023-06-01'
AND place LIKE '서울%'
ORDER BY upload_time DESC;'''
Connection 객체의 커서(cusor) 객체를 생성하고, 조건을 만족하는 상품을 조회하는 SQL문을 실행합니다. 조건을 만족하는 상품들을 데이터를 fetchall 메서드를 통해 받아옵니다. with 절을 이용하였기에 커서는 SQL문을 실행 한 후 자동으로 close 됩니다.
# SQL문 실행 - 데이터 조회
with conn.cursor() as cursor:
cursor.execute(query)
result = cursor.fetchall()
조건을 만족하는 데이터가 총 몇 개인지 len 메서드를 통해 확인했습니다. 또한 상품의 전체 데이터를 데이터프레임으로 변환하여 확인했습니다.
# 결과 출력
print(f'조건에 일치하는 상품: 총 {len(result)}개')
result_df = pd.DataFrame(result, columns=['pid', 'title', 'price', 'upload_time', 'place', 'Inspection', 'delivery_fee', 'pay', 'href', 'volume', 'type', 'version'])
result_df
➡️
결과
데이터를 업로드 시간으로 정렬하였기에 가장 위에 있는 데이터의 상세 페이지 링크를 출력하였습니다. 이 링크를 통해 해당 상품의 상세 페이지에 접속이 가능합니다.
필터링2의 상품 판매자와 원만한 거래가 성사되지 않아 직거래가 어려울 경우, 택배 거래를 하려고 합니다. 안전한 택배 거래를 위해 번개 페이가 가능한 상품을 조회해 보겠습니다. 변경된 조건은 아래와 같습니다.
Column
조건
이유
place
(거래 지역)
서울 제외
서울 내에서 직거래가 성사되지 않은 상품들은 제외힙니다.
pay
(번개 페이)
번개 페이 가능
(1)
택배 거래를 진행하게 된다면, 안전 결제 시스템을 이용하고 싶습니다.
2.8.1의 조건을 만족하며 번개 페이 사용이 가능한 상품들을 조회하는 SQL문입니다.
# 필터링3 조건에 만족하는 데이터를 조회하는 SQL문
query = '''SELECT *
FROM market_product
WHERE volume = '128GB'
AND version != 'LTE'
AND price < 330000
AND upload_time > '2023-06-01'
AND pay = 1
ORDER BY upload_time DESC;'''
Connection 객체의 커서(cusor) 객체를 생성하고, 조건을 만족하는 상품을 조회하는 SQL문을 실행합니다. 조건을 만족하는 상품들을 데이터를 fetchall 메서드를 통해 받아옵니다. with 절을 이용하였기에 커서는 SQL문을 실행한 후 자동으로 close 됩니다.
# SQL문 실행 - 데이터 조회
with conn.cursor() as cursor:
cursor.execute(query)
result = cursor.fetchall()
조건을 만족하는 데이터가 총 몇 개인지 len 메서드를 통해 확인했습니다. 또한 상품의 전체 데이터를 데이터프레임으로 변환하여 확인했습니다.
# 결과 출력
print(f'조건에 일치하는 상품: 총 {len(result)}개')
result_df = pd.DataFrame(result, columns=['pid', 'title', 'price', 'upload_time', 'place', 'Inspection', 'delivery_fee', 'pay', 'href', 'volume', 'type', 'version'])
result_df
➡️
결과
데이터를 업로드 시간으로 정렬하였기에 가장 위에 있는 데이터의 상세 페이지 링크를 출력하였습니다. 이 링크를 통해 해당 상품의 상세 페이지에 접속이 가능합니다.
