Python Excel to MySQL

대량 엑셀을 효율적으로 ...

배경

현업이 IT부서에 Raw Data를 뽑아달라고 했다.

뽑아보니 레코드 수도 그렇지만 700Mb가 넘는 사이즈였다...

이걸로 뭘 하겠냐고 물어보니 엑셀함수를 이용해서 데이터 검증을 하겠다고 한다...

일단 뽑아줄테니 이걸로 업무하기는 어려울것 같다, 화일 여는데만 10분걸리고 수정/저장할때마다 10분씩 걸릴거다..

라고 하였다. ( 매주 하던 작업이고 할때마다 하루종일 걸린다고 했다..)

 

업무처리 방식 제안

도저히 생산성이 안나올테니 걸어볼 엑셀 함수와 최종 어떤 작업할껀지 알아내고, IT부서에서 기본적인 엑셀 작업을 해주기로 했다.

 

Python 활용..

Python script를 통해 제공한 엑셀을 내 Local DB( MySQL ) 로 로드하여 각종 함수를 적용하고 다시 그것을 엑셀로 뽑아서 주면 된다.

다른 라이브러리도 있지만 나는 pandas 를 익숙하게 잘 쓰고 싶어서 pandas로 하고 python orm은 sqlalchemy를 활용하기로 했다.

 

따로 엑셀함수등 구현은 업무마다 많이 상이하니..

"Excel File을 DB에 로드" 하는 부분만 예시로 기재한다.

import pandas as pd
from sqlalchemy import create_engine
table = pd.read_excel('엑셀화일명.xlsx', sheet_name='쉬트명', header=0,)
engine = create_engine("mysql+pymysql://DB계정:비밀번호@127.0.0.1:3306/스키마명", encoding='utf-8-sig')
table.to_sql(name='테이블명', con=engine, if_exists='append', index=False)

위 Script는 연습해서 외워서 필요할때 테이블만 만들고 간단히 메일쓰듯이 할 수 있으면 좋겠다.

이거 하나 해주니 현업이 엄청 좋아했다. 덩달아 나의 평가도 올라갔다.

---

잠깐! 꼭 알아야 할 것

1. Encoding

내 Table 의 첫번째 컬럼은 Bigint(20) 이었다.

그런데 들어가다가 오류가 났다. 분명히 눈엔 47748649 만 보이는데...Why?

위 engine 의 encoding option을 보면 처음에는 utf-8 로 했다가 utf-8-sig 로 하니까 잘된다.

utf-8의 BOM문제라고 한다.  그래서 그냥 utf-8 이 아닌 utf-8-sig ( 약어는...signature로 예상됨. )

로 변경하니 잘된다. 따라서 위 오류나면 ( \ufeff --> \u : 유니코드 의미, feff : UTF-16 Big Endian 의미)

즉 UTF-16 Big Endian으로 Encoding된 문자를 utf-8로 해서 앞에 BOM 문자가 포함되어 오류남.

위처럼 하면 utf-8 로 인코딩하되, signature를 보고 input 문자의 encoding을 확인하라는 의미임.

참고 URL : http://blog.wystan.net/2007/08/18/bom-byte-order-mark-problem

 

 2. sqlalchemy , pymysql 등 환경

pip install pandas
pip install pymysql
pip install sqlalchemy

---

따라하기 예제코드

아래는 Full Code 이며, 계정과 비밀번호, 스키마명만 변경해서 하면 된다.

테이블구조는 예제 실습을 위해 임의로 정하였다.

 

내 Local에 테이블이 없는 상태이다.

mysql table 확인

처리해야 할 엑셀 화일 샘플이다.( raw_data_test.xlsx)

아래 코드 실행

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://계정:비밀번호@127.0.0.1:3306/스키마명", encoding='utf-8-sig')

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.mysql import BIGINT
from sqlalchemy import Column, String, Integer

Base = declarative_base()

class BillRawTest(Base):
    __tablename__ = 'bill_raw_test'
    __table_args__ = {'extend_existing': True}
    bill_id = Column(BIGINT(20), primary_key  = True)
    item = Column(String(20))
    amount = Column(Integer)

metadata = Base.metadata
metadata.create_all(engine)

df = pd.read_excel('~/Downloads/bill_raw_test.xlsx', sheet_name='Sheet1', header=0)
df.to_sql(name='bill_raw_test', con = engine, if_exists='append', index=False)

 

실행 후 DB조회

 

끝.