# -*- coding=utf-8 -*-
# coding=utf-8
import xlrd
import os


def convert_type(data_type):
    """Normalize MySQL `data_type`"""
    if 'CHAR' == data_type:
        return 'varchar'
    elif 'CLNT' == data_type:
        return 'varchar'
    elif 'NUMC' == data_type:
        return 'numeric'
    elif 'DATS' == data_type:
        return 'timestamp'
    elif 'QUAN' == data_type:
        return 'varchar'
    elif 'CUKY' == data_type:
        return 'varchar'
    elif 'CURR' == data_type:
        return 'varchar'
    elif 'DEC' == data_type:
        return 'varchar'
    elif 'INT4' == data_type:
        return 'varchar'
    elif 'TIMS' == data_type:
        return 'varchar'
    elif 'string' == data_type:
        return 'varchar'

    else:
        return data_type


# 在mysql中创建表
def mysql_create(fields):
    stg_table_name = 'db.' + fields[0]['table_name']
    columns = []
    primary_key = []
    for field in fields:
        if field['primary_key'] == 'Y':
            primary_key.append(field['column_name'])
        table_column = field['column_name'] + '          ' + field['type'] + '          ' + 'COMMENT ' + "'" + field[
            'column_exp'] + "'" + ',\n'
        # print(table_column)
        columns.append(table_column)
    # print(columns)
    stg_create_columns = ''.join(
        columns)[:-2]
    # print(stg_create_columns)
    create_stg_sql = "drop table if exists {};\ncreate table {} (\n{});".format(
        stg_table_name, stg_table_name, stg_create_columns)
    # print(create_stg_sql)
    return create_stg_sql


# print(os.getcwd())
#par_path = os.getcwd()
#paths = [par_path + '\\']
paths = [r'E:/data/']
print('---------------paths-------------------')
print(paths)
for path in paths:
    for filename in os.listdir(path):
        print(filename)
        if filename.endswith(".xlsx") or filename.endswith(".xls"):
            result_sql = ''
            print(path)
            print(filename)
            print(path + filename)
            worksheet = xlrd.open_workbook(path + filename)
            table_names = worksheet.sheet_names()
            for table_name in range(len(table_names)):
                sheet = worksheet.sheet_by_index(table_name)
                nrows = sheet.nrows
                fields = []
                for i in range(1, nrows):
                    res = sheet.row_values(i)
                    desc = {
                        'table_name': table_names[table_name].lower(),
                        'column_exp': res[1],
                        'column_name': res[2].lower(),
                        'type': convert_type(res[3]).upper(),
                        'primary_key': res[4],
                    }
                    #print(desc)
                    fields.append(desc)
                #print(fields)
                result_sql += mysql_create(fields) + '\n\n'
            with open(path + '\\' + filename[:-5] + '.sql', "w", encoding='utf-8') as f:
                f.write(str(result_sql))
                f.close()