Python SQL Script 작성

Python SQL Script 작성

Mysql 1227 Error 의 연장선으로 파일들의 GTID를 일일이 삭제하기 귀찮아서 스크립트를 작성했습니다.

AWS RDS의 Super Privileged를 얻는데 실패했다면 Python 코드를 이용한 SQL Script를 작성합니다.

스크립트에서 Product Dump의 SQL 파일들을 수정하여 Data Import를 할 수 있습니다.

SQL 주석 변환 스크립트를 작성하기 전, 실제 데이터의 Encoding 형식을 확인하는 스크립트를 먼저 실행하고,

아래 스크립트에서 해당 인코딩 방식을 사용하여 디코딩 합니다.

아래 스크립트는 폴더 내부의 .sql을 돌면서 .sql 파일의 인코딩 방식을 리스트에 담고 모아서 출력하는 스크립트 입니다.

터미널에서 pip3 install chardet을 입력해 chardet 모듈을 설치해줍니다.

chardet 모듈의 위치를 파악하기 위해 파이썬 실행기를 열어서 print(chardet.__file__) 을 입력해서 위치를 확인합니다.

위치를 확인하면 site-packages 까지의 경로를 스크립트 변수에 추가합니다.

check_encoding.py

import os
import sys

# chardet 모듈 경로 추가
chardet_path = '/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages'
sys.path.append(chardet_path)

# chardet 모듈 import
import chardet

# 특정 디렉토리 내의 모든 .sql 파일을 찾습니다.
directory = '/Users/space/Prod/Prod_DB_Dump'  # 실제 폴더 경로로 변경해야 합니다.
sql_files = [file for file in os.listdir(directory) if file.endswith(".sql")]

# 인코딩 확인 결과를 저장할 리스트를 생성합니다.
encoding_list = []

# 각 파일을 순회하면서 인코딩을 확인합니다.
for sql_file in sql_files:
    file_path = os.path.join(directory, sql_file)

    # 파일을 바이너리 모드로 읽어들입니다.
    with open(file_path, 'rb') as file:
        raw_data = file.read()

        # 파일의 인코딩을 감지합니다.
        result = chardet.detect(raw_data)
        encoding = result['encoding']
        confidence = result['confidence']

    # 인코딩 정보를 리스트에 추가합니다.
    encoding_list.append((sql_file, encoding, confidence))

# 인코딩 정보를 출력합니다.
for file_name, encoding, confidence in encoding_list:
    print(f"File: {file_name}")
    print(f"Encoding: {encoding} (confidence: {confidence})")
    print()

그럼 .sql 파일들의 인코딩 방식이 리스트로 출력이 됩니다.

---

이제 추가로 작성할 스크립트는 해당 라인들을 .sql 파일들을 돌며 주석처리를 합니다.

• SET @@SESSION.SQL_LOG_BIN=0;
• SET @@GLOBAL.GTID_PURGE=/!80000 '+'/
• SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP

아래 코드에서

commented_lines에 GLOBAL.GTID 부분의 <hash-code>부분은 직접 .sql 파일에 적혀있는 hash 로 지정해야 합니다.

그리고, with open() 함수에 encoding 값을 정해줬으면 아래 open()함수에도 똑같이 붙여줘야 합니다.

sql_script.py

import os

# 주석 처리할 라인들
commented_lines = [
    "SET @@SESSION.SQL_LOG_BIN= 0;",
    "SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '<hash-code>';",
    "SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;"

]

# 특정 디렉토리 내의 모든 .sql 파일을 찾습니다.
directory = '/Users/space/Prod/Prod_DB_Dump'
sql_files = [file for file in os.listdir(directory) if file.endswith(".sql")]


# 각 파일을 순회하면서 주석 처리를 진행합니다.
for sql_file in sql_files:
    file_path = os.path.join(directory, sql_file)


    # 파일을 읽어들입니다.
    with open(file_path, 'r', encoding='utf-8') as file:
        lines = file.readlines()


    # 주석 처리할 라인을 찾아서 주석 처리합니다.
    with open(file_path, 'w', encoding='utf-8') as file:
        for line in lines:
            if line.strip() in commented_lines:
                file.write(f'-- {line}')
            else:
                file.write(line)

.sql 파일 중 아무거나 들어가보면 주석처리가 잘 되있는것을 볼 수 있고, RDS로 DB 데이터 이전도 잘 됩니다.

---

스크립트 병합

위의 두 스크립트를 합쳐놓은 스크립트를 작성하였습니다.

import os
import sys

# chardet 모듈 경로 추가
chardet_path = '/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages'
sys.path.append(chardet_path)

# chardet 모듈 import
import chardet

# 폴더 경로 설정
directory = '/Users/space/Prod/Prod_DB_Dump'  # 실제 폴더 경로로 변경해야 합니다.

# 주석 처리할 라인
commented_lines = [
    "SET @@SESSION.SQL_LOG_BIN= 0;",
    "SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '77432309-a684-11ec-b662-f220afbb1580:1-709257';",
    "SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;"
]

# 특정 디렉토리 내의 모든 .sql 파일을 찾습니다.
sql_files = [file for file in os.listdir(directory) if file.endswith(".sql")]

# 각 파일을 순회하면서 인코딩을 확인하고 주석 처리 및 인코딩을 진행합니다.
for sql_file in sql_files:
    file_path = os.path.join(directory, sql_file)

    # 파일의 인코딩을 확인합니다.
    with open(file_path, 'rb') as file:
        raw_data = file.read()
        result = chardet.detect(raw_data)
        encoding = result['encoding']

    # 파일을 해당 인코딩으로 다시 열어서 주석 처리 및 인코딩을 진행합니다.
    with open(file_path, 'r', encoding=encoding, errors='replace') as file:
        lines = file.readlines()

    with open(file_path, 'w', encoding=encoding, errors='backslashreplace') as file:
        for line in lines:
            if any(commented_line in line for commented_line in commented_lines):
                file.write(f'-- {line}')  # 주석 처리된 라인으로 수정
            else:
                file.write(line)

    print(f"Processed file: {sql_file} (Encoding: {encoding})")

    # 실행 로그를 파일에 기록합니다.
    log_file = "execution_log.txt"  # 실행 로그를 저장할 파일 이름
    with open(log_file, 'a', encoding='utf-8') as log:
        log.write(f"Processed file: {sql_file} (Encoding: {encoding})\n")

print("Script execution completed.")

스크립트 실행 시 파일마다 인코딩 방식을 체크해서 변환이 잘 되는걸 볼 수 있습니다.

이제 RDS의 Super Privileged 문제와 Encoding 문제 전부 해결 완료했습니다.