Sub Query 연습 & MariaDB연동 & 팟빵 크롤링

1. SubQuery연습

- 쿼리문 실행 순서(from -> group by -> select)상 select에서 쓴 alias를 group by 에서 사용하지 못하지만 MariaDB에서는 사용할 수 있도록 처리를 해준다.

- 데이블생성 및 데이터 추가 쿼리

drop table IF EXISTs emp;
drop table IF EXISTs dept;
# DML start
CREATE TABLE dept (
	deptno               int  NOT NULL  AUTO_INCREMENT,
	dname                varchar(20)    ,
	loc                  varchar(20)    ,
	CONSTRAINT pk_dept PRIMARY KEY ( deptno )
 );

CREATE TABLE emp (
	empno                int  NOT NULL  AUTO_INCREMENT,
	ename                varchar(20)    ,
	job      			 varchar(20),
	mgr                  smallint    ,
	hiredate             date    ,
	sal                  numeric(7,2)    ,
	comm                 numeric(7,2)    ,
	deptno               int    ,
	CONSTRAINT pk_emp PRIMARY KEY ( empno )
 );

CREATE TABLE SALGRADE
        (GRADE int,
         LOSAL int,
         HISAL int);

CREATE INDEX idx_emp ON emp ( deptno );

ALTER TABLE emp ADD CONSTRAINT fk_emp_dept FOREIGN KEY ( deptno ) REFERENCES dept( deptno ) ON DELETE NO ACTION ON UPDATE NO ACTION;

# DML end

# DDL start
insert into dept values(10, 'ACCOUNTING', 'NEW YORK');
insert into dept values(20, 'RESEARCH', 'DALLAS');
insert into dept values(30, 'SALES', 'CHICAGO');
insert into dept values(40, 'OPERATIONS', 'BOSTON');

insert into emp values( 7839, 'KING', 'PRESIDENT', null, STR_TO_DATE ('17-11-1981','%d-%m-%Y'), 5000, null, 10);
insert into emp values( 7698, 'BLAKE', 'MANAGER', 7839, STR_TO_DATE('1-5-1981','%d-%m-%Y'), 2850, null, 30);
insert into emp values( 7782, 'CLARK', 'MANAGER', 7839, STR_TO_DATE('9-6-1981','%d-%m-%Y'), 2450, null, 10);
insert into emp values( 7566, 'JONES', 'MANAGER', 7839, STR_TO_DATE('2-4-1981','%d-%m-%Y'), 2975, null, 20);
insert into emp values( 7788, 'SCOTT', 'ANALYST', 7566, DATE_ADD(STR_TO_DATE('13-7-1987','%d-%m-%Y'),INTERVAL -85 DAY)  , 3000, null, 20);
insert into emp values( 7902, 'FORD', 'ANALYST', 7566, STR_TO_DATE('3-12-1981','%d-%m-%Y'), 3000, null, 20);
insert into emp values( 7369, 'SMITH', 'CLERK', 7902, STR_TO_DATE('17-12-1980','%d-%m-%Y'), 800, null, 20);
insert into emp values( 7499, 'ALLEN', 'SALESMAN', 7698, STR_TO_DATE('20-2-1981','%d-%m-%Y'), 1600, 300, 30);
insert into emp values( 7521, 'WARD', 'SALESMAN', 7698, STR_TO_DATE('22-2-1981','%d-%m-%Y'), 1250, 500, 30);
insert into emp values( 7654, 'MARTIN', 'SALESMAN', 7698, STR_TO_DATE('28-09-1981','%d-%m-%Y'), 1250, 1400, 30);
insert into emp values( 7844, 'TURNER', 'SALESMAN', 7698, STR_TO_DATE('8-9-1981','%d-%m-%Y'), 1500, 0, 30);
insert into emp values( 7876, 'ADAMS', 'CLERK', 7788, DATE_ADD(STR_TO_DATE('13-7-1987', '%d-%m-%Y'),INTERVAL -51 DAY), 1100, null, 20);
insert into emp values( 7900, 'JAMES', 'CLERK', 7698, STR_TO_DATE('3-12-1981','%d-%m-%Y'), 950, null, 30);
insert into emp values( 7934, 'MILLER', 'CLERK', 7782, STR_TO_DATE('23-1-1982','%d-%m-%Y'), 1300, null, 10);

INSERT INTO SALGRADE VALUES (1,  700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);
# DDL end
commit;

- 쿼리문 연습

select * from emp;
#emp : 사원번호,이름,업무,관리자번호,입사일자,급여,커미션,부서번호
select * from dept;
#dept : 부서번호,부서명,도시명
select * from salgrade;
#salgrade : 등급,최저급여,최고급여

