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()