MYSQL : Python 웹스크레핑 데이터 MySQL 서버 저장하기

sqlalchemy, pymysql 라이브러리 설치

sqlalchemy는 python에 제공되는 ORM(Object-relational Mapping)이라고도 하는데, MySQL 서버를 URL (인터넷 주소 치듯이)로 접속할 수 있도록 만들어주는 것이라고 생각하면 된다. URL 객체가 만들어지면 이 객체와 connection 하고 SQL 문을 주고받고 해야 하는데 이것을 pymysql 라이브러리가 도와준다. 설치는 아나콘다 cmd를 켜고 아래와 같은 명령어만 입력하면 된다. ( 추가적인 설명은 "01 파이썬 라이브러리 설치(Windows)"를 참조 )

# sqlalchemy 설치하기 
pip install sqlalchemy
# pymysql 설치하기 
pip install pymysql

python_sql 연결 

DB 가동

python 과 MySQL 서버를 연결하려면 MySQL 서버가 켜져 있어야 한다. 먼저 MySQL이 설치되어 있어야 하는데,  03 MYSQL 설치(Windows)를 참조해서 MySQL설치를 끝내야 한다. 그리고 윈도우-> 검색에서 "서비스"를 검색하면,

그림1: 서비스 설정1

위와 같은 화면이 나오는데 MySQL 서비스를 찾아서 우클릭해서 "시작"을 누르면 된다. 매번 켜주는 것이 귀찮다면, 우클릭 후 "속성"으로 들어가서 시작 유형을 "자동"으로 해두면 컴퓨터가 켜질 때마다 자동 실행이 된다.

그림2: 서비스설정 2

DB 서버정보 url 화

sqlalchemy는 서버에 접속하기 쉽게 MySQL DB를 URL 객체화 해준다. 그 기능이 create_engine 함수에 있는데, create_engine 안에 db에 접속할 수 있는 url을 입력해 주면 db에 접속이 가능한 상태가 된다.

import sqlalchemy as db

user = 'MySQL에 설정한 서버명'
password = '서버 비밀번호'
host = '127.0.0.1' # 내 컴퓨터의 localhost 접속이므로 기본 ip인 127.0.0.1 입력
db_port = '포트번호' # 보통 3306 
db_name = '접속할 스키마 이름'
db_connection_str = "mysql+mysqldb://" + user + ":" + password + "@" + host + ":" + db_port +"/"+ db_name
db_connection = db.create_engine(db_connection_str,encoding='utf-8')

위에서 입력할 정보를 잘 모르겠으면 아래 그림을 참고해서 해당 워크벤치에서 해당 정보를 찾고 입력하면 된다. 

그림3: 서버명, 포트번호, 스키마 이름 확인

DB 접속: connect

이제 위의 db_connection 이라는 URL 객체에 pymysql 라이브러리에서 제공하는 connect라는 함수를 통해서 연결만 하면 된다. 

import pymysql
pymysql.install_as_MySQLdb()

conn = db_connection.connect()

이때 중요한 것이 pymysql.install_as_MySQLdb() 를 입력해 주는 것이다. 해당 코드 없이 pymysql을 실행하면 보통은  MySQLdb를 읽는 모듈이 없다고 에러가 발생한다. 그래서 컴퓨터가 pymysql 이 MySQLdb 인 것처럼 인식하게 해 줘야 하는데 그것이 pymysql.install_as_MySQLdb() 코드이다.

데이터를 DataBase에 전송

데이터를 DataBase에 보낼 때는 크게 두 가지 방법이 있다. 하나는 list나 array 한 줄씩 DataBase에 INSERT 쳐 주는 방법, 다른 하나는 데이터 테이블을 (데이터 프레임) 통째로 DataBase에 보내는 방법이 있다. ( 파이썬 데이터 형식에 대한 자세한 내용은 01 파이썬 데이터 형식(&Pandas 데이터) 를 참고하자.) 우리는 두 번째 방법을 이용해서 우리가 웹스크레핑한 데이터를 DataBase에 보내 보겠다.

02 웹스크레핑: 네이버 파이낸스 데이터수집 에서 아래와 같은 코드를 이용해서 웹스크레핑한 결괏값을 확인했다. 

