[PostGRES] API 검색 결과를 데이터베이스로 비교

NAVER web crawling 결과로 도무지 파악할 수 없었던 인허가 데이터를

NAVER API 를 활용해 하나하나 쿼리해본 결과를 csv로 저장해뒀다.

 

이제 이 결과물을 PostGRES 로 옮겨서 SQL로 다시 한 번 매칭해볼 것이다.

 

폴더에 저장된 csv 들을 불러들여 PostGRES로 이동시키는 건 간단하다.

 

import pandas as pd
from sqlalchemy import create_engine #Dataframe -> SQL 에는 sqlalchemy 를 불러오자
from glob import glob

# csv 파일들 불러오기
filenames = glob('./폴더이름이뭐에요/*.csv') #glob 함수에서 '*.csv' 는 모든 csv 파일을 불러준다. 이 결과물은 'text의 리스트'

# csv 파일들 합치기
dfs = []
for file in filenames:
    t = pd.read_csv(file)
    dfs.append(t)
df_concat = pd.concat(dfs) # 하나하나 읽어서 리스트에 추가하고 concatenate 한다.

engine = create_engine('postgresql+psycopg2://사용자이름이뭐에요:비밀번호뭐에요@호스트이름이뭐에요:5432/데이터베이스이름이뭐에요') #SQL 접속을 위한 비밀번호야아

df_concat.drop(columns = ['Unnamed: 0'], inplace = True) #Unnamed 는 정말 지긋지긋해
df_concat.to_sql('테이블이름이뭐에요', con = engine, chunksize = 1000, index = False) # 데이터프레임을 SQL로.index 는 없애주자

 

이 결과, 

3만 5천개의 결과물...

 

이 중에서 음식점 이름과 주소를 가지고

검색에 성공한 음식점들을 골라재보자.

내 생각에 주소에 text similarity 를 적용하는 건 조금 위험한 일인 것 같고,

'구' + '도로명' + text similarity 로 필터를 만들어보면 될 것 같다.

 

SELECT pmid, p_title, n_title, similarity(p_title, n_title), 
split_part(p_addr, ' ', 2) p_SIG_NM,
split_part(p_addr, ' ', 3) p_RD_NM,
split_part(n_addr, ' ', 2) n_SIG_NM,
split_part(n_addr, ' ', 3) n_RD_NM

FROM api_crawled

WHERE split_part(p_addr, ' ', 2) = split_part(n_addr, ' ', 2) AND -- 구 이름이 같고
split_part(p_addr, ' ', 3) = split_part(n_addr, ' ', 3) AND -- 도로 이름이 같고
similarity(p_title, n_title) > 0.15 -- 상호명의 유사도가 0.15 이상

ORDER BY similarity

 

 

몇 번의 관찰 결과,  0.15가 그래도 적합해보인다.

마음에 안 드는 녀석들이 있긴 하지만...

 

이제 API 로 검색해도 안나오는 Third Place 들을 찾아볼 차례.

pmid 를 이용해서 JOIN 해보자.

 

WITH cralwed AS
(SELECT pmid, p_title, n_title, similarity(p_title, n_title), 
	split_part(p_addr, ' ', 2) p_SIG_NM,
	split_part(p_addr, ' ', 3) p_RD_NM,
	split_part(n_addr, ' ', 2) n_SIG_NM,
	split_part(n_addr, ' ', 3) n_RD_NM

	FROM api_crawled

	WHERE split_part(p_addr, ' ', 2) = split_part(n_addr, ' ', 2) AND
	split_part(p_addr, ' ', 3) = split_part(n_addr, ' ', 3)
	and similarity(p_title, n_title) > 0.15)
	
SELECT pp.* FROM crawl_permit_place pp
LEFT JOIN cralwed cc
ON pp.pmid = cc.pmid
WHERE cc.pmid is NULL

 

와아아 그런데 진짜 열받는 포인트,

 