# 1. EMP TABLE 에서 이름, 급여, 커미션 금액, 총액 (SAL + COMM) 을 구하여 총액이 많은 순서로 출력하라. 
# 단 커미션이 NULL인 사람은 제외한다.
select ename, sal, comm, (sal+comm) as 총액
from emp
where comm is not null;


# 2. EMP와 DEPT TABLE 을 JOIN 하여 부서번호, 부서명, 이름, 급여를 출력하라.
select d.deptno, d.dname, e.ename, e.sal
from emp e
inner join dept d on e.deptno = d.deptno;


# 3. EMP 테이블에서 사원번호가 7521인 사원과 업무가 같고, 급여가 7934인 사원보다 많은 사원의
# 사원번호, 이름, 담당업무, 입사일자, 급여를 출력하여라.
select empno, ename, job, hiredate, sal
from emp
where job = (select job from emp where empno = 7521)
and sal > (select sal from emp where empno = 7934);




# 4. EMP 테이블에서 평균급여보다 적은 급여를 받는 사원의 사원번호, 이름, 담당업무, 급여, 부서번호를 출력하라.
select empno, ename, job, sal, deptno
from emp
where sal < (select avg(sal) from emp);


# 5. EMP TABLE 에 있는 EMPNO와 MGR을 이용하여 서로의 관계를 다음과 같이 출력하라.
# SMTH의 매니저는 FORD이다.
# concat함수 사용 **************************************
# 5.1 (self query) 같은 emp 테이블 2개를 조인합니다.
select concat(e1.ename, '의 매니저는',e2.ename,'이다') as 사원과매니저
from emp e1 inner join emp e2
on e2.empno = e1.mgr;

# 5.2 inline view
select concat(e1.ename, '의 매니저는',e2.ename,'이다') as 사원과매니저
from emp e1,(select * from emp)e2
where e2.empno = e1.mgr;

#6. ALLEN의 직무와 같은 사람의 이름, 부서명, 급여, 직무를 출력하라.
select  ename, d.dname, sal, job
from emp e inner join dept d on e.deptno = d.deptno
where job = (select job from emp where ename='ALLEN');


#7. EMP 테이블에서 SALES 부서 사원의 이름,업무를 출력하는 SELECT문을 작성하시오.
#7.1 Nested Query
select ename, job
from emp
where deptno = (select dept.deptno from dept where dname = 'SALES');

#7.2 Inline View
select e.ename, e.job,d.deptno
from emp e, (select deptno from dept where dname='SALES') as  d
where e.deptno = d.deptno;


#8. EMP 테이블에서 이름에 “T”가 있는 사원이 근무하는 부서에서 근무하는 모든 사원에 대해 사원 번호,이름,급여를 출력하는 SELECT문을 작성하시오.
#단 사원번호 순으로 출력하여라.
select empno, ename, sal, deptno
from emp
where deptno in (select distinct deptno from emp where ename like '%T%')
order by empno;


# 9. 전체 사원의 평균 임금보다 많은 사원의 사원번호, 이름, 부서명, 입사일, 지역, 급여를 출력하라.
select empno, ename, d.dname, hiredate,d.loc, sal
from emp e inner join dept d on e.deptno = d.deptno
where sal > (select avg(sal) from emp);


# 10. EMP 테이블에서 관리자중에서 부하직원을 2명이상 관리하는 관리자의 이름을 출력하세요.
select mgr, count(*) as subem
from emp
group by mgr
having subem >= 2;
# 10.1 Nested Query (where 절에 subquery)
select empno, ename
from emp
where empno in (select mgr from emp group by mgr having count(*) >= 2);

# 10.2 Inline View (from 절에 subquery)
select empno, ename
from emp, (select mgr from emp group by mgr having count(*) >= 2) e2
where empno = e2.mgr;

# 11.EMP 테이블에서 CHICAGO에서 근무하는 사원과 같은 업무를 하는 사원의 이름,업무를 출력하는 SELECT문을 작성하시오.
# 11.1.1 Nested Query ( where 절에 in 구문에  subquery 대입 )
select ename, job
from emp
where job in
(select job
from emp
where deptno = (select deptno from dept where loc = 'CHICAGO'));

# 11.2 Inline View
select ename, job
from (select ename, job from emp where job in (select job from emp where deptno = (select deptno from dept where loc='CHICAGO'))) as a;

