Excel 또는 csv파일을 Pandas을 이용하여 DB(Oracle 등)에 insert 하는 방법

서론

엑셀파일을 DB에 넣어서 관리하고 싶을때 Python 의 pandas를 이용하여 간단하게 입력하는 방법을 소개하겠습니다.

먼저 python을 이용하지 않고 DB에 입력을 할때는 Table 생성 > data import의 과정이 필요한데

여기서 엑셀 컬럼별 데이터 형식을 일일이 지정하고 컬럼명을 만들어 주는게 귀찮아서 파이썬으로 한번에 입력하는 방법을 찾게되었다.

 

준비사항

필요 패키지

• pandas
• oracledb or cx_Oracle
• sqlalchemy

 

코드

from sqlalchemy import create_engine,types
import pandas as pd
import oracledb

#sqlalchmy 에서 oracledb를 사용하기 위해서 셋팅값 수정(default는 cx_Oracle 패키지 사용)
import sys
oracledb.version = "8.3.0"

#oracle client 위치 지정
oracledb.init_oracle_client(lib_dir="C:/Oracle/product/19.0.0/client")
sys.modules["cx_Oracle"] = oracledb

# 데이터 불러오기(csv or excel)
df_excel = pd.read_excel('샘플 데이터.xlsx',dtype=str)

id = 'id'
pw = 'password'
dsn = 'tnsname'
engine = create_engine(f'oracle://{id}:{pw}@{dsn}')

# dataframe에 있는 값을 그대로 insert시 object 타입의 컬럼은 clob으로 생성되어
# 해당 데이터에 맞는 varchar 타입으로 변경
# 값이 Nan일땐 기본값으로 varchar(100)으로 생성
dtypes = {col_name: types.VARCHAR(df_excel[col_name].str.len().max() if not pd.isna(df_excel[col_name].str.len().max()) else 100) for col_name in df_excel.columns}

# 데이터 insert 테이블이 없을때 자동으로 Create됨
with engine.connect() as conn:
    df_excel.to_sql(name='TMP_TABLE_NAME', con=conn, if_exists='replace', index=False,dtype=dtypes)

 

결론

위에 샘플소스중 dtype을 지정하지 않으면 object 타입의 컬럼은 clob으로 생성되는 부분이 있어 해당 부분을 varchar로 변경하는 소스를 작성하였다.

원하는 데이터 타입의 형식이 있는경우 해당 부분을 변경하여 사용하면 된다.

샘플 소스는 oracle을 기준으로 작성하였지만, 다른 Database로도 사용이 가능하다.