[Python] 파이썬 데이터프레임(엑셀 데이터) MySQL에 저장하기

목표 : 매일 배치로 다운로드 받는 엑셀 데이터에 현재 시간 컬럼을 추가해 MySQL에 insert하기

---

 

처음 완성한 코드

 

import pandas as pd
import pymysql

# DB 정보
host = "[host]"
user = "[user]"
password = "[pwd]"
database = "[database]"

# 엑셀 파일 불러오기
df = pd.read_excel("[.xls]", nrows=1000)
print(df)

# DB 연결
conn = pymysql.connect(host=host, user=user, password=password, db=database)
curs = conn.cursor(pymysql.cursors.DictCursor)

# DB insert
sql = 'INSERT INTO [테이블명] ([컬럼], [컬럼1], ...) VALUES(%s, %s, ..., now())'

for idx in range(len(df)):
	curs.execute(sql, tuple(df.values[idx]))

conn.commit()

#종료
curs.close()
conn.close()

*nrows=1000 : 엑셀 데이터 1000줄만 읽어온다

for문을 돌려, 데이터프레임의 튜플을 한줄씩 DB에 저장.

 

참고 : https://juun42.tistory.com/23

 

 

 

 

 

해결한 오류

첫번째 오류 :
pymysql.err.DataError: (1366, "Incorrect string value: '\\xEC\\xA0\\x91\\xEC\\x88\\x98...' for column `*`.`*`.`*` at row 1")

원인 : 테이블 인코딩 설정 문제

참고 : https://mitny.tistory.com/208

해결 : 테이블 인코딩 설정 변경
alter table [테이블] convert to charset utf8;





두번째 오류 :
raise ProgrammingError("%s can not be used with MySQL" % s)
pymysql.err.ProgrammingError: nan can not be used with MySQL

원인 : 데이터프레임에 빈 값이 NaN으로 채워져 있는데, 이 부분을 MySQL에 넣어주면서 오류 발생

해결 : 해당 값을 None으로 변경. MySQL에는 NULL 값으로 들어감.

df = df.where(pd.notnull(df), None)

*df.where : 참값에 대해서는 첫번째 처리를 하고, 참이 아니면 두번째 처리를 한다.
데이터프레임 중 notnull이 참이면, 즉, null이 아닌 값은 그대로 값이 들어가고, null인 값은 None을 넣어준다.

참고 : https://ndlessrain.tistory.com/entry/nan-can-not-be-used-with-MySQL 

 

세번째 오류:
pymysql.err.DataError: (1406, "Data too long for column '*' at row 1")

원인 :  말 그대로 DB 컬럼에 설정된 길이보다 데이터로 긴 값이 들어가서 난 오류 

해결 : 해당 오류나는 컬럼 길이 다 늘려줌

 

 

 

최종 코드

 

import pandas as pd
import pymysql

# DB 정보
host = "[host]"
user = "[user]"
password = "[pwd]"
database = "[database]"

# 엑셀 파일 불러오기
df = pd.read_excel("[.xls]", nrows=1000)
df = df.where(pd.notnull(df), None)
print(df)

# DB 연결
conn = pymysql.connect(host=host, user=user, password=password, db=database)
curs = conn.cursor(pymysql.cursors.DictCursor)

# DB insert
sql = 'INSERT INTO [테이블명] ([컬럼], [컬럼1], ...) VALUES(%s, %s, ..., now())'

for idx in range(len(df)):
	curs.execute(sql, tuple(df.values[idx]))

conn.commit()

#종료
curs.close()
conn.close()

+
테스트 완료 후
insert into -> replace into 로 변경
매일 바뀌는 데이터 값을 덮어쓰기 해줘야 하기 때문에


replace into
기존의 ROW는 삭제되고 현재 실행한 쿼리의 ROW가 삽입된다.
Primary Key로 Auto Increment 옵션을 사용하고 있다면 값이 증가된 ROW가 남는다.

출처 : https://til.songyunseop.com/mysql/some_case_insert_with_duplicated_key.html