# 12.EMP 테이블에서 업무가 JONES와 같거나 월급이 FORD이상인 사원의 이름,업무,부서번호,급여를 출력하는 SELECT문을 작성하시오.
# 단 업무별, 월급이 많은 순으로 출력하여라.
select ename, job, d.deptno, sal
from emp e inner join dept d on e.deptno = d.deptno
where job = (select job from emp where ename='JONES')
or sal >= (select sal from emp where ename='FORD')
order by job, sal desc;

# 13. EMP 테이블에서 업무별로 최소 급여를 받는 사원의 사원번호, 이름, 업무, 입사일자, 급여, 부서번호를 출력하여라.
select empno, ename, job, hiredate, sal, deptno
from emp
where (job,sal) in (select job, min(sal) from emp group by job)
group by job;

# 14. emp와 dept 테이블에서 업무가 manager인 사원의 이름, 업무, 부서명, 근무지를 출력하여라.
# Inline View 를 사용하자
select ename, job, d.dname, d.loc
from dept d, (select * from emp where job='manager') e
where e.deptno = d.deptno;

# 15. EMP 테이블에서 30번 부서원 중 최저급여를 받는 사원을 제외한 나머지 사원들의 모든 정보를 출력하는 SELECT문을 작성하시오.
# (Multi Column Sub Query)
select *
from emp
where deptno = 30
and sal != (select min(sal) from emp where deptno=30);

select *
from emp
where (deptno,sal) not in (select deptno, min(sal) from emp group by deptno)
and deptno = 30;

# 16. EMP 테이블에서 말단 사원의 사원번호,이름,업무,부서번호를 출력하는 SELECT 문을 작성하시오.
# (말단사원: 다른 사원을 관리하지 않는 사원)
# - ORACLE : NVL(VALUE1, VALUE2)
# - MSSQL  : ISNULL(VALUE1, VALUE2)
# - MYSQL  : IFNULL(VALUE1, VALUE2)
SELECT DISTINCT(IFNULL(mgr,0)) FROM emp;
select ename, empno
from emp
where empno not in (SELECT DISTINCT(IFNULL(mgr,0)) FROM emp);

# 결과가 출력되지 않는다. in 구문은 null 값을 비교하지 못하기 때문이다.
select ename, empno
from emp
where empno not in (SELECT DISTINCT mgr FROM emp);

# 17. EMP 테이블에서 사원번호, 이름, 업무, 급여, 급여의 등급을 출력하되 3등급 이상인 사원의 정보만을 출력하세요.
# (emp와 salgrade 테이블을 이용);
select empno, ename, job, sal, s.grade
from emp e, salgrade s
where (e.sal >= s.LOSAL) and (e.sal <= s.HISAL) and s.grade >= 3;


# 18. 부서번호, 부서에 속한 직원수, 부서명, 도시명을 출력하세요.
# 직원수가 5명이상인 부서만 출력하세요.
select d.deptno, count(*) as emp_cnt, d.dname, d.loc
from emp e, dept d
where e.deptno = d.deptno
group by d.deptno
having emp_cnt >= 5;

# 20. EMP 테이블에서 적어도 한 명 이상으로부터 보고를 받을 수 있는 사원의 업무,이름,사원번호,부서번호를 출력하시오.
# (즉 관리자를 출력하세요)
select ename, empno
from emp
where empno in (SELECT DISTINCT(IFNULL(mgr,0)) FROM emp);

- SQL , Program 연동

Java - JDBC(java database connectivity), MyBatis프레임워크, JPA(Java Persistance API)

Python - pymysql, sqlalchemy(JPA와 비슷), Django(ORM포함)

2. Pymysql과MariaDB연동

- sql문 작성

sql = """
CREATE TABLE product (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    model_num VARCHAR(10) NOT NULL,
    model_type VARCHAR(10) NOT NULL,
    PRIMARY KEY(id)
);
"""

- pymysql 을 import하고 db에 connect 한다.

- 작성한 sql문을 실행하고 commit 해보았다.

import pymysql
db = pymysql.connect(host = 'localhost', port=3306, db='python_db',user='python',\
                     passwd='python', charset='utf8')
cursor = db.cursor()
cursor.execute(sql)
db.commit()

- drop을 통해 테이블을 날릴 수 도 있다.

cursor.execute('drop table product')
cursor.execute('show tables')

- 완료 후 항상 close()를 통해 닫아주는 것이 원칙!

db.close()

- for문으로 데이터를 한번에 insert했다.

import pymysql
db = pymysql.connect(host = 'localhost', port=3306, db='python_db',user='python',\
                     passwd='python', charset='utf8')

