python / 파이썬 오라클 연동

151 파이썬과 Oracle 연동

회사의 비즈니스 데이터는 전부 오라클이나 mySQL 그리고 MSSQL에 들어있음.

오라클에 있는 데이터를 분석하고자 한다면 SQL을 이용하거나 파이썬, R을 이용하면 되는데 SQL은 오라클에서 바로 수행할 수 있으므로 실시간으로 변경되는 데이터를 바로 볼 수 있음.

그런데 파이썬이나 R로 오라클의 데이터를 보려면 csv파일로 내려야함. 변경되지 않는 데이터라면 csv 파일로 내려서 파이썬에서 봐도 되지만 실시간 변경 데이터라면 일일이 csv파일로 내릴 수 없음.

따라서 오라클과 파이썬을 연동해야함.

 

예제1. 오라클 정상인지 확인

도스창 열고 다음과 같이 접속

sqlplus "/as sysdba"

sqlplus c##scott/tiger

문제476. (복습문제) 직업이 SALESMAN인 사원들의 이름과 월급과 직업을 출력하는데 월급이 높은 사원부터 출력하시오.

select ename, sal, job
 from emp
 where job = 'SALESMAN'
 order by sal desc;

문제477. (복습문제) DALLAS에서 근무하는 사원들의 이름, 부서위치를 출력하시오.

select e.ename, d.loc
 from emp e, dept d
 where e.deptno = d.deptno and d.loc = 'DALLAS';

예제2. 오라클과 파이썬을 연동하려면 리스너가 정상인지 확인해야함.

리스너(Listener)? 오라클 쪽에 떠있는 프로세서로 경비원 같은 역할을 함.

오라클로 접속하려는 유저들을 허용하게끔하는 프로세서.

 

ㅇ리스너가 정상인지 아닌지 확인

lsnrctl satatus

파이썬에서 오라클로 접속 하려면 3가지 정보를 알아야 함.

- 서비스 이름 : xe 

- IP주소(도스창에서 HOST주소 확인) >  HOST=DESKTOP-5QBH3AQ

- 포트(port) 번호 > PORT=1521

sqlplus c##scott/tiger@DESKTOP-5QBH3AQ:1521/xe

@컴퓨터이름(HOST주소):포트번호/서비스이름

위의 3가지 정보로 도스창에서 오라클로 접속

 

예제3. 아나콘다 프롬프트 창을 열고 cx_Oracle모듈을 설치

conda install cx_Oracle

> 오라클과 파이썬을 연동하는 모듈 설치

 

예제4. 파이썬과 오라클을 연동하는 코드를 작성

주피터 노트북에서 하면 됨~

import cx_Oracle
import pandas as pd

dsn = cx_Oracle.makedsn("DESKTOP-5QBH3AQ", 1521, 'xe') # 위에서 알아낸 3가지 정보 모두 입력
db = cx_Oracle.connect('c##scott','tiger', dsn ) # 오라클에 접속할 유저명과 패스워드 그리고 위의 정보
cursor = db.cursor()  # SQL수행결과 데이터를 담을 메모리 이름을 cursor로 선언
cursor.execute("""select * from emp""") # SQL 쿼리문의 결과가 cursor 메모리에 담김.
row = cursor.fetchall()  # cursor 메모리에 담긴 결과를 한번에 row변수에 담음 >  딕셔너리 형태로 담김
emp = pd.DataFrame(row)
emp

예제5. 위의 결과는 컬럼명이 출력되지 않고 있음. 컬럼명 정보도 가져와서 출력될 수 있게 하세요.

colname = cursor.description # emp 테이블에 대한 컬럼명 정보 받아오는 코드
col = []  # 컬럼명만 담을 리스트 생성
for i in colname:
    col.append(i[0])
#print(col)  #['EMPNO', 'ENAME', 'JOB', 'MGR', 'HIREDATE', 'SAL', 'COMM', 'DEPTNO']

emp.columns = col
emp

문제 478. 컬럼명을 모두 소문자로 바꾸시오.

colname = cursor.description 
col = [] 
for i in colname:
    col.append(i[0].lower())  # 담을 때 소문자로 변환해서 담음

emp.columns = col
emp

	# 또는
    
emp.columns = col
emp.columns = emp.columns.str.lower()  # 컬럼을 문자열로 변환해서 소문자로 변환

문제479. (복습문제) 부서번호가 30번인 사원들의 이름과 월급과 부서번호를 출력하시오. (판다스로 출력)

emp[['ename','sal','deptno']][emp['deptno'] == 30]

문제480. (복습문제) 오라클에 c##scott으로 도스창 또는 sqldeveloper로 접속해서 emp테이블을 update하는데 부서번호가 30번인 사원들의 월급을 전부 0으로 변경하시오.

update emp
set sal = 0
where deptno = 30;

문제481. (복습문제) 지금 update한 데이터를 데이터 베이스에 영구히 저장하고 반영해라

commit;

> 파이썬에서 확인해도 결과가 반영되었음을 확인할 수 있음.

 

문제482. 오라클에서 dept테이블의 데이터를 불러와서 dept 데이터 프레임을 생성하시오.

import cx_Oracle
import pandas as pd

dsn = cx_Oracle.makedsn("DESKTOP-5QBH3AQ", 1521, 'xe') 
db = cx_Oracle.connect('c##scott','tiger', dsn ) 
cursor2 = db.cursor()
cursor2.execute("""select * from dept""")
row2 = cursor2.fetchall()
dept = pd.DataFrame(row2)
colname2 = cursor2.description
col2 = []
for i in colname2:
    col2.append(i[0].lower())
    
dept.columns = col2
dept

문제483. (복습문제) SQL을 판다스로 구현하시오.

-- SQL
select e.ename, d.loc
 from emp e, dept d
 where e.deptno = d.deptno ;

# 판다스
result = pd.merge(emp, dept, on = 'deptno')
result[['ename','loc']]

문제484. 오라클에 있는 우리반 emp14테이블을 판다스 데이터 프레임으로 만드시오.

import cx_Oracle
import pandas as pd

dsn = cx_Oracle.makedsn("DESKTOP-5QBH3AQ", 1521, 'xe') 
db = cx_Oracle.connect('c##scott','tiger', dsn ) 
cursor3 = db.cursor() 
cursor3.execute("""
select *
from emp14
 """) 
row3 = cursor3.fetchall()  
emp14 = pd.DataFrame(row3)

colname3 = cursor3.description 
col3 = [] 
for i in colname3:
    col3.append(i[0].lower())

emp14.columns = col3
emp14

문제485. (오늘의 마지막 문제) 아래와 같이 우리반 테이블의 나이와 나잇대별 인원수를 출력하는데, 인원수가 높은 것부터 출력하시오.

import cx_Oracle
import pandas as pd

dsn = cx_Oracle.makedsn("DESKTOP-5QBH3AQ", 1521, 'xe') 
db = cx_Oracle.connect('c##scott','tiger', dsn ) 
cursor3 = db.cursor() 
cursor3.execute("""
select *
from emp14
 """) 
row3 = cursor3.fetchall()  
emp14 = pd.DataFrame(row3)

colname3 = cursor3.description 
col3 = [] 
for i in colname3:
    col3.append(i[0].lower())
emp14.columns = col3

result = emp14.groupby('age')['ename'].count().reset_index()
result.columns = ['age', 'cnt']
result.sort_values(by = ['cnt'], ascending = False)