[Python] csv, text 파일 mysql 업로드 방법

 

아래와 같은 데이터를 가진 csv 파일이 있을때, mysql db에 빨리, 쉽게 넣는 방법에 대해서 알아보죠.

우선 데이터베이스에 테이블 fw4를 만들고, 거기에 항목들을 넣습니다.

CREATE TABLE `fw4` (
	`list_num` INT(10) NOT NULL AUTO_INCREMENT,
	`kst` DATETIME NULL DEFAULT NULL,
	`event` VARCHAR(25) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`src_ip` VARCHAR(25) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`src_port` VARCHAR(25) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`dst_ip` VARCHAR(25) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`dst_port` VARCHAR(25) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	PRIMARY KEY (`list_num`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;

 

방식 1). HeidiSQL 같은 sql 접속 프로그램에서 지원하는 기능 사용

HeidiSQL 같은 sql 접속 프로그램에서 지원하는 "메뉴>도구>문서 파일 가져오기" 메뉴를 사용하여 csv 파일을 DB에 넣는 방법도 있겠지만, csv 파일이 엄청난 크기일때는 프로그램이 멈추고 오류도 납니다.

 

방식 2). 파이썬 스크립트를 이용하여 csv 파일 mysql db에 넣기

아래 코드는 파이썬 스크립트를 이용하여 csv 파일을 DB에 넣는 방법으로,

크게 어렵지 않는 스크립트입니다. 전체 코드는 아래와 같습니다. ^^

• DB 연결 정보를 수정하세요
• 메인함수에서 DB 테이블명을 수정하세요
• 메인함수에서 입력 csv 파일명을 수정하세요

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import os
import sys
import pymysql
import datetime
import time

#--------------------------
# csv2db_loadfile(csv 파일명, DB 테이블명)
#--------------------------
def csv2db_loadfile(input_filename, table_name):
    start_time1 = time.time()  # 시작 시간 저장
    
    # DB 연결 정보
    conn = pymysql.connect(host='localhost', user='root', password='패스워드', port=3306, db='my', local_infile=True)
    
    print(input_filename)
    try:
        # Get Cursor
        curs = conn.cursor()

        # Sql query
        # 
        sql_query="LOAD DATA LOW_PRIORITY LOCAL INFILE '{0}' INTO TABLE {1} FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\"' LINES TERMINATED BY '\r\n' (kst, event, src_ip, src_port, dst_ip, dst_port)".format(input_filename, table_name)
        
        curs.execute(sql_query)
        conn.commit()
        
    except Exception as e:
        # 에러시 메세지 표시
        print('Error: {}'.format(str(e)))
        sys.exit(1)
    finally:
        conn.close()

    #걸린시간 출력 = 현재시간 - 시작시간
    print("time :{0}".format(time.time() - start_time1)) 


#--------------------------
# Main
#--------------------------
if __name__ == "__main__":

    print("Start Program")
    
    # csv, text 파일명
    file_path="D:/work/fw_parsing.txt"
    
    # 테이블 명
    table_name='my.fw3'
    csv2db_loadfile(file_path, table_name)
    print("[complete] - {0}".format(file_path))
    
    print("End Program")