파이썬으로 DB 정제

목표

총 28023개의 네이버 플레이스 링크 중 실제로는 등록되지 않은 가맹점이나, 크롤링 과정에서 어쩔 수 없이 섞이게 된 더미링크 제거

분석

해당 링크를 들어가면 다음과 같은 화면이 나타난다.

• requests 라이브러리를 사용해 플레이스 링크로 get 요청을 날려 html 문자열 수집
• bs4의 html parser와 select를 이용하여 해당 태그의 값 가져오기
• 해당 값이 “조건에 맞는 업체가 없습니다.” 인 경우에 해당 데이터 제거
• 해당 태그가 존재하지 않거나 그 값이 “조건에 맞는 업체가 없습니다.” 인 경우 패스

필터링 과정

1. DB 연결

• 전남청년문화복지카드 가맹점 안내 앱 서버의 DB는 Mysql을 사용하므로 pymysql 라이브러리를 이용하여 DB에 접근.
• pip install pymysql

• 해당 서버가 가동되고 있는 환경에서 3306은 외부에서 접근할 수 없고 localhost에 대해서만 3306포트가 열려있으므로 ssh tunneling 접근이 필요함. 따라서 sshtunnel 라이브러리 이용.
• pip install sshtunnel

• with문으로 먼저 서버 터널 개방.
• with SSHTunnelForwarder(("ip주소",포트번호), ssh_username="유저이름", ssh_password='패스워드', remote_bind_address=('localhost', 3306)) as tn: #서버 접속에 pem 파일을 사용한다면 ssh_pkey 파라미터에 파일 경로를 지정

• 다음으로 pymysql을 통해 DB와의 연결 생성.
• with pymysql.connect(host="127.0.0.1", port=tn.local_bind_port, #서버에서 설정한 mysql 포트가 아니라 터널 객체의 local_bind_port값 입력 user="DB유저이름", passwd="DB유저비밀번호", database="스키마이름" ) as conn:
• 마지막으로 생성한 연결에서 cursor를 생성.
• with conn.cursor() as cur:

2. 요소 찾기

• 가게 정보가 플레이스에 없는 링크로 들어가 개발자 도구를 열고 해당 요소 위치 탐색

• 해당 태그에 우클릭 - copy - Copy selector 선택

• 코드 작성. element[0].text의 값을 확인하면 플레이스에 등록되어 있는 업체인지 더미 데이터인지 판단 가능.
• html_data = requests.get("https://pcmap.place.naver.com/place/list?query=%EB%82%98%EC%A3%BC%EC%8B%9C%EB%9F%AC%EB%B8%94%EB%A6%AC%ED%95%84%EB%9D%BC%ED%85%8C%EC%8A%A4&x=126.78641849999718&y=35.02211919999884&clientX=126.961288&clientY=35.959818&bounds=126.77853280549135%3B35.01211113806433%3B126.79451877122051%3B35.031950334899335&ts=1706102563135&mapUrl=https%3A%2F%2Fmap.naver.com%2Fp%2Fsearch%2F%EB%82%98%EC%A3%BC%EC%8B%9C%EB%9F%AC%EB%B8%94%EB%A6%AC%ED%95%84%EB%9D%BC%ED%85%8C%EC%8A%A4") bs = BeautifulSoup(html_data.content,"html.parser",from_encoding="cp949") element = bs.select("#app-root > div > div.XUrfU > div > div",) # bs의 from_encodeing 파라미터는 필수는 아니지만, 이 작업의 경우 한글이 깨져서 추가하였음 print(element[0].text) #출력 결과: 조건에 맞는 업체가 없습니다.

3. DB에서 place 정보 가지고 오기

• 커서를 통해 DB에 sql문을 실행한 결과를 가지고 와 변수에 저장. 다른 정보는 필요 없으므로 고유값인 shop_num과 place만 호출해도 무관함.
• cur.execute("SELECT shop_num, place FROM kj;") results = cur.fetchall()

4. 더미데이터 제거

• fetchall은 리스트를 반환하므로 각 데이터들에 대해 2의 요소 찾기, 더미데이터 확인을 수행.
• for shop_num,place in results: html_data = requests.get(place) bs = BeautifulSoup(html_data.content,"html.parser",from_encoding="cp949") element = bs.select("#app-root > div > div.XUrfU > div > div",) for text in element: if text.text==TARGET_STR: print(shop_num) cur.execute("UPDATE kj SET place=null WHERE shop_num=%s",(shop_num,)) conn.commit() #conn.commit()을 호출해야 현재 실행한 쿼리문이 DB에 반영된다. #매번 호출하고 있지만, 오류나서 꺼질 일이 없다는 자신이 있다면 마지막에 한 번에 commit하여도 상관 없다.

전체 코드

import pymysql
from sshtunnel import SSHTunnelForwarder

import requests
from bs4 import BeautifulSoup

TARGET_STR = "조건에 맞는 업체가 없습니다."
with SSHTunnelForwarder(("SERVER_HOST",22),
                        ssh_username="SERVER_USERNAME",
                        ssh_password='SERVER_PASSWORD',
                        remote_bind_address=('DB_HOST', DB_PORT)) as tn:
    with pymysql.connect(host="DB_HOST_FROM_SERVER",
                         port=tn.local_bind_port,
                         user="DB_USER",
                         passwd="DB_PASSWORD",
                         database="SCHEMA"
                         ) as conn:
        with conn.cursor() as cur:
            cur.execute("SELECT shop_num, place FROM kj;")
            results = cur.fetchall()
            for shop_num,place in results:
                if place is not None and place != '':
                    html_data = requests.get(place)
                    bs = BeautifulSoup(html_data.content, "html.parser", from_encoding="cp949")
                    element = bs.select("#app-root > div > div.XUrfU > div > div", )
                    for text in element:
                        if text.text == TARGET_STR:
                            print(shop_num, )
                            cur.execute("UPDATE kj SET place=null WHERE shop_num=%s", (shop_num,))
                            conn.commit()

힘들게 노가다 하지 말고 편하게 살자.