[데이터 아키텍처] DA 구축 예제 | 2. 현행 DB 준비 (feat. 파이썬)

[데이터 아키텍처] DA 구축 예제 | 2. 현행 DB 준비 - 파일 업로드 (feat. 파이썬)

     < 목차 >

1. 개요 - 오픈 데이터 활용

2. 현행 DB 준비 - 파일 업로드 (feat. 파이썬)

3. 리버스 모델링

4. 현행 논리 데이터 모델링

5. 현행 개념 데이터 모델링

6. 현행 모델 문제점 분석

7. 목표 개념 데이터 모델링

8. 목표 논리 데이터 모델링

9. 목표 물리 데이터 모델링

 

공공데이터포털(data.go.kr)에서 다운로드한 『전국 병의원 및 약국 현황 2023.03..zip』 파일을 테이블로 업로드하여 현행 시스템으로 구성한다.

 

엑셀 파일에 대한 테이블명을 아래와 같이 정의한다.

엑셀 파일에 대한 테이블명

 

12개의 엑셀 파일을 테이블로 업로드하기 위하여 파이썬 프로그램을 작성한다.  본 포스트에서는 해당 파이썬 프로그램을 기술한다.

 

해당 파이썬 프로그램의 주요 기능은 다음과 같다.

• 12개의 엑셀 파일명을 병의원정보.cfg 파일에 등록
• 파이썬의 pandas 라이브러리의 read_excel() 함수를 사용하여 엑셀 파일 read
• read_excel() 함수에서 read 한 데이터에서 해당 항목의 data type을 추출하여 CREATE TABLE 구문 생성
• CREATE TABLE 구문을 실행하여 테이블 생성
• 생성된 테이블에 데이터 INSERT

파이썬 소스 프로그램은 아래와 같다.

import pandas as pd
import cx_Oracle as co
from datetime import datetime

def main():
    # read config file
    cfg_path = 'C:/Users/pgsus/Documents/Python Scripts/data/전국병의원 및 약국현황 2023.03/'
    cfg_file = '병의원정보.cfg'

    xls_list = []
    f = open(cfg_path + cfg_file, 'r', encoding='UTF8')
    f_lines = f.readlines()
    for fl in f_lines:
        fl = fl.strip()
        xls_list.append(fl)
    f.close()

    # oracle client library 설정 & connect
    co.init_oracle_client(lib_dir=r"C:\Users\pgsus\Downloads\instantclient_19_10")
    con = co.connect('soctt', 'tiger', '192.110.110.110:1521/orcl') # ip 임의 변경 

    # open cursor
    cur = con.cursor()

    # 엑셀 파일을 순차적으로 read 및 DB insert
    for xf in xls_list:
        print('starting ...', xf)
        df = pd.read_excel(cfg_path + xf).fillna('')
        # 엑셀 파일에서 table명 생성
        table_nm = xf.replace('.xlsx','').replace('.','_')
        # column명 추출
        col_lst = df.columns.values.tolist()
        # CREATE TABLE 구문 생성
        ct_stmt = "CREATE TABLE " + table_nm + " ( "
        # insert의 values (:1, :2 .. ) 구문 생성
        ins_values = " VALUES ("
        # table의 컬럼 및 datatype 정의
        for i, col in enumerate(col_lst, start=1):
            # 컬럼명 변환 : 예) 의과일반의 인원수 -> 의과일반의_인원수, 좌표(X) -> 좌표_X
            ct_stmt += col.replace(' ','_').replace(')','').replace('(','_')
            # VALUE (:1, :2 ...) 구문 생성
            ins_values += ':' + str(i)

            if df[col].dtypes == 'object':
                ct_stmt += ' VARCHAR2(500)'
            elif df[col].dtypes == 'int64':
                ct_stmt += ' NUMBER'
            elif df[col].dtypes == 'float64':
                ct_stmt += ' NUMBER'
            else:
                ct_stmt += ' VARCHAR2(200)'
            if col != col_lst[-1]:      # check last element of list
                ct_stmt += ', '
                ins_values += ','
            else:
                ct_stmt += ') '
                ins_values += ') '
            i += 1
        # execute Create Table statement
        try:
            cur.execute(ct_stmt)
            print('Create table ', table_nm, '... completed.')
        except co.DatabaseError as e:
            error, = e.args
            if error.code == 955:
                print('table already exists')
            else:
                print(error.message)
        # insert 구문 생성
        ins_stmt = "INSERT INTO " + table_nm + ins_values
        # convert pandas to list
        rows = df.values.tolist()
        # insert 수행
        for i, row in enumerate(rows):
            cur.execute(ins_stmt, row)
        print('rows inserted: ', i+1)
        # commit
        con.commit()
        print('commit : ', datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
    # close
    cur.close()
    con.close()
    
if __name__ == '__main__':
    s_time = datetime.now()
    print('start time :', datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
    main()
    e_time = datetime.now()
    print('end_time : ', e_time.strftime('%Y-%m-%d %H:%M:%S'))
    elapsed_time = e_time - s_time
    print('elapsed_time(s) : ', elapsed_time.total_seconds())

 

생성된 테이블의 결과는 아래와 같다.

생성된 테이블 목록