csv 를 읽어 mysql create table 문장 생성

# Ubuntu 에서 특정 디렉토리에 있는 CSV 들을 읽어서 각 csv 에 매칭되는 create table sql 생성한다. 

 

import csv
import os
import getpass

# 특정 디렉토리 경로
username = getpass.getuser()
directory = os.path.join("/home", username, "mycsvdirectory/")

# 디렉토리 내의 모든 csv 파일 목록 가져오기
filenames = os.listdir(directory)
csv_files = [f for f in filenames if f.endswith(".csv")]

with open("CREATETABLE.sql", "w") as f:
    f.truncate()

# 각 csv 파일을 읽어 CREATE TABLE 문 생성 및 저장
for csv_file in csv_files:
    print("Filename :" + csv_file)
    with open(os.path.join(directory, csv_file), "r") as f:
        reader = csv.reader(f)

        # 첫 번째 행 (헤더)을 읽어 컬럼 목록으로 변환
        headers = next(reader)

        # 컬럼명에서 '-'를 '_'로 변경
        headers = [col.replace("-", "_") for col in headers]
        print(headers)

        # 첫 번째 컬럼을 제외한 나머지 컬럼 목록 생성
        remaining_headers = headers[0:]
        print(remaining_headers)

        # 각 컬럼의 최대 길이 저장
        column_max_lengths = {}
        for col in remaining_headers:
            column_max_lengths[col] = 0

        # 데이터 읽어서 각 컬럼 최대 길이 계산
        for row in reader:
            for i, col in enumerate(row):
                # print(row)
                if len(col) > column_max_lengths[remaining_headers[i]]:
                    column_max_lengths[remaining_headers[i]] = len(col)
        print(column_max_lengths);

        # DROP TABLE IF EXISTS 문 생성
        drop_table_sql = f"DROP TABLE IF EXISTS MYTABLE_{csv_file[:-4].upper()}_TBL;\n"

        # CREATE TABLE 문 생성
        create_table_sql = f"""
            CREATE TABLE IF NOT EXISTS MYTABLE_{csv_file[:-4].upper()}_TBL (
                {", ".join([f"{col} VARCHAR({column_max_lengths[col]})" for col in remaining_headers])}
            );

            CREATE INDEX idx_{csv_file[:-4].upper()}_TBL ON MYTABLE_{csv_file[:-4].upper()}_TBL ({headers[0]});
        """

        # SQL 문을 1.0.result.CREATETABLE.sql 파일에 저장
        with open("CREATETABLE.sql", "a") as f:
            f.write(drop_table_sql)
            f.write(create_table_sql + "\n")