엑셀로 대량의 쿼리문 생성하기2 (Feat. pandas)

엑셀 파일의 내용을 가공하여 쿼리문과 같이 일정한 형태로 변환하려고 할 때, 엑셀 수식을 이용하는 방법을 소개해드린바 있습니다. (링크)

이번에는 Pandas를 이용해 업로드한 엑셀 파일을 읽어 원하는 결과를 출력해주는 간단한 프로그램을 만들어 보려고 합니다. Pandas는 데이터 분석과 조작을 쉽게 할 수 있도록 도와주는 Python 라이브러리입니다.

 

1.  Colab

 

먼저 Python 실행환경을 갖추어야 하는데, Pycharm같은 IDE를 사용하셔도 되지만 저는 웹에서 간편하게 프로그램을 작성하고 실행할 수 있는 Colab을 써보려고 합니다.

Google Colab(https://colab.research.google.com/)은 Python을 이용해 데이터 분석, 머신러닝, 딥러닝 등의 작업을 쉽게 수행할 수 있도록 하는 클라우드 기반의 Jupyter Notebook 환경입니다. 구글 계정만 있으면 무료로 사용할 수 있고, 노션과 유사한 서비스라고 생각하시면 되겠습니다.

사용 방법은 간단합니다. 새로운 노트를 생성하고 코드를 작성하고 실행 버튼을 누르기만 하면 됩니다.

 

2. 쿼리문 생성

 

아래는 엑셀 파일의 내용을 이용해 UPDATE문을 만들어주는 프로그램입니다.

from google.colab import files
import pandas as pd
uploaded = files.upload()
file_name = list(uploaded.keys())[0]

df = pd.read_excel(file_name)

def generate_query(df, tb_name, key_col, upd_col):
  queries = []
  for _, row in df.iterrows():
		  # SET 절 구성
      if pd.notna(row[upd_col]):
        set_clause = "{} = '{}'".format(upd_col, str(row[upd_col]).replace("'", "''"))
      else :
        set_clause = "{} = NULL".format(upd_col)
        
      # WHERE 절 구성
      if pd.notna(row[key_col]):
        key_value = str(row[key_col]).replace('\\'', '\\'\\'') if pd.notna(row[key_col]) else 'NULL'
        where_clause = f"{key_col} = '{key_value}'"
      else:
        where_clause = "{} IS NULL".format(key_col)
      
      # SQL 쿼리문 생성
      query = f"UPDATE {tb_name} SET {set_clause} WHERE {where_clause};"
      queries.append(query)
  return queries

# LOCALE 테이블에서 CODE를 기준으로 
queries = generate_query(df, "LOCALE", "CODE", "ENGLISH")
print(queries)

먼저 colab에서 제공하는 files 모듈을 통해서 엑셀 파일을 업로드 합니다.

그리고 엑셀의 내용을 데이터프레임 형태로 저장해줍니다. 데이터프레임(DataFrame)은 Pandas 라이브러리에서 제공하는 가장 중요한 데이터 구조 중 하나로, 2차원 표 형태의 데이터입니다. 행과 열로 구성되어 엑셀과 유사한 형태를 가지고 있죠.

generate_query 함수는 앞서 생성한 데이터프레임df과 테이블명, 조건문에 사용할 key_col, Update할 upd_col을 인자로 받아서 쿼리문을 출력해줍니다.

즉,

code	english	korean
Greetings	hello	안녕

이런 형태의 데이터를

UPDATE LOCALE
SET ENGLISH = ‘hello’
WHERE CODE = ‘Greetings’

요런 쿼리문으로 바꿔주는 거죠.