SQL 학습과제 - 2

문제 설명

지난번 학습과제 1에서 사용했던 데이터베이스를 이용하여 강의에서도 했고 EDA 과제에서도 했던 유가 데이터를 저장하고 분석 하는 문제였다.

문제 1

테이블 생성 문제였다.
-생략-

문제 2

참고할 브랜드명을 GAS_BRAND 테이블에 넣는 문제인데 SQL파일을 만들어 처리하였습니다. 이것도 지난번에도 했던 문제여서 자세한 설명은 생략

문제 3

앞으로는 이런식으로 해야한다고 가르처주는 문제였던거 같다. 유가 데이터를 크롤링하였을때 생기는 문제들을 먼져 함수로 만들어 아래 크롤링 코드를 보다 깔끔하게 만들어줄수 있었다.

문제 4

데이터를 크롤링하고 이것을 바로바로 데이터베이스에 넣는 문제인데 위에서 각 항목에 대한 테스트코드를 만들어 테스트한 뒤 한번에 돌렸더니 큰 오류없이 해결할수있었다.

for gu in gu_names:
    element = gu_list_raw = driver.find_element(By.ID, "SIGUNGU_NM0")
    element.send_keys(gu)
    search_result = int(driver.find_element_by_css_selector("#totCnt").text)
    
    for i in range(1, search_result +1):
        driver.find_element_by_css_selector(f'#body1 > tr:nth-child({i}) > td.rlist > a').click()
        html = driver.page_source
        soup = BeautifulSoup(html,"html.parser")
        
        # 초기화
        datas = []
        # id
        datas.append(n)
        # 브랜드명
        datas.append(brand_search(soup.select_one("#poll_div_nm").text))  
        # 주유소명
        datas.append(soup.select_one("#os_nm").text)
        # 서울
        datas.append(city)
        # 구
        datas.append(split_adr(soup.select_one("#rd_addr").text))
        # 주소
        datas.append(soup.select_one("#rd_addr").text)

        # 휘발유
        datas.append(remove_commas(soup.select_one("#b027_p").text))
        # 경유
        if soup.select_one("#d047_p").text == '':
            datas.append(0)
        else:
            datas.append(remove_commas(soup.select_one("#d047_p").text))
        
        # 셀프여부
        if soup.find('img', {'alt': '셀프주유소', 'class': 'bul'}):
            datas.append(1)
        else:
            datas.append(0)
        # 세차장
        if "off" in soup.select_one("#cwsh_yn").get('src'):
            datas.append(0)
        else:
            datas.append(1)
        # 충전소
        if "off" in soup.select_one("#lpg_yn").get('src'):
            datas.append(0)
        else:
            datas.append(1)
        # 경정비
        if "off" in soup.select_one("#maint_yn").get('src'):
            datas.append(0)
        else:
            datas.append(1)
        # 편의점
        if "off" in soup.select_one("#cvs_yn").get('src'):
            datas.append(0)
        else:
            datas.append(1)
        # 24시간
        if "off" in soup.select_one("#sel24_yn").get('src'):
            datas.append(0)
        else:
            datas.append(1)

        # 위도 경도
        tmp = get_lat_lng(soup.select_one("#rd_addr").text)
        lat = tmp[0]
        lng = tmp[1]
        datas.append(lat)
        datas.append(lng)
        cursor.execute(sql, tuple(datas))
        print(tuple(datas))
        conn.commit() 
        time.sleep(0.5)
        n += 1

경유와 휘발유를 동시에 취급하는 매장이 있는방면 경유만 취급하는 주유소가 있어 데이터베이스에 업로드시 자리수가 맞지않는 오류가 생겼는데 if문으로 0을 넣어 자리수를 맞춰주는 식으로 하여 해결하였다. 또 셀프, 편의점, 24시 등 편의기능들의 테이블 값이 bool인데 0,1 혹은 T,F로 저장해야한다는 사실을 하나 배웟다.

문제 5

저장한 데이터를 다시 파이썬으로 불러와 csv파일로 만드는 문제인데 까다로운 점이라면 brand값이 숫자로 되어있어 sql에서 처리할수도있겠지만 파이썬 replce기능으로 5번 돌려 완성하는 방법이 더 편해 보여서 해당 방법으로 해결하였다.

문제 6 😒

미왕 빌딩이라는 곳과 거리를 계산해서 가까운 매장을 구하는 문제인데 보자마자 잠깐 뇌정지가 와서 한숨자고 일어나서 풀엇다. ㅎㅎ 구글링을 통해 찾아보니 해결방법이 여러가지 존재하여 저는 이 블로그를 참고하여 테스트 코드를 만들어 진행하였습니다.

구글맵 라이브러리로 미왕빌딩의 좌표값을 구하고 위도 경도 데이터를 거리를 구하는 sql코드에 대입하여 데이터를 구할 수 있었습니다.

SELECT id, brand, name, address, ROUND((6371*acos(cos(radians(37.4955525))*cos(radians(lat))*cos(radians(lng) -radians(127.0292924))+sin(radians(37.4955525))*sin(radians(lat)))), 2) AS distance
FROM GAS_STATION HAVING distance < 1
ORDER BY distance;

문제 7

6번의 응용문제인데 크게 달라진게 없어 생략!

문제 8

강의에서 했던 것들의 응용이라고 생각되는데 primary key를 사용하는 이유는 알겠지만 foreign key를 쓰는 이유를 알게된 문제인거 같다. group by로 구와 브렌드를 묶어 휘발유 가격을 정렬하는 문제인데 brand 값이 숫자로만 출력되는 문제가 있어서 지난주 해설강의에서 배웟던 것을 응용하여 풀수있었습니다.

전체코드

전체코드