try:
    with db.cursor() as cursor:
        cursor.execute(sql)
        db.commit()
        
        for num in range(10,20):
            name = 'S20'+str(num)
            #ins_sql = 'insert into product (name, model_num, model_type) values(%s, %s, %s)'
            #cursor.execute(ins_sql,(name, '7700','Phone'))
            ins_sql = "insert into product (name, model_num, model_type) values('"+name+"','7700','Phone')"
            cursor.execute(ins_sql)
            print(ins_sql)
        db.commit()
        print(cursor.lastrowid)
finally:
    db.close()

- update문과 select문도 실행해보았다.

# import pymysql
db = pymysql.connect(host = 'localhost', port=3306, db='python_db',user='python',\
                     passwd='python', charset='utf8')
try:
    # select, update
    with db.cursor() as cursor:
        cursor.execute('select * from product where id=3')
        result = cursor.fetchone()
        print(type(result), result)
        
        upd_sql = "update product set model_type='%s' where name between 'S2010' and 'S2015'" %'핸드폰'
        cursor.execute(upd_sql)
        db.commit()
        print(cursor.rowcount)
        
        cursor.execute('select * from product')
        result_list = cursor.fetchall()
        print(type(result_list), result_list)
        for row in result_list:
            print(row[0],row[1],row[2],row[3])
            
        # model_type별로 group by하는 쿼리 실행
        cursor.execute('select model_type,count(*) from product group by model_type')
        result_list = cursor.fetchall()
        print(type(result_list), result_list)
        for row in result_list:
            print(row)
    pass
finally:
    db.close()

3. 팟빵 스크래핑

- 팟캐스트 관련 웹사이트인 팟빵 사이트에서 스크래핑을 통해 mp3파일을 다운로드 받아보자.

- 실제 URL은 다음과 같다. 하지만 팟빵에서 사이트 개편을 통해 크롤링을 하지 못하도록 URL을 변경하여 해당 사이트에서는 크롤링이 불가능하다.

http://www.podbbang.com/ch/16255

여바라 : 여행 바이러스 라디오

경비, 안전, 언어... 등 해외여행에 대한 막연한 선입견! 가이드북, 방송, 유명 블로거가 알려주는 틀에 박힌 루트, 맛집! 당신의 여행공식, 상식을 파괴할 그/녀들이 온다. 울트라버짓(초저예산) �

www.podbbang.com

- 이 URL은 팟빵의 이전 URL이다. 이 URL을 통해 스크래핑이 가능하다.

http://www.podbbang.com/podbbangchnew/episode_list?id=16255&page=1

www.podbbang.com

- 해당페이지에 가서 개발자 도구를 통해 html태그들을 확인한다.

- 스크래핑 할 page url정의

import requests
from bs4 import BeautifulSoup
from urllib.parse import urljoin

#여행바이러스 라디오(52건)
page_url = "http://www.podbbang.com/podbbangchnew/episode_list?id=16255&page=1"
#엄마의 미드공부(71건)
#page_url = 'http://www.podbbang.com/podbbangchnew/episode_list?id=16524&page=1'
page_url

- 이 페이지는 user-agent를 주지 않으면 응답이 오지 않으므로 headers에 user-agent를 다음과 같이 정의해준다.

- 한글이 깨지는 것을 맊기 위해 인코딩 설정을 반드시 해준다.

- selector를 사용해 쉽게 파싱하기 위해 Beautifulsoup으로 변환한 뒤 출력해본다.

headers = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/65.0.3325.181 Safari/537.36',
}
r = requests.get(page_url,headers=headers)
# 인코딩 설정
r.encoding = "utf-8"
html = r.text
soup = BeautifulSoup(html,"html.parser")
soup

- 개발자 도구를 통해 어떤 Dom형태로 되어있는지 확인한 후 parsing한다.

- 유료 다운로드의 경우 a태그가 존재하지 않으므로 if else문으로 따로 분기처리를 해준다.

- a태그를 통해 가져온 링크들을 또 한번 request로 날려 데이터를 분석해야한다.

- 해당 url이 존재하는 찐 페이지를 알려줘야 스크래핑할 수 있기때문에 regerer에 찐페이지(메인?)url을 준다.

for li_tag in soup.select('li'):
#     print(li_tag)
    title = li_tag.find('dt')['title']
    #유료인 경우 a tag가 없으므로 한번 체크를 해준다.
    if li_tag.find('a'):
        link = urljoin(page_url,li_tag.find('a')['href'])
    else:
        link = None
    print(title,link)
    
    req_headers={
        'referer':'http://www.podbbang.com/ch/16255',
        'User-Agent':'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/65.0.3325.181 Safari/537.36',
    }
    if link:
        mp3_bin = requests.get(link, headers=req_headers).content
        title = clean_text(title)
        file_name = '{}.mp3'.format(title)
        #print(file_name)
        with open('MP3/'+file_name,'wb') as f:
            f.write(mp3_bin)

