MySQL用の Excel から INSERTーSQL文を作る

Oralce用、PostgreSQL用を書いたので、MySQL用を用意しました。

mkInsertMySQL.py

# -*- coding: utf-8 -*-
# TRUNCATE して　INSERT する SQLも生成
import datetime
import openpyxl
import sys
import os

# MySQL用　INSERT 作成
class MakeInsertSQL(object):
    dateformatlist = [
        'mm-dd-yy',
        'm/d;@',
        '[$-F800]dddd\\,\\ mmmm\\ dd\\,\\ yyyy',
        'yyyy\\-mm\\-dd;@',
        'yyyy"年"m"月"d"日";@',
        'm"月"d"日";@',
        'yyyy/m/d;@',
        'm/d;@',
        'yyyy/mm/dd',
        'yyyy\\-mm\\-dd',
        'yyyy\\-m\\-d'
    ]
    def __init__(self, file):
        self.book = openpyxl.load_workbook(file)
        self.dirname = os.path.dirname(file)
        self.allsqlpath = os.path.splitext(file)[0] + ".sql"
    def exec(self):
        with open(self.allsqlpath, 'w', encoding='utf-8') as af:
            for name in self.book.sheetnames:
                af.write("TRUNCATE TABLE %s\n;\n" % name)
                self.create(af,name)
            af.write("COMMIT\n;\n")
        print("created : %s" % self.allsqlpath)
    def create(self, afp, sheetName):
        sqlfile = "%s/%s.sql" % (self.dirname, sheetName)
        with open(sqlfile, 'w', encoding='utf-8') as f:
            sheet = self.book[sheetName]
            row = list(sheet)
            columns = ", ".join(list(map(lambda x: x.value, row[0])))
            f.write("INSERT INTO %s (%s) VALUES\n" % (sheetName, columns))
            afp.write("INSERT INTO %s (%s) VALUES\n" % (sheetName, columns))
            vlist = []
            for r in row[1:]:
                vary = []
                for cell in r:
                    cellvalue = cell.value
                    if type(cellvalue) is str:
                        vary.append(f"'{cellvalue}'")
                    elif type(cellvalue) is int:
                        vary.append(f"{cellvalue}")
                    elif type(cellvalue) is float:
                        vary.append(f"{cellvalue}")
                    elif type(cellvalue) is datetime.datetime:
                        if cell.number_format in self.dateformatlist:
                            vary.append(f"STR_TO_DATE('{cellvalue.strftime('%Y-%m-%d')}', '%Y-%m-%d)")
                        else:
                            vary.append(f"STR_TO_DATE('{cellvalue.strftime('%Y-%m-%d %H:%M:%S')}', '%Y-%m-%d %H:%i:%s')")
                    elif cellvalue == None:
                        vary.append("NULL")
                v = ", ".join(vary)
                vlist.append(f"({v})")
            f.write(",\n".join(vlist))
            afp.write(",\n".join(vlist))
            f.write("\n;\n")
            afp.write("\n;\n")
        print("created : %s" % sqlfile)
if __name__ == '__main__':
    args = sys.argv
    if len(args) == 2:
        if os.path.isfile(args[1]):
            maker = MakeInsertSQL(args[1])
            maker.exec()
        else:
            print("Not Found %s" % args[1])
    else:
        print("Usage: mkInsertMySQL.py ExcelfilePath")
    os.system("PAUSE")