3주차 Note: 데이터 수집

🗓️ 3주차 : 4월 20일 - 4월 24일

---

멀티캠퍼스 부트캠프 3주차 요약✍

[ 4/20 ] 데이터 수집 (AI 활용): python과 db 연결(+모듈 생성), openAPI data((json/xml)를 DataFrame으로 변환
[ 4/21 ] 데이터 수집 (AI 활용): HTML, 크롤링
[ 4/22 ] 데이터 수집 (AI 활용): Selenium
[ 4/23 ] 데이터 수집 (AI 활용): SQL과 Python의 문법 비교
[ 4/24 ] Github 특강: 본문에서는 생략

---

4월 20일👩🏻‍💻

 

오늘의 소감: 🤯🤯🤯🤯🤯🤯오류의 늪 시작...🥲🥲

초심자의 입장에서 주석은 정말 중요하지만 긴 주석과 긴 코드를 보고 있으니 머리가 더욱 어지럽다.

주석과 코드를 같이 쓸 때의 가독성을 확보할 수 있는 방법을 강구해보아야겠다.

---

💻 python과 db 연결

dotenv

 

python과 database를 연결할 때 주소, 포트 등 서버의 정보를 입력하게 되는데,

이를 그냥 온라인에 업로드하면 보안상 취약점이 된다.
그러한 부분을 숨기는 기능이 dotenv이다.

!pip install python-dotenv

# 라이브러리 load
from dotenv import load_dotenv
import pymysql
import os
import pandas as pd

# os → python 환경에서 사용하는 변수들에 접근하기 위해 사용
# load_dotenv → .env 파일의 내용을 환경 변수에 임시 등록

 

dotenv를 사용하기 위해 .env 파일을 생성하고, python에서 load_dotenv 실행

load_dotenv()		# True

# 등록된 환경변수에 접근
os.getenv('port')	# 포트번호가 '문자열'로 출력: 숫자 변환 필요

나의 경우 OperationalError: (2003, "Can't connect to MySQL server on '`서버코드`' ([WinError 10061] 대상 컴퓨터에서 연결을 거부했으므로 연결하지 못했습니다)") 라는 에러가 출력되었는데, 서비스 → MySQL80이 실행되고 있는지 확인해야한다.
백그라운드에서 꺼져있었기에 발생한 문제이고, 실행하고 난 뒤 정상적으로 작동하였다.

---

Cursor 생성

cursor = _db.cursor(pymysql.cursors.DictCursor)

# DictCursor는 select의 결과값을 Dict 형태로 받아온다.

---

Table 생성

# DDL: 테이블 생성
# 기존의 테이블이 존재한다면 테이블 생성 X
# 없으면 생성

create_table = """
    CREATE TABLE IF NOT EXISTS
    `user_info`
    (
        `id` VARCHAR(32) PRIMARY KEY,
        `password` VARCHAR(32) NOT NULL,
        `name` VARCHAR(32),
        `age` INT
    )

---

질의

# cursor를 통해서 sql 쿼리문을 실행할 때는 execute() 함수를 이용
cursor.execute(create_table)		# 0

---

데이터 대입

signup_query = """
    INSERT INTO
    `user_info`
    VALUES (
        "test", "1234", "kim", 30
    )
"""
cursor.execute(signup_query)	# 1

---

fetchall()

# 결과값을 불러오는 함수

cursor.fetchall()
# [{'id': 'test', 'password': '1234', 'name': 'kim', 'age': 30}]

 

그런데 아직까지는 MySQL Workbench에서는 데이터가 등록되지 않은 상태이다.
이는 아직 확인 절차에 있다는 것을 의미한다.

---

commit

# _db에서 확정 작업(commit()), 결과를 DB와 동기화
_db.commit()

 

이후 Workbench를 확인해보면 데이터가 등록됨을 볼 수 있다.

---

여러 데이터 입력

# 들어오는 데이터가 바뀔 때마다 query문을 작성하는 건 불필요

signup_query = """
    INSERT INTO `user_info`
    VALUES (%s, %s, %s, %s)
"""

# %s 는 들어오는 데이터들의 위치를 지정
input_id = input('아이디를 입력하시오')
input_pass = input('비밀번호를 입력하시오')
input_name = input('이름을 입력하시오')
input_age = input('나이를 입력하시오')

# execute(query, [datas]) 함수에 리스트의 각각의 원소들이 %s 위치에 순서대로 대입
cursor.execute(signup_query, [input_id, input_pass, input_name, input_age])		# 1

cursor.execute(user_list_query)		# 2

cursor.fetchall()
# [{'id': 'test', 'password': '1234', 'name': 'kim', 'age': 30},
# {'id': 'test2', 'password': '1111', 'name': 'lee', 'age': 20}]

---

💻 DB 연동 class 선언, 생성

로직

• 생성자 함수
  • 서버의 정보를 입력한다. (매개변수 필요)
  • class가 생성이 될 때마다 다른 DB server에 정보를 담을 수 있다.
  • 2개의 객체를 생성하여 다른 DB server와 연동
• 함수 2개 생성
  • 1. query문 실행 함수 (매개변수: query문(필수), *datas)
    • 서버와의 연결
    • cursor 생성
    • CUD (insert, update, delete)
      • query문 작성
      • execute() 함수를 이용하여 cursor에 질의를 보낸다.
        • query문의 시작이 select라면 fetchall() 함수를 사용하여 데이터를 return
  • 2. DataBase에 변화를 주는 함수
    • DB server에서 commit()을 이용해서 데이터 확정
    • DB server와의 연결을 종료(close())

---

코드

• class 선언

class MyDB:
    def __init__(self, host, port, user, password, db):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.db = db

    # 데이터베이스에 변화를 주는 함수 
    def commit(self):
        try:
            self.db_server.commit()
            self.db_server.close()
            del self.db_server
        except:
            print( "데이터베이스 서버와의 연결이 되어있지 않습니다. sql_query() 함수를 호출하여 서버와의 연결을 해주세요" )
    
    def sql_query(self, query, *datas):
        try:
            self.db_server
            print('접속된 서버가 존재함')
        except:
            self.db_server = pymysql.connect(
                host = self.host, 
                port = self.port, 
                user = self.user, 
                password = self.password, 
                db = self.db
            )
        # cursor 생성 
        cursor = self.db_server.cursor(pymysql.cursors.DictCursor)

        try:
            cursor.execute(query, datas)
            if query.lstrip().lower().startswith('select'):
                result = cursor.fetchall()
            else:
                result = "Query OK!"
            return result
        except Exception as e:
            print('query문 execute중 에러')
            print(e)

---

• class 생성

db1= MyDB(
    host = os.getenv('host'),
    port = int(os.getenv('port')),
    user = os.getenv('user'),
    password = os.getenv('pwd'),
    db = os.getenv('db_name')
)

db1.sql_query('a')
db1.commit()

생성자 함수에 콤마 넣지 말자, port에 int 제대로 쓰자

다 했는데 안 되면 재시작해라 그게 최고다

---

• data insert

insert_query = """
    INSERT INTO `user_info`
    VALUES (%s, %s, %s, %s)
"""
select_query = """
    SELECT * FROM `user_info`
"""

data_list = ['test3', '0000', 'lee', 40]
db1.sql_query(insert_query, *data_list)

# 접속된 서버가 존재함
# 'Query OK!'

---

• data 확인

db1.sql_query(select_query)

# [{'id': 'test', 'password': '1234', 'name': 'kim', 'age': 30},
# {'id': 'test3', 'password': '0000', 'name': 'lee', 'age': 40}]

---

• data delete

delete_query = """
    DELETE FROM `user_info`
"""

data_list = ['test']

db1.sql_query(delete_query)

# 접속된 서버가 존재함
# 'Query OK!'

 

??: 다 했는데 안 되면 재시작해라 그게 최고다
아니? 새로고침 안 좋은데? 초기화 안 좋은데?ㅠㅠ 초기화되면 로딩했던 거 다 사라지는데??

한참동안 고쳐지지 않는 에러가 떠서 강사님 코드도 복붙해보고... 프로그램을 새로 키기를 몇번이나 했고 라이브러리도 죄다 새로 import 해놓았는데

정말 바보도 아니고 라이브러리는 임포트했으면서  load_dotenv()  를 실행하지 않고 계속 작업한 것이다...
그리고 콤마!!!! 자꾸 써야할 곳에 안 쓰고 안 써야할 곳에 쓴다.
언젠가 또 실수할 것이 뻔하므로 기록...🥹

---

• 모듈화

py 파일에 import한 라이브러리들과 아까 만들어뒀던 클래스를 붙여넣어 모듈화 진행

# 파일명 db.py

import db
db3 = db.MyDB()
db3.sql_query(select_query)

# [{'id': 'test', 'password': '1234', 'name': 'kim', 'age': 30}]

잘 불러와지는 것을 확인할 수 있다.

---

💻 Open API

개인적으로 학교에서 알려준 바가 없어서 가장 궁금했던 부분이었던 Open API 활용...
Key를 발행하는 것까지는 해본 적이 있는데 그 이후에 이 Key를 가지고 뭐 어떡하라는 건지 몰라서 마구 찾아보다 그냥 돌아선 적이 있다🥹

data download

 

https://www.data.go.kr/data/15111004/openapi.do#/API%20%EB%AA%A9%EB%A1%9D/corp%2FgetEsgStatus데이터를 받아와서 python에서 DataFrame으로 변형시켰다.

# 라이브러리 설치/로드
!pip install requests
import requests

# 인증키 변수에 저장

service_key = '내가 발급받은 API key'
page_no = 1
num_of_rows = 100

url = 'https://apis.data.go.kr/B553069/esg/corp/getEsgStatus'

# 파라미터 지정

params = {
    'serviceKey': service_key,
    'pageNo' : page_no,
    'numOfRows': num_of_rows
}

# server에게 요청을 보낸다
# requests 안에 get() 함수를 이용. 대부분 get을 사용
    # get()
        # 첫번째 인자에는 주소
        # params 매개변수: 파라미터 값들을 dict 형태로 대입

res = requests.get(url, params=params)

res # <Response [200]>
type(res.content) # bytes

---

bytes를 dictionary type으로 변환

import json
res_data = json.loads(res.content)
type(res_data) # dict

# data를 예쁘게 출력해서 보고 싶을 때
from pprint import pprint
pprint(res_data)

---

dictionary를 DataFrame으로 변환

 

배운지 얼마나 됐다고 아는 내용 나오니 이리 기쁠 수가 없었다

df = pd.DataFrame(res_data['response']['body']['items'])

---

xml, json 파일을 DataFrame으로 변환

 

또 다른 데이터셋을 download
https://www.data.go.kr/data/15126832/openapi.do#/API%20%EB%AA%A9%EB%A1%9D/pscd
해당 데이터를 다운로드했다.

---

env 파일을 활용하여 데이터 load

 

env 파일에 다음과 같이 작성하여 저장

service_key = '내가 발급받은 API key'

---

xml 데이터 변환

url = 'https://apis.data.go.kr/B010003/kamcoRlctRlst/pscd'

params = {
    'serviceKey': os.getenv('service_key'),
    'pageNo' : 1,
    'numOfRows': 100,
    'resultType': 'xml'
}

res = requests.get(url, params=params)
res # <Response [200]>

# -----------------------------------------------------

!pip install xmltodict
# xml을 dict 형태로 변환하는 라이브러리
from xmltodict import parse

res_data_xml = parse(res.content)
pprint(res_data_xml['response']['body']['items']['item'])

df3 = pd.DataFrame(res_data_xml['response']['body']['items']['item'])

---

json 데이터 변환

params2 = {
    'serviceKey': os.getenv('service_key'),
    'pageNo' : 1,
    'numOfRows': 100,
    'resultType': 'json'
}

res2 = requests.get(url, params=params2)
# url은 xml 데이터 변환 코드에 작성되어 있음
res # <Response [200]>
pprint(res_data_json['body']['items']['item'])

 

df2 = pd.DataFrame(res_data_json['body']['items']['item'])

이렇게 하여 open API xml, json 형태의 데이터를 불러와 DataFrame 형식으로 변환할 수 있다.

코드가 길고 불러올 라이브러리들도 많으니 코드의 가독성이 이래서 필요하구나 싶다.

앞으로 더 복잡해지겠지

이제 더 점점 어려워질테니 마음 단단히 먹고 가자🥹

---

4월 21일👩🏻‍💻

오늘의 소감: 아래 이미지로 대체

듀얼 모니터 없이 노트북만으로 HTML, 줌, VS code 병행하려니 죽을 맛이다...

결국 어제 모니터를 구매했다...ㅎㅎ

왜 느는 게 실력이 아니라 장비욕심이냐

---

💻 HTML

각종 태그 모음

---

💻 크롤링

find()

html 문서 안에서 특정 태그의 첫번째 정보를 출력
 find(속성명=속성값) 
결과값의 type은 TAG

# 라이브러리 설치/로드
!pip install bs4 selenium
import requests
from bs4 import BeautifulSoup as bs

res = requests.get("https://www.naver.com")
res.content     # type: bytes
res.text        # type: str
html_text = res.text

# 문자열에서 특정 문자의 위치를 찾는 함수: find()
html_text.find("네이버") # 378

---

parsor

문자 형태로 이루어진 html 문서를 BeautifulSoup class를 이용해서 객체 변환
문자 데이터에서는 특정 영역의 content를 추출하기 어렵기에, BeautifulSoup에 내장된 함수를 이용
데이터를 추출하기 위해 class 생성 시 데이터를 대입

soup = bs(html_text, 'html.parser')
type(soup) # bs4.BeautifulSoup

---

find_all()

html 문서 안에서 특정 태그의 모든 정보를 출력
결과값의 type은 ResultSet (TAG의 list형태)

# versis find

soup.find('a')
# <a href="#topAsideButton"><span>상단영역 바로가기</span></a>


soup.find_all('a')
# [<a href="#topAsideButton"><span>상단영역 바로가기</span></a>,
#  <a href="#shortcutArea"><span>서비스 메뉴 바로가기</span></a>,
#  <a href="#newsstand"><span>새소식 블록 바로가기</span></a>,
#  <a href="#shopping"><span>쇼핑 블록 바로가기</span></a>,
#  <a href="#feed"><span>관심사 블록 바로가기</span></a>,
#  <a href="#account"><span>MY 영역 바로가기</span></a>,
#  <a href="#widgetboard"><span>위젯 보드 바로가기</span></a>,
#  <a href="#viewSetting"><span>보기 설정 바로가기</span></a>]

 

 

+) find, find_all 함수는 TAG type에서는 사용 가능하지만, ResultSet type에서는 사용 불가능하다.

---

네이버 증권 사이트 크롤링

 

미리 말하자면, 아래 코드는 `pd.read_html()` 한 줄로 대체되는 항목이다.

import requests
from bs4 import BeautifulSoup as bs
import pandas as pd

result = requests.get('https://finance.naver.com')
soup = bs(result.text, 'html.parser')

div_data = soup.find('div', attrs = {'class': 'section_sise_top'})

tables = div_data.find_all('table', attrs = {'class': 'tbl_home'})

# ------------------------------------------------------------------

# 실행 때마다 변수명이 다른 df 생성

vari_num = 1

for table_data in tables:
    # thead 추출: columns
    head_data = table_data.find('thead')
    cols = [th.string for th in th_list]

    # tbody 추출: values
    body_data = table_data.find('tbody')
    tr_list = body_data.find_all('tr')

    values = []

    for tr in tr_list:
        row_list = tr.find_all(['th', 'td'])
        value_data = [data.get_text().strip() for data in row_list]
        values.append(value_data)

    globals()[f"df{vari_num}"] = pd.DataFrame(values, columns=cols)
    vari_num += 1

---

pd.read_html()

 

하지만 위 코드는  pd.read_html()  한 줄로 정리될 수 있다.
이때,  pd.read_html()  은 테이블 형태에만 적용할 수 있다.

pd.read_html(str(div_data))

# os 에러가 나는 경우
from io import StringIO
pd.read_html(StringIO(str(div_data)), encoding='cp949')

---

4월 22일👩🏻‍💻

오늘의 소감: 하루 했다고 html에 적응했나보다... 재밌다🥹

---

💻 크롤링 Review

 

https://www.google.com/robots.txt 과 같이 robots.txt를 붙이면 크롤링 규칙이 나온다. (Allow/Disallow)

get_text()를 이용하는 걸 자꾸 까먹는데, 데이터 type에 대해 추가적인 공부가 필요할 듯 하다.
(어떤 type이 get_text() 사용이 가능한지, 어떤 함수를 쓰면 어떤 type이 되는지)
학교에서 파이썬 배울 때는 for문 나올 때마다 머리를 감싸쥐고 절규했었는데,
이 과정에서는 마치 구세주라도 찾는 것인 양 for문을 마구 찾아 쓰고 있었다... (왜 반복문이 만능인지 제대로 체감 중🤩)

---

💻 크롤링-Selenium 활용

너무너무 신기했던 Selenium!!!😲 다들 이렇게 매크로 만드는구나...

다음 방켓팅 때 활용을

라이브러리 설치, 로드

!pip install selenium
import requests
from bs4 import BeautifulSoup as bs
import pandas as pd
from selenium import webdriver
from seleniuhttp://m.webdriver.common.by import By
from seleniuhttp://m.webdriver.common.keys import Keys
import re

---

네이버 쇼핑 사이트 크롤링

네이버를 열고, 검색창에 검색하고, enter를 누르는 것까지 모두 입력할 수 있다.

# 웹 브라우저를 제어하기 위해 변수에 저장
driver = webdriver.Chrome()
driver.get("http://naver.com")

# 네이버 메인 화면에서 검색어를 입력하는 input Tag는 id가 query이다.
search_element = driver.find_element(By.ID, 'query')

# search_element → 검색어 입력창을 의미
search_element.send_keys('아이폰')

# 검색어의 입력이 끝났으면 ENTER 키를 눌러서 검색을 시작
search_element.send_keys(Keys.ENTER)

---

이러면 갑자기 네이버에 접속해서 검색창에 아이폰이 입력되고 검색을 하는데 신세계였다... 이걸 내가 했다니 말도 안돼

(팩트: 내가 한 게 아니다)

# 하이퍼링크의 콘텐츠 데이터가 '쇼핑'인 태그를 선택
len(driver.find_elements(By.LINK_TEXT, '쇼핑'))

shopping_button = driver.find_element(By.LINK_TEXT, '쇼핑')
# 쇼핑 버튼을 클릭한다.
shopping_button.click()

 

머릿속에서 계속 이게 생각났다... 셀레니움아 자... 이게 클릭이야

원하는 화면이 나왔다면 python에 불러온다.

---

html_data = driver.page_source
soup = bs(html_data, 'html.parser')


# 상품의 정보가 모두 들어있는 영역인 div 태그 중 id가 content인 태그를 추출
items_div = soup.find(
    'div',
    attrs = {
        'id': 'content'
    }
)

item_list = items_div.find_all(
    'div',
    attrs= {
        'class': re.compile('product_item')
    }
)

# 반복문을 이용하여 item_list의 상품명, 가격, 배송비 2차원 데이터로 생성

values = []

for item in item_list:
    
    # 상품명 추출
    item_name = item.find(
        'div',
        attrs = {
            'class': re.compile('product_title')
        }
    ).get_text()

    # 가격을 추출
    item_price = item.find(
        'span',
        attrs= {
            'class': 'price'
        }
    ).get_text()

    # 배송비 추출
    item_fee = item.find(
        'div',
        attrs= {
            'class' : re.compile('price_delivery_fee')
        }
    ).get_text()

    # 상품의 링크 주소를 추출
        # (상품명 추출해서 그 안에서 a 태그 찾고 href 속성 값 추출)
    name_tag = item.find(
        'div',
        attrs= {
            'class' : re.compile('product_title')
        }
    )
    item_url = name_tag.find('a')['href']

    # 상품명과 가격 배송비를 딕셔너리 형태로 생성하여 values에 추가
    values.append(
        {
            '상품명': item_name,
            '가격': item_price,
            '배송비': item_fee,
            'url': item_url
        }
    )

df = pd.DataFrame(values)

데이터프레임 형태로 만들 수 있다.

---

💻 수집한 데이터를 mysql db server에 저장

 

to_sql()

from sqlalchemy import create_engine
engine = create_engine(
    "서버 주소"
)

df.to_sql(name='naver', con=engine, index=False)

---

4월 23일👩🏻‍💻

오늘의 소감: 오랜만에 SQL 다루니까 얘가 정말 직관적이구나 싶다...

python 문법은 아직도 가끔 까먹을 때가 있는데 SQL 문법은 까먹지를 않는다.

---

💻 Python과 SQL 문법 비교

데이터 로드

emp라는 csv 파일 load
20일에 만들어뒀던 db.py 파일을 이용하였다.
※ pandas로 슬라이싱하는 경우 인덱스 번호가 유지되고, query문을 작성하면 인덱스가 새롭게 들어간다.

import pandas as pd
from db import MyDB
db = MyDB()


query_1 = """
    SELECT * FROM `emp`
"""

emp_df = pd.DataFrame(db.sql_query(query_1))
emp_df

 

 pd.read_csv('../csv/emp.csv')  도 가능하다.

---

💡 데이터 필터링

SAL 컬럼의 데이터가 1500 이상인 사원의 모든 정보를 확인

 

# 1
emp_df[emp_df['SAL'] >= 1500]

# 2
emp_df.loc[emp_df['SAL'] >= 1500, ]

이때만 해도 1번 방식이 편했는데, col 조건식이 붙으니까 2번이 더 편했다.

 

# 위 조건식을 Query로 만든다면

query_3 = """
    SELECT *
    FROM `emp`
    WHERE SAL >= 1500
"""

emp_filter = db.sql_query(query_3)
pd.DataFrame(emp_filter)

 

---

SAL이 1500 이상이고, JOB이 MANAGER인 사원의 사원번호와 이름을 출력

 

# pandas
flag_1 = emp_df['SAL'] >= 1500
flag_2 = emp_df['JOB'] == 'MANAGER'
col_flag = ['EMPNO', 'ENAME']

emp_df.loc[ flag_1 & flag_2, col_flag]


# sql query
query_4 = """
    SELECT EMPNO, ENAME
    FROM `emp`
    WHERE SAL >= 1500 AND JOB = 'MANAGER'
"""

pd.DataFrame(db.sql_query(query_4))

---

SAL 1500 이상 2500 이하인 사원의 모든 정보 확인

 

# pandas 1
flag_3 = emp_df['SAL'] >= 1500
flag_4 = emp_df['SAL'] <= 2500
emp_df.loc[flag_3 & flag_4, ]

# pandas 2
emp_df.loc[
    emp_df['SAL'].isin(range(1500,2501)),
]

# pandas 3
emp_df.loc[
    emp_df['SAL'].between(1500, 2500)
]


# sql query
query_5 = """
    SELECT *
    FROM `emp`
    WHERE SAL BETWEEN 1500 AND 2500
"""

pd.DataFrame(db.sql_query(query_5))

모두 동일한 결과값이 추출된다.

---

JOB이 MANAGER 이거나 SALESMAN인 모든 사원 정보를 확인

# pandas 1
flag_5 = emp_df['JOB'] == 'MANAGER'
flag_6 = emp_df['JOB'] == 'SALESMAN'
emp_df.loc[flag_5 | flag_6, ]

# pandas 2
flag_7 = emp_df['JOB'].isin(['MANAGER', 'SALESMAN'])
emp_df.loc[flag_7,]

# sql query
query_6 = """
    SELECT *
    FROM `emp`
    WHERE JOB in ('MANAGER', 'SALESMAN')
"""
pd.DataFrame(db.sql_query(query_6))

역시 동일한 결과가 추출된다.

---

• 계속 pd.DataFrame 쓰기 귀찮아서 함수를 만들었다.

def sql(query):
    result = pd.DataFrame(
        db.sql_query(query)
    )

    return result

---

사원의 이름이 S로 시작하는 사람, S가 포함된 사람 추출

 

# pandas 1: for문

# s로 시작하는 사람
word = 's'
flag_list = []
for name in emp_df['ENAME']:
    flag_list.append(
        name.lower().startswith(word.lower())
    )
emp_df.loc[flag_list,]

# S로 끝나는 경우
        # name.lower().endswith(word.lower())
        
        # S를 포함하는 경우
        # word.lower() in name.lower()

# -----------------------------------------------------

# pandas 2: for문 없이

# s로 시작하는 사람
word = 's'
flag_8 = emp_df['ENAME'].str.lower().str.startswith(word.lower())
emp_df.loc[flag_8,]
# s로 끝나는 경우
                                        # .endswith
# s를 포함하는 경우
                                        # .contains


# -----------------------------------------------------

# sql query
# pandas에서 %는 특수기호이기에 %만 사용해서는 작동하지 않는다

# s로 시작하는 사람
query_7 = """
    SELECT *
    FROM `multicam`.`emp`
    WHERE ENAME LIKE "s%%"
"""
# S가 포함되는 경우
    # WHERE ENAME LIKE "%%s%%"

sql(query_7)

 

•  s로 시작하는 경우

 

• s를 포함하는 경우

 

 python, sql 모두 동일한 결과가 추출된다.

---

💡 데이터 결합

join 결합

emp table과 dept table을 DEPTNO라는 column을 통해 결합

# pandas
pd.merge(emp_df, dept_df, on='DEPTNO', how='left')

# SQL

query_8 = """
    SELECT *
    FROM emp
        LEFT JOIN dept
            ON emp.DEPTNO = dept.DEPTNO
"""

sql(query_8)

---

pandas에서 column이 다를 때 join

 

dept_df2 = dept_df.copy()
dept_df2.rename(
    columns={
        'DEPTNO' : 'DEPTNUM'
    }, inplace=True
)

이후 아까 한 것과 같이  pd.merge(emp_df, dept_df2, on='DEPTNO', how='left') 를 하게 되면 ERROR가 발생한다.
이 경우 결합하고자 하는 데이터프레임의 key를 모두 써주면 결합에 성공한다.

pd.merge(
    emp_df, dept_df2, left_on='DEPTNO', right_on='DEPTNUM', how='left'
)

---

union

 

tran_1과 tran_2 table을 union 결합

# sql
query_9 = """
    SELECT * FROM `tran_1`
    UNION
    SELECT * FROM `tran_2`
"""
sql(query_9)


# pandas
tran_1 = pd.read_csv('../csv/tran_1.csv')
tran_2 = pd.read_csv('../csv/tran_2.csv')
tran_d1 = pd.read_csv('../csv/tran_d_1.csv')
tran_d2 = pd.read_csv('../csv/tran_d_2.csv')

pd.concat([tran_1, tran_2], axis=0, ignore_index=True)

 ignore_index = True  때문에 index가 동일한 모습으로 나타난다.

---

join: 중복된 키 select

 

잠시 다시 join으로 돌아와서, join을 수행한 key를 select하는 과정에 대해 풀이한다.
transaction_id를 동일한 key로 가지는 tran_1과 tran_d_1 테이블을 조인 결합한다.

query_11 = """
    SELECT
        transaction_id,
        price,
        payment_date,
        quantity
    FROM
    `tran_1` `t1`
    LEFT JOIN
    `tran_d_1` `td1`
    ON
    t1.transaction_id = td1.transaction_id
"""

sql(query_11)

해당 문제를 해결하려면 select문에 있는 transaction_id를 어느 테이블에서 가져올 것인지를 명시해야한다.

 

# tran_1과 tran_d_1 테이블을 조인 결합

query_11 = """
    SELECT
        t1.transaction_id,
        price,
        payment_date,
        quantity
    FROM
    `tran_1` `t1`
    LEFT JOIN
    `tran_d_1` `td1`
    ON
    t1.transaction_id = td1.transaction_id
"""

sql(query_11)

그나저나 as 없이도 이름 변경 가능한 거 충격이다... 적응하면 편할 것 같긴 한데 매번 as 써왔던 입장에서는 어색하달까...

---

💡 데이터 정렬

 

ENAME을 기준으로 내림차순 정렬

 

# sql
query_12 = """
    SELECT * FROM
    `emp` ORDER BY ENAME DESC
"""
sql(query_12)


# pandas
emp_df.sort_values('ENAME', ascending=False)

---

💡 그룹화

 

JOB으로 그룹화하여 SAL의 통계량 추출

 

# sql
# pandas와 다르게 SELECT 문에 그룹화 연산을 적어주어야 함.

query_13 = """
    SELECT
        JOB,
        AVG(SAL) AS SAL_AVG,
        SUM(SAL) AS SAL_SUM
    FROM `emp`
    GROUP BY JOB
"""
sql(query_13)

---

# pandas
emp_df[ ['JOB', 'SAL'] ].groupby('JOB').agg(['mean', 'sum', 'count'])

 

형태는 조금 다르지만 값은 동일하게 나오는 것을 볼 수 있다.
값들의 정렬도 조금 다른데, 이는 SQL 쿼리문에서 HAVING을 통해 동일하게 만들 수 있다.

---

💡 서브쿼리

부서의 지역이 NEW YORK, CHICAGO인 부서의 번호를 받아서 사원의 정보를 확인

# sql
query_16 = """
    SELECT *
    FROM `emp`
    WHERE DEPTNO IN
    (
        SELECT DEPTNO
        FROM `dept`
        WHERE LOC IN ('NEW YORK', 'CHICAGO')
    )
"""
sql(query_16)


# pandas
flag_9 = dept_df.loc[
    dept_df['LOC'].isin( ['NEW YORK', "CHICAGO"] ), 'DEPTNO'
]
emp_df.loc[emp_df['DEPTNO'].isin(flag_9), ]

둘 다 두 번의 작업을 거친다는 것에서 공통점이 있다.
pandas의 경우, flag_9에 담긴 코드를 두번째 코드의 flag_9 자리에 그대로 써놓으면 sql과 비슷하게 서브쿼리 느낌의 코드가 될 것이다.

---

💡 HAVING

직종에서 ANALYST를 제외하고 직종별로 그룹화를 하여
SAL의 평균이 2500 이상인 데이터에서 평균 SAL이 높은 순서대로 출력

# sql

query_17 = """
    SELECT
        JOB,
        AVG(SAL) SAL_AVG
    FROM `emp`
    WHERE JOB != 'ANALYST'
    GROUP BY JOB
    HAVING SAL_AVG >= 2500
    ORDER BY SAL_AVG DESC
    
"""
sql(query_17)

# pandas

df = emp_df.loc[
    emp_df['JOB'] != 'ANALYST'
]
group_df = df[ ['JOB', 'SAL'] ].groupby('JOB').mean()
group_df = group_df.loc[
    group_df['SAL'] >= 2500
]
group_df.sort_values('SAL', ascending=False).reset_index()

---

3주차 소감

오랜만에 SQL을 다루니까 반가웠고, 궁금했던 크롤링에 대해 알 수 있어 시간 가는 줄 몰랐던 주차였다.
역시 익숙한 것은 쉽고, 새로운 것은 어렵다. 하지만 새로운 것을 마주하는 것에 익숙해져야겠지.

다음 주는 시각화다. 맷플롯립, 구글 애널리틱스부터 태블로까지!!
정말 필요로 했던 부분이자 나의 취약점이다. 완전히 내 것으로 만든다는 생각으로 열심히 하자!