MYSQL에 CSV file 삽입하기: DATA IMPORT WIZARD 등

TABLE DATA IMPORT WIZARD 

스키마 선택 및 위자드 실행

MySQL의 Workbench는 CSV 파일 import를 지원한다. 아래 그림 1 위쪽처럼 "스키마 이름" 부분을 우클릭해서 Table Data Import Wizard를 실행하면 그림 1 아래쪽처럼 파일 선택 화면이 뜬다. 

그림1: Table Data Import Wizard 실행

다음 단계에서 아래 그림2 왼쪽 그림처럼 현재 존재하는 테이블에 데이터를 넣을 것인지 신규 테이블을 만들 것인지 선택한다. 마지막 단계에서 아래 그림 2 오른쪽 그림처럼 encoding 선택하고 next를 누르면 import 가 완료된다.

그림2: Table Data Import Wizard 설정

제약사항

현재 MySQL Table Data Import Wizard에서는 encoding을 utf-8, utf-16, cp1250, latin1, latin2 만 제공한다. 그래서 UTF-8이 아닌 방식으로 저장된 데이터는 ( euc-kr나 cp949 등으로 인코딩 된 경우) import 시 에러가 발생한다. 한글로 된 많은 파일들이 cp949, euc-kr 등으로 인코딩 되어 있어서 한글로 된 많은 csv 파일들은 Table Data Import Wizard를 쓸 수 없다.

제어판에서 컴퓨터 설정을 바꾸어서 csv file import 하는 방법도 있지만, 컴퓨터 설정 전체에 영향을 미치기 때문에 애매하다. 그래서 python을 이용해서 한글이 있는 csv 파일을 SQL import 하는 식의 접근이 낫다. 

PYTHON을 이용하는 방법

pandas, sqlalchemy, pymysql 라이브러리 설치

pandas 라이브러리는 데이터프레임 객체를 만들어주는 라이브러리이다. csv 파일도 읽어서 데이터프레임 형태로 만들고 이것을 MySQL DB에 넣어야 하기 때문에 필요하다. 데이터프레임에 대한 구체적인 설명은 01 파이썬 데이터 형식(&Pandas 데이터)을 참고하면 된다. 판다스는 보통 아나콘다-파이썬 설치하면 기본 라이브러리로 제공해주는데, 혹시나 최신 버전이 필요하거나 없다면, cmd 창에서 아래 명령어를 실행하면 된다. 

# 판다스 설치하기 
pip install pandas

sqlalchemy와 pymysql은 python에서 MySQL 서버에 접속할 수 있도록 도와주는 python 라이브러리이다. 라이브러리 개념 및 사용 원리에 대한 설명은 03 데이터 : Python 웹스크레핑 데이터 MySQL 서버 저장하기를 참고하면 된다. 혹시 라이브러리가 설치가 안되어 있다면 cmd 창에서 아래와 같은 명령어를 입력해 주면 된다. 

# sqlalchemy 설치하기 
pip install sqlalchemy
# pymysql 설치하기 
pip install pymysql

MySQL DB에 csv 데이터 import 하기 

MySQL DB에 csv 데이터를 import 하는 것은 세 단계의 과정을 거친다. 먼저, pandas 라이브러리의 csv_read 함수를 이용해서 CSV 파일을 읽고 그것을 data라는 변수에 저장한다. 다음으로 create_engine, connect 함수를 이용해서 MySQL에 연결한다. 마지막으로 data를 "to_sql"로 SQL 서버로 보내면 끝난다. 

이 과정을 코드로 짜면 아래 파이썬 코드가 나온다. 내 컴퓨터 환경에 맞게 변수만 잘 맞춰준다면 잘 작동할 것이다.

# pandas 라이브러리
import pandas as pd
# Sql 관련 라이브러리
import sqlalchemy as db
import pymysql
# ModuleNotFo  undError: No module named 'MySQLdb' 코드실행 에러문제 해결
pymysql.install_as_MySQLdb()


###### CSV 파일 읽어서 data 변수에 저장
data = pd.read_csv('내 파일의 경로')

###### MySQL DB에 data 입력하기
user = 'MySQL에 설정한 서버명'
passwd = '서버 비밀번호'
host = '127.0.0.1'
db_port = '포트번호'
db_name = '접속할 스키마 이름'

# DB 서버정보 url 화
db_connection_str = "mysql+mysqldb://" + user + ":" + passwd + "@" + host + ":" + db_port +"/"+ db_name
db_connection = db.create_engine(db_connection_str,encoding='euc-kr')
# DB 접속 connect
conn = db_connection.connect()
# 데이터를 sql 로 보내기
data.to_sql(name='새로만들 테이블 명', con=conn, if_exists='replace', index=False)
# if_exists 'fail': 테이블 명이 이미 존재하면 실행 안 함, 
#           'append': 테이블 있으면 데이터 추가 
#           'replace': 테이블 있으면 교체