テーブルごとに 読み込みファイル階層に 『/data/(テーブル名).tsv』ファイルを作成 + sqlのLOAD DATA構文作成。

create_data_postgre2mysql.py

import os


start_table_str = "COPY public."
table_name = "user" # mysqlのテーブル名(docker-composeより)
docker_dir = os.path.join("my_data", "data")# docker(mysqlコンテナ)は階層が違うので追加

def isStartTable(line):
    start_table_len = len(start_table_str)
    return len(line) > start_table_len and line[0:start_table_len] == start_table_str
def isFinishTable(line):
    return line.strip() == "\."
def getTableName(line):
    st_i = len(start_table_str)
    ed_i = line.find("(")
    return line[st_i:ed_i].strip()
def getTableColumns(line):
    st_i = line.find("(")
    ed_i = line.find(")")
    return line[st_i+1: ed_i].strip()

def list2tsvAndN(li):
    return "\t".join(li) + "\n"


def changeTableValue(line):
    # mysqlでは t/fは 1/0より
    def changeTFValue(c):
        if c == "t":
            return 1
        elif c == "f":
            return 0
        else:
            return c
    # NULLじゃないとダメ？（\Nでいいらしい）
    def changeNullValue(c):
        if c == "\\N":
            return c
            #return "NULL"
        else:
            return c

    before_str = line.split("\t")
    if len(before_str) < 2:
        return line

    changed_str = []
    for c in before_str:
        c = c.strip()# 改行、空白を削除
        c = changeTFValue(c)
        c = changeNullValue(c)

        changed_str.append(str(c))

    return list2tsvAndN(changed_str)


def saveFile(path, file_name, write_line):
    dirname = os.path.dirname(path)
    new_dir = os.path.join(dirname, 'data')
    os.makedirs(new_dir, exist_ok=True)

    path_file_name = os.path.join(new_dir, f'{file_name}.tsv')
    with open(path_file_name, mode="w") as f:
        f.writelines(write_line)

    #return path_file_name



def main():
    while True:
        print("ファイル相対パスを入力↓")
        path = input()
        if os.path.isfile(path):
            break
            
        print("パスが存在しません\n")


    dump_line = []# mysqlimportができないので代用
    ## dataをtableごとに分けて保存
    with open(path) as f:
        in_table = False
        write_line = []
        file_name = ""

        for s_line in f:
            if isFinishTable(s_line):
                saveFile(path, file_name, write_line)
                in_table = False
                write_line = []
                # localがないと、ERROR 1290 (HY000) at line 1: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
                # headerを無視するので ignoreを入れる
                dump_line.append(f"LOAD DATA LOCAL INFILE '{docker_dir}/{file_name}.tsv' INTO TABLE {table_name}.{file_name} FIELDS TERMINATED BY '\\t' IGNORE 1 LINES;\n")

            elif in_table:
                write_line.append(changeTableValue(s_line))

            elif isStartTable(s_line):
                in_table = True
                file_name = getTableName(s_line)
                # headerを挿入
                write_line.append(
                    list2tsvAndN(
                        getTableColumns(s_line).split(", ")
                    )
                )

    # LOAD DATAのsql命令を保存する
    #with open(os.path.join(os.path.dirname(path), "load_data_dump.sql"), mode="w") as f:
    #    f.writelines(dump_line)








    

if __name__ == "__main__":
    main()