import requests
from bs4 import BeautifulSoup
import pandas as pd
headers = {"User-Agent":"User-Agent 사이트에서 내가 검색한 값"}

# soup 인스턴스에 page 1 의 정보를 저장
url="https://finance.naver.com/sise/sise_market_sum.nhn?&page=1"
site_text = requests.get(url, headers=headers)
soup = BeautifulSoup(site_text.text, "lxml")

# soup 인스턴스 이용해 최대페이지 수 구하는 부분: 최대 페이지를 maxpage 인스턴스에 저장
pagelist = soup.find("table",attrs={"class":"Nnavi"}).find("td", attrs={"class":"pgRR"}).a["href"]
maxpage = int(str(pagelist[pagelist.find("=")+1:]))

for pages in range(1,maxpage):     # 1 페이지부터 maxpage까지 사이트 검토
    url="https://finance.naver.com/sise/sise_market_sum.nhn?&page={}".format(str(pages))
    site_text = requests.get(url, headers=headers)
    soup = BeautifulSoup(site_text.text, "lxml")
    data_rows = soup.find("table", attrs={"class":"type_2"}).find("tbody").find_all("tr")
    for data_row in data_rows:
        data = []                               # data list 형식을 선언
        data_columns = data_row.find_all("td")  #td tag를 갖고 있는 데이터를 data_columns로 선언
        if len(data_columns) == 1:              #data_columns 값 공란 즉, data길이가 1인 것 제외 
            continue
        for data_column in data_columns:        #data_columns를 data_column 변수에 입력
            data.append(data_column.get_text().strip())  #위에서 선언한 data list에 column 값 추가
        print(data)                             #결과값 print

위 코드는 data를 단순 print 해 줄 뿐이다. totdata라는 큰 list를 만들어서 그 안에 data라는 list를 계속 append 한다면 totdata = [ [data], [data], ....] 이런 list로 이루어진 list 덩어리를 만들 수 있다. 이렇게 만들어진 totdata에 칼럼명을 지정해서 Dataframe에 담으면 DataBase에 바로 보낼 수 있는 Data 덩어리가 된다. 추가적으로 여기에 작업일이 있으면 좋을 것 같아서 0번째, 1번째 뒤에 2번째 칼럼 위치에 'DATA'라는 칼럼을 추가했다. 그 코드는 아래와 같다.

import requests
from bs4 import BeautifulSoup
import pandas as pd
headers = {"User-Agent":"User-Agent 사이트에서 내가 검색한 값"}

# soup 인스턴스에 page 1 의 정보를 저장
url="https://finance.naver.com/sise/sise_market_sum.nhn?&page=1"
site_text = requests.get(url, headers=headers)
soup = BeautifulSoup(site_text.text, "lxml")

# soup 인스턴스 이용해 최대페이지 수 구하는 부분: 최대 페이지를 maxpage 인스턴스에 저장
pagelist = soup.find("table",attrs={"class":"Nnavi"}).find("td", attrs={"class":"pgRR"}).a["href"]
maxpage = int(str(pagelist[pagelist.find("=")+1:]))
# data list를 담을 전체 list 추가
totdata = []

for pages in range(1,maxpage):     # 1 페이지부터 maxpage까지 사이트 검토
    url="https://finance.naver.com/sise/sise_market_sum.nhn?&page={}".format(str(pages))
    site_text = requests.get(url, headers=headers)
    soup = BeautifulSoup(site_text.text, "lxml")
    data_rows = soup.find("table", attrs={"class":"type_2"}).find("tbody").find_all("tr")
    for data_row in data_rows:
        data = []                               # data list 형식을 선언
        data_columns = data_row.find_all("td")  #td tag를 갖고 있는 데이터를 data_columns로 선언
        if len(data_columns) == 1:              #data_columns 값 공란 즉, data길이가 1인 것 제외 
            continue
        for data_column in data_columns:        #data_columns를 data_column 변수에 입력
            data.append(data_column.get_text().strip())  #위에서 선언한 data list에 column 값 추가
        #totdata list 에 data 란 list 추가
        totdata.append(data)
        
 # totdata 에 컬럼명을 지정하고 Dataframe 형식에 담기