'곰달래로 육회대첩' 하면 안 나오는 검색 결과가

'강서구 육회대첩' 하면 나온다....

와 ...

도로명 기준으로 크롤링 하면 실패하고

구 이름 기준으로 크롤링하면 성공한다고?

 

누가 이기나 해보자...

저 녀석들 기준으로 내가 '구 + 상호명' 으로 API 크롤링 다시 돌린다...

 

############################SQL을 따로 쓸까? 하다가 그냥 text로 만들었다.
query_0 = 'WITH cralwed AS'
query_1 = '(SELECT pmid, p_title, n_title, similarity(p_title, n_title), '
query_2 = 'split_part(p_addr, \' \', 2) p_SIG_NM, split_part(p_addr, \' \', 3) p_RD_NM, split_part(n_addr, \' \', 2) n_SIG_NM, split_part(n_addr, \' \', 3) n_RD_NM '
query_3 = 'FROM api_crawled '
query_4 = 'WHERE split_part(p_addr, \' \', 2) = split_part(n_addr, \' \', 2) AND split_part(p_addr, \' \', 3) = split_part(n_addr, \' \', 3) AND similarity(p_title, n_title) > 0.15)'
query_5 = 'SELECT pp.* FROM crawl_permit_place pp LEFT JOIN cralwed cc ON pp.pmid = cc.pmid WHERE cc.pmid is NULL'

# 불러오기
df_gu = pd.read_sql(query_0  + query_1  + query_2  + query_3  + query_4 + query_5, conn)

#근성의 for문은 언제나 승리한다. 될 때 까지 돌리니까.
for i in range(0, 20):
    sample = df[i*1000:(i+1)*1000] #10,000개까지, 1,000개 단위로 불러오겠다
    jsons = []
    columns = ['pmid', 'p_title', 'p_addr', 'n_title', 'n_addr', 'n_category', 'n_x', 'n_y']
    df1000 = pd.DataFrame(columns = columns) 
    for s in range(i*1000,(i+1)*1000):
        title = sample.loc[s]['title']
        gu = sample.loc[s]['sig_nm']
        try:
            encText = urllib.parse.quote(gu + ' ' + title) #gu 이름으로 슬쩍 바꿔보다
        except: pass
        url = "https://openapi.naver.com/v1/search/local.json?query=" + encText # JSON 결과
        # url = "https://openapi.naver.com/v1/search/blog.xml?query=" + encText # XML 결과
        request = urllib.request.Request(url)
        request.add_header("X-Naver-Client-Id",client_id)
        request.add_header("X-Naver-Client-Secret",client_secret)
        response = urllib.request.urlopen(request)
        rescode = response.getcode()
        if(rescode==200):
            response_body = response.read()
            #print(response_body.decode('utf-8'))
            js = json.loads(response_body.decode('utf-8'))
            js_total = len(js['items'])
            new_rows = []
            if js_total > 0:
                for q in range(js_total):
                    new_row = {'pmid': sample.loc[s]['pmid'],
                            'p_title': sample.loc[s]['title'],
                            'p_addr': sample.loc[s]['addr'],
                            'n_title': js['items'][q]['title'],
                            'n_addr': js['items'][q]['roadAddress'],
                            'n_category': js['items'][q]['category'],
                            'n_x': js['items'][q]['mapx'],
                            'n_y': js['items'][q]['mapy']}
                    new_rows.append(new_row)
            df1000 = pd.concat([df1000, pd.DataFrame(new_rows)], ignore_index = True)
        else:
            print("Error Code:" + rescode)
    df1000.to_csv(f'api_crawl_2/api_crawl_step2_{i*1000}.csv') #이럴 줄 알고 api_crawl 폴더에 넘버링을 붙여놨지롱

 

고로, api crawling 의 1단계는 도로명 + 상호명 / 2단계는 구명 + 상호명이다.

하루 2만개... 3일이면 또 다 돌아갈게다...