python excel to mysql

概述import sysimport xlrdimport pymysqlimport mathimport jsonfrom collections import OrderedDict# json 转换成对像class JSONObject: def __init__(self, d): self.__dict__ = d @H_419_6@

import sysimport xlrdimport pyMysqLimport mathimport Jsonfrom 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 = Nonewith 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,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()

总结

以上是内存溢出为你收集整理的python excel to mysql全部内容，希望文章能够帮你解决python excel to mysql所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。