[스터디] MySQL 사이트 크롤링

강세준·2023년 2월 17일
0

크롤링 사이트

MySQL 8.0 Server System Variables
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html
MySQL 5.7 Server System Variables
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html

라이브러리 설명

셀 단위로 프로그래밍이 가능하여 빠르게 값을 확인하여 프로그래밍 하기 위해
jupyter Notebook이용

requests : 파이썬용 HTTP 라이브러리, 웹 서비스 및 API와 상호작용 할 수 있고 API에서 웹 페이지나 데이터를 다운로드 하는데 사용할 수 있다.

BeautifulSoup4 : HTML 및 XML 파일에서 데이터를 가져오기 위한 파이썬 라이브러리
웹 스크래핑, 데이터 추출등에 사용할 수 있다.

pandas : 대규모 데이터 세트를 효율적으로 저장하고 조작 및 분석하기 위한 라이브러리

re : 파이썬에서 정규 표현식으로 작업할 수 있는 라이브러리

Beautifulsoup4

find

  • 특정 태그 이름이나 속성과 일치하는 요소(element)를 검색하는 메소드
  • find는 첫 번째 요소를 검색하고 find_all은 일치하는 모든 요소의 목록을 반환한다.

select

  • CSS Selector를 이용하여 요소를 검색하는 메소드

get_text() vs .string

  • get_text() : 각 단락의 요소와 그 하위 요소의 모든 텍스트를 추출하여 문자열로 반환하는 메소드

  • .string : 각 단락 요소의 텍스트를 추출하는 메소드

  • get_text() vs .string

    • get_text()는 하위 요소로 구분된 모든 경우에 모든 텍스트를 추출하지만 .string은 현재 요소에 단일 문자열이 하위에 있는 경우에만 추출한다.
    • 요소에 텍스트가 없을 경우 get_text()는 빈 문자열을 반환하고 .string은 None을 반환한다.

패턴 분석

  1. https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html 페이지에서 설명하는 모든 시스템 변수들은 table안에서 설명하고 있다.
  2. 테이블안 시스템 변수안에는 링크가 달려있고 링크는 테이블 밑에 상세 변수 설명에 연결되어 있다.

  1. 변수와 그 특징을 가지는 각 테이블의 HTML을 살펴보면 table 태그 안에 summary로 Properties라는 설명이 함께 들어가 있다.
  2. th 태그안의 value에 세부 옵션의 이름(첫번째 열)이 있고 td 태그안의 value에 해당하는 값(두번째 열)이 존재한다.

  • 시스템 변수의 이름은 a 태그를 통해 링크 형태로 있으머 href안의 링크 주소 안에는 sysvar 문자가 함께 포함되어있다.

코드

MySQL 8.0

#Prerequisite
import requests
from bs4 import BeautifulSoup
import re
import pandas as pd

#MySQL 8.0
reference_version=8.0
mysql8_system_variable_name=[]
mysql8_system_variable_information=[]

#크롤링 데이터 응답 확인
disconnect_count=0
for i in range(3):
    url = 'https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html'
    response = requests.get(url)
    if response.status_code == 200:
        break
    else:
        disconnect_count += 1
if disconnect_count == 3:
    print("Error : 연결에 문제가 있습니다. 스크립트를 종료합니다.")
    exit()

# SystemVariable name 추출 (모든 variable의 Link)    
soup = BeautifulSoup(response.content, 'html.parser')
target_tables = soup.find_all('table', attrs={'summary' : re.compile('Properties')})
for target_table in target_tables:
    variable_name = target_table.find('a', attrs={'href': re.compile('sysvar')}).get_text()
    mysql8_system_variable_name.append(variable_name)
        
# Dictionary List 형태로 데이터 변환
table_count=0
for target_table in target_tables:
    option_list=[]
    value_list=[]
    options = target_table.find_all('th')
    values = target_table.find_all('td')
    for option in options:
        option_list.append(option.get_text())
    for value in values:
        value_list.append(value.get_text())
    for option, value in zip(option_list,value_list):
        variable_dic={'system_variable' : mysql8_system_variable_name[table_count], 'option' : option,
                    'value' : value, 'reference version' : reference_version}
        mysql8_system_variable_information.append(variable_dic)
    table_count += 1

# Pandas DataFrame으로 변환 및 필요 전처리
mysql8_system_variable_df = pd.DataFrame.from_dict(mysql8_system_variable_information)

# 예외처리로 제거할 옵션 Command-Line Format, System Variable
mysql8_system_variable_df.drop(mysql8_system_variable_df[(mysql8_system_variable_df["option"] == 'System Variable') | 
                          (mysql8_system_variable_df["option"] == 'Command-Line Format')].index,
                              inplace = True)


MySQL 5.7

#Prerequisite
import requests
from bs4 import BeautifulSoup
import re
import pandas as pd

#MySQL 5.7
reference_version=5.7
mysql5_system_variable_name=[]
mysql5_system_variable_information=[]

#크롤링 데이터 응답 확인
disconnect_count=0
for i in range(3):
    url = 'https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html'
    response = requests.get(url)
    if response.status_code == 200:
        break
    else:
        disconnect_count += 1
