엑셀 SQL 구문 추출

Insert

import sys
from openpyxl import load_workbook

reload(sys)
sys.setdefaultencoding('utf-8')

file_name = sys.argv[1]
table_name = sys.argv[2]

wb = load_workbook(file_name)
ws = wb.active

def row_name_value(row_num):
    row_values = []
    for col in ws.columns:
        row_values.append((col[0].value, col[row_num].value))
    return row_values

def makeSql(table_name, name_value_list):
    keymap = set(["now()", "null"])
    names = ''
    values = ''

    for (name, value) in name_value_list:
        if not value:
            value = "0"
        names = names + '{0},'.format(name)
        if value not in keymap:
            values = values + "'{0}',".format(value)
        else:
            values = values + "{0},".format(value)
    if names[-1] == ',':
        names = names[:-1]
    if values[-1] == ',':
        values = values[:-1]
    return "insert into {0} ({1}) values({2});".format(table_name, names, values)

with open('{0}.sql'.format(file_name), 'w') as fw:
    for idx in range(1, ws.max_row):
        sql = makeSql(table_name, row_name_value(idx)) + '\n'
        fw.write(sql)

Update

import sys
from openpyxl import load_workbook

reload(sys)
sys.setdefaultencoding('utf-8')

file_name = sys.argv[1] # test.xlsx
table_name = sys.argv[2] # schema.table

wb = load_workbook(file_name)
ws = wb.active

def row_name_value(row_num):
    row_values = []
    for col in ws.columns:
        row_values.append((col[0].value, col[row_num].value))
    return row_values

def makeSql(table_name, name_value_list):
    keymap = set(["now()", "null"])
    update_datas = ''
    where_clause = "{0} = '{1}'".format(name_value_list[0][0], name_value_list[0][1])

    for (name, value) in name_value_list[1:]:
        # print value, name
        if not value or "*" not in name:
            continue
        name = name.replace("*", "")
        if value not in keymap:
            update_datas = update_datas + "{0} = '{1}',".format(name, value)
        else:
            update_datas = update_datas + "{0} = {1},".format(name, value)
    if update_datas[-1] == ',':
        update_datas = update_datas[:-1]
    return "update {0} set {1} where {2};".format(table_name, update_datas, where_clause)

with open('{0}.sql'.format(file_name), 'w') as fw:
    for idx in range(1, ws.max_row):
        sql = makeSql(table_name, row_name_value(idx)) + '\n'
        fw.write(sql)