[置顶] python 多excel导入到mysql中去

场景：文件夹下有N多Excel,每个excel中有1个sheet，sheet页中首行为表格的列头名

实现：将以上所有表格导入到MySQL中去，以文件名命名为表名，以列头名命名为字段名

细节：将所有字段处理成字符串，其中半角"处理成了全角”    等

不足：……

#!/usr/bin/python# -*- coding: UTF-8 -*-import xlrdimport stringimport osimport MySQLdbimport sys reload(sys) sys.setdefaultencoding("utf-8")def IsSubString(SubStrList,Str):      '''''     #判断字符串Str是否包含序列SubStrList中的每一个子字符串     #>>>SubStrList=['F','EMS','txt']     #>>>Str='F06925EMS91.txt'     #>>>IsSubString(SubStrList,Str)#return True (or False)     '''      flag=True      for substr in SubStrList:          if not(substr in Str):              flag=False      return flag  def fn_get_filelist(FindPath,FlagStr=[]):      '''''     #获取目录中指定的文件名     #>>>FlagStr=['F','EMS','txt'] #要求文件名称中包含这些字符     #>>>FileList=GetFileList(FindPath,FlagStr) #     '''      import os      FileList=[]      FileNames=os.listdir(FindPath)      if (len(FileNames)>0):         for fn in FileNames:             if (len(FlagStr)>0):                 #返回指定类型的文件名                 if (IsSubString(FlagStr,fn)):                     fullfilename=os.path.join(FindPath,fn)                     FileList.append(fullfilename)             else:                 #默认直接返回所有文件名                 fullfilename=os.path.join(FindPath,fn)                 FileList.append(fullfilename)      #对文件名排序      if (len(FileList)>0):          FileList.sort()      for i in range(len(FileList)):        PRint FileList[i]    return FileList def fn_create(f):    book = xlrd.open_workbook(f)    sheet = book.sheet_by_index(0)    rows = sheet.nrows    cols = sheet.ncols    c = ""    for ic in range(cols):        arr_v = sheet.col_values(ic)        h = fn_arr_maxLen(arr_v)        c = c + '`' + sheet.cell(0,ic).value + '` varchar(' + str(h) + ') null  comment /"' +  sheet.cell(0,ic).value + "/"," + "/n/r"    t = unicode(f.split("/")[-1].split(".")[0],"utf-8")# f.replace('/tmp/excel/','')    t1 = ' create table if not exists `suyang_'    t2 = ' id int AUTO_INCREMENT ,primary key (id)) ENGINE=INNODB   DEFAULT CHARSET=utf8;'    t = t1 + t + '` (' + c + t2    #print t    return tdef fn_insert(f):    book = xlrd.open_workbook(f)    sheet = book.sheet_by_index(0)    rows = sheet.nrows    cols = sheet.ncols    c = ''    for ic in range(cols):        c = c + '`' + sheet.cell(0,ic).value + '`,'     t = f.split("/")[-1].split(".")[0]    # f.replace('/tmp/excel/','')    print t    insertSql = ' insert into  `suyang_' + t + '` (' + c.rstrip(',') + ' ) values ( '    for ir in range(1,rows):        row_values = ''        insertSql = ' insert into  `suyang_' + t + '` (' + c.rstrip(',') + ' ) values ( '        for icc in range(cols):            if isinstance(sheet.cell(ir,icc).value,int) or isinstance(sheet.cell(ir,icc).value,float):                vs = repr(sheet.cell(ir,icc).value).split(".")[0]            else:                vs = str(sheet.cell(ir,icc).value)            insertSql = insertSql + row_values + '"' + str(vs).replace('"','”') + '",'        insertSql = insertSql.rstrip(',')+');' #sheet.cell(ir,icc)        yield insertSql       #print tdef fn_arr_maxLen(arr):    o = 0        for v in arr:        if (o < len(str(v).strip())):            o = len(str(v).strip())    return o        if __name__ == '__main__':     '''''cs=fn_create('/tmp/excel/注销信息.xlsx')    print cs'''    mycn = MySQLdb.connect("10.18.141.52","dba","xxxxxxxxxxxxxxxx","ys" )    mycn.set_character_set('utf8')    mycursor = mycn.cursor()    mycursor.execute('SET CHARACTER SET utf8;')    mycursor.execute('SET NAMES utf8;')    mycursor.execute('SET character_set_connection=utf8;')    fs=fn_get_filelist("/tmp/excel",['xlsx'])    for ii in range(len(fs)):        tcreateSql = fn_create(fs[ii])        print tcreateSql        try:            mycursor.execute(tcreateSql)            for isql in fn_insert(fs[ii]):                #print isql                mycursor.execute(isql)            mycn.commit()        except Exception , e:            print e    mycursor.close()    mycn.close()