[복습] Python DBMS 연동

파이썬은 메모리만 갖고 있는 소프트웨어로 따라서 디스크 공간이 없다. 데이터를 저장하려면 외부 파일에 저장하여야 한다. 즉 파이썬은 저장공간(디스크)이 있는 SW가 아니므로 파이썬에서 만든 객체는 반드시 외부 파일 혹은 DBMS에 저장될 필요가 있다.

 

 

1. 데이터 불러오기

  - 외부 파일: pd.read_csv

emp = pd.read_csv('emp.csv')

  - DBMS 데이터: 각 DBMS 연동 후 데이터 가져오기

 

 

2. 데이터 저장

  - 외부 파일: pd.read_csv

emp.to_csv('emp_20240122.csv', index = False)

emp.to_csv('emp_20240122.txt', index = False)

  - DBMS 데이터: 각 DBMS 연동 후 데이터 저장

 

 

 

ORACLE DBMS 연동 패키지 설치

0. 패키지 설치(os에 설치(cmd 창에서 실행))

pip install sqlalchemy

conda install -c anaconda cx_oracle

 

import sqlalchemy

import cx_Oracle

from sqlalchemy import create_engine

 

1. target DBMS 정보

  - IP

  - PORT

  - DB_NAME(SID)        # TNS NAME이라고도 함

  - USERID

  - PASSWD 

이렇게 총 5가지가 필요함

위 정보는 cmd 창에서 lsnrctl status 로 확인이 가능하다.

 

 

2. DB 연동

con1 = create_engine('oracle+cx_oracle://scott:scott계정의passwd@localhost:포트번호/DB_NAME')

scott 계정으로 접속하려고 한다.

 

** DB connection 과정에 create_engine은 정상 작동. 그러나 실제 데이터를 가져오는 과정에 connection 문제가 있으면 error 발생

 

pd.read_sql('select sysdate from dual', con = con1) 로 connection 테스트 해보기

 

** connection에 문제가 있는 경우 해결 방법 모색

1) create_engine에 명시된 정보가 잘못됐을 가능성(오타, 잘못된 정보)

→ cmd에서 직접 sqlplus scott/scott계정 passwd  접속 시도

→ cmd에서 sqlplus / as sysdba  하여 슈퍼 권한을 갖고 접속한 후 alter user scott account unlock identified by scott계정 바꿀 비밀번호;

 

2) DBMS 자체 문제(버전관리 → 19c server path 가 가장 상단에 있는지 확인)

 

 

 

3. 조회(DB에서 데이터 불러오기)

pd.read_sql('select ename, sal from emp where deptno = 10', con = con1)

 

 

4. DB에 데이터 저장(테이블 생성)

emp3.to_sql(name,                     # 저장할 테이블명(소문자)

                     con,                        # db connection 이름

                     schema,                 # 테이블 소유자(username)

                     if_exists = fail,        # 테이블이 이미 존재할 경우 액션(fail, replace, append)

                     index =  True)         # index 저장 여부

 

schema: 한 유저의 전체(테이블, index, 권한 기타 등등 유저에 파생된 전체 객체)

 

 

 

728x90

 

 

위 내용으로 연습문제를 풀어보자

 

1. DBMS에서 emp 테이블을 불러온 뒤 각 부서별 평균 급여를 모든 행에 출력하여 저장하여라(MEAN_SAL)

 

이렇게 emp table을 불러왔다.(가린 내용은 댓글 참고)

 

 

 

 

2. 각 부서별로 평균 급여보다 높은 급여를 받는 직원의 이름, 부서번호, 급여, 평균급여를 출력하여라.

 

1번 문제에서 구한 mean_sal을 이용하여 문제에서 필요한 정보를 일단 emp2로 저장하였다.

 

 

여기서 query 메서드를 사용하였는데, query 메서드란 조건식을 문자열로 입력받아 해당 조건에 만족하는 행을 추출하여 출력해주는 함수이다.  query를 사용하면 쉽게 추출할 수 있다.

이제 emp3을 DBMS로 저장하여보자.

 

 

to_sql을 사용하여 oracle DBMS의 테이블로 저장하면 되겠다.

 

잘 저장되었는지 확인해보자.

 

 

확인 완료 ! 

 

 

** 주의: 접속한 계정이 테이블 생성 권한이 있을 때만 가능하다.