if disconnect_count == 3:
    print("Error : 연결에 문제가 있습니다. 스크립트를 종료합니다.")
    exit()

# SystemVariable name 추출 (모든 variable의 Link)
soup = BeautifulSoup(response.content, 'html.parser')
target_tables = soup.find_all('table', attrs={'summary' : re.compile('Properties')})
for target_table in target_tables:
    variable_name = target_table.find('a', attrs={'href': re.compile('sysvar')}).get_text()
    mysql5_system_variable_name.append(variable_name)
        
# Dictionary List 형태로 데이터 변환
table_count=0
for target_table in target_tables:
    option_list=[]
    value_list=[]
    options = target_table.find_all('th')
    values = target_table.find_all('td')
    for option in options:
        option_list.append(option.get_text())
    for value in values:
        value_list.append(value.get_text())
    for option, value in zip(option_list,value_list):
        variable_dic={'system_variable' : mysql5_system_variable_name[table_count], 'option' : option,
                    'value' : value, 'reference version' : reference_version}
        mysql5_system_variable_information.append(variable_dic)
    table_count += 1

# Pandas DataFrame으로 변환 및 필요 전처리
mysql5_system_variable_df = pd.DataFrame.from_dict(mysql5_system_variable_information)

# 예외처리로 제거할 옵션 Command-Line Format, System Variable
mysql5_system_variable_df.drop(mysql5_system_variable_df[(mysql5_system_variable_df["option"] == 'System Variable') | 
                          (mysql5_system_variable_df["option"] == 'Command-Line Format')].index,
                              inplace = True)


데이터 프레임 병합 및 csv 추출

# 데이터 프레임 병합 및 csv 추출
mysql_system_variable_df = pd.concat([mysql8_system_variable_df, mysql5_system_variable_df])
mysql_system_variable_df.to_csv('Mysql_system_variable_info.csv',index=False)

결과

데이터프레임


Csv파일

수정사항

하나의 python 파일로 만들기 위해 중복 작업을 메소드로 따로 빼내어 처리가 필요

#Prerequisite
import requests
from bs4 import BeautifulSoup
import re
import pandas as pd


# 데이터베이스 시스템 변수 추출 및 데이터 프레임 변환 함수
def mysql_crawl_data(db_version):
    #MySQL version
    reference_version = db_version
    mysql_system_variable_name = []
    mysql_system_variable_information = []

    #크롤링 데이터 응답 확인
    disconnect_count = 0
    for i in range(3):
        url = 'https://dev.mysql.com/doc/refman/'+ str(db_version) + '/en/server-system-variables.html'
        response = requests.get(url)
        if response.status_code == 200:
            break
        else:
            disconnect_count += 1
    if disconnect_count == 3:
        print("Error : 연결에 문제가 있습니다. 스크립트를 종료합니다.")
        exit()

    # SystemVariable name 추출 (모든 variable의 Link)    
    soup = BeautifulSoup(response.content, 'html.parser')
    target_tables = soup.find_all('table', attrs={'summary' : re.compile('Properties')})
    for target_table in target_tables:
        variable_name = target_table.find('a', attrs={'href': re.compile('sysvar')}).get_text()
        mysql_system_variable_name.append(variable_name)

    # Dictionary List 형태로 데이터 변환
    table_count = 0
    for target_table in target_tables:
        option_list = []
        value_list = []
        options = target_table.find_all('th')
        values = target_table.find_all('td')
        for option in options:
            option_list.append(option.get_text())
        for value in values:
            value_list.append(value.get_text())
        for option, value in zip(option_list,value_list):
            variable_dic = {'system_variable' : mysql_system_variable_name[table_count], 'option' : option,
                        'value' : value, 'reference version' : reference_version}
            mysql_system_variable_information.append(variable_dic)
        table_count += 1

    # Pandas DataFrame으로 변환 및 필요 전처리
    mysql_system_variable_df = pd.DataFrame.from_dict(mysql_system_variable_information)

    # 예외처리로 제거할 옵션 Command-Line Format, System Variable
    mysql_system_variable_df.drop(mysql_system_variable_df[(mysql_system_variable_df["option"] == 'System Variable') | 
                              (mysql_system_variable_df["option"] == 'Command-Line Format')].index,
                                  inplace = True)

    return mysql_system_variable_df
    

    
# 데이터 프레임 추출
mysql5_sysvar_df = mysql_crawl_data(5.7)
mysql8_sysvar_df = mysql_crawl_data(8.0)


# 데이터 프레임 병합 및 csv 추출
mysql_system_variable_df = pd.concat([mysql8_sysvar_df, mysql5_sysvar_df])
mysql_system_variable_df.to_csv('Mysql_system_variable_info.csv',index=False)

참고자료

https://www.crummy.com/software/BeautifulSoup/bs4/doc/#find-all

profile
강세준
데이터를 탐구하는 개발자
이전 포스트

[과제] 인프라 엔지니어의 교과서 11장

다음 포스트

[스터디] Python Mutable vs Immutable

0개의 댓글