pandas to_sql 사용법(오류 해결방법)

pandas에서 to_sql을 하다 보면 

( Background on this error at: http://sqlalche.me/e/14/9h9h)

이런 문구를 자주 마주합니다.

 

---

예시

대충 엑셀 자료는 이렇다고 가정하고

 

SQL tools 이름 : HeidiSQL

테이블은 이렇다고 가정하겠습니다.

 

이렇게 여러 가지 복합적인 이유로 SQL에 들어가지 않게 됩니다.

(pandas to_sql의 제일 큰 문제가 엑셀의 모든 데이터를 한번에 집어넣기 때문에 오류가 많이 납니다...)

그럼 일단 SQL에 맞게 엑셀을 변경하셔야 합니다.

 

변경해야 되는 부분을 잘 파악해야 하는데 저 같은 경우는

첫 번째 : 1번째, 2번째 row 값이 맞지 않습니다.(엑셀에 1번째 row가 4번째 row에 적용이 안되기 때문에 가공을 한번 하셔야 합니다..)

두 번째 : 필요 없는 이름 있습니다.

세 번째 : PRIMARY KEY로 선언된 '승인확인' 이 있습니다.

(SQL의 VARCHAR길이, INT길이, NULL허용, 부호 있음&없음, 기본값 등 잘 확인하셔야 합니다.)

마지막으로 젤 중요한 ',' 부분을 제거해야 합니다!!(이거 때문에 삽질 많이 했습니다;;)

 

소스코드

확인을 했다면 코드는 이렇습니다.(이건 테스트 이기 때문에 자신이 맞게 수정하셔야 합니다.)

import time
import os, glob
import pandas as pd

from sqlalchemy import create_engine

# 해당 .py파일 위치
path=os.path.dirname(os.path.realpath(__file__))

# 다운로드 받을때까지 대기(크롤링해당)
count = 1
while not glob.glob(os.path.join(path,'거래내역*.xls')):
    print(count)
    count+=1
    time.sleep(1)

# 엑셀파일 확인
xlsFiles = glob.glob(os.path.join(path,'거래내역*.xls'))

######   여기서부터   ######
# 설명(블로그)의 마지막 부분
df = pd.read_excel(xlsFiles[-1],thousands = ',')

# 설명(블로그)의 두번째 부분
df = df.drop(index=[0,1], axis=0)
#만약 마지막 행을 삭제하고 싶을때
#df= df[:-1]
print(df) #확인용

### 설명(블로그)의 첫번째(빨간 글씨) 부분 ###
# column 이름정의
new_column_names = []
for i,col_name in enumerate(df.columns):
    if (col_name[:7].strip()=="Unnamed"):
        new_column_names.append(df.iloc[0,i].replace(" ", "_"))
    # 엑셀의 1번째 row이름 입력
    elif (col_name=="TEST DATA"):
        new_column_names.append(df.iloc[0,i].replace(" ", "_"))
    else:
        base_name = col_name
        new_column_names.append(base_name)
print(new_column_names) #확인용

# dict 키 쌍으로 변환
dictionary = dict(zip(df.columns.tolist(),new_column_names))
# 열 이름 바꾸기
df = df.rename(columns=dictionary)
# print(df)
# 열이름 바꾼후 기존 column 제거
df = df.iloc[1:].reset_index(drop=True)
print(df)
###########################################

# 설명(블로그)의 첫번째 부분
# 필요없는 데이터 삭제
df = df.drop(columns=['의뢰인/수취인', '구분','거래점'], axis=1)
print(df)

# 설명(블로그)의 세번째 부분
# 승인번호 추가
df.insert(0,'승인확인',['은행 : '+str(df.iloc[i,0])+str(df.iloc[i,1])+str(df.iloc[i,2])+str(df.iloc[i,3]) for i in range(0,len(df))])
print(df)

### SQL 넣는 부분 ###
db_connection_str = r'mysql+pymysql://계정명:"비번@IP주소 또는 localhost":포트번호/DB명'

db_connection = create_engine(db_connection_str)

#df.to_sql(name='test', con=db_connection, if_exists='append',index=False)  
####################

# 다운받은 엑셀 삭제(크롤링해당)
try:
    df.to_sql(name='test', con=db_connection, if_exists='append',index=False)  
    os.remove(xlsFiles[-1])
except:
    os.remove(xlsFiles[-1])

저는 엑셀 파일을 크롤링했기 때문에 (크롤링 해당) 부분을 주석한 뒤에 사용하시면 되겠습니다.

 

코드 설명 잘되어 있으니 모르는 부분이나 질문 사항 있으면 댓글 남겨주세요~

 

 

--- 추가내용 ---

하다 보면 날짜도 골치 덩어리입니다.

형식이 안 맞기 때문에 에러 나는 경우가 태반이기도 하죠

 

예를 들어서

 이런식으로 있는 날짜 형식이라면

# 날짜 재정의
df['date'] = pd.to_datetime(df.date,format='%Y.%m.%d (%H:%M:%S)')

# 설명
#df['컬럼명'] = pd.to_datetime(df.컬럼명,format='형식에 맞게 설정')

format에 이미지 형식과 맞혀서 써주시면 되겠습니다.

 

참고 format 형식

https://www.dataindependent.com/pandas/pandas-to-datetime/

---

판다스 to_sql 관련 내용은 아니지만 DB에 넣는 insert문을 자동 완성하는 코드를 한번 만들어 봤습니다.

판다스 to_sql의 한번에 넣기 때문에 문제가 많이 발생하는거 같아 하나씩 넣게하기 위해서 만들어 봤습니다.

2021.06.24 - [Project/Python] - python mysql & MariaDB INSERT문 자동 완성 함수

python mysql & MariaDB INSERT문 자동 완성 함수