SQL_2

mod_sql모듈

1. mod_sql오듈 로드
2. class를 생성
3. select문을 만들어서 excutedAll 함수를 이용해서 결과값 리턴
   • corona데이터를 select
   • 데이터의 개수는 10개
4. inser문을 이용하여 데이터삽입
   • user_list테이블에 id와 password를입력
   • id는 자기 자신의 이름, password 랜덤하게 입력
5. commit(), close() 해서 데이터베이스와의 접속 종료

import pymysql
import pandas as pd

class Database():
    def __init__(self, _host, _user, _pass):
        self._db = pymysql.connect(
            host = _host,
            user = _user,
            password = _pass,
            database= 'database',
            port = 3306
        ) 
        self.cursor = self._db.cursor(pymysql.cursors.DictCursor)
 ## select 쿼리문을 실행하는 경우 사용하는 함수
    def excuteAll(self, _sql, _values=[], _limit=0):
        self.cursor.execute(_sql,_values)
        self.result  = self.cursor.fetchall()
        if _limit !=0:
            self.df = pd.DataFrame(self.result).head(_limit)
        else:
            self.df = pd.DataFrame(self.result)

        return self.df
        
 ## select문을 제외한 데이터베이스의 내용을 삽입, 수정, 삭제하는 경우
    def execute(self, _sql, _values=[]):
        self.cursor.execute(_sql,_values)
        self.result = self.cursor.fetchall()
        return self.result

    def commit (self):
        self._db.commit()
        return ' commit complete'

## 모든 작업을 끝낸 후 데이터베이스와의 접속을 종료
    def  close (self):
        self._db.close()
        return 'Database close'

모듈을 통하여 SQL 실행

import mod_sql as ms
import pandas as pd

sql_class = ms.Database("172.16.12.149",'database','1234')

1. emp라는 변수의 sql에 있는 emp table의 전체데이터를 를 데이터프레임으로 저장
2. dept라는 변수에 sql에 있는 dept table의 전체데이터를 데이터프레임으로 저장

sql_1 = """ 
        select * 
        from `emp`
        """
emp = sql_class.excuteAll(sql_1)

sql_2 = """ 
        select * 
        from `dept`
        """
dept = sql_class.excuteAll(sql_2)
emp_copy = emp

1. 새로운 파생변수 생성 job_code
   -salesman 은 1, manager는 2, 그 외의 직업은 3
   -컬럼은 EMPNO, ENAME, job_code만 출력

sql = """ 
    select EMPNO,ENAME,JOB,
    CASE
        when JOB ='SALESMAN'
        then 1
        when JOB = 'MANAGER'
        then 2
        else 3
    END as job_code
    from EMP
    """
sql_class.excuteAll(sql)

2. emp에서 SAL컬럼의 값이 2000이상이고 5000미만인 사원 정보 출력

#case1

sql = """ 
    select *
    from emp
    where SAL >=2000 and SAL<5000
        """
sql_class.excuteAll(sql)

#case2

sql = """ 
    select *
    from emp
    where SAL between 2000 and 4999
    """
sql_class.excuteAll(sql)

3. JOB컬럼의 데이터를 변환
4. 'SALESMAN' 값을 'SALES'변환

## sql 쿼리문 

sql = """ 
    select JOB,
    replace(JOB, 'SALESMAN','SALES') as JOB_
    from emp
        """
sql_class.excuteAll(sql)

5. 2개의 컬럼의 데이터를 하나의 컬럼에 삽입
6. emp데이터에서 ENAME, JOB두 데이터를 결합
7. ENAME_ 컬럼에 ENAME(JOB)형태로 데이터를 생성

## sql 쿼리문
sql = """ 
    select concat(ENAME,'(',JOB,')') as ENAME_
    from emp
    """
sql_class.excuteAll(sql)

8. emp 데이터에서 파생변수 H/L 생성
9. SAL이 2000이상이면 HIGH, 2000미만이면 LOW

### sql 쿼리문
#case1
sql = """ 
    select SAL,
    if(SAL >=2000,"HIGH","LOW") as `H/L`
    from emp
"""
sql_class.excuteAll(sql)

#case2

sql_2 = """ 
    select SAL,
    case 
        when SAL >=2000 then 'HIGH'
        else 'Low'
    end as `H/L`
    from emp
    """
sql_class.excuteAll(sql_2)

10. emp와 dept데이터프레임을 결합
11. 조건은 emp에 있는 depno, dept에 있는 deptno가 같은 경우
12. 데이터프레임을 조인 결합

sql = """ 
        select * 
        from emp
        left join dept
        on emp.DEPTNO = dept.DEPTNO
        """
sql_class.excuteAll(sql)

### emp table에서 s 시작하는 사원의 정보 출력
### python 에서 %를 쓰기위해서 두개를 사용해야 한다.
sql = """ 
    select * 
    from emp
    where `ENAME` like 'S%%'
    """
db.excuteAll(sql)
## S로 끝나는 사람
sql_2 = """ 
    select * 
    from emp
    where `ENAME` like '%%S'
    """
db.excuteAll(sql_2)
## 앞에있던 뒤에있던 가운데에 S가 있으면
sql_3 = """ 
    select * 
    from emp
    where `ENAME` like '%%S%%'
    """
db.excuteAll(sql_3)