[Python] Python과 CSV 파일을 활용한 MySQL INSERT 쿼리 생성 방법

 

 

사용 라이브러리 
- csv
- datetime

 

지난 글에 이어서 번역한 메시지 데이터를 테이블에 삽입해야 하는 일이 생겼다. 하나씩 복사해서 insert문을 작성하기엔 너무 많다는 생각이 들어서 한 번에 쿼리문을 생성할 수 있는 방법에 대해 고민했다. 

 

Python을 사용하여 CSV 파일의 데이터를 MySQL 데이터베이스에 삽입할 수 있도록 csv 모듈을 이용해 CSV 파일을 읽고, 각 행의 데이터를 기반으로 MySQL INSERT 쿼리문을 생성하는 프로그램을 만들어봤다.

 

en.csv 파일 (CSV 파일은 다음과 같은 구조를 가정한다)

• 첫 번째 열: 한국어 메시지 (MSG)
• 두 번째 열: 메시지 코드 (MSG_CD)
• 세 번째 열: 영어로 번역된 메시지 (MSG_EN)
• 나머지 열은 무시된다.
  
  

01. Python 코드

다음은 en.csv 파일을 읽어서 각 행의 데이터를 사용하여 MySQL INSERT 쿼리문을 생성하는 Python 코드이다.

 

import csv

# en.csv 파일 경로
csv_file = 'en.csv'

# 생성할 INSERT 쿼리문을 저장할 리스트
query_list = []

# en.csv 파일 읽기
with open(csv_file, 'r', newline='', encoding='utf-8') as f:
    reader = csv.reader(f)
    header = next(reader)  # 첫 번째 줄은 헤더로 처리

    # 각 행마다 INSERT 쿼리문 생성
    for row in reader:
        LANG_CD = 'ko'  # 언어 코드 (예: 한국어)
        MSG_CD = row[1]  # MSG_CD 열의 데이터
        MSG = row[0]     # MSG 열의 데이터
        MSG_EN = row[2]  # MSG_EN 열의 데이터
        CRT_DT = '20240627143000'  # 생성 일시 (YYYYMMDDHHMMSS 형식)
        UPDT_DT = '20240627143000'  # 업데이트 일시 (YYYYMMDDHHMMSS 형식)

        # INSERT 쿼리문 생성하여 리스트에 추가
        query = f"INSERT INTO cm_message (LANG_CD, MSG_CD, MSG, MSG_EN, CRT_DT, UPDT_DT) VALUES ('{LANG_CD}', '{MSG_CD}', '{MSG}', '{MSG_EN}', '{CRT_DT}', '{UPDT_DT}');"
        query_list.append(query)

# 생성된 쿼리문 출력 또는 파일에 저장
for query in query_list:
    print(query)

1. en.csv 파일을 읽는다. 이 파일은 한국어 메시지, 메시지 코드, 영어 메시지를 포함한다.

2. csv.reader를 사용하여 CSV 파일을 읽고, 첫 번째 줄을 헤더로 처리한다.

3. 각 행마다 데이터를 추출하여 INSERT INTO ... VALUES ... 형식의 SQL 쿼리문을 생성한다.

4. 생성된 쿼리문을 query_list에 추가한다.

5. 최종적으로 생성된 쿼리문을 출력하거나 파일에 저장한다.

 

02. 주요 포인트

• CSV 파일을 읽을 때, encoding='utf-8' 옵션을 사용하여 UTF-8 인코딩을 지정한다.
• 생성 일시 (CRT_DT)와 업데이트 일시 (UPDT_DT)는 우선 고정된 값을 사용하였다. 실제로는 현재 시간을 사용하거나 다른 로직을 적용할 수 있다.
• 쿼리문을 데이터베이스에 실제로 삽입하기 전에, 생성된 쿼리문이 올바른지 확인하는 것이 중요하다.
  
  

03. 코드 개선하기

하드코딩을 줄이고 보다 유연하게 데이터를 추출할 수 있는 방법을 적용해 보자. 각 열 이름에 대응되는 데이터를 추출할 수 있도록 하였다. 고정된 일시를 사용하지 않고 datetime으로 현재 시간을 가져오도록 수정하였다. 

import csv
from datetime import datetime

# en.csv 파일 경로
csv_file = 'en.csv'

# 생성할 INSERT 쿼리문을 저장할 리스트
query_list = []

# 현재 시간을 YYYYMMDDHHMMSS 형식으로 가져오기
current_time = datetime.now().strftime('%Y%m%d%H%M%S')

# en.csv 파일 읽기
with open(csv_file, 'r', newline='', encoding='utf-8') as f:
    reader = csv.DictReader(f)

    # 각 행마다 INSERT 쿼리문 생성
    for row in reader:
        # 각 열 이름에 대응되는 데이터 추출
        LANG_CD = row['LANG_CD']
        MSG_CD = row['MSG_CD']
        MSG = row['MSG']
        MSG_EN = row['MSG_EN']
        CRT_DT = current_time  # 생성 일시
        UPDT_DT = current_time  # 업데이트 일시

        # INSERT 쿼리문 생성하여 리스트에 추가
        query = (
				    f"('{LANG_CD}', "
				    f"'{MSG_CD}', "
				    f"'{MSG.replace('\\'', '\\\\\\'')}', "
				    f"'{MSG_EN.replace('\\'', '\\\\\\'')}', "
				    f"'{CRT_DT}', "
				    f"'{UPDT_DT}')"
				)
        query_list.append(query)

# 쿼리문 리스트를 이용해 최종 INSERT 쿼리문 생성
insert_query = f"INSERT INTO cm_message (LANG_CD, MSG_CD, MSG, MSG_EN, CRT_DT, UPDT_DT) VALUES\\\\n"
insert_query += ",\\\\n".join(query_list) + ";"

# 생성된 쿼리문 출력
print(insert_query)

 

05. 개선된 점

• csv.DictReader()로 각 행을 사전(dictionary) 형태로 읽어 열 이름을 직접 지정해 데이터에 접근할 수 있도록 했다.
• 현재 시간을 변수에 저장하고, 이를 이용하여 생성 일시와 업데이트 일시를 할당했다.
• 각 데이터 추출 부분에서 열 이름을 직접 사용하지 않고, 사전 형태로 데이터를 읽어 하드코딩을 줄일 수 있도록 변경했다.