テスト1

import openpyxl
from datetime import datetime as dt

'''
エクセルのシート名にテーブル名を設定
ヘッダ列にカラム名を設定
ファイル名は任意
'''


def calc(ex):
    if isinstance(ex, int):
        return str(ex)
    elif (ex is None) or (ex == ''):
        return "''"
    elif ex in func_str_list:
        return str(ex)
    else:
        return "'" + str(ex) + "'"


func_str_list = ['CURRENT_TIMESTAMP', 'NULL']
# TODO 適宜修正
file_path = 'input_xlsx_path'
output_path = 'output_sql_path'
wb = openpyxl.load_workbook(filename=file_path)
tablename = wb.sheetnames
sheet = wb[wb.sheetnames[0]]
sql_str1 = 'INSERT INTO '
with open(output_path, 'w') as f:
    for i, row in enumerate(sheet):
        if i == 0:
            values = [cell.value for cell in row]
            col_list = ', '.join(values)
        else:
            values = [calc(cell.value) for cell in row]
            val_list = ', '.join(values)
            sql = 'INSERT INTO ' + tablename[0] + ' ( ' + col_list + ' ) VALUES ( ' + val_list + ' );'
            f.write(sql)
            f.write('\n')
            print(sql)
    tdatetime = dt.now()
    tstr = tdatetime.strftime('%Y-%m-%d %H:%M:%S')
    f.write('-- 出力日時：' + tstr + '\n')

wb.close()