python excel to mysql

import sys
import xlrd
import pymysql
import math
import json
from collections import OrderedDict


# json 转换成对像
class JSONObject:
    def __init__(self, d):
        self.__dict__ = d


# 异常处理
class MyError(Exception):
    def __init__(self, v):
        self.value = v

    def __str__(self):
        return repr(self.value)


# 读取数据
config = None

with open(‘config.json‘, ‘r‘) as f:
    try:
        config = json.load(f, object_pairs_hook=OrderedDict)
    except IOError as err:
        print("OS error: {0}".format(err))
        sys.stdin.readline()
    finally:
        if sys.exc_info()[0] is not None:
            print("else Unexpected error:", sys.exc_info())
            sys.stdin.readline()
            # raise MyError(‘else Unexpected error‘)

fileList = []
# print(config)
for k in config:
    if k == ‘fileList‘:
        for d in config[k]:
            if config[k][d] == 1:
                fileList.append(d)

# 建立mysql连接
conn = pymysql.connect(
    host=config[‘host‘],
    user=config[‘user‘],
    passwd=config[‘passwd‘],
    db=config[‘db‘],
    port=config[‘port‘],
    charset=config[‘charset‘]
)

# 获得游标
cur = conn.cursor()

for filename in fileList:
    cur.execute(‘delete from ‘ + filename);
    print(filename + ‘ 删除数据!‘)
    book = xlrd.open_workbook(‘excel/‘ + filename + ‘.xlsx‘)
    sheet = book.sheets()[0]
    ops = []
    nCols = sheet.ncols  # 获取列表的有效列数
    colName = ‘insert into ‘ + filename
    first = 0
    flag = ‘,‘
    # 表中的数据有单引号，直接拼接处理
    if filename == ‘tbl‘:
        names = []
        for r in range(0, sheet.nrows):
            if first != 0:
                flag = ‘^‘
            values = ‘‘
            itemSql = colName
            for col in range(0, nCols):
                value = sheet.cell(r, col).value
                if isinstance(value, int):
                    values += str(math.floor(value)) + flag
                elif isinstance(value, float):
                    values += str(math.floor(value)) + flag
                else:
                    values += value + flag

            values = values[0:-1]

            # 第0行为字段行
            if first == 0:
                colName += ‘(‘ + values + ‘)‘ + ‘ values (‘
            else:
                for d in values.split(flag):
                    itemSql += ‘"‘ + d + ‘",‘
                itemSql = itemSql[0:-1]
                itemSql += ‘);‘
                # print(itemSql)
                try:
                    cur.execute(itemSql)
                except IOError as err:
                    print("OS error: {0}".format(err))
                    sys.stdin.readline()
                finally:
                    if sys.exc_info()[0] is not None:
                        print("finally Unexpected error:", sys.exc_info())
                        sys.stdin.readline()

            first = 1

        print(filename + ‘ 导入新数据!‘)
    else:
        for r in range(0, sheet.nrows):
            if first != 0:
                flag = ‘&‘
            values = ‘‘
            for col in range(0, nCols):
                value = sheet.cell(r, col).value
                if isinstance(value, int):
                    values += str(math.floor(value)) + flag
                elif isinstance(value, float):
                    values += str(math.floor(value)) + flag
                else:
                    values += value + flag

            values = values[0:-1]

            if first == 0:
                colName += ‘(‘ + values + ‘)‘ + ‘ values (‘
                for j in range(0, nCols):
                    if j == nCols - 1:
                        colName += ‘%s)‘
                    else:
                        colName += ‘%s, ‘
            else:
                ops.append(values.split(flag))

            first = 1

        # print(colName)
        # print(ops)
        try:
            cur.executemany(colName, ops)
            print(filename + ‘ 导入新数据!‘)
        except IOError as err:
            print("OS error: {0}".format(err))
            sys.stdin.readline()
        finally:
            if sys.exc_info()[0] is not None:
                print("finally Unexpected error:", sys.exc_info())
                sys.stdin.readline()

cur.close()
conn.commit()
conn.close()

print(‘导入完成, 按任意键关闭...‘)
sys.stdin.readline()