df = pd.DataFrame(totdata, columns=['NO', '종목명','현재가','전일비','등락률','액면가','시가총액','상장주식수','외국인비율','거래량','PER','ROE','기타'])
# 작업일자를 today에 저장
today = datetime.today().strftime("%Y-%m-%d")
# 0번째 컬럼: 'NO', 1번째 컬럼:'종목명' 뒤에 today 라는 작업일 추가
df.insert(2,'DATE',today)

Python 웹스크레핑 데이터 MySQL 서버 저장하기

위에서 소개한 모든 내용을 조합해서 코드를 완성하면 아래와 같다. 

import requests
from bs4 import BeautifulSoup
import pandas as pd
import sqlalchemy as db
import pymysql
from datetime import datetime
pymysql.install_as_MySQLdb() # ModuleNotFo  undError: No module named 'MySQLdb' 코드실행 에러문제 해결

headers = {"User-Agent":"User-Agent 사이트에서 내가 검색한 값"}

# soup 인스턴스에 page 1 의 정보를 저장
url="https://finance.naver.com/sise/sise_market_sum.nhn?&page=1"
site_text = requests.get(url, headers=headers)
soup = BeautifulSoup(site_text.text, "lxml")

# soup 인스턴스 이용해 최대페이지 수 구하는 부분: 최대 페이지를 maxpage 인스턴스에 저장
pagelist = soup.find("table",attrs={"class":"Nnavi"}).find("td", attrs={"class":"pgRR"}).a["href"]
maxpage = int(str(pagelist[pagelist.find("=")+1:]))
# data list를 담을 전체 list 추가
totdata = []

for pages in range(1,maxpage):     # 1 페이지부터 maxpage까지 사이트 검토
    url="https://finance.naver.com/sise/sise_market_sum.nhn?&page={}".format(str(pages))
    site_text = requests.get(url, headers=headers)
    soup = BeautifulSoup(site_text.text, "lxml")
    data_rows = soup.find("table", attrs={"class":"type_2"}).find("tbody").find_all("tr")
    for data_row in data_rows:
        data = []                               # data list 형식을 선언
        data_columns = data_row.find_all("td")  #td tag를 갖고 있는 데이터를 data_columns로 선언
        if len(data_columns) == 1:              #data_columns 값 공란 즉, data길이가 1인 것 제외 
            continue
        for data_column in data_columns:        #data_columns를 data_column 변수에 입력
            data.append(data_column.get_text().strip())  #위에서 선언한 data list에 column 값 추가
        #totdata list 에 data 란 list 추가
        totdata.append(data)
        
# totdata 에 컬럼명을 지정하고 Dataframe 형식에 담기
df = pd.DataFrame(totdata, columns=['NO', '종목명','현재가',
                                    '전일비','등락률','액면가','시가총액',
                                    '상장주식수','외국인비율','거래량','PER',
                                    'ROE','기타'])
# 작업일자를 today에 저장
today = datetime.today().strftime("%Y-%m-%d")
# 0번째 컬럼: 'NO', 1번째 컬럼:'종목명' 뒤에 today 라는 작업일 추가
df.insert(2,'DATE',today)

# DB 서버정보 url 화
user = 'MySQL에 설정한 서버명'
passwd = '서버 비밀번호'
host = '127.0.0.1'
db_port = '포트번호'
db_name = '접속할 스키마 이름'
db_connection_str = "mysql+mysqldb://" + user + ":" + passwd + "@" + host + ":" + db_port +"/"+ db_name
db_connection = db.create_engine(db_connection_str,encoding='utf-8')

# DB 접속 connect
conn = db_connection.connect()

# 데이터를 sql로 보내기
df.to_sql(name='위 db_name 하위 level에 추가할 테이블 명', con=conn, if_exists='append', index=False)
# if_exists 'fail': 테이블 명이 이미 존재하면 실행 안 함, 
#           'append': 테이블 있으면 데이터 추가 
#           'replace': 테이블 있으면 교체

아래 코드에 header 정보, 서버명, 비밀번호, 포트번호, 접속할 스키마 이름 정도만 잘 넣으면 아래와 같이 DB가 생성되는 것을 SQL 워크벤치에서 확인할 수 있다.

그림4: 최종 DB 생성

이때 위 그림 4에서 처럼 워크벤치가 켜져 있는 상태에서 위 코드를 실행했다면, 리프레쉬 버튼을 한 번 눌러줘야 결과를 확인할 수 있다.