- 제목의 경우 파일이름으로 저장할 예정인데, 특수문자가 들어갈 경우 저장이 안될 수 있으므로 정규식을 통해 특수문자를 제거하는 함수를 만들어 준다.

import re
# 클리닝 함수 (file에 특수문자가 있을 경우 )
def clean_text(text):
    text = text.replace("\n", "")
    cleaned_text = re.sub('[\{\}\[\]\/?.,;:|\)*~`!^\-_+<>@\#$%&\\\=\(\'\"]',
                          '', text)
    return cleaned_text

- 함수 인자에 값을 넣어 확인해본다.

clean_text('김영철의 파워FM - 진짜 미국식 영어 757회 - Thanks. / Cheers! (고마워요.)')

clean_text('조지아(Georgia)를 아시나요? feat 바쿠소녀 송의영')

4. 팟빵 스크래핑 - 함수로 만들어 사용하기

- clean_text() 함수 : 파일에 특수문자 저장하지 않기 위해, 특수문자 없애는 함수

- get_pang(pid) 함수 : http://www.podbbang.com/ch/16255 와 같이 URL의 맨 마지막 번호(각 팟캐스트 페이지를 구분하는 번호)를 입력받아 해당 페이지에 있는 다운로드 파일을 내 폴더에 저장하는 함수

- for page_id in count(1) : itertools 라이브러리를 통해 1페이지무터 무한 루프를 돌려 마지막 페이지까지 다운로드받을 수 있도록 한다. 대신 try except문을 써서 예외 처리를 해줘야 한다.

import requests
from bs4 import BeautifulSoup
from urllib.parse import urljoin
import re
from itertools import count
from time import sleep

# 클리닝 함수 (file에 특수문자가 있을 경우 )
def clean_text(text):
    text = text.replace("\n", "")
    cleaned_text = re.sub('[\{\}\[\]\/?.,;:|\)*~`!^\-_+<>@\#$%&\\\=\(\'\"]',
                          '', text)
    return cleaned_text

# pid는 팟캐스트 하나하나의 id
def get_pang(pid):
    page_url = "http://www.podbbang.com/podbbangchnew/episode_list"
    headers = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/65.0.3325.181 Safari/537.36',
    }
    
    #for page_id in range(1,3):
    for page_id in count(1):#1부터 무한루프 -> try catch 해줘야함
        params_dict = {'id':pid, 'page':page_id}
        res = requests.get(page_url,params=params_dict, headers=headers)
        res.encoding = 'utf-8'
        html = res.text
        soup = BeautifulSoup(html,'html.parser')

        for li_tag in soup.select('li'):
            try:
                #print(li_tag)
                title = li_tag.find('dt')['title']
                #유료인 경우 a tag가 없으므로 한번 체크를 해준다.
                if li_tag.find('a'):
                    link = urljoin(page_url,li_tag.find('a')['href'])
                else:
                    link = None
                print(title,link)
            except(TypeError, KeyError):
                #li Tag가 없는 경우는 TypeError가 발생한다.
                print('END')
                return None
            else:
                req_headers={
                    'referer':'http://www.podbbang.com/ch/16255',
                    'User-Agent':'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/65.0.3325.181 Safari/537.36',
                }
                if link:
                    mp3_bin = requests.get(link, headers=req_headers).content
                    title = clean_text(title)
                    file_name = '{}.mp3'.format(title)
                    #print(file_name)
                    with open('MP3/'+file_name,'wb') as f:
                        f.write(mp3_bin)
                # sleep(10) : 10초간 프로세스 중지, 기계가 아니라 사람처럼 보이게 하기위한 위장
                sleep(0.5)

- 만들어진 함수에 http://www.podbbang.com/ch/16255 다음과 같은 링크를 크롤링 하기 위해 인자로 16255 를 써서 실행한다.

#여행바이러스 라디오(52건)
get_pang(16255)

- 0.5초 간격으로 지정한 폴더에 다운로드 되는 것을 볼 수 있다.

'데이터베이스 > MariaDB' 카테고리의 다른 글

파이썬 MariaDB_07월 27일 (0)	2020.07.27

1. SubQuery연습

2. Pymysql과MariaDB연동

3. 팟빵 스크래핑

4. 팟빵 스크래핑 - 함수로 만들어 사용하기

'데이터베이스 > MariaDB' 카테고리의 다른 글

티스토리툴바