[파이썬] 오라클 DB 연동 - DB 조회, 업데이트, cx_Oracle

오라클 DB 조회

---

오라클 DB 연동 - DB 조회, 업데이트, cx_Oracle

오라클 DB 연동 - DB 연결, cx_Oracle에 이어서 작성합니다.

---

들어가며

---

데이터 분석을 하기 위해 DB연동을 해야 할 때, 파이썬은 매우 편리한 언어입니다. cx_Oracle을 통해 DB를 조회하고, 업데이트하는 방법을 소개합니다.

---

• DB 조회 | SELECT
• DB UPDATE
• 전체 코드

---

먼저, 필요한 패키지 import와 함께 DB에 연결합니다. 

import os
import pandas as pd
import cx_Oracle

LOCATION = r"C:/instantclient-basic-windows.x64-21.3.0.0.0/instantclient_21_3/"
os.environ['PATH'] = LOCATION + ';' + os.environ['PATH'] # 환경변수 등록

# DB 연결
connect = cx_Oracle.connect('username','password','ip:port/database')
cursor = connect.cursor()

---

DB 조회 | SELECT

---

DB를 조회할 때는 pandas의 read_sql을 활용합니다. 파이썬 코드 내부에서 설정한 변수를 통한 필터링도 가능합니다.

---

임의로 생성한 IMSI_DATA 테이블을 읽어보겠습니다. '''~''' 안에 쿼리를 작성하고, 위에서 설정한 DB정보를 con을 통해 연결합니다.

df = pd.read_sql('''SELECT * FROM IMSI_DATA''', con=connect)

---

설정한 변수를 통해 필터링도 가능합니다. 쿼리 앞에 f를 붙이고, {} 안에 변수를 넣습니다.

RT_ID = 'SYSTEM'
df = pd.read_sql(f'''SELECT * FROM IMSI_DATA WHERE RT_CD = {RT_ID}''', con=connect)

반응형

---

DB UPDATE

---

파이썬 내부에서 생성한 데이터를 DB에 업데이트 할 때는 중복이 일어나지 않게 INSERT 보다는 MERGE INTO를 사용하는 것을 추천합니다. DB를 업데이트 하는 방법은 여러 가지가 있지만, 아래 코드는 저의 많은 시행착오 끝에 가장 편리하고 빠른 방법을 소개합니다. 저는 이 방법이 가장 편하더라고요 :)

---

아래 result_df를 DB에 업데이트할 데이터프레임이라고 가정해보겠습니다. SDATE와 DATE_CD를 키값으로 하는 데이터프레임입니다.

result_df = pd.DataFrame({'DATE':['20240101','20240101','20240102'],
                          'DATE_CD':['00','01','00'],
                          'RT_CD':['SYSTEM','SYSTEM','MANUAL'],
                          'GLOBAL_NO':['32521','33085','34896']})
result_df # key: ['DATE', 'DATE_CD']

result_df

---

업데이트하기 전, 딕셔너리로 타입을 변경합니다.

result_df_rec = result_df.to_dict('records') # 타입 변환

---

SDATE와 DATE_CD를 키값이므로 아래와 같이 쿼리문을 작성합니다. 쿼리문 뒤에 딕셔너리 변수(result_df_rec)를 잊지 마세요!

cursor.executemany('''merge into IMSI_DATA TT using dual 
    on (TT.SDATE = :SDATE and TT.DATE_CD = :DATE_CD) 
    when matched then update set
    TT.RT_CD = :RT_CD, TT.GLOBAL_NO = :GLOBAL_NO
    when not matched then insert (SDATE, DATE_CD, RT_CD, GLOBAL_NO) values (:SDATE, :DATE_CD, :RT_CD, :GLOBAL_NO)''', result_df_rec)

---

DB에 적용하기 위해 connect.commit()을 통한 커밋을 꼭 실행해야 합니다. 커밋 후에는 연결을 종료합니다.

connect.commit() # commit

cursor.close() # cursor 종료
connect.close() # connect 종료

---

전체 코드

---

## package import
import os
import pandas as pd
import cx_Oracle

## DB CONNECT
ic_loc = r"C:/instantclient-basic-windows.x64-21.3.0.0.0/instantclient_21_3/"
os.environ['PATH'] = ic_loc + ';' + os.environ['PATH']
connect = cx_Oracle.connect('username','password','ip:port/database')
cursor = connect.cursor()

## DB READ
df = pd.read_sql(f'''SELECT * FROM IMSI_DATA''', con=connect)

## DB UPDATE
result_df = pd.DataFrame({'DATE':['20240101','20240101','20240102'],
                          'DATE_CD':['00','01','00'],
                          'RT_CD':['SYSTEM','SYSTEM','MANUAL'],
                          'GLOBAL_NO':['32521','33085','34896']})

result_df_rec = result_df.to_dict('records') # 타입 변환

cursor.executemany('''merge into IMSI_DATA TT using dual 
    on (TT.SDATE = :SDATE and TT.DATE_CD = :DATE_CD) 
    when matched then update set
    TT.RT_CD = :RT_CD, TT.GLOBAL_NO = :GLOBAL_NO
    when not matched then insert (SDATE, DATE_CD, RT_CD, GLOBAL_NO) values (:SDATE, :DATE_CD, :RT_CD, :GLOBAL_NO)''', result_df_rec)
    
## COMMIT
connect.commit()

## DB DISCONNECT
cursor.close()
connect.close()

---