152 파이썬과 mySQL 연동
mySQL은 1995년 오픈소스로 배포된 DBMS(DataBase Management System)
2010년 mySQL이 오라클에 인수된 후 현재 mySQL 8.0까지 배포되었으며 상용버전과 커뮤니티 버전으로 구분됨.
오라클에 인수되고나서 개발 지침과 라이센스 정책의 변화에 따라 mySQL 핵심 개발자의 주도로 오픈소스 정책을 지향하는 maria DB가 탄생. maria DB는 mySQL 소스코드에 기반을 두고 개발되었고, SQL을 사용하는 개발자 입장에서 현재까지 별다른 차이가 없고 더군다나 무료
| mySQL 버전 | mariaDB버전 | |
| 2018.05 기준 | 8.0 | 10.3 |
| 2018.11 | 10.4 | |
| 2019.12 | 10.5 | |
| 2021.04 | 10.6 |
ㅇmySQL 설치
https://m.blog.naver.com/bjh7007/221829548634
설치 후 mySQL workbench 열면 됨.
root 계정 비밀번호 1234
데이터 베이스 생성하고 사용하겠다고 선언.
create database orcl;
use orcl;drop table emp;
drop table dept;
CREATE TABLE DEPT
(DEPTNO int(10),
DNAME VARCHAR(14),
LOC VARCHAR(13) );
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');
select * from dept;
CREATE TABLE EMP (
EMPNO int(4) NOT NULL,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR int(4) ,
HIREDATE DATE,
SAL int(7),
COMM int(7),
DEPTNO int(2) );
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'81-11-17',5000,NULL,10);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'81-05-01',2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'81-05-09',2450,NULL,10);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'81-04-01',2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'81-09-10',1250,1400,30);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'81-02-11',1600,300,30);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'81-08-21',1500,0,30);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'81-12-11',950,NULL,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'81-02-23',1250,500,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'81-12-11',3000,NULL,20);
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'80-12-09',800,NULL,20);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'82-12-22',3000,NULL,20);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'83-01-15',1100,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'82-01-11',1300,NULL,10);
commit;
select count(*) from emp;emp테이블과 dept테이블을 생성하여 넣음 = > 참고로 mySQL은 한줄씩 커밋을 일일이 눌러줘야함. 전체 선택> 전체 커밋 안됨
문제486. (복습문제) 월급이 3000 이상인 사원들의 이름과 월급을 출력하시오.
select ename, sal
from emp
where sal >= 3000;문제487. (복습문제) 직업이 SALESMAN인 사원들의 이름, 월급, 직업을 출력하는데 월급이 높은 사원부터 출력하시오.
select ename, sal, job
from emp
where job = 'SALESMAN'
order by 2 desc;=> 오라클과의 차이점 : 대소문자를 가리지 않음. 따라서 where job = 'salesman'이라고 해도 검색이 됨.
만약 대소문자 구분하고 싶다면 환경 세팅 설정 변경해주면 됨.
=> 오라클과 다르게 mySQL workbench를 껏다 켰으면 다시 use 데이터베이스이름;으로 접속해줘야함.
use orcl; # orcl = 내가 만든 데이터베이스 이름
select * from emp;
문제488. (복습문제) 직업, 직업별 토탈월급을 출력하는데 직업별 토탈월급이 5000이상인것만 출력하고 직업별 토탈월급이 높은 것부터 출력하시오.
select job, sum(sal)
from emp
group by job
having sum(sal) >= 5000
order by 2 desc;=> mySQL도 Oracle처럼 having절을 지원함.
문제489. (복습문제) emp 테이블과 dept테이블을 조인해서 이름과 부서위치를 출력하시오.
- Oracle 조인문법
select e.ename, d.loc
from emp e, dept d
where e.deptno = d.deptno;- 1999 ANSI 조인문법
select e.ename, d.loc
from emp e join dept d
on (e.deptno = d.deptno);=> mySQL에서 Oracle조인문법, 1999ANSI 조인문법 모두 다 사용할 수 있음.
문제490. (복습문제) 부서위치, 부서위치별 토탈월급을 출력하는데 부서위치별 토탈월급이 높은 것부터 출력하시오.
select d.loc, sum(e.sal)
from emp e, dept d
where e.deptno = d.deptno
group by d.loc
order by 2 desc;
ㅇ Oracle과 mySQL의 주요 함수의 차이 비교
| Oracle | mySQL |
| nvl | ifnull |
| sysdate | sysdate() |
| months_between | period_add |
| decode | if |
| rollup | with rollup |
| listagg | group_concat |
문제491. (복습문제) 이름, 커미션을 출력하는데 커미션이 null인 사원들은 0으로 출력하시오.
select ename, ifnull(comm,0)
from emp;문제492. (복습문제) 오늘 날짜를 출력하시오.
-- Oracle
select sysdate
from dual;
-- mySQL
select sysdate();=> Oracle과 다르게 dual 테이블이 없음.
+) mySQL에서는 full outer join이 안먹힘. union으로 묶어서 left outer join/right outer join을 해주면 됨.
select e.ename, d.loc
from emp e left outer join dept d
on (e.deptno = d.deptno)
union
select e.ename, d.loc
from emp e right outer join dept d
on (e.deptno = d.deptno);
문제493. (복습문제) 1981년도에 입사한 사원들의 이름과 입사일을 출력하시오.
-- Oracle
select ename, hiredate
from emp
where hiredate between to_date('1981/01/01','rrrr/mm/dd')
and to_date('1981/12/31','rrrr/mm/dd')+1;
-- mySQL
select ename, hiredate
from emp
where hiredate between date_format('1981-01-01', '%Y%m%d')
and date_format('1982-01-01', '%Y%m%d');
-- date_format('1981-12-31', '%Y%m%d')+1 이 안먹히므로 그 다음날로 해줘야함
-- Oracle, mySQL 모두 날짜는 00시 00분 00초 기준=> 날짜 형식 date_format('날짜','형식')으로 해야함. Oracle과 달리 rrrr과 yyyy의 차이는 없음. 다만 %y는 연도의 2자리, %Y는 연도의 4자리를 나타냄.
=> mySQL은 날짜함수가 따로 있어서 그 함수를 이용해 더하기/빼기를 해줘야함
select date_add('1981-12-31', interval 1 day );
문제494. (복습문제) 오늘 날짜와 현재 시간을 확인하시오.
-- Oracle
select systimestamp
from dual;
-- mySQL
select now();문제495. (복습문제) 오늘 날짜만 출력하는데 연도를 4자리로 출력하시오.
select date_format(now(), '%Y-%m-%d') as 오늘날짜;문제496. (복습문제) 아래의 오라클 SQL을 mySQL로 구현하시오.
-- Oracle
select ename, sal, deptno, decode(deptno, 10, sal*1.1, 0) as 보너스
from emp;
-- mySQL
select ename, sal, deptno, if(deptno = 10, sal*1.1, 0) as 보너스
from emp;=> mySQL에서 if문을 사용할 때 if(조건, 참일때 값, 거짓일 때 값)으로 작성하면 됨
문제497. (복습문제) 아래의 오라클 SQL을 mySQL로 구현하시오.
-- Oracle
select ename, sal, deptno, decode( deptno, 10, sal*1.1,
20, sal*1.4, 0) as 보너스
from emp;
-- mySQL
select ename, sal, deptno, if(deptno=10, sal*1.1, if (deptno=20, sal*1.4, 0) ) as 보너스
from emp;=> mySQL에 if문에서 조건을 여러개 넣을 경우 if문을 중첩하여 쓰면 됨
문제498. (점심시간 문제) 아래의 오라클 SQL을 mySQL로 구현하시오.
-- Oracle
select ename, sal, job, decode(job, 'SALESMAN',6000,
'ANALYST',8000, 0) 보너스
from emp;
-- mySQL
select ename, sal, job, if(job='SALESMAN', 6000, if (job='ANALYST', 8000, 0) ) as 보너스
from emp;문제499. 부서번호, 부서번호별 토탈월급을 출력하는데 맨 아래에 전체 토탈월급을 출력하시오.
-- Oracle
select deptno, sum(sal)
from emp
group by rollup(deptno);
-- mySQL
select deptno, sum(sal)
from emp
group by deptno with rollup;문제500. 아래의 오라클 SQL을 mySQL로 구현하시오.
-- Oracle
select nvl(job, 'Total'), sum(sal)
from emp
group by rollup(job);
-- mySQL
select ifnull(job,'Total'), sum(sal)
from emp
group by job with rollup;문제501. 부서번호, 부서번호별로 속한 사원들의 이름을 가로로 출력하시오
-- Oracle
select deptno, listagg(ename,',') within group (order by ename asc)
from emp
group by deptno;
-- mySQL
select deptno, group_concat(ename order by ename asc)
from emp
group by deptno;
-- 구분자를 바꾸는 코드 ( mySQL 구분자 기본은 콤마(,) )
select deptno, group_concat(ename separator '/')
from emp
group by deptno;문제502. 이름과 월급을 출력하는데 아래와 같이 붙여서 출력하시오.
-- Oracle
select ename || sal
from emp;
-- mySQL
select concat(ename, sal)
from emp;=> mySQL에서 2개 이상의 컬럼을 붙여서 출력할 때는 연결연산자가 아닌 concat()으로 붙여야함.
문제503. (복습문제) 다음과 같이 결과를 출력하시오.
| 10 | CLARK(2450),KING(5000),MILLER(1300) |
| 20 | ADAMS(1100),FORD(3000),JONES(2975),SCOTT(3000),SMITH(800) |
| 30 | ALLEN(1600),BLAKE(2850),JAMES(950),MARTIN(1250),TURNER(1500),WARD(1250) |
select deptno, group_concat(concat(ename, '(',sal,')') order by ename asc) as 'ename(sal)'
from emp
group by deptno;
ㅁ mySQL을 사용할 때 반드시 필수로 알아둬야 하는 내용
1. emp 테이블을 delete문으로 지웁니다.
delete from emp;Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.
=> safe모드로 사용중이라 delete해도 지워지지 않음. where 절 없이 delete하면 지워지지 않음.
Edit > Preferences > SQL Editor > Safe Updates 해제 후 reconnect하면 safe모드 해제되어 delete 됨.
delete 됐음을 확인할 수 있음.
2. rollback 하시오.
rollback;
rollback 안됨
=> mySQL은 오라클과 다르게 자동커밋 기능이 활성화 되어 있음. 따라서 자동커밋 되어버려서 rollback을 할 수가 없음. mySQL 사용자들이 가장 많이 하는 실수 중에 하나이므로 주의해야함!!
자동커밋 기능이 활성화 되어져 있는지 확인해보세요.
select @@autocommit;
autocommit이 0이면 off, 1이면 on상태임.
자동 커밋 기능을 끕니다.
set autocommit= FALSE;
select @@autocommit;
=> mySQL을 사용할 때 가장 먼저 확인해야하는 것이 자동커밋 활성화 되어있는지 여부!
아주 중요함!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!1
★ safe모드를 해제할 때 자동커밋 기능을 반드시 확인 ★
emp테이블을 다시 insert하세요~~
ㅇmySQL과 파이썬 연동하기
1. 아나콘다 프롬프트 창을 열고 pymysql을 설치하세요.
conda install pymysql2. 주피터 노트북에서 아래와 같이 코딩하세요.
import pymysql
import pandas as pd
conn = pymysql.connect( host = "localhost", user = "root", password = "1234", db = "orcl", charset = "utf8") # 접속정보 입력
cursors = conn.cursor() # mySQL에 접속한 후 생성된 메모리 이름을 cursors로 하겠다
sql = "select * from emp"
cursors.execute(sql) # sql 쿼리를 실행해서 결과를 cursors라는 메모리에 담음
rows = cursors.fetchall() # 메모리에 있는 결과를 rows에 넣음
colname = cursors.description # emp테이블의 컬럼이름이 포함된 정보를 colname에 담음
col = []
for i in colname:
col.append(i[0].lower()) # emp테이블의 컬럼이름을 소문자로 변경해서 col리스트에 담음
emp = pd.DataFrame(rows, columns = col)
emp
문제504. 위의 파이썬 연동코드를 함수로 만들어서 아래와 같이 실행되게 하시오.
mysql_connect()
result 변수에 담고 싶은 "테이블명"을 입력하시오. emp
result
def mysql_connect(table_name):
import pymysql
import pandas as pd
conn = pymysql.connect( host = "localhost", user = "root", password = "1234", db = "orcl", charset = "utf8") # 접속정보 입력
cursors = conn.cursor() # mySQL에 접속한 후 생성된 메모리 이름을 cursors로 하겠다
sql = "select * from "+ table_name
cursors.execute(sql) # sql 쿼리를 실행해서 결과를 cursors라는 메모리에 담음
rows = cursors.fetchall() # 메모리에 있는 결과를 rows에 넣음
colname = cursors.description # emp테이블의 컬럼이름이 포함된 정보를 colname에 담음
col = []
for i in colname:
col.append(i[0].lower()) # emp테이블의 컬럼이름을 소문자로 변경해서 col리스트에 담음
dataframe = pd.DataFrame(rows, columns = col)
return dataframe문제505. 위의 mysql_connect 함수를 자동화 스크립트에 추가하시오.
1. result라는 전역변수 선언
result = None # 전역변수 선언2. 데이터 분석 함수 (data_service() )에 코드 추가
elif num == 8: # mySQL연동
table_name = input('result라는 변수에 담고 싶은 테이블명을 입력하세요.\n▶ ')
global result
result = mysql_connect(table_name)
result3. 데이터 프레임 및 변수들은 함수 안에서 나온 결과이므로 출력할 때 메소드 명을 붙여야 함.
# 호출
m.result
# 변수로 만들어서 사용하는 방법
emp = m.result
emp
문제506. 직업, 직업별 토탈월급을 출력하시오. (판다스)
emp.groupby('job')['sal'].sum().reset_index()문제507. 위의 결과를 막대그래프로 그리시오.
a = emp.groupby('job')['sal'].sum().reset_index()
a.plot(kind='bar', color = 'lightsteelblue', x = 'job')
문제508. 부서번호, 부서번호별 토탈월급을 막대그래프로 시각화 하시오.
a = emp.groupby('deptno')['sal'].sum().reset_index()
a.plot(kind='bar', color = 'rosybrown', x = 'deptno')
ㅇ 판다스 그래프에 대한 메뉴얼을 주피터 노트북에서 사용하는 방법
import pandas as pd
help(pd.DataFrame.plot)
문제509. (복습문제) emp 데이터 프레임에서 입사일을 출력하는데 연도 4자리만 출력하시오
emp['hiredate_year'] = emp['hiredate'].apply(lambda x: str(x)[:4]) # 문자형으로 바꿔서 슬라이싱하거나
emp['hiredate_year'] = emp['hiredate'].apply(lambda x: x.year) # 연도만 출력
emp['hiredate_year']ㅇ날짜형 함수
.year() : 연도만 출력
.month() : 월만 출력
.day() : 일만 출력
문제510. 입사한 연도 4자리와 입사한 연도별 평균 월급을 출력하시오.
emp.groupby('hiredate_year')['sal'].mean().reset_index()문제511. 위의 결과를 막대그래프로 그리시오.
a = emp.groupby('hiredate_year')['sal'].mean().reset_index()
a.plot(kind = 'bar', color = 'thistle', x = 'hiredate_year')
문제512. (오늘의 마지막 문제) 데이터 분석 자동화 스크립트에 9번째로 오라클과의 연동을 추가하시오.
# 9. Oracle 연동
def oracle_connect(table_name):
import cx_Oracle
import pandas as pd
dsn = cx_Oracle.makedsn("DESKTOP-5QBH3AQ", 1521, 'xe')
db = cx_Oracle.connect('c##scott','tiger', dsn )
cursor = db.cursor()
sql = "select * from "+table_name
cursor.execute(sql)
row = cursor.fetchall()
colname = cursor.description
col = []
for i in colname:
col.append(i[0].lower())
df_oracle = pd.DataFrame(row, columns = col)
return df_oracle
'Study > class note' 카테고리의 다른 글
| python / 막대그래프, 원형그래프, 히스토그래프 (0) | 2022.01.11 |
|---|---|
| python / 파이썬 데이터베이스 연동 시 주의점1(날짜형 데이터) (0) | 2022.01.11 |
| python / 파이썬 오라클 연동 (0) | 2022.01.09 |
| python / 웹스크롤링 이미지 (0) | 2022.01.07 |
| python / 웹스크롤링 감성분석 (0) | 2022.